mybatis--动态sql

一、测试环境：

建包如下图：

核心配置文件：
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"/>

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <typeAliases>
     <package name="com.zhou.pojo"/>
      </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}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers >
        <package name="com.zhou.mapper"/>
</mappers>
</configuration>

工具类：
IDUtils：
可以随机生成id

public class IDUtils {
    public static String getId(){
        String s = UUID.randomUUID().toString().replaceAll("-", "");
        return s;
    }
}

db.properties:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8
username=root
password=123456

MybatisUtils：

package com.zhou.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;

public class MybatisUtils {

   private static SqlSessionFactory sqlSessionFactory;
    static{
        try {
            InputStream inputStream;
            String resource = "mybatis-config.xml";
            inputStream = Resources.getResourceAsStream(resource);
            // 工厂模式
            sqlSessionFactory= new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取sqlsession的连接
    public static SqlSession getsession(){
        return sqlSessionFactory.openSession();

    }
    //获取sqlsession的连接
    //flag 为true，则表示事物自动提交
    public static SqlSession getsession(Boolean flag){
        return sqlSessionFactory.openSession(flag);

    }
}

接口：Blog

public interface BlogMapper {
  int addBlog(Blog blog);
}

与接口对应的配置文件：
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">
<!-- 原来我们都是编写具体的执行sql -->
<mapper namespace="com.zhou.mapper.BlogMapper">
<insert id="addBlog" parameterType="Blog">
    insert into mybatis.blog(id,title,author,create_time,views) values (#{id},#{title},#{author},#{createDate},#{views});
</insert>
</mapper>

实体类：Blogmapper

import java.util.Date;

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

}

测试类为：

public class Test {
    public static void main(String[] args) {
        SqlSession getsession = MybatisUtils.getsession(true);
        BlogMapper mapper = getsession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
//        blog.setId(IDUtils.getId());
//        blog.setTitle("Mybatis so easy");
//        blog.setAuthor("hah");
//        blog.setCreateDate(new Date());
//        blog.setViews(333);
//        mapper.addBlog(blog);
        blog.setId(IDUtils.getId());
        blog.setTitle("java so easy");
        blog.setAuthor("zhoudan");
        blog.setCreateDate(new Date());
        mapper.addBlog(blog);
        blog.setId(IDUtils.getId());
        blog.setTitle("js so easy");
        blog.setAuthor("xixi");

        mapper.addBlog(blog);
        getsession.close();
    }
}

生成了如下表格：

二、动态sql

概念：
传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题， 其通过 if, choose, when, otherwise, trim, where, set, foreach标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率。下面就去感受Mybatis动态SQL的魅力吧。

if：利用if实现简单的条件选择

choose(when,otherwise):相当于java中的switch语句,通常与when和otherwise搭配

where:简化Sql语句中where的条件判断

set:解决动态更新语句

trim:可以灵活的去除多余的关键字

foreach:迭代一个集合,通常用于in条件

动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的格式，去排列组合就可以了
if：利用if实现简单的条件选择

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

注意：if语句最好放在where标签里面

测试类：

 SqlSession session = MybatisUtils.getsession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Map map = new HashMap<String, String>();
     //   map.put("title","java so easy");

        List<Blog> blogs = mapper.selectBlog(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        session.close();

当放入参数进行查询时：map.put("title","java so easy");
结果如下：

choose：相当于java中的switch语句,通常与when和otherwise搭配
查询符合条件的一个

<select id="selectBlogChoose" 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>

public void testselectBlogChoose(){
        SqlSession session = MybatisUtils.getsession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Map map = new HashMap<String, String>();
        map.put("author","zhoudan");
    //   map.put("title","java so easy");
     //   map.put("views",333);
     //   map.put("id","673760c21a0d439d8654f514bfb3a6f3");

        List<Blog> blogs = mapper.selectBlogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        session.close();
    }

set：动态更新一个语句

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

测试：

public void testupdate(){
        SqlSession session = MybatisUtils.getsession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Map map = new HashMap<String, String>();
       map.put("id",2);
       map.put("author","dandan");
        int i = mapper.updateBlog(map);
        System.out.println(i);

        session.close();
    }

foreach：迭代一个集合,通常用于in条件

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

测试：

public void testForeach(){
        SqlSession session = MybatisUtils.getsession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Map map = new HashMap<String, List>();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(3);
        ids.add(4);
        map.put("ids",ids);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }

查询结果输出为：