For reprinting, please indicate the source: http://www.cnblogs.com/Joanna-Yan/p/6908763.html 

I mentioned earlier. Spring+Spring MVC+MyBatis Deep Learning and Building (IV):MyBatis Input and Output Mapping

mybatis core: flexible operation of SQL statements, judgment through expressions, flexible splicing and assembly of sql.

mybatis provides various tag methods to realize dynamic splicing sql.

1. if&where

1.2 Demand

Dynamic sql is used to define the two states: the comprehensive query list of user information and the total number of query lists of user information.

The query conditions are judged, if the input parameters are not empty, the query conditions are mosaic.

1.3 mapper.xml

    <select id="findUserList" parameterType="joanna.yan.mybatis.entity.UserQueryVo" resultType="joanna.yan.mybatis.entity.UserCustom">
        SELECT * FROM USER
        <!--where The first of the conditions can be automatically removed and  -->
        <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>
        </where>
    </select>


    <select id="findUserCount" parameterType="joanna.yan.mybatis.entity.UserQueryVo" resultType="int">
        SELECT count(*) FROM USER 
        <!--where The first of the conditions can be automatically removed and  -->
        <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>
        </where>
    </select>

1.4 Test Code

    @Test
    public void findUserListTest() throws Exception{
        SqlSession sqlSession=sqlSessionFactory.openSession();
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        //Create wrapper objects and set query conditions
        UserQueryVo userQueryVo=new UserQueryVo();
        UserCustom userCustom=new UserCustom();
        //Because dynamic is used here sql，If a value is not set, the condition will not be spliced together sql in
//        userCustom.setSex("1");
        userCustom.setUsername("Zhang Sanfeng");
        userQueryVo.setUserCustom(userCustom);
        List<UserCustom> list=userMapper.findUserList(userQueryVo);
        System.out.println(list);
    }

Printed sql: If the value of sex is not set, the condition will not be spliced into SQL

2.sql fragment

2.1 Demand

The dynamic sql judgment code block implemented above is extracted to form an sql fragment. sql fragments can be referenced in other states. It is convenient for programmers to develop.

2.2 Define sql fragments

    <! -- Define sql fragments
        Id: Unique identification of SQL fragments
        
        Experience: 1. Define sql fragments based on single tables, so that the sql fragments are highly reusable.
            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 Reference to sql fragments

Refer to sql fragment in defining state in mapper.xml:

    <select id="findUserList" parameterType="joanna.yan.mybatis.entity.UserQueryVo" resultType="joanna.yan.mybatis.entity.UserCustom">
        SELECT * FROM USER
        <!--where The first of the conditions can be automatically removed and  -->
        <where>
            <!--Quote sql Fragmental id，If refid Designated id Not in this book mapper In the file, you need to add it in front namespace  -->
            <include refid="query_user_where"></include>
            <!--There are other references here. sql fragment  -->
        </where>
    </select>


    <select id="findUserCount" parameterType="joanna.yan.mybatis.entity.UserQueryVo" resultType="int">
        SELECT count(*) FROM USER 
        <!--where The first of the conditions can be automatically removed and  -->
        <where>
            <!--Quote sql Fragmental id，If refid Designated id Not in this book mapper In the file, you need to add it in front namespace  -->
            <include refid="query_user_where"></include>
            <!--There are other references here. sql fragment  -->
        </where>
    </select>

3. foreach

Pass an array or List to sql, and mybatis uses foreach parsing.

3.1 Demand

Multiple id input queries are added to the statement of the user query list and the total number of queries.

The sql statement is as follows, in two ways:

SELECT * FROM USER WHERE id=1 OR id=10 OR id=16

SELECT * FROM USER WHERE id IN(1,10,16)

3.2 Add List < Integer > IDs to the input parameter type to pass in multiple IDS

3.3 Modify mapper.xml

WHERE id=1 OR id=10 OR id=16

In the previous query condition, the query condition was defined as an sql fragment. Now we need to modify the sql fragment.

    <! -- Define sql fragments
        Id: Unique identification of SQL fragments
        
        Experience: 1. Define sql fragments based on single tables, so that the sql fragments are highly reusable.
            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 test="ids!=null">
                <! -- Traverse the incoming ids using foreach
                    Collection: Specifies the collection properties in the input object
                    item: The name of the object generated by each traversal
                    open: Strings spliced at the beginning of traversal
                    close: A string spliced at the end of traversal
                    separator: Strings that need to be spliced between two objects traversed
                 -->
                 <! - To achieve the following sql splicing:
                     AND (id=1 OR id=10 OR id=16)
                   -->
                <foreach collection="ids" item="user_id" open="AND (" close=")" separator="or">
                    <! - Strings that need splicing for each traversal - >
                    id=#{user_id}
                </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 objects and set query conditions
        UserQueryVo userQueryVo=new UserQueryVo();
        UserCustom userCustom=new UserCustom();
        //Because dynamic is used here sql，If a value is not set, the condition will not be spliced together sql in
//        userCustom.setSex("1");
        userCustom.setUsername("Xiao Ming");
        //Input multiple id
        List<Integer> ids=new ArrayList<>();
        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 by Wechat.~