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

Reprint Please specify source: http://www.cnblogs.com/Joanna-Yan/p/6908763.html

We talked about spring+springmvc+mybatis. Deep learning and building (d)--mybatis input mapping and output mapping

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

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

    <SelectID= "Findusercount"ParameterType= "Joanna.yan.mybatis.entity.UserQueryVo"Resulttype= "int">SELECT Count (*) from USER<!--where can automatically remove the first and in a condition -        <where>            <ifTest= "Usercustom!=null">                <ifTest= "Usercustom.sex!=null and usercustom.sex!=">and User.sex=#{usercustom.sex}</if>                <ifTest= "Usercustom.username!=null and usercustom.username!=">and user.username like '%${usercustom.username}% '</if>            </if>        </where>    </Select>

1.4 Test Code

@Test Public voidFinduserlisttest ()throwsexception{sqlsession sqlsession=sqlsessionfactory.opensession (); Usermapper Usermapper=sqlsession.getmapper (Usermapper.class); //Create wrapper objects, set query criteriaUserqueryvo userqueryvo=NewUserqueryvo (); Usercustom Usercustom=NewUsercustom (); //because dynamic SQL is used here, if you do not set a value, the condition will not be stitched together in SQL//usercustom.setsex ("1");Usercustom.setusername ("Zhang San Feng");        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 a SQL fragment Id:sql fragment experience: 1. The SQL fragment is defined based on a single table, so that the SQL fragment is 2 reusable. Do not include where in the SQL fragment /c0> -    <SQLID= "Query_user_where">        <ifTest= "Usercustom!=null">            <ifTest= "Usercustom.sex!=null and usercustom.sex!=">and User.sex=#{usercustom.sex}</if>            <ifTest= "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:

    <SelectID= "Finduserlist"ParameterType= "Joanna.yan.mybatis.entity.UserQueryVo"Resulttype= "Joanna.yan.mybatis.entity.UserCustom">SELECT * from USER<!--where can automatically remove the first and in 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 in front -            <includerefID= "Query_user_where"></include>            <!--Other SQL fragments can also be referenced here -        </where>    </Select>

    <SelectID= "Findusercount"ParameterType= "Joanna.yan.mybatis.entity.UserQueryVo"Resulttype= "int">SELECT Count (*) from USER<!--where can automatically remove the first and in 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 in front -            <includerefID= "Query_user_where"></include>            <!--Other SQL fragments can also be referenced 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 a SQL fragment Id:sql fragment experience: 1. The SQL fragment is defined based on a single table, so that the SQL fragment is 2 reusable. Do not include where in the SQL fragment /c1> -    <SQLID= "Query_user_where">        <ifTest= "Usercustom!=null">            <ifTest= "Usercustom.sex!=null and usercustom.sex!=">and User.sex=#{usercustom.sex}</if>            <ifTest= "Usercustom.username!=null and usercustom.username!=">and user.username like '%${usercustom.username}% '</if>            <ifTest= "Ids!=null">                <!--traversal of incoming IDs collection using foreach: Specifies the collection property in the Input object item: the name of the object to be generated for each traversal open : Start the concatenation of the string close: End of the concatenation of the string separator: traversing the two objects need to splice the string -                 <!--is to implement the SQL stitching below: and (id=1 or id=10 or id=16) -                <foreachCollection= "IDs"Item= "user_id"Open= "and ("Close=")"Separator= "or">                    <!--each traversal requires a concatenation of the string -id=#{user_id}</foreach>            </if>        </if>    </SQL>

3.4 Test Code

@Test Public voidFinduserlisttest ()throwsexception{sqlsession sqlsession=sqlsessionfactory.opensession (); Usermapper Usermapper=sqlsession.getmapper (Usermapper.class); //Create wrapper objects, set query criteriaUserqueryvo userqueryvo=NewUserqueryvo (); Usercustom Usercustom=NewUsercustom (); //because dynamic SQL is used here, if you do not set a value, the condition will not be stitched together in SQL//usercustom.setsex ("1");Usercustom.setusername ("Xiao Ming"); //Pass in multiple IDsList<integer> ids=NewArraylist<>(); Ids.add (1); Ids.add (10); Ids.add (16);        Userqueryvo.setids (IDS);        Userqueryvo.setusercustom (Usercustom); List<UserCustom> list=usermapper.finduserlist (USERQUERYVO);    SYSTEM.OUT.PRINTLN (list); }

If this article is helpful to you, please give me a reward!

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