Mybatis动态SQL

前提环境搭建

1.在数据库中创建表

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;

insert into blog values(1,"mybatis","chen","2022-03-26 17:17:00","9999");
insert into blog values(2,"spring","chen","2022-03-26 17:17:00","9999");
insert into blog values(3,"springboot","chen","2022-03-26 17:17:00","9999");
insert into blog values(4,"springmvc","chen","2022-03-26 17:17:00","9999");

2.导入相关依赖包

<!-- Mybatis核心 -->
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.7</version>
</dependency>
<!-- junit测试 -->
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
  <scope>test</scope>
</dependency>
<!-- MySQL驱动 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.3</version>
</dependency>
<!-- lombok插件 -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.18.10</version>
</dependency>

3.创建MyBatis的核心配置文件

先配置一个数据库配置文件db.properties，配置文件存放的位置是src/main/resources目录下

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.111.131:3306/mybatis?characterEncoding=UTF-8
username=root
password=123456

然后创建MyBatis核心配置文件，习惯上命名为mybatis-config.xml，核心配置文件存放的位置是src/main/resources目录下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties"/>

    <settings>
        <!--实体类驼峰命名与数据库_命名映射开启-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--设置连接数据库的环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--引入映射文件-->
    <mappers>
      <mapper resource="mappers/BlogMapper.xml"/>
    </mappers> 
</configuration>

4.编写Mybatis工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //读取MyBatis的核心配置文件
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //创建SqlSessionFactoryBuilder对象
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            //通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory，生产SqlSession对象
            sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        //参数true为设置自动提交
        return sqlSessionFactory.openSession(true);
    }
}

动态SQL

1.创建实体类

该地方我们使用的是Lombok配置实体类，可以通过Lombok的配置使用对其进行了解

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

2.创建Mapper接口

MyBatis中的mapper接口相当于以前的dao。但是区别在于，mapper仅仅是接口，我们不需要提供实现类。

public interface BlogMapper {
    //动态SQL之IF:查询博客
    List<Blog> queryBlogIF(Map map);

    //动态SQL之Choose，相当于Java中的switch:查询博客
    List<Blog> queryBlogChoose(Map map);

    //动态SQL之set:更新博客
    int updateBlog(Map map);

    //动态SQL之Foreach:通过Id动态查询博客
    List<Blog> queryBlogForeach(Map map);
}

3.创建MyBatis的映射文件

注：mapper接口的全类名和映射文件中的命名空间（namespace）保持一致。

<?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.chen.mybatis.mapper.BlogMapper">

    <!--动态SQL之IF-->
    <select id="queryBlogIF" parameterType="map" resultType="com.chen.mybatis.pojo.Blog">
        select * from blog
        <!--如果后面的SQL子句都不成立，将会自动去除where-->
        <where>
            <!--如果title标签不为空，则加上后面的SQL-->
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>

    <!--动态SQL之Choose，相当于Java中的switch-->
    <select id="queryBlogChoose" parameterType="map" resultType="com.chen.mybatis.pojo.Blog">
        select * from blog
        <where>
            <choose>
                <!--要是SQL字句都满足的话则只会执行第一个-->
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <!--otherwise代表其他，也就是说上面SQL字句都不成立的话，该字句必须成立-->
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

    <!--动态SQL之Set-->
    <update id="updateBlog" parameterType="map">
        update blog
        <!--set的主要作用其实就是为了帮我们去掉无关的逗号-->
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>

    <!--动态SQL之Foreach-->
    <select id="queryBlogForeach" parameterType="map" resultType="com.chen.mybatis.pojo.Blog">
        select * from blog
        <where>
            <!--自定义的集合名字ids，也是我们要传递进来的集合，然后遍历集合给item-->
            <!--原始语句:select * from blog WHERE ( id = ? or id = ? )，通过该SQL理解open，close,separator的用法-->
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>
    
</mapper>

4.测试

public class MybatisTest {

    //动态SQL之IF
    @Test
    public void queryBlogIF(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("title","spring");
        List<Blog> blogs = blogMapper.queryBlogIF(map);
        for(Blog blog:blogs){
            System.out.println(blog);
        }
        sqlSession.close();
    }

    //动态SQL之Choose，相当于Java中的switch
    @Test
    public void queryBlogChoose(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("title","mybatis");
        //map.put("views",9999);
        List<Blog> blogs = blogMapper.queryBlogChoose(map);
        for(Blog blog:blogs){
            System.out.println(blog);
        }
        sqlSession.close();
    }

    //动态SQL之Set
    @Test
    public void updateBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("id",1);
        map.put("title","mybatis1");
        map.put("author","wang");
        int res =  blogMapper.updateBlog(map);
        System.out.println(res);
        sqlSession.close();
    }

    //动态SQL之Foreach
    @Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        map.put("ids",ids);

        List<Blog> blogs =  blogMapper.queryBlogForeach(map);
        for(Blog blog:blogs){
            System.out.println(blog);
        }
        sqlSession.close();
    }

}

---

该文档来自狂神说Java

b站视频链接https://www.bilibili.com/video/BV1NE411Q7Nx?p=25