mybatis（5）—复杂查询、动态SQL_mapper select 复杂结构-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42182892/article/details/108081808

一、复杂查询

1、多对一环境搭建

Mysql代码以及对应的表结构
student与teacher是一个一对多的关系，student表中的tid作为外键连接teacher中的id

USE `mybatis`;
DROP TABLE IF EXISTS teacher 
CREATE TABLE `teacher`(
   id INT(10) NOT NULL PRIMARY KEY,
   `name` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(id,`name`) VALUES(1,'王老师');

CREATE TABLE student(
   id INT(10) NOT NULL PRIMARY KEY,
   `name` VARCHAR(30) DEFAULT NULL,
   tid INT(10) DEFAULT NULL,
   KEY `fktid`(`tid`),
   CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO student(`id`,`name`,`tid`) VALUES('1','小明','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES('2','小红','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES('3','小张','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES('4','小李','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES('5','小王','1');

在这里插入图片描述

mybatis环境搭建
student中将teacher作为一个属性来实现外键连接

public class student {
    private int id;
    private String name;

    private teacher teacher;
}

配置student和teacher表对应的实体类、各自的Mapper以及对应的Mapper.xml配置文件
测试查询成功即可

    @Test
    public void test1() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

        teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);

        sqlSession.close();
    }

Opening JDBC Connection
Created connection 1881561036.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@702657cc]
==>  Preparing: select * from teacher where id=? 
==> Parameters: 1(Integer)
<==    Columns: id, name
<==        Row: 1, 王老师
<==      Total: 1
teacher{id=1, name='王老师'}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@702657cc]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@702657cc]
Returned connection 1881561036 to pool.

2、多对一的处理

多个student对应一个老师

嵌套查询

首先查询所有的学生信息，然后根据查询出来的学生tid，寻找对应的老师
studentMapper.xml：

    <select id="getStudent" resultMap="StudentTeacher">
        select *from student
    </select>

    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--对象使用association，集合使用collection-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from  teacher where id=#{id}
    </select>

按照结果嵌套处理

首先通过MySql连表查询，查得结果
再对结果的列名进行嵌套处理

<select id="getStudent" resultMap="StudentTeacher">
       select s.id sid,s.name sname,t.name tname
       from student s,teacher t
       where s.tid=t.id;
   </select>

查询结果

<select id="getStudent" resultMap="StudentTeacher">
       select s.id sid,s.name sname,t.name tname
       from student s,teacher t
       where s.tid=t.id;
   </select>

    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

查询结果

student{id=1, name='小明', teacher=teacher{id=0, name='王老师'}}
student{id=2, name='小红', teacher=teacher{id=0, name='王老师'}}
student{id=3, name='小张', teacher=teacher{id=0, name='王老师'}}
student{id=4, name='小李', teacher=teacher{id=0, name='王老师'}}
student{id=5, name='小王', teacher=teacher{id=0, name='王老师'}}

3、一对多环境搭建

查询一个老师对应的多个学生，因此两个实体类：

public class teacher {
    private int id;
    private String name;

    //一个老师拥有多个学生
    private List<student> students;
}

public class student {
    private int id;
    private String name;
    private int tid;
}

其余同上

4、一对多的处理

嵌套查询

    <select id="getTeacher" resultMap="TeacherStudent">
        select * from teacher where id=#{tid}
    </select>

    <resultMap id="TeacherStudent" type="teacher">
        <collection property="students" javaType="ArrayList" ofType="student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="student">
        select * from student where tid=#{tid}
    </select>

其中< select=“getStudentByTeacherId” column=“id”/> 是将查询到的teacher表中的id作为参数传递给getStudentByTeacherId查询

按照结果嵌套处理

    <select id="getTeacher" resultMap="TeacherStudent">
        select t.id tid,t.name tname,s.id sid,s.name sname
        from student s,teacher t
        where s.tid=t.id
    </select>

    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>

二、动态SQL

1、环境搭建（MySql+maven工程配置文件）

创建MySql表

CREATE TABLE blog(
   id VARCHAR(50) NOT NULL COMMENT '博客id',
   title VARCHAR(100) NOT NULL COMMENT '博客标题',
   author VARCHAR(30) NOT NULL COMMENT '博客作者',
   create_time DATETIME NOT NULL COMMENT '创建时间',
   views INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

对应的实体类

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

实体类的mapper接口

public interface BlogMapper {
}

mapper接口的配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hdu.Mapper.BlogMapper">

</mapper>

数据库中插入数据

    <select id="addBlog" parameterType="blog">
        insert into mybatis.blog (id,title,author,create_time,views)
        values(#{id},#{title},#{author},#{createTime},#{views})
    </select>

@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog=new Blog();
        blog.setId(IdUtiles.getId());
        blog.setTitle("MySql学习");
        blog.setAuthor("小王");
        blog.setCreateTime(new Date());
        blog.setViews(11);

        mapper.addBlog(blog);

        blog.setId(IdUtiles.getId());
        blog.setTitle("Mybatis学习");
        blog.setAuthor("小明");
        blog.setCreateTime(new Date());
        blog.setViews(236);

        mapper.addBlog(blog);

        blog.setId(IdUtiles.getId());
        blog.setTitle("spring学习");
        blog.setAuthor("小六");
        blog.setCreateTime(new Date());
        blog.setViews(10011);

        mapper.addBlog(blog);

        blog.setId(IdUtiles.getId());
        blog.setTitle("Java学习");
        blog.setAuthor("小丽");
        blog.setCreateTime(new Date());
        blog.setViews(236896);

        mapper.addBlog(blog);

        sqlSession.close();
    }

maven工程

2、IF

官方文档：

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如：

  <select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

这条语句提供了可选的查找文本功能。如果不传入 “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果。
如果希望通过 “title” 和 “author” 两个参数进行可选搜索该怎么办呢？首先，我想先将语句名称修改成更名副其实的名称；接下来，只需要加入另一个条件即可。

  <select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

实例

if语句可以添加多个判定条件，根据传入的参数中所包含的若干个条件进行查询
一般的MySql语言实现：

select * from mybatis.blog where title=xxx abd author=xxx

可以添加多个查询或者模糊查询，但是查询的条件是固定的，因此可以使用IF描写动态SQL：

    <select id="selectByIF" parameterType="map" resultType="Blog">
        select * from blog
        where 1=1
        <if test="author != null">
            and author = #{author}
        </if>
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="views != null">
            and views = #{views}
        </if>
    </select>

测试类：

    @Test
    public void testIF(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<>();
        map.put("author","小王");
        map.put("title","MySql学习");
        List<Blog> blogs = mapper.selectByIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

传入的map中只有author和title两个参数，因此查询语句被解释为：

==>  Preparing: select * from blog where 1=1 and author = ? and title = ?

3、choose（when、otherwise）

官方文档：

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句
还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员挑选的 Blog）

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

实例

choose中的多个条件按照先后顺序查询，满足一个条件就返回查询结果

   <select id="selectByChoose" parameterType="map" resultType="Blog">
        select * from blog where 1=1
        <choose>
            <when test="id!=null">
                and id=#{id}
            </when>
            <when test="author!=null">
                and author=#{author}
            </when>
            <when test="title!=null">
                and title=#{author}
            </when>
        </choose>
    </select>

测试类：

    @Test
    public void testChoose(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<>();
        map.put("id","1");
        map.put("title","MySql学习");
        map.put("author","小王");
        map.put("title","MySql学习");
        List<Blog> blogs = mapper.selectByChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

描写的select语句中第一个choose条件是id，因此查询到id=1的结果就返回了，此时的查询语言为：

==>  Preparing: select * from blog where 1=1 and id=?

4、trim（where、set）

在if和choose查询语句中，都需要先写一个where 1=1的判定条件，再添加if和choose判定条件，因此需要加入trim将这句也动态化

官方文档:

前面几个例子已经合宜地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例，这次我们将 “state = ‘ACTIVE’” 设置成动态条件，看看会发生什么

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  <if test="state != null">
    state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：

SELECT * FROM BLOG
WHERE

这会导致查询失败。如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’

这个查询也会失败。这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决，以至于解决过的人不会再想碰到这种问题。
MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中，可以对其进行自定义以符合需求。而这，只需要一处简单的改动：

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。
用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

这个例子中，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
来看看与 set 元素等价的自定义 trim 元素吧：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

注意，我们覆盖了后缀值设置，并且自定义了前缀值。

实例

因此，where和set本质上都是trim，trim能够定制我们所需要的标签，参数包括：

    <trim prefix="" suffix="" prefixOverrides="" suffixOverrides=""></trim>

preifx和suffix为标签名，即在句首或句尾加上标签，prefixOverrides和suffixOverrides指去除内容，在句首或句尾去除掉格式
因此choose语句可以使用where优化：

<select id="selectByChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="id!=null">
                    and id=#{id}
                </when>
                <when test="author!=null">
                    and author=#{author}
                </when>
                <when test="title!=null">
                    and title=#{author}
                </when>
            </choose>
        </where>

    </select>

此时的SQL语言被编译为：

==>  Preparing: select * from blog WHERE id=?

5、foreach：用于集合遍历

官方文档：

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。比如：

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！
你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

实例

将一个List作为需要遍历的集合，List中放置遍历的id指
ids作为键，List作为值，添加进map中
最后将map作为参数传递进查询方法
查询方法中将collection与map中的键对应

    <select id="selectByForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" separator="or" close=")">
                id=#{id}
            </foreach>
        </where>
    </select>

测试类：

    @Test
    public void testForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<>();
        ArrayList<String> ids = new ArrayList<>();
        ids.add("1");
        ids.add("2");
        ids.add("4");
        map.put("ids",ids);
        List<Blog> blogs = mapper.selectByForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

Mysql语言被编译为：

==>  Preparing: select * from blog WHERE ( id=? or id=? or id=? )