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

Source: Internet
Author: User
Tags sql using

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.)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.