Mybatis学习笔记09 -动态SQL

luoyu1985

已于 2022-12-14 19:43:07 修改

阅读量115

点赞数 1

文章标签： mybatis 学习 sql

于 2022-12-13 17:41:47 首次发布

本文链接：https://blog.csdn.net/luoyu1985/article/details/128305152

版权

动态SQL

动态sql指的是通过映射的文件标签来动态拼接sql语句，而不用每次都单独写SQL，可以极大的节省开发时间和代码
缓存的实体类都需要加序列化

@Data
public class Blog implements Serializable { //序列化

    private String Id;
    private String Title;
    private String Author;
    private Date CreateTime;
    private int Views;

}

实现原理

在这里插入图片描述
默认存在sqlsession的一级缓存里，如果sqlsession关闭，那么缓存会自动存到mapper里的二级缓存里，mapper默认存512条记录。

环境搭建

类

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

}

lombok插件

方法

package com.mango.dao;

import com.mango.pojo.Blog;

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

public interface BlogMapper {
    int addBook(Blog blog); //环境初始化
    int upDataBlog(Map map);
    List<Blog> queryBlogIF(Map map);
    List<Blog> queryBlogChoose(Map map);
}

映射文件

    <insert id="addBook" parameterType="Blog">
        insert into blog(id, title, author, create_time,views)
        values (#{id}, #{title}, #{author}, #{CreateTime}, #{views});
    </insert>

UID生成工具

package com.mango.utils;
import org.junit.Test;

import java.util.UUID;

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

    @Test
    public void test(){
        System.out.println(IDUtils.getId());

    }
}

测试文件

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

        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("Mybatis");
        blog.setAuthor("mango");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("Java");
        mapper.addBook(blog);
        blog.setViews(9543);

        blog.setId(IDUtils.getId());
        blog.setTitle("Spring");
        mapper.addBook(blog);
        blog.setViews(6755);

        blog.setId(IDUtils.getId());
        blog.setTitle("微服务");
        mapper.addBook(blog);
        blog.setViews(3345);

        sqlSession.close();

    }

if语句

if语句的意思是，如果查询里含有某个字段，那么就按照这个字段查询，if可以叠加。

接口文件

package com.mango.dao;
import com.mango.pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBook(Blog blog);
    int upDataBlog(Map map);
    List<Blog> queryBlogIF(Map map); //if语句查询
    List<Blog> queryBlogChoose(Map map);
}

映射文件

    <select id="queryBlogIF" parameterType="Blog" resultType="Blog">
        select * from blog
        <where>
            <if test="id != null">
                and id like #{id}
            </if>
            <if test="title != null">
                and title like #{title}
            </if>
            <if test="author != null">
                and author like #{author}
            </if>
            <if test="views != null">
                and views > #{views}
            </if>
        </where>
    </select>

测试文件

    @Test
   //@Test //给update用来查询使用
	public void queryBlogIFTest(){
    //public List<Blog> queryBlogIFTest(Map map){ //给update用来查询使用
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","Java");
        map.put("views","9800");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        

        //return blogs; //给update用来查询使用

choose、when、otherwise语句

等同于java的switch语句，选择一条满足条件的语句执行

接口文件

package com.mango.dao;
import com.mango.pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBook(Blog blog);
    int upDataBlog(Map map);
    List<Blog> queryBlogIF(Map map);
    List<Blog> queryBlogChoose(Map map);//choose、when、otherwise语句
}

映射文件

    <select id="queryBlogChoose" parameterType="Blog" resultType="Blog">
        select * from blog
        <where>
            <!--选择一个去执行，只要任何一个when满足就执行该条语句-->
            <choose>
                <when test="title != null">
                    title =#{title}
                </when>
                <when test="id != null">
                    and id =#{id}
                </when>
                <when test="author != null">
                    and author =#{author}
                </when>
                <otherwise>
                    and views > #{views}
                </otherwise>
            </choose>
        </where>
    </select>

测试文件

    public void queryBlogChooseTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","Java");
        map.put("id","eb0319103a5c4f7db743e418592d77df");
        map.put("author","狂神说");
        map.put("views","8000");
        List<Blog> blogs = mapper.queryBlogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();

    }

SQL片段

有些代码可以将部分的功能抽取出来，便于后期复用。
1、使用sql标签抽取功能的部分
2、在需要的地方使用include标签引用
3、注意sql标签里不要包含where标签

    <!--可以复用的部分-->
    <sql id="idTitleAuthorViews">
        <if test="id != null">
            id like #{id}
        </if>
        <if test="title != null">
            and title like #{title}
        </if>
        <if test="author != null">
            and author like #{author}
        </if>
        <if test="views != null">
            and views > #{views}
        </if>
    </sql>


    <select id="queryBlogIF" parameterType="Blog" resultType="Blog">
        select * from blog
        <where>
            <!--引用SQLid-->
            <include refid="idTitleAuthorViews"/>
        </where>
    </select>

foreach遍历

接口文件

package com.mango.dao;
import com.mango.pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBook(Blog blog);
    int upDataBlog(Map map);
    List<Blog> queryBlogIF(Map map);
    List<Blog> queryBlogChoose(Map map);
    List<Blog> queryBlogForeach(Map map); //foreach

}

映射文件

    <!--ids是一个集合，里面包含所有id，从"and("开始，")"结束，中间用or分隔符-->
    <!--
    select * from 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 queryBlogForeachTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
//        List<String> ids = new ArrayList<String>();
//        ids.add("1");

        Map map = new HashMap();
        map.put("ids",ids);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }



        sqlSession.close();


    }

练习

通过if语句来进行updata数据

接口文件

package com.mango.dao;
import com.mango.pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBook(Blog blog);
    int upDataBlog(Map map); //updata
    List<Blog> queryBlogIF(Map map);
    List<Blog> queryBlogChoose(Map map);
}

映射文件

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

测试文件

    public void upDataBlogTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("id", "fb5e512bc7ad41ccb34e9edfba714ab8");
        map.put("author", "mango");
        mapper.upDataBlog(map);
        List<Blog> blogs = queryBlogIFTest(map);//这里调用了上面查询语句
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }