Mybatis 动态SQL(include 标签)

写代码的时候有可能出现下面这样的情况,这样代码重复度很高,看起来笨笨的,我们可以把重复的代码给抽出来,通过<sql>标签给封装起来,然后再通过<include>标签进行引用

任意sql语句都是可以被封装起来的

然后我们举个例子吧

新建了一个 userInfo2Mapper 接口,然后写下如下代码,声明两个方法

package com.example.mybatisdemo.mapper;
import com.example.mybatisdemo.model.UserInfo;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface UserInfo2Mapper {

    List<UserInfo> selectByCondition(UserInfo userInfo);
    List<UserInfo> selectByCondition3(UserInfo userInfo);
}

 在resources 中创建 Userinfo2XMLMapper.xml 文件,写下两个方法的XML代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatisdemo.mapper.UserInfo2Mapper">
   
    <select id="selectByCondition" resultType="com.example.mybatisdemo.model.UserInfo">
        select * from userinfo
        <where>
            <if test="username!=null">
                username = #{username}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
        </where>
    </select>
    <select id="selectByCondition3" resultType="com.example.mybatisdemo.model.UserInfo">
        select * from userinfo
        <where>

            <if test="username!=null">
                username = #{username}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
        </where>
    </select>
</mapper>

很明显,这两个代码里面都有 select * from userinfo,我们把他封装起来,id 不一定是selectTable,你随意取名

所以我们就可以把这个sql标签进行引入和使用

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatisdemo.mapper.UserInfo2Mapper">
    <sql id="selectTable">
        select * from userinfo
    </sql>
    
    <select id="selectByCondition" resultType="com.example.mybatisdemo.model.UserInfo">
        <include refid="selectTable"></include>
        <where>

            <if test="username!=null">
                username = #{username}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
        </where>
    </select>
    <select id="selectByCondition3" resultType="com.example.mybatisdemo.model.UserInfo">
        <include refid="selectTable"></include>
        <where>

            <if test="username!=null">
                username = #{username}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
        </where>
    </select>
</mapper>

 回到 userInfo2Mapper 接口,右键 Generate,test,勾选 selectByCondition ,ok,补充 test 代码

package com.example.mybatisdemo.mapper;

import com.example.mybatisdemo.model.UserInfo;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.lang.reflect.Array;
import java.util.Arrays;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@Slf4j
@SpringBootTest
class UserInfo2MapperTest {
    @Autowired
    private UserInfo2Mapper userInfo2Mapper;
  
    @Test
    void selectByCondition() {
        UserInfo userInfo = new UserInfo();
        //userInfo.setUsername("io");
        userInfo.setAge(23);
        //userInfo.setGender(0);
        List<UserInfo> userInfos = userInfo2Mapper.selectByCondition(userInfo);
        log.info(userInfos.toString());
    }

   

运行成功,没毛病