Original address: http://www.cnblogs.com/shanheyongmu/p/7121807.html
MyBatis Core: The SQL statement flexible operation, through the expression of judgment, the SQL is flexible splicing, assembly.
MyBatis provides a variety of labeling methods for dynamic splicing of SQL.
1. if&where1.2 Requirements
User Information comprehensive query list and user information query list Total The two statement definitions use dynamic SQL.
The query conditions are judged, if the input parameters are not empty to query conditional stitching.
1.3 Mapper.xml
<select id= "finduserlist" parametertype= "Joanna.yan.mybatis.entity.UserQueryVo" resulttype= " Joanna.yan.mybatis.entity.UserCustom "> select * from USER <!--where you can automatically remove the first and-< in a condition ;where> <if test= "Usercustom!=null" > <if test= "Usercustom.sex!=null and USERCUSTOM.S Ex!= "> and User.sex=#{usercustom.sex} </if> <if test=" UserC Ustom.username!=null and usercustom.username!= ' "> and user.username like '%${usercustom.username}% ' </if> </if> </where> </select> <select id= "Findusercoun T "parametertype=" Joanna.yan.mybatis.entity.UserQueryVo "resulttype=" int "> SELECT COUNT (*) from USER < ;! --where can automatically remove the first and-<where> <if test= "Usercustom!=null" > <if T in a condition Est= "Usercustom.sex!=null and usercustom.sex!="> user.sex=#{usercustom.sex} </if> <if test= "usercustom.u Sername!=null and usercustom.username!= ' "> and user.username like '%${usercustom.username}% ' </if> </if> </where> </select>
1.4 Test Code
@Test public void Finduserlisttest () throws exception{ sqlsession sqlsession=sqlsessionfactory.opensession ( ); Usermapper Usermapper=sqlsession.getmapper (usermapper.class); Create wrapper object, set query condition userqueryvo userqueryvo=new userqueryvo (); Usercustom usercustom=new Usercustom (); Since dynamic SQL is used here, if you do not set a value, the condition will not be spliced in SQL// usercustom.setsex ("1"); Usercustom.setusername ("Zhang San Fung"); Userqueryvo.setusercustom (usercustom); List<usercustom> list=usermapper.finduserlist (USERQUERYVO); SYSTEM.OUT.PRINTLN (list); }
Printed sql: If you do not set a value for sex, the condition will not be stitched together in SQL
2.sql Fragment 2.1 Requirements
Extract the dynamic SQL judgment blocks implemented above to form a SQL fragment. SQL fragments can be referenced in other statement. Easy for programmers to develop.
2.2 Defining SQL Fragments
<!--defining the unique identity of the SQL fragment Id:sql fragment experience: 1. The SQL fragment is defined based on a single table, so that the SQL fragment can be reused 2. Do not include where--in the SQL fragment <sql id= "Query_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!= ' "> and user.username like '%${usercustom.username}% ' </if> </if> </sql>
2.3 Referencing SQL fragments
The SQL fragment is referenced in the definition statement in Mapper.xml:
<select id= "finduserlist" parametertype= "Joanna.yan.mybatis.entity.UserQueryVo" resulttype= " Joanna.yan.mybatis.entity.UserCustom "> select * from USER <!--where you can automatically remove the first and--from a condition <where> <!--reference the ID of the SQL fragment, if the ID specified by refID is not in this mapper file, you need to add namespace to the front and <include refid= " Query_user_where "></include> <!--can also refer to other SQL clips here- - </where> </ select> <select id= "Findusercount" parametertype= "Joanna.yan.mybatis.entity.UserQueryVo" resulttype= " int "> select COUNT (*) from USER <!--where you can automatically remove the first and--from a condition <!--reference the ID of the SQL fragment, if the ID specified by refID is not in this mapper file, you need to add namespace to the front and <include refid= "Query_user_where "></include> <!--can also refer to other SQL clips here- - </where> </select>
3. foreach
Pass an array or list,mybatis to SQL using foreach parsing.
3.1 Requirements
Add multiple ID input queries to the statement of the user query list and the total number of queries.
The SQL statements are as follows, two methods:
SELECT * from USER WHERE id=1 or id=10 or id=16
SELECT * FROM USER WHERE ID in (1,10,16)
3.2 Adding list<integer> IDs to input parameter types multiple IDs passed in
3.3 Modifying Mapper.xml
WHERE id=1 or id=10 or id=16
In the previous query criteria, the query condition was defined as a SQL fragment, and now we need to modify the SQL fragment.
<!--defining the unique identity of the SQL Fragment Id:sql fragment experience: 1. The SQL fragment is defined based on a single table, so that the SQL fragment can be reused 2. Do not include where in the SQL fragment --<sql id= "Query_user_where" > <if test= "usercustom!=null" > <if test= "Usercusto M.sex!=null and usercustom.sex!= ' "> and User.sex=#{usercustom.sex} </if> &L T;if test= "Usercustom.username!=null and usercustom.username!=" "> and user.username like '%${usercustom . username}% ' </if> <if test= "Ids!=null" > <!--using foreach to traverse an incoming IDs Collection: Specifies the collection property in the Input object item: the name of the object to be generated for each traversal open: begins the concatenation of the string Close: End of the concatenation of the string separator: Traversal of the two objects need to splice the string--<!--is to achieve the following SQL stitching: and (id=1 or id=10 or id=16)-<foreach collection= "IDs" item= "user_id" open= "and (" CLose= ")" separator= "or" > <!--each traversal needs stitching----id=#{user_id} & lt;/foreach> </if> </if> </sql>
3.4 Test Code
@Test public void Finduserlisttest () throws exception{ sqlsession sqlsession=sqlsessionfactory.opensession ( ); Usermapper Usermapper=sqlsession.getmapper (usermapper.class); Create wrapper object, set query condition userqueryvo userqueryvo=new userqueryvo (); Usercustom usercustom=new Usercustom (); Since dynamic SQL is used here, if you do not set a value, the condition will not be spliced in SQL// usercustom.setsex ("1"); Usercustom.setusername ("Xiao Ming"); Pass in multiple IDs list<integer> ids=new arraylist<> (); Ids.add (1); Ids.add (ten); Ids.add (+); Userqueryvo.setids (IDs); Userqueryvo.setusercustom (usercustom); List<usercustom> list=usermapper.finduserlist (USERQUERYVO); SYSTEM.OUT.PRINTLN (list); }
Reprinted from Http://www.cnblogs.com/Joanna-Yan/p/6908763.html
Spring+springmvc+mybatis Deep Learning and Building (v)--Dynamic SQL (forwarding Ibid.)