8. MyBatis 动态SQL-CSDN博客

本文链接：https://blog.csdn.net/qq_41775769/article/details/120114681

MyBatis 动态SQL

1. 动态SQL背景需求

从实际开发的业务场景出发，有时候我们经常需要设计出用户字段信息的组合查询操作，比如现在有一张用户信息表如下：

在这里插入图片描述

根据用户的 ID 和姓名 name 查询用户记录；
根据用户的姓名 name 和专业 major 查询用户信息；
根据用户的姓名 name 和分数 score 查询用户记录；

实体类 User：

package com.trainingl.enity;

public class User {
    private Integer id;
    private String name;
    private Double score;
    private String major;
    
    public User(){
        
    }
    public User(Integer id, String name, Double score, String major) {
        this.id = id;
        this.name = name;
        this.score = score;
        this.major = major;
    }

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Double getScore() {
        return score;
    }
    public void setScore(Double score) {
        this.score = score;
    }
    public String getMajor() {
        return major;
    }
    public void setMajor(String major) {
        this.major = major;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", score=" + score +
                ", major='" + major + '\'' +
                '}';
    }
}

自定义接口 UserMapper：

package com.trainingl.mapper;

import com.trainingl.enity.User;

public interface UserMapper {
    //根据用户的 ID 和姓名 name 查询
    public User findByUser1(User user);
    //根据用户的姓名 name 和专业 major 查询
    public User findByUser2(User user);
    //根据用户的姓名 name 和分数 score 查询
    public User findByUser3(User user);
}

SQL 配置文件 UserMapper.xml

<mapper namespace="com.trainingl.mapper.UserMapper">
    <select id="findByUser1" parameterType="com.trainingl.enity.User" resultType="com.trainingl.enity.User">
        select * from user where id=#{id} and name=#{name};
    </select>
    <select id="findByUser2" parameterType="com.trainingl.enity.User" resultType="com.trainingl.enity.User">
        select * from user where name=#{name} and major=#{major};
    </select>
    <select id="findByUser3" parameterType="com.trainingl.enity.User" resultType="com.trainingl.enity.User">
        select * from user where name=#{name} and score=#{score};
    </select>
</mapper>

可以发现这样写的代码复用性很差，可维护的难度大，所以可以使用动态的 SQL 来解决这个问题。

2. 动态SQL标签

使用动态 SQL 可以简化代码的开发，减少开发者的工作量，程序可以自动根据业务参数来决定 SQL 的组成。

1、if 标签

package com.trainingl.mapper;

import com.trainingl.enity.User;

public interface UserMapper {
    public User findByUser(User user);
}

<mapper namespace="com.trainingl.mapper.UserMapper">
    <select id="findByUser" resultType="com.trainingl.enity.User">
        select * from user where
        <if test="id!=null">
            id = #{id}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
        <if test="score!=null">
            and score = #{score}
        </if>
        <if test="major!=null">
          and major = #{major}
        </if>
    </select>
</mapper>

package com.trainingl.test;

import com.trainingl.enity.User;
import com.trainingl.mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test {
    public static void main(String[] args) {
        //加载MyBatis配置信息
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
        //构建SQLSessionFactoryBuilder
        SqlSessionFactoryBuilder builder = new
                SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(inputStream);
        //获取SqlSession
        SqlSession sqlSession = factory.openSession();
        //获取实现接口的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
        user.setName("小明");
//        user.setMajor("软件工程");
        System.out.println(mapper.findByUser(user));
    }
}

控制台的打印信息如下：

在这里插入图片描述

if 标签可以自动根据表达式的结果来决定是否将对应的语句添加到 SQL 中，如果条件不成立则不添加，如果条件成立则添加。

需要注意的是，上述代码中假如 id=null 条件成立，则 select * from user where 后面会直接跟上 and + 条件，就会出现 SQL 的语法错误。

<select id="findByUser" resultType="com.trainingl.enity.User">
    select * from user where
    <if test="id!=null">
        id = #{id}
    </if>
    <if test="id==null">
        1 = 1
    </if>
    <if test="name!=null">
        and name = #{name}
    </if>
    <if test="score!=null">
        and score = #{score}
    </if>
    <if test="major!=null">
        and major = #{major}
    </if>
</select>

在这里插入图片描述

2、where 标签

<select id="findByUser" resultType="com.trainingl.enity.User">
    select * from user
    <where>
        <if test="id!=null">
            id = #{id}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
        <if test="score!=null">
            and score = #{score}
        </if>
        <if test="major!=null">
            and major = #{major}
        </if>
    </where>
</select>

where 标签可以自动判断是否要删除语句块中的 and 关键字，如果检测到 where 直接跟 and 拼接，则自动删除 and，通常情况下 if 和 where 结合起来使用。

3、choose、when 组合标签

跟 JAVA 中的 switch 效果差不多，是按照条件的顺序依次判断，当 when 中有条件满足的时候，就会跳出 choose，即所有的 when 和 otherwise 条件中，只有一个会输出，当所有的条件都不满足的时，就输出 otherwise 中的内容。

<select id="findByUser" resultType="com.trainingl.enity.User">
    select * from user
    <where>
        <choose>
            <when test="id!=null">
                id = #{id}
            </when>
            <when test="name!=null">
                and name = #{name}
            </when>
            <when test="score!=null">
                and score = #{score}
            </when>
            <when test="major!=null">
                and major = #{major}
            </when>
            <otherwise>
                id = 1
            </otherwise>
        </choose>
    </where>
</select>

4、trim 标签

trim 标签的 prefix 属性中的值，如果和 prefixOverrides 中的值直接拼接，则自动删除 prefixOverrides 中的值。

<select id="findByUser" resultType="com.trainingl.enity.User">
    select * from user
    <trim prefix="where" prefixOverrides="and">
        <if test="id!=null">
            id = #{id}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
        <if test="score!=null">
            and score = #{score}
        </if>
        <if test="major!=null">
            and major = #{major}
        </if>
    </trim>
</select>

上面介绍的标签都是为了解决查询多样性的问题，针对不同业务的查询条件，动态生成不同SQL 执行语句。

5、set 标签

set 标签用于 update 操作，会自动根据参数选择生成的 SQL 语句。

<update id="updateUser">
    update user
    <set>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="score != null">
            score = #{score},
        </if>
        <if test="major != null">
            major = #{major}
        </if>
    </set>
    where id = #{id}
</update>

在这里插入图片描述

6、foreach 标签

foreach 标签可以迭代生成一系列的值，这个标签主要用于 SQL 的 in 语句。

需求：查找某一用户的同学列表

修改 User 实体类，添加一个 List 类型的属性 classMateId。

package com.trainingl.enity;

import java.util.List;

public class User {
    private Integer id;
    private String name;
    private Double score;
    private String major;
    private List<Integer> classMateId;

    public User(){

    }

    public User(Integer id, String name, Double score, String major, List<Integer> classMateId) {
        this.id = id;
        this.name = name;
        this.score = score;
        this.major = major;
        this.classMateId = classMateId;
    }

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Double getScore() {
        return score;
    }
    public void setScore(Double score) {
        this.score = score;
    }
    public String getMajor() {
        return major;
    }
    public void setMajor(String major) {
        this.major = major;
    }

    public List<Integer> getClassMateId() {
        return classMateId;
    }

    public void setClassMateId(List<Integer> classMateId) {
        this.classMateId = classMateId;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", score=" + score +
                ", major='" + major + '\'' +
                ", classMateId=" + classMateId +
                '}';
    }
}

UserMapper.java

package com.trainingl.mapper;

import com.trainingl.enity.User;

import java.util.List;

public interface UserMapper {
    public List<User> findAll(User user);
}

UserMapper.xml

<select id="findAll" resultType="com.trainingl.enity.User">
    select * from user
    <where>
        <foreach collection="classMateId" open="id in (" close=")"
                 item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

测试类 Test

public class Test {
    public static void main(String[] args) {
        //加载MyBatis配置信息
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
        //构建SQLSessionFactoryBuilder
        SqlSessionFactoryBuilder builder = new
                SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(inputStream);
        //获取SqlSession
        SqlSession sqlSession = factory.openSession();
        //获取实现接口的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Integer> classMateIds = new ArrayList();
        User user = new User();
        classMateIds.add(2);
        classMateIds.add(3);
        classMateIds.add(4);
        user.setClassMateId(classMateIds);
        List<User> users = mapper.findAll(user);
        for (User item:users) {
            System.out.println(item);
        }
    }
}

在这里插入图片描述