"MyBatis Learning 07" Dynamic SQL

1. Dynamic SQL

　　Dynamic SQL is a core of mybatis, what is dynamic SQL? Dynamic SQL is a flexible operation of SQL statements, which is judged by expressions, and the SQL is flexibly spliced and assembled. Just take a blog post. A comprehensive query to the user, for example:

SELECT * from user where user.sex = #{user.sex} and user.username like '%${user.username}% '

What if this user is NULL? Or is user.sex or User.username null? So the more rigorous approach should be to execute this statement before the first to make a judgement on, to ensure that it is not empty, then I go to query. This involves the dynamic SQL in the MyBatis.
　　In MyBatis, dynamic SQL can be represented by tags, which is very similar to jstl expressions, and we can change the SQL statements above into dynamic SQL, as follows:

<SelectID= "Finduserlist"ParameterType= "Mybatis.po.UserQueryVo"Resulttype= "Mybatis.po.User">SELECT * from user<!--where can automatically remove the first and in a condition -    <where>        <ifTest= "User!=null">            <ifTest= "User.sex!=null and user.sex!=">and user.sex = #{user.sex}</if>            <ifTest= "User.username!=null and user.username!=">and user.username like '%${user.username}% '</if>        </if>    </where></Select>

　The above code is very good understanding, the main is to add a few judgments, conditions are not empty, only to query the conditions of splicing, let MyBatis dynamic to execute. Then in the test code, we can deliberately user.sex do not assign the initial value, you can see the results of the query is not the same.

2. SQL Fragment

　　Now there is a problem, if several statement need to do this, and the dynamic SQL section is the same, which will lead to some code duplication, so if we encounter this situation, we should extract, dynamic SQL can also be extracted, we can be dynamic of this part of SQL extracted into SQL fragments, You can then refer to it in the specific statement. As follows:

<SQLID= "Query_user_where">    <ifTest= "User!=null">        <ifTest= "User.sex!=null and user.sex!=">and user.sex = #{user.sex}</if>        <ifTest= "User.username!=null and user.username!=">and user.username like '%${user.username}% '</if>    </if></SQL>

The ID is the name of the SQL fragment, the inside is the where dynamic section above, and then we change the original dynamic part above to a reference to this SQL fragment, as follows:

<SelectID= "Finduserlist"ParameterType= "Mybatis.po.UserQueryVo"Resulttype= "Mybatis.po.User">SELECT * from user<where>        <!--reference the ID of the SQL fragment, if the ID specified by refID is not in the mapper file, you need to precede the namespace -        <includerefID= "Query_user_where"></include>        <!--You can also reference other SQL fragments -    </where></Select>

3. Foreach

　　One more question: if we're going to pass an array or list to SQL, what's the whole thing? MyBatis is using a foreach parsing. In order to simulate this scenario, we will change the above query to multiple ID queries, there are two ways to query:

SELECT *  from USER WHEREId=1 ORId= A ORId= -SELECT *  from USER WHEREIdinch(1, A, -)

First of all it is very clear that since you want to use multiple IDs to query, then multiple IDs must be passed in as parameters, so the list of storing multiple IDs needs to be put into userqueryvo as a property, which is very well understood, so we first add this property in Userqueryvo:

// Pass in multiple IDs Private list<integer> IDs;

Then we modify the SQL fragment in the Usermapper.xml (or write it in the SQL fragment), as follows:

<SQLID= "Query_user_where">    <ifTest= "User!=null">        <ifTest= "User.sex!=null and user.sex!=">and user.sex = #{user.sex}</if>        <ifTest= "User.username!=null and user.username!=">and user.username like '%${user.username}% '</if>    </if>    <ifTest= "Ids!=null">        <!--use the SQL stitching on the right: and (id=1 or id=12 or id=17) -        <foreachCollection= "IDs"Item= "user_id"Open= "and ("Close=")"Separator= "OR">id=#{user_id}</foreach>    </if></SQL>

Here's a brief look at the effects of the related attributes in this foreach:

Collection: Specifies the collection attribute in the input object, this is the IDs.
Item: Represents each iteration of the generated object, which is used in the foreach body as a name.
Open: Begins the concatenation of SQL strings.
Close: Ends the concatenation of SQL strings.
Separator: The SQL string to be spliced in the two objects traversed.

Let's test it and then look at the printed SQL on the console to be easy to understand. Test procedure:

@Test Public voidTestfinduserlist ()throwsException {sqlsession sqlsession=sqlsessionfactory.opensession (); //Create Usermapper object, MyBatis automatically generate Mapper proxy objectUsermapper usermapper = Sqlsession.getmapper (usermapper.class); //Create wrapper objects, set query criteriaUserqueryvo Userqueryvo =NewUserqueryvo (); User User=NewUser (); //because dynamic SQL is used here, if you do not set a value, the condition will not be stitched together in SQLUser.setsex ("Male"); User.setusername (Rivers); //Pass in multiple IDslist<integer> ids =NewArraylist<integer>(); Ids.add (1); Ids.add (12); Ids.add (17);    Userqueryvo.setids (IDS);      Userqueryvo.setuser (user); //methods for calling Usermapperlist<user> list =usermapper.finduserlist (USERQUERYVO); SYSTEM.OUT.PRINTLN (list);}

Look at the SQL that the console prints out:

SELECT * FROM user WHERE User.sex =? And user.username like '% mountains and Rivers ' and (id=? OR id=? OR id=? )

"Note a detail: in MyBatis, if the input is an integer or int type 0, the above if the label returned is false, that is, even if non-null", will not be stitching the tag in the body of SQL. (thank a_dream1 for giving me this question in the comments) "
　　So MyBatis automatically spliced multiple IDs into SQL. Then another SQL implementation will not repeat, as above, the only difference is the SQL fragment part, as follows:

<!---<collection= "IDs"  item= "USER_ID"  open= "and ID in ("  close= ")"  separator= ","  >    #{user_id}</foreach>

MyBatis Dynamic SQL in the summary so much of it ~

"MyBatis Learning 07" Dynamic SQL