mybatis的动态SQL学习

动态SQL

什么是动态SQL:动态SQL是指不同的条生成不同的SQL语句

数据库搭建

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 '浏览量',
  PRIMARY KEY (`id`)
) ENGINE= INNODB DEFAULT CHARSET=utf8;

创建一个基础工程

环境搭建

1.写一个实体类

1.写一个实体类

package org.westos.pojo;

import org.apache.ibatis.type.Alias;

import java.util.Date;
@Alias("Bolg")
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;  //属性名和字段名不一致
    private  int views;

    @Override
    public String toString() {
        return "Blog{" +
                "id='" + id + '\'' +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }

    public String getId(String s) {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle(String mybatis如此简单) {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public Blog() {
    }
}

2.接口

package org.westos.dao;

import org.westos.pojo.Blog;

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

3.接口所对应的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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="org.westos.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>

4.mybatis核心配置映射

<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
....
....
....
    <mappers>
        <mapper resource="org/westos/dao/BlogMapper.xml" />
    </mappers>

5.编写连个工具类

package org.westos.utils;

import java.util.UUID;

public class IDutils {
    public static String gitID(){
        return UUID.randomUUID().toString().replace("-","");
    }

}

package org.westos.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 {
            //使用mybatis第一步：获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //        既然有了 SqlSessionFactory，顾名思义，我们就可以从中获得 SqlSession 的实例了。
        // SqlSession 完全包含了面向数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句

    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

6编写测试类插入数据

package org.westos.dao;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.westos.pojo.Blog;
import org.westos.utils.IDutils;
import org.westos.utils.MybatisUtils;

import java.util.Date;

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

        Blog blog = new Blog();
        blog.setId(IDutils.gitID());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("狂神");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        int i = mapper.addBlog(blog);

        blog.setId(IDutils.gitID());
        blog.setTitle("java如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.gitID());
        blog.setTitle("Spring如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.gitID());
        blog.setTitle("微服务如此简单");
        mapper.addBlog(blog);
		sqlSession.commit();
        sqlSession.close();
    }
}

IF

接口

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

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

    @Test
    public void querBlogIF(){
        BlogMapper mapper = MybatisUtils.getSqlSession().getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","Spring如此简单");
        List<Blog> blogs = mapper.queryBlogIf(map);

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

choose (when ,otherwis)

1.先写一个接口

List<Blog> queryBlogChoose(Map map);

2.写mybatis得XML配置文件

 <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 view = #{view}
                </otherwise>
            </choose>
        </where>
    </select>
<!--这样得SQL只要满足第一个就结束了-->

3.测试类

 @Test
    public void  queryBlogChoose(){
        BlogMapper mapper = MybatisUtils.getSqlSession().getMapper(BlogMapper.class);
        HashMap map = new HashMap ();
        map.put("title","Spring如此简单");

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

    }

trim(where,set)

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

实现一个实体类

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

配置SQL语句

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

编写测试文档

    @Test
    public void updateBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","Spring如此简单2");
        map.put("id","b74b0a056c6a45d9bf7be99994fb6c62");
        int i = mapper.updateBlog(map);
        sqlSession.commit();
        sqlSession.close();
    }

所谓得动态SQL　本质还是SQL语句，只是我们可以在SQL层面去执行一些逻辑代码.

SQL片段

有的时候,我们可能会将一些功能得部分抽取出来,方便复用.

    <sql id = "if-title-author">
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
<!--抽取-->
    <select id="queryBlogIf" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

• 在使用SQL标签的巩固部分是可以抽取得
• 在需要使用得地方使用include标签引用即可

注意事项:

• 最好根据单表来定义SQL片段

• 不要定义Ｗｈｅｒｅ标签

Foreach

select * from user where 1=1 and (id =1 or id =2 id=3);

<foreach item="id" index="index" collection="ids"
         pen="(" separator="or" close=")"
</foreach>

写一个接口

    //查询博客第123号记录的博客

    List<Blog> queryBlogForeach(Map map)

写一个SQLXML

    <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<Integer>();

        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);
        }
    }