Spring+springmvc+mybatis Deep Learning and Building (v)--Dynamic SQL (forwarding Ibid.)

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-&lt 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 &lt ;! --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.)