Mybatis 动态SQL语句测试笔记

搭建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

IDutils工具包

package com.zzqsmile.utils;

import org.junit.Test;

import java.util.UUID;

@SuppressWarnings("all")    //抑制警告
public class IDutils {
    public static String getID(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }

    @Test
    public void test(){
        System.out.println(IDutils.getID());
    }

}

步骤

1.导包

pom.xml

2.编写配置文件

mybatis-config.xml:

<?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"/>
    <!--    可以给实体类起别名-->
    <typeAliases>
        <package name="com.zzqsmile.pojo"/>
<!--        <typeAlias type="com.zzqsmile.pojo.User" alias="User"/>-->
    </typeAliases>

    <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}"/>
<!--                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>-->
<!--                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false"/>-->
<!--                <property name="username" value="root"/>-->
<!--                <property name="password" value="root"/>-->
            </dataSource>
        </environment>

    </environments>

    <!--    绑定接口-->
    <mappers>
        <mapper class="com.zzqsmile.dao.BlogMapper"></mapper>
    </mappers>
</configuration>

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false
username=root
password=root

3.编写实体类

package com.zzqsmile.pojo;

import lombok.Data;

import java.util.Date;

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

}

4.编写实体类对应的Mapper接口和 Mapper.xml文件

BlogMapper:

package com.zzqsmile.dao;

import com.zzqsmile.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
    //插入数据
    int addBlog(Blog blog);

    //查询博客
    List<Blog> queryBlogIF(Map map);

    //选择查询
    List<Blog> queryBlogChoose(Map map);

    //更新博客
    int updateBlog(Map map);

   //ForEach
    List<Blog> queryBlogForEach(Map map);
}

BlogMapper.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.zzqsmile.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views})
    </insert>
</mapper>

---

• if

BlogMapper.xml

 <mapper>
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog where 1=1
        <if test="title != null">
            AND title like #{title}
        </if>

        <if test="author != null">
            AND author like #{author}
        </if>

    </select>
 </mapper>

优化where

    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <if test="title != null">
                AND title like #{title}
            </if>

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

    </select>

测试：

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

        HashMap map = new HashMap<>();
        map.put("title","Spring");
        map.put("author","zzqsmile");

        List<Blog> blogs = mapper.queryBlogIF(map);

        for (Blog blog : blogs) {
            System.out.println(blog);
        }


        sqlSession.close();
    }

• choose

    <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.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>

• updateBlog()

    <update id="updateBlog" parameterType="map">
        update mybatis.blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>

            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>
 </mapper>

trim标签可以自定义

• 动态SQLForEach
  BlogMapper.xml

<!--    select * from mybatis.blog where 1=1 and (id=1 or id =2 or id=3)-->
    <select id="queryBlogForEach" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

测试：

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

        HashMap map = new HashMap<>();

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


        List<Blog> blogs = mapper.queryBlogForEach(map);

        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }