MyBatis Dynamic SQL

The content of this section:

• Dynamic SQL

First, dynamic SQL

Dynamic splicing of SQL is achieved through various labeling methods provided by MyBatis.

Requirements: Query users based on gender and name. Query sql:

SELECT ID, username, birthday, sex, address from ' user ' WHERE sex = 1 and username like '% sheet '

1. If label

(1) Mapper.xml file
Usermapper.xml configuration sql, as follows:

    <!--query users by condition--    <select id= "Queryuserbywhere" parametertype= "user" resulttype= "user" >        Select ID, username, birthday, sex, address from ' user '        WHERE sex = #{sex} and username like        '%${username}% '    </sel Ect>

(2) Mapper interface

Write the Mapper interface, such as:

    Query user public list<user> Queryuserbywhere according to the condition (users user    );

(3) test method

Add the test method to Usermappertest as follows:

    Query user @Test public    void TestUser () throws Exception {        //load Core profile        String resource = "Based on criteria Sqlmapconfig.xml ";        InputStream in = Resources.getresourceasstream (Resource);        Create Sqlsessionfactory        sqlsessionfactory sqlsessionfactory = new Sqlsessionfactorybuilder (). build (in);        Get sqlsession, and spring is integrated by spring management        sqlsession sqlsession = sqlsessionfactory.opensession ();        Sqlsession will help me generate an implementation class  (which requires us to give the interface, which helps us to generate the implementation class, returned to us or the interface)        usermapper usermapper = Sqlsession.getmapper ( Usermapper.class);        User user = new user ();       user.setsex ("1");        User.setusername ("Zhang");        list<user> users = Usermapper.queryuserbywhere (User);        for (User u:users) {            System.out.println (U);        }        Sqlsession.close () managed by spring after integration with spring        ;    }

To execute the test method, view the console log:

DEBUG [main]-==>  preparing:select ID, username, birthday, sex, address from ' user ' WHERE sex =? and username like '% sheets ' debug [main]-==> parameters:1 (String) debug [main]-<==      total:4user [id=10, username = Zhang San, sex=1, Birthday=thu Jul 00:00:00 CST, address= Beijing]user [id=16, Username= Zhang Xiaoming, sex=1, Birthday=null, address= Henan Zhengzhou]user [id=24, Username= Zhang Sanfeng, sex=1, Birthday=null, address= Henan Zhengzhou]user [id=27, Username= Zhang Fei, sex=1, Birthday=mon Dec 25 00:0 0:00 CST, address= Shu]

Assuming that the condition of gender is not needed now, comment out User.setsex ("1"), the test result is as follows:

DEBUG [main]-==>  preparing:select ID, username, birthday, sex, address from ' user ' WHERE sex =? and username like '% sheets ' DEBUG [main]-==> parameters:nulldebug [main]-<==      total:0

This is obviously unreasonable.

According to the previous study, to solve this problem, you need to write more than one SQL, more query conditions, you need to write more SQL, obviously this is not reliable.

Solution, use the IF label for Dynamic SQL.

(4) using the IF label

    <!--query users by condition--    <select id= "Queryuserbywhere" parametertype= "user" resulttype= "user" >        Select ID, username, birthday, sex, address from ' user '        WHERE 1=1        <if test= "Sex! = null and sex! = '" > and            sex = #{sex}        </if>        <if test= "username! = null and Username! =" "> and            username like '%${username}% '        </if>     </select>

Note The string type of data needs to be done not equal to the null string checksum. Also, why add 1=1 to the where, because if this is not added, when the code is commented out User.setsex ("1"), the printed SQL statement is "where and username like ...", obviously grammatical error.

or comment out user.setsex ("1"), execute the test method again, and view the console log:

DEBUG [main]-==>  preparing:select ID, username, birthday, sex, address from ' user ' WHERE 1=1 and username LI KE '% Zhang% ' DEBUG [main]-==> parameters:debug [main]-<==      total:4User [id=10, Username= Zhang San, sex=1, Bir  Thday=thu Jul 00:00:00 CST, address= Beijing]user [id=16, Username= Zhang Xiaoming, sex=1, Birthday=null, address= Henan Zhengzhou]user [id=24, Username= Zhang Sanfeng, sex=1, Birthday=null, address= Henan Zhengzhou]user [id=27, Username= Zhang Fei, sex=1, Birthday=mon Dec 00:00:00 CST 2017, Address= Shu]

2. Where label

The above SQL also has a where 1=1 such statement, very troublesome. You can use the Where tab to retrofit.

    <!--query users by condition--    <select id= "Queryuserbywhere" parametertype= "user" resulttype= "user" >        Select ID, username, birthday, sex, address from ' user '        <where>            <if test= ' sex! = null and sex! = ' "> and                sex = #{sex}            </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.

Extract the ID, username, birthday, sex, address from the example above as a SQL fragment, as follows:

    <!--SQL Fragment--    <select id= "queryuserbysql" parametertype= "user" resulttype= "user" >        <!-- SELECT ID, username, birthday, sex, address from ' user '-        <!--loading SQL fragments with the include tag; refID is the SQL fragment ID        -- Select <include refid= "Userfields"/> from ' user '        <!--where label can automatically add the WHERE keyword while processing the first and keyword in the SQL statement-        <where>            <if test= "Sex! = null" > and                sex = #{sex}            </if>            <if test= "Username! = null and Username! = "" > and                username like                '%${username}% '            </if>        </where>    </select>    <!--statement SQL fragment    --<sql id= "Userfields" >        ID, username, birthday, sex, Address    </sql>

4. Foreach Label

Pass an array or list,mybatis to SQL using foreach parsing, as follows:

Query the user information based on multiple IDs, query sql:

SELECT * FROM user WHERE ID in (1,10,24)

(1) Usermapper.java Add method (there are three types)

    Querying user information based on multiple IDs public    list<user> queryuserbyids (integer[] IDs);    Public list<user> Queryuserbyids (list<integer> IDs);    Public list<user> queryuserbyids (Queryvo vo);

First, a public list<user> queryuserbyids (Queryvo vo); Retrofit Queryvo.java

As defined in Pojo, the list property IDs stores multiple user IDs and adds the Getter/setter method.

public class Queryvo implements Serializable {//serialized, the object is transferred to binary for transmission. Private static final Long Serialversionuid = 1l;private user user; list<integer> idslist;integer[] IDs; Public list<integer> getidslist () {return idslist;} public void Setidslist (list<integer> idslist) {this.idslist = idslist;} Public integer[] GetIDs () {return IDs;} public void Setids (integer[] ids) {this.ids = IDs;} Public User GetUser () {return user;} public void SetUser (user user) {this.user = user;}}

(2) Mapper.xml file

Usermapper.xml add SQL, as follows:

    <!--query user information based on multiple IDs (<select)--    id= "Queryuserbyids" parametertype= "Queryvo" resulttype= "User" >        SELECT * from ' user '        <where>            ID            in <!--foreach tag, traverse-to            <!--collection: Traversed collection, Here is Queryvo's IDs properties-            <!--item: Traversed items, can be written casually, but with the following #{} to be consistent--            <!--open: The SQL fragment that was added earlier--            <!--close: SQL Fragment added at the end--            <!--separator: Specifies the delimiter used between traversed elements--            <foreach collection= " Idslist "item=" Item "separator=", "open=" ("close=") ">                #{item}            </foreach>        </where>    </select>

Or put the ID in into open, as follows:

<foreach collection= "idslist" item= "Item" separator= "," open= "id in (" close= ")" >

(4) test method

//query user @Test public void TestID () throws Exception based on multiple IDs {        Load Core configuration file String resource = "Sqlmapconfig.xml";        InputStream in = Resources.getresourceasstream (Resource);        Create sqlsessionfactory sqlsessionfactory sqlsessionfactory = new Sqlsessionfactorybuilder (). build (in);        Get sqlsession, and spring is integrated by spring management sqlsession sqlsession = Sqlsessionfactory.opensession (); Sqlsession will help me generate an implementation class (which requires us to give the interface, which helps us to generate the implementation class, return to us or interface) Usermapper Usermapper = Sqlsession.getmapper (usermapper.clas        s);        Queryvo vo = new Queryvo ();        list<integer> ids = new arraylist<> ();        Ids.add (10);        Ids.add (16);        Ids.add (22);        Vo.setidslist (IDS);        list<user> users = Usermapper.queryuserbyids (VO);        for (User u:users) {System.out.println (U);    }//And spring are managed by spring to manage Sqlsession.close (); }

To execute the test method, view the console log:

DEBUG [main]-==>  preparing:select * from ' user ' WHERE ID in (?,?,?) debug [main]-==> parameters:10 (integer), (integer), (integer) debug [main]-<==      total:3user [id=10, user Name= Zhang San, sex=1, Birthday=thu Jul 00:00:00 CST, address= Beijing]user [id=16, Username= Zhang Xiaoming, sex=1, Birthday=null, addres s= Henan Zhengzhou]user [id=22, Username= Chen Xiaoming, sex=1, Birthday=null, address=, Zhengzhou, Henan]

In implementing a public list<user> Queryuserbyids (integer[] IDs);

Modify the Usermapper.xml as follows:

    <!--query user information based on multiple IDs (<select)--    id= "Queryuserbyids" parametertype= "Queryvo" resulttype= "User" >        select * from ' user '        <where>            ID in            <foreach collection="array" item= "Item" Separator= "," open= "(" close= ")" >                #{item}            </foreach>        </where>    </select>

"Note": When the passed parameter is an array, the value of collection must be an array, otherwise an error will be given. Similarly, when the parameter passed is list, the value of collection must be list. When an incoming parameter is an object (such as Queryvo), the property name inside the object is used directly (for example, idslist in the previous example).

Test method:

    Query users based on multiple IDs    @Test public    void TestID2 () throws Exception {        //load Core profile        String resource = " Sqlmapconfig.xml ";        InputStream in = Resources.getresourceasstream (Resource);        Create Sqlsessionfactory        sqlsessionfactory sqlsessionfactory = new Sqlsessionfactorybuilder (). build (in);        Get sqlsession, and spring is integrated by spring management        sqlsession sqlsession = sqlsessionfactory.opensession ();        Sqlsession will help me generate an implementation class  (which requires us to give the interface, which helps us to generate the implementation class, returned to us or the interface)        usermapper usermapper = Sqlsession.getmapper ( Usermapper.class);        integer[] ids = new Integer[3];        Ids[0] = +;        IDS[1] =;        IDS[2] = ten;        list<user> users = usermapper.queryuserbyids (IDs);        for (User u:users) {            System.out.println (U);        }        Sqlsession.close () managed by spring after integration with spring        ;    }

Execute the test method to view the log:

DEBUG [main]-==>  preparing:select * from ' user ' WHERE ID in (?,?,?) debug [main]-==> parameters:16 (integer), (integer), (integer) debug [main]-<==      total:3user [id=10, user Name= Zhang San, sex=1, Birthday=thu Jul 00:00:00 CST, address= Beijing]user [id=16, Username= Zhang Xiaoming, sex=1, Birthday=null, addres s= Henan Zhengzhou]user [id=22, Username= Chen Xiaoming, sex=1, Birthday=null, address=, Zhengzhou, Henan]

　　　

"Note": To get the demand, you can define the interface first, and then write the SQL. This is more of a clue.

MyBatis Dynamic SQL