Mybatis动态SQL

1、环境搭建

SQL:

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

pojo:

package com.xu.pojo;

import lombok.Data;

import java.util.Date;
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date creatTime;
    private int views;
}

utils:

//用UUID当做id
package com.xu.utils;

import java.util.UUID;

public class GetUUID {
    public static String getUUId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

//获得Sqlsession
package com.xu.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

//SqlSession 完全包含了面向数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。
//SqlSessionFactoryBuilder--->SqlSessionFactory--->sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //获得sqlSessionFactory对象，通过它来获得sqlSession
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getsqlSession(){
                //获得SqlSession
                return sqlSessionFactory.openSession(true);//开启事务的自动提交
    }
}

dao：

BlogMapper：

package com.xu.dao;

import com.xu.pojo.Blog;

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

public interface BlogMapper {
    int addBolg(Blog blog);

    List<Blog> BlogList(Map map);
}

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.xu.dao.BlogMapper">
        <insert id="addBolg" parameterType="com.xu.pojo.Blog">
            insert into mybatis.blog(id, title, author, creat_time, views)
            VALUES (#{id},#{title},#{author},#{creatTime},#{views})
        </insert>

        <select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
            select * from mybatis.blog
            <where>
            <if test="author !=null">
                author = #{author}
            </if>
            <if test="title != null">
                and title = #{title}
            </if>
            </where>
        </select>
</mapper>

插入数据：

@Test
    public void addBolg(){
        Blog blog = new Blog();
        blog.setId(GetUUID.getUUId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("龙傲天");
        blog.setCreatTime(new Date());
        blog.setViews(9999);
        SqlSession sqlSession = MybatisUtils.getsqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        mapper.addBolg(blog);

        blog.setId(GetUUID.getUUId());
        blog.setTitle("Sping如此简单");
        mapper.addBolg(blog);

        blog.setId(GetUUID.getUUId());
        blog.setTitle("SpingMVC如此简单");
        mapper.addBolg(blog);

        blog.setId(GetUUID.getUUId());
        blog.setTitle("SpingIOC如此简单");
        mapper.addBolg(blog);
        sqlSession.close();
    }

测试：

@Test
    public void BlogList(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        //map.put("author","龙傲天");
        map.put("title","Mybatis如此简单");
        List<Blog> blogs = mapper.BlogList(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

结果：

==>  Preparing: select * from mybatis.blog WHERE title = ? 
==> Parameters: Mybatis如此简单(String)
<==    Columns: id, title, author, creat_time, views
<==        Row: 80c47860984a4bb88376752850c4a8a5, Mybatis如此简单, 龙傲天, 2020-02-27 16:38:05.0, 9999
<==      Total: 1
Blog(id=80c47860984a4bb88376752850c4a8a5, title=Mybatis如此简单, author=龙傲天, creatTime=Thu Feb 27 16:38:05 CST 2020, views=9999)

2、用法

if
choose (when, otherwise)
trim (where, set)
foreach

使用JDBC时，比如做一个搜索功能。避免不了的要使用SQL拼接，没有参数的时候查全部，有参数就按照参数查。还有考虑拼接SQL产生的问题，这样非常的麻烦，在Mybatis中用几个标签就解决了

<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
            select * from mybatis.blog where 
            <if test="author !=null">
                author = #{author}
            </if>
            <if test="title != null">
                and title = #{title}
            </if>
        </select>

<!--如果传进来的参数符合要求test的条件，mybatis就会把它自动的拼接上去。有个问题下面会说-->

choose

 <select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
            select * from mybatis.blog where           
            <choose>
                <when test="author !=null">
                author = #{author}
                </when>
                <when test="title !=null">
                author = #{author}
                </when>
                <otherwise>
                author = #{author}
                </otherwise>
            </choose>          
        </select>
<!--choose类似我们Java中的Switch语句-->

foreach

<select id="BlogList2" parameterType="map" resultType="com.xu.pojo.Blog">
            select * from mybatis.blog where id in
            <foreach item="item" collection="list" open="(" separator="," close=")">
                #{item}
            </foreach>
        </select>
<!--你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值-->

trim (where, set)

<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
    select * from mybatis.blog
    <trim prefix="where" prefixOverrides="AND | OR">
        <if test="author !=null">
            author = #{author}
        </if>
        <if test="title != null">
            and title = #{title}
        </if>
    </trim>
    </select>

<!--用来定制一些标签的功能的，where和set就够用了-->

上面if的问题，如果第一个条件不满足的话，sql就变成下面这样了：

select * from mybatis.blog where and title = #{title}

这样sql就出错了，炸裂。我们可以定制个where标签，trim的作用就是这个。where标签就是这样实现的，变成下面这样：

<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
    select * from mybatis.blog
    <where>
        <if test="author !=null">
            author = #{author}
        </if>
        <if test="title != null">
            and title = #{title}
        </if>
    </where>
  </select>

<trim prefix="where" prefixOverrides="AND | OR"></trim>

where 标签就相当于sql后面的where，所以使用where标签后就不用写where了，它还有其它的功能，像上面的例子。如果第一个条件不满足，第二个条件满足但是有and，where标签很只能的会帮你去掉 ！
（前置where关键字 ，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有AND 和 OR 遗留导致sql崩溃  where会只能的去掉）

set 标签会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。（因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留导致sql崩溃）


<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
    select * from mybatis.blog
    <trim prefix="where" prefixOverrides="AND | OR">
        <if test="author !=null">
            author = #{author}
        </if>
        <if test="title != null">
            and title = #{title}
        </if>
    </trim>
    </select>
    
    ===================================================================
    
    <trim prefix="SET" suffixOverrides=",">
  ...
	</trim>
    
prefix="where" ：前置的关键字
prefixOverrides="AND | OR" ：前面需要去掉的东西
suffixOverrides="," ：后面要去掉的东西

3、SQL片段

   <sql id="xxx">
        <if test="author !=null">
            author = #{author}
        </if>
        <if test="title != null">
            and title = #{title}
        </if>
    </sql>
    
    <select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
    select * from mybatis.blog
    <where>
        <include refid="xxx"></include>
    </where>
    </select>

可能我们的程序中好多的地方用到了这个判断条件，我们可以用<sql></sql>标签提取出来
用<include></include>来引用提取的sql片段！