MyBatis Dynamic SQL statements using

In the actual development, sometimes the query condition may be indeterminate, the query condition may have many or may not have, this time needs to use the dynamic SQL statement stitching function.

First, if , where , SQL Use of tags

Requirements: In some advanced queries, there is an indeterminate number of query conditions. For example, SELECT * from user WHERE username like '%% ' and sex = ' 1 ';

In order to be able to dynamically splice SQL statements, you can use the following methods:

Usermapper.xml

<SelectID= "Finduserbyusernameandsex"ParameterType= "Cn.itheima.pojo.User"Resulttype= "Cn.itheima.pojo.User">\
SELECT * from user<!--where tag action: automatically adding a where keyword to an SQL statement removes the first condition's and statement -        <where>            <ifTest= "Username ! = null and Username! =" ">and username like '%${username}% '</if>            <ifTest= "Sex ! = null and sex! =" ">and Sex=#{sex}</if>        </where>    </Select>

The query conditions under the Where tab are placed in Finduserbyusernameandsex and can only be used within that range. In order to implement code reusability, it is common to put query conditions outside, which are called by different queries.

<SQLID= "User_where">       <where>           <ifTest= "Username ! = null and Username! =" ">and username like '%${username}% '</if>            <ifTest= "Sex ! = null and sex! =" ">and Sex=#{sex}</if>        </where>    </SQL>
    <SelectID= "Finduserbyusernameandsex"ParameterType= "Cn.itheima.pojo.User"Resulttype= "Cn.itheima.pojo.User">SELECT * from user<!--Invoke SQL Condition -        <includerefID= "User_where"></include>    </Select>

Writing interfaces in Usermapper.java

List<user> finduserbyusernameandsex (user user);

Test

    @Test    publicvoidthrows  Exception {        =  Sqlsessionfactory.opensession ();         = Session.getmapper (usermapper.  Class);         New User ();        User.setusername ("Ming");        User.setsex ("1");        List<User> userlist = usermapper.finduserbyusernameandsex (User);        System.out.println (userlist);    

Second, foreach Use of tags

Requirements: SQL statement, SELECT * from user WHERE ID in (1,15,22,28), to receive multiple ID values from the service layer, and then use the foreach tag to dynamically stitch multiple ID values into a complete SQL statement.

Usermapper.xml

<SelectID= "Finduserbyids"ParameterType= "Cn.itheima.pojo.QueryVO"Resulttype= "Cn.itheima.pojo.User">SELECT * from user<where>            <ifTest= "IDs! = null">                <!--foreach: Loop passed in collection parameter collection: variable name of incoming collection item: Each loop will loop out the data into this variable Open: Loop begins stitching the string close: Loop ends Stitching The string separator: the concatenation separator in the loop -                <foreachCollection= "IDs"Item= "id"Open= "id in ("Close=")"Separator=",">#{id}</foreach>            </if>        </where>    </Select>

Queryvo.java

 Package Cn.itheima.pojo; Import java.util.List;  Public class Queryvo {    private list<integer> IDs;      Public List<integer> getids () {        return  ids;    }      Public void setids (list<integer> IDs) {        this. ids = IDs;    }} 

Multiple ID values can be passed to the DAO layer through the Queryvo class.

Programming interfaces in the Usermapper.java interface

List<user> finduserbyids (Queryvo vo);

Test

@Test Public voidTestfinduserbyids ()throwsException {sqlsession session=sqlsessionfactory.opensession (); Usermapper Usermapper= Session.getmapper (usermapper.class); Queryvo Vo=NewQueryvo (); List<Integer> ids =NewArraylist<integer>(); Ids.add (1); Ids.add (15); Ids.add (22); Ids.add (28);        Vo.setids (IDS); List<User> userlist =Usermapper.finduserbyids (VO);    System.out.println (userlist); }

MyBatis Dynamic SQL statements using