MyBatis configuring dynamic SQL statements

In the SQL mapping file of MyBatis, sometimes you need to select different SQL statements according to some query conditions. If you rewrite SQL in every scenario, it's obvious that the efficiency is not very high, and the dynamic SQL of MyBatis solves this problem well. The dynamic SQL of MyBatis processes SQL according to the conditions, especially simply, writes SQL once, but according to the jump of branches, etc., in multiple It can also be used in the scene, for example:

• When the query criteria cannot be determined due to different parameters, you can use the < where > tag to include
• In < where > you can use < if test = "...." > to process conditionally to achieve dynamic
• The < foreach > traversal tag is put in the following code

In addition, dynamic SQL ends at the same time. When SQL statements need to be spliced in native JDBC, errors are reported due to writing problems

(1) where and if tags

UserMapper interface

/**
* Query by criteria
* @return
*/
List<User> findUserByCondition(User user);

UserMapper.xml

<select id="findUserByCondition" resultType="cn.ideal.domain.User" parameterType="cn.ideal.domain.User">
	select  * from user
    <where>
   		<if test="username != null">
        	and username = #{username}
        </if>
        <if test="gender != null">
            and gender = #{gender}
        </if>
    </where>
</select>

Note: in SQL, "and" is used to splice statements with one or more query conditions. When this statement is the first query condition, the first "and" will be blocked due to the existence of < where >

MyBatisTest

/**
 * Query by criteria
 * @throws Exception
 */
@Test
public void testFindByCondition() throws Exception{
    User user = new User();
    user.setUsername("Tom");
    user.setGender("female");

    List<User> users = userMapper.findUserByCondition(user);
    for (User u : users){
        System.out.println(u);
    }

Execution effect

(2) Reuse SQL

There are some statements that are used very frequently in our programs. At this time, we can also configure them separately, and then achieve the effect of reuse

First, we need to make a simple declaration

<sql id="xxxxx">
	<!-- Multiplex SQL -->
</sql>

Where reference is needed, we can do so

<where>
	include refid="xxxxx"></include>
	<!-- You may also use references -->
</where>

(3) foreach label

Put forward such a requirement, query multiple IDS in the user, for example (12,16,17), we can write SQL like this

select * from user where id=12 or id=16 or id=17

Or so

select * from user where id in (12,16,17)

In this case, we need to pass a data or List type parameter to SQL, and then use the < foreach > tag to traverse and parse it

UserMapper interface

/**
     * Query user information according to the id set provided in QueryUserVo
     * @param vo
     * @return
     */
    List<User> findUserInIds(QueryUserVo vo);

UserMapper.xml

<select id="findUserInIds" resultType="cn.ideal.domain.UserInstance" parameterType="cn.ideal.domain.QueryUserVo">
	select * from user
	<where>
        <if test="ids != null and ids.size() > 0">
        	<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
            	#{uid}
            </foreach>
        </if>
    </where>
</select>

Explain it.

• Collection specifies the collection properties in the input object
• item the name of the object generated for each traversal
• open is the string spliced at the beginning of traversal
• close is the string to be spliced at the end of convenience
• The separator is a string to be spliced between two objects

In this case, we use the form of select * from user where id in (12,16,17). If we want to use the form of or, we just need to modify the splicing format

/**
 * Query user information according to the id set provided in QueryUserVo
 * @throws Exception
 */
    @Test
    public void testfindUserInIds() throws Exception{
        QueryUserVo vo = new QueryUserVo();
        List<Integer> list = new ArrayList<Integer>();
        list.add(12);
        list.add(16);
        list.add(17);
        vo.setIds(list);

        List<User> users = userMapper.findUserInIds(vo);
        for (User u : users){
            System.out.println(u);
        }
    }

Execution effect

Ending

If there are any deficiencies in the article, welcome to leave a message and exchange, thank you for your support!

If you can help, then pay attention to me! If you prefer the way of reading WeChat articles, you can pay attention to my public number.