MyBatis notes-SQL tagging method

SQL is defined in the Mpper.xml mapping file and a variety of tagging methods are provided to enable dynamic splicing of SQL. Each SQL is a statement, and the mapping file is the core of MyBatis. I. Content label 1, nameplace

In the case of DAO development, the nameplace of the mapping file can be arbitrarily named, but if the Mapper interface proxy is developed, the mapper mapping file must be the full name of the interface.

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "http://mybatis.org/dtd/mybatis-3-mapper.dtd "><mapper namespace="mapper.empmapper">//curd Action label

        //if clips </mapper>

2. Crud Label

<?xml version= "1.0"Encoding="UTF-8"? ><! DOCTYPE Mapperpublic "-//mybatis.org//dtd Mapper 3.0//en""HTTP://MYBATIS.ORG/DTD/MYBATIS-3-MAPPER.DTD"><mapper namespace="Mapper.empmapper"><!--query--><select id=""Parametertype=""Resulttype=""></select><!--add--><insert id=""Parametertype=""></insert><!--Delete--><delete id=""Parametertype=""></delete><!--update--><update id=""Parametertype=""></update></mapper>

II. Dynamic SQL label 1, if label

//null string checksum <select id= "finduserlist" parametertype= "user" resulttype= "user" >select * from user  where 1=1<if test= "id!=null and id!=" ">and id=#{id}</if ><if test= "username!=null and username!=" ">and username like '%${username}% ' </ if></select>

2. Where label

//<where/> can automatically handle the firstand <select id= "finduserlist" parametertype= "user" Resulttype= "user" >select * from user<where><if test= "id!=null and id!=" > and id=#{id}</if><if test= "username!=null and username!=" ">and username like '%${username}% ' </if></where></select>

3. SQL Fragment

SQL can extract duplicate SQL, use the include reference, and finally achieve the purpose of SQL reuse, as follows:

//Create a SQL fragment<sql id= "Query_user_where"><ifTest= "Id!=null and id!= "">and id=#{id}</if><ifTest= "Username!=null and username!= "">and username like '%${username}% ' </if></sql>//Use include to reference SQL fragments<select id= "finduserlist"Parametertype="User"Resulttype="User">select * from User<where><include refid="Query_user_where"/></where></select>//reference to other Mapper.xml SQL fragments<include refid= "Namespace.sql Fragment"/>

Third, foreach label 1, pass the list through the Pojo class

To pass an array or list,mybatis to SQL using foreach parsing, the foreach parameter is defined as follows: Collection specifies the collection property in the input object, item each traversal build object, open begins the concatenation of the string, Close ends the concatenation of strings, separator: The string to be spliced in the two objects traversed.

(1) SQL statements

SELECT * from the users where username like '% sheet% ' and (ID =10 or ID =89 or id=16) SELECT * from the users where username like '% sheet% ' ID in (10,89,16)

(2) Vo class

 Public class queryvo{private user user;

        Private Usercustom Usercustom; //Pass multiple user IDs Private List<integer> Ids;set ()/get () ...  }

(3) Mapping file

<select id= "finduserlist" parametertype= "Userqueryvo" resulttype= "Usercustom" >
SELECT * from USER
<where>
<!--use to implement the SQL stitching below: and (id=1 or id=10 or id=16)--

<if test= "ids!=null and ids.size>0" >

<foreach collection= "ids" item= "user_id" open= "and(" close= ")" separator= " or">

ID=#{USER_ID}

</foreach>

</If>
</where>
</select>

<!--using the SQL stitching below: and ID in (1,10,16)

<foreach collection= "ids" item= "user_id" open= "andID in (" close= ")" separator = "," >

#{user_id}

</foreach>

(4) Test code

New Arraylist<integer> (); Ids.add (1); //query user with ID 1 //Query ID 10 user queryvo.setids (IDS); list<user> list = Usermapper.finduserlist (QUERYVO);

2. Passing a single list

(1) Mapper mapping file

<select id= "selectuserbylist" parametertype= "java.util.List" resulttype= "user" >    SELECT * from user      <where>  <!--delivery List,list is Pojo--  <if test= "list!= Null">     <foreach collection="list"item="item"open=""separator=" ,"close=")">    #{item.id}     </foreach>   </if>     </where></select>

(2) Mapper interface

 Public List<user> selectuserbylist (List userlist);

(3) test procedure

//Construct query criteria list New New New User (); User.setid (2); Userlist.add (user);

//Pass userlist List query user list List<user>list = Usermapper.selectuserbylist (userlist);

3. Passing an array of Pojo classes

(1) Mapper mapping file

Parameter meaning: Index is the subscript for the array, item is the name of each element of the array, the name is arbitrarily defined, the open loop begins, the close loop ends, and the output is separator intermediate.

<select id= "selectuserbyarray" parametertype= "object[]" resulttype= "user" >select * FROM user  <where> <!--pass array--><if test= "array!=null" ><foreach collection= "array" index= "index" item= "item"     open= "andID in (" separator= "," close= ")" >    #{item.id}</foreach></if>   </where ></select>

(2) Mapper interface

 Public List<user> Selectuserbyarray (object[] userlist)

(3) test procedure

//Construct query criteria list New New New User (); User.setid (2); userlist[1]=user; //Pass User Object query list of users List<user>list = Usermapper.selectuserbyarray (userlist);

4. Passing an array of string classes

(1) Mapper mapping file

<select id= "selectuserbyarray" parametertype= "object[]" resulttype= "user" >select * FROM user<where><!--Pass array--><if test= "array!=null" ><foreach collection= " array"index="index"item="item"open=" andID in ("separator=","close=" )">    #{item}</foreach></if></where></select>

(2) Mapper interface

 Public List<user> Selectuserbyarray (object[] userlist)

(3) test procedure

//Construct query criteria list New object[2];userlist[0]= "1"; userlist[1]= "2"; //Pass User Object query list of users List<user>list = Usermapper.selectuserbyarray (userlist);

MyBatis notes-SQL tagging method