[大数据]MyBatis-04

MyBatis-04

• 复杂查询环境搭建
• 多对一处理
• 一对多处理
• 动态SQL环境搭建
  • IF标签
  • 常用标签
    • WHERE
    • CHOOSE
    • WHEN
    • TRIM
    • OTHERWISE
    • SET
  • ForEach标签

一、复杂查询环境搭建

重复的文件从之前搭建好的拿就行😁，在此不再赘述

1、首先创建两个表（teacher表，student表）：

DROP TABLE IF EXISTS `teacher`;
DROP TABLE IF EXISTS `student`;

CREATE TABLE `teacher` (
	`id` bigint NOT NULL PRIMARY KEY,
    `name` varchar(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher (id, name) VALUES (1,'Jack');

CREATE TABLE `student` (
	`id` bigint NOT NULL PRIMARY KEY,
    `name` varchar(30) DEFAULT NULL,
    `tid` bigint NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO student (id, name, tid) VALUES (1,'Mary',1);
INSERT INTO student (id, name, tid) VALUES (2,'Mike',1);
INSERT INTO student (id, name, tid) VALUES (3,'John',1);
INSERT INTO student (id, name, tid) VALUES (4,'Lily',1);
INSERT INTO student (id, name, tid) VALUES (5,'Judy',1);

SELECT * FROM student, teacher;

2、构建实体类（Student，Teacher）：

Student实体类：

@Data
public class Student {
    private long id;
    private String name;
    private Teacher teacher;
}

Teacher实体类：

@Data
public class Teacher {
    private long id;
    private String name;
}

3、编写数据访问层接口（StudentMapper，TeacherMapper）：

StudentMapper接口：

public interface StudentMapper {

    @Select("select * from student")
    List<Student> getStudentList();
}

TeacherMapper接口：

public interface TeacherMapper {
    @Select("select * from teacher")
    List<Teacher> getTeacherList();
}

4、构造*Mapper.xml文件：

StudentMapper.xml：

<?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.jack.dao.StudentMapper">

</mapper>

TeacherMapper.xml：

<?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.jack.dao.TeacherMapper">

</mapper>

5、在config文件中配置mapper映射器：

<mappers>
    <package name="com.jack.dao"/>
</mappers>

6、单元测试保证环境搭建成功：

TestMapper：

public class TestMapper {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacherList();
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }

    @Test
    public void testGetStudentList(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

至此文件结构如下：

二、多对一处理

如果上一步环境搭建成功的话，那么就是多对一的关系（多名学生对应一位老湿）

1、单元测试：

现在如果直接进行单元测试的话，就会出现如下情况：

2、问题分析：

看到这个结果我们第一时间想到的应该是利用ResultMap进行结果集映射，但是teacher字段是对象类型，如何进行映射呢？

其实，MyBatis已经提供了两种映射属性：association属性以及collection属性，其中association属性符合多对一的情况下的结果映射，collection属性符合之后会叙述的一对多的情况

3、问题处理：

有两种方式进行解决：

• 查询嵌套
• 结果嵌套

（1）、查询嵌套：

<!--结果映射1 查询嵌套-->
<select id="getStudentList" resultMap="StudentTeacher">
    select * from test.student
</select>

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" column="tid" javaType="Teacher" select="getTeachers"/>
</resultMap>

<select id="getTeachers" resultType="Teacher">
    select * from test.teacher where id = #{id}
</select>

（2）、结果嵌套：

<!--结果映射2 结果嵌套-->
<select id="getStudentList2" resultMap="TeacherStudent">
    select s.id sid, s.name sname, t.name tname
    from student s,teacher t
    where s.tid = t.id
</select>

<resultMap id="TeacherStudent" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

4、单元测试

@Test
public void testGetStudentList(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudentList();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

@Test
public void testGetStudentList2(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudentList2();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

三、一对多处理：

1、创建实体类：

Student实体类：

@Data
public class Student {
    private long id;
    private String name;
    private long tid;
    //    private Teacher teacher;
}

Teacher实体类：

@Data
public class Teacher {
    private long id;
    private String name;
    private List<Student> students;
}

2、编写数据访问层接口（TeacherMapper）：

TeacherMapper接口：

public interface TeacherMapper {

    List<Teacher> getTeacher(@Param("tid") long id);

    List<Teacher> getTeacher2(@Param("tid") long id);
}

3、构造TeacherMapper.xml文件：

TeacherMapper.xml：

<?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.jack.dao.TeacherMapper">

    <!--结果映射1 查询嵌套-->
    <select id="getTeacher" resultMap="StudentTeacher">
    select * from test.teacher where id = #{tid}
</select>

    <resultMap id="StudentTeacher" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudents" column="id"/>
    </resultMap>

    <select id="getStudents" resultType="Student">
    select * from test.student where tid = #{id}
</select>

    <!--结果映射2 结果嵌套-->
    <select id="getTeacher2" resultMap="TeacherStudent">
    select s.id sid, s.name sname, t.name tname,t.id tid
    from student s,teacher t
    where s.tid = t.id and t.id = #{tid}
</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"/>
    <result property="tid" column="tid"/>
    </collection>
    </resultMap>
    </mapper>

单元测试：

public class TestMapper {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacher(1);
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }

    @Test
    public void testGetTeacher2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacher2(1);
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }
}

四、动态SQL环境搭建

首先创建一个表用于测试：

DROP TABLE IF EXISTS `blog`;

CREATE TABLE `blog` (
    `id` BIGINT NOT NULL PRIMARY KEY COMMENT '博客ID',
    `title` VARCHAR(30) DEFAULT NULL COMMENT '博客标题',
    `author` VARCHAR(30) DEFAULT NULL COMMENT  '博客作者',
    `created_time` DATETIME NOT NULL COMMENT  '创建时间',
    `views` INT(30) NOT NULL COMMENT '访问量'
)ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO blog
    (id, title, author, created_time, views)
    VALUES (1,'Java从入门到放弃','jack','2020-02-14',4433);

INSERT INTO blog
(id, title, author, created_time, views)
VALUES (2,'Java从入门到起飞','MATY','2020-02-14',123);

INSERT INTO blog
(id, title, author, created_time, views)
VALUES (3,'Java从起飞到放弃','lili','2020-02-14',1098);

INSERT INTO blog
(id, title, author, created_time, views)
VALUES (4,'Maven入门','jk','2020-02-14',344);

INSERT INTO blog
(id, title, author, created_time, views)
VALUES (5,'MyBats使用','json','2020-02-14',100);

SELECT  * FROM blog

重复配置文件不再赘述

1、构建实体类

@Data
public class Blog {
    private long id;
    private String title;
    private String author;
    private Date createdTime;
    private int views;
}

2、构建Mapper接口：

public interface BlogMapper {

    @Select("select * from blog")
    List<Blog> getBlogList();
    List<Blog> getBlogs(Map map);
}

3、构建BlogMapper.xml文件：

<?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.jack.dao.BlogMapper">

</mapper>

4、注册Mapper映射文件：

<mappers>
    <mapper resource="mapper/BlogMapper.xml"/>
</mappers>

5、单元测试保证环境搭建成功：

public class TestMapper {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> blogList = mapper.getBlogList();
        for (Blog blog : blogList) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
}

一、IF标签使用：

Mapper文件：

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

    <if test="author != null">
        and author = #{author}
    </if>
</select>

更标准的写法：

<?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.jack.dao.BlogMapper">

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

            <if test="author != null">
                and author = #{author}
            </if>
        </where>

    </select>
</mapper>

单元测试：

@Test
public void testGetBlogByIfLabel(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    //        map.put("title","Maven入门");
    //        map.put("author","jack");
    List<Blog> blogList = mapper.getBlogs(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

输出：

去掉一行注释再进行单元测试：

@Test
public void testGetBlogByIfLabel(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    map.put("title","Maven入门");
    //        map.put("author","jack");
    List<Blog> blogList = mapper.getBlogs(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

输出：

试着再删除一行注释再进行单元测试：

@Test
public void testGetBlogByIfLabel(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    //        map.put("title","Maven入门");
    map.put("author","jack");
    List<Blog> blogList = mapper.getBlogs(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

输出：

PS：where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

二、CHOOSE、WHEN、OTHERWISE标签使用：

Mapper文件：

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

            <when test="author != null">
                and author = #{author}
            </when>

            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>

</select>

Mapper接口：

public interface BlogMapper {

    @Select("select * from blog")
    List<Blog> getBlogList();

    List<Blog> getBlogs(Map map);

    List<Blog> getBlogs2(Map map);
}

单元测试：

@Test
public void testGetBlogByChooseLabel(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    //        map.put("title","Maven入门");
    //        map.put("author","jack");
    map.put("views","999");
    List<Blog> blogList = mapper.getBlogs2(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

输出结果同IF标签，也就是在otherwise哪里不同：

三、SET标签使用：

Mapper文件：

<update id="updateBlog" parameterType="blog">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>
        <if test="views != null">
            views = #{views},
        </if>
    </set>
    where id = #{id}
</update>

Mapper接口：

int updateBlog(Blog blog);

单元测试：

@Test
public void testUpdateBlog(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    mapper.updateBlog(new Blog(2,"SpringBoot开源代码",null,null,333));
    sqlSession.close();
}

输出：

数据库信息：

四、ForEach标签使用：

Mapper文件：

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

Mapper接口：

List<Blog> queryBlogByForEachLabel(Map map);

单元测试：

@Test
public void testQueryBlogByForEach(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();

    ArrayList<Object> ids = new ArrayList<>();

    ids.add(1);

    map.put("ids",ids);

    List<Blog> blogList = mapper.queryBlogByForEachLabel(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

输出：

小插曲：Sql公共代码段使用：

Mapper文件：

<!-- Set标签-->
<update id="updateBlog" parameterType="blog">
    update blog
    <set>
        <include refid="publicCodeBlock"></include>
    </set>
    where id = #{id}
</update>

<!--    Sql代码块-->
<sql id="publicCodeBlock">
    <if test="title != null">
        title = #{title},
    </if>
    <if test="author != null">
        author = #{author},
    </if>
    <if test="views != null">
        views = #{views},
    </if>
</sql>

作用：降低代码复用，但是只能包含简单语句，不建议插入复杂语句

总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.jack.dao.BlogMapper">

    <!--    if标签-->
    <select id="getBlogs" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>

            <if test="author != null">
                and author = #{author}
            </if>
        </where>

    </select>

    <!--    choose以及when还有otherwise标签-->
    <select id="getBlogs2" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>

                <when test="author != null">
                    and author = #{author}
                </when>

                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>

    </select>
    <!-- Set标签-->
    <update id="updateBlog" parameterType="blog">
        update blog
        <set>
            <include refid="publicCodeBlock"></include>
        </set>
        where id = #{id}
    </update>

    <!--    ForEach标签-->
    <select id="queryBlogByForEachLabel" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" index="index" open="and (" separator="or" close=")">
                id = #{id}
            </foreach>
        </where>
    </select>

    <!--    Sql代码块-->
    <sql id="publicCodeBlock">
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>
        <if test="views != null">
            views = #{views},
        </if>
    </sql>
</mapper>

相关文章：

• MyBatis-01

  • 什么是MyBatis
  • 为什么要用MyBatis
  • 如何使用MyBatis？

• MyBatis-02

  • 增删改查实现
  • Map以及模糊查询
  • 配置属性优化
  • 配置别名优化
  • 配置映射器说明
  • 生命周期以及作用域
  • ResultMap结果集映射

• MyBatis-03

  • 日志实现
  • 分页实现
  • 注解开发
  • MyBatis执行流程
  • 通过注解实现增删改查
  • LomBok使用

• MyBatis-05

  • 缓存简介
  • 一级缓存
  • 二级缓存
  • MyBatis缓存原理