MyBatis Learning 17-Dynamic SQL

1.mybatis Core: The SQL statement flexible operation, through the expression of judgment, the SQL is flexible splicing, assembly.

2. Source of the problem

<select id= "finduserlist" parametertype= "Userqueryvo" resulttype= "Usercustom" >
SELECT * from user where sex= #{usercustom.sex} and username like '%${usercustom.username}% '
</select>

The above query conditions may be empty, so you need to judge the query criteria, and then splicing. Modified to:

<select id= "finduserlist" parametertype= "Userqueryvo" resulttype= "Usercustom" >
SELECT * FROM user
<where>
<if test= "Usercustom!=null" >
<if test= "Usercustom.sex!=null and usercustom.sex!=" >
and user.sex = #{usercustom.sex}
</if>
<if test= "Usercustom.username!=null and usercustom.username!=" >
Username like '%${usercustom.username}% '
</if>
</if>
</where>
</select>

Where can automatically remove the first and in a condition

3.sql pieces

Extract the judgment block of the dynamic SQL above to form a SQL fragment. Then the other statement can refer to this SQL fragment.

Define a SQL fragment to remove the <where></where> from the above boldface characters

<sql id= "Query_user_where" >
<if test= "Usercustom!=null" >
<if test= "Usercustom.sex!=null and usercustom.sex!=" >
and sex = #{usercustom.sex}
</if>
<if test= "Usercustom.username!=null and usercustom.username!=" >
and username like '%${usercustom.username}% '
</if>
</if>
</sql>

Referencing SQL fragments

<select id= "finduserlist" parametertype= "Userqueryvo" resulttype= "Usercustom" >
SELECT * FROM user
<where>
<include refid= "Query_user_where" ></include>
</where>
</select>

4.for-each

Passing arrays or List,mybatis to SQL using foreach parsing

Requirements: Public list<usercustom> finduserlist (Userqueryvo userqueryvo) throws Exception;

A. Adding list<integer> IDs for incoming multiple IDs in the input parameter type Userqueryvo

public class Userqueryvo {

Pass in multiple IDs
Private list<integer> IDs;

。。。

B. Modify Mapper.xml

<sql id= "Query_user_where" >

<if test= "Usercustom!=null" >
<if test= "Usercustom.sex!=null and usercustom.sex!=" >
and sex = #{usercustom.sex}
</if>
<if test= "Usercustom.username!=null and usercustom.username!=" >
and username like '%${usercustom.username}% '
</if>
<if test= "Ids!=null" >
<foreach collection= "IDs" item= "user_id" open= "and (" close= ")" separator= "or" >
ID=#{USER_ID}
</foreach>
</if>
</if>

</sql>

C. Test, Test success

@Test
public void Testfinduserlist () throws Exception {

Sqlsession sqlsession = Sqlsessionfactory.opensession ();
Usermapper usermapper = Sqlsession.getmapper (Usermapper.class);

Userqueryvo Userqueryvo = new Userqueryvo ();
Usercustom usercustom = new Usercustom ();
Usercustom.setsex ("1");
Usercustom.setusername ("Xiao Ming");
Userqueryvo.setusercustom (Usercustom);

list<integer> ids = new arraylist<integer> ();
Ids.add (16);
Ids.add (22);
Ids.add (27);
Userqueryvo.setids (IDS);

list<usercustom> list = Usermapper.finduserlist (USERQUERYVO);
SYSTEM.OUT.PRINTLN (list);
}

MyBatis Learning 17-Dynamic SQL