mybatis in query incoming String mode

mybatis in query incoming String

When using mybaits to query in, pass in String, such as 1, 2, 3, and find that the query result is not what we want

This is because # {} automatically adds double quotation marks "" when compiled, which means it becomes in ("1, 2, 3")

If we want to get the results we want, we can use ${}, and the compiled in (1, 2, 3) is like this

For example, query the total number of multiple ringtones in the ringtone library

<select id="getProgsResourceCount" resultType="java.lang.Long"
 parameterType="com.progandresource.entity.ProgsResourceCond">
 select count(ring_no)
     from progandresmanage_ringinfo where valid_day > now()
 <if test="ringNo != '' and ringNo != null">
     and ring_no in (${ringNo})
 </if>
</select>

If the passed parameter is List or Array, use foreach directly

For example

<select id="getProgsResourceCount" resultType="java.lang.Long" parameterType="java.util.List">
 select count(ring_no)
     from progandresmanage_ringinfo where valid_day > now() and ring_no in 
 <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item, jdbcType=VARCHAR}
        </foreach>
</select>

mybatis in Query Incoming String Parameters

The in operator in sql allows us to specify multiple values to match in the where clause.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

In mybatis, you can specify parameters to the in operator through the foreach tag by passing in arrays or containers (array, list, set, map).

Problem: You want to match the data of the field org_id in OR001, OR002, OR004 from the org table, where org_id is a string type field.

The conventional method is to pass an list object orgIdList containing "OR001", "OR002", "OR004" in mapper. java. In xml:

SELECT * from org where org_id in 
<foreach item="orgId" index="index" collection="orgIdList" open="(" close=")" separator=",">
#{orgId}
</foreach>

If multiple values to be used as matching parameters of in are in an object orgs of type String, and you want to pass them directly through String, there are two ways to implement them.

1. Use the whole String as part 1 of sql with ${orgs} in xml

SELECT * from org where org_id in (${orgs})

This writing method needs to pay attention to whether the whole sql conforms to the syntax after the content of the string orgs is spelled. According to the above requirements and sql writing method, the values of orgs as the part of sql1 are required to be "'OR001', 'OR002' and 'OR004'".

There is a risk of sql injection when parameters are directly used as query statements in the form of sql1 parts, and some items may restrict developers from using this writing method.

2. In the foreach tag of xml, when passing in the collection attribute, the string is converted into an array by split function

SELECT * from org where org_id in 
<foreach item="orgId" index="index" collection="orgs.split(',')" open="(" close=")" separator=",">
#{orgId}
</foreach>

The values of orgs strings passed in here can be "OR001, OR002, OR004". When calling split function, the "," grid can be set, and three strings can be directly segmented as elements of the array.