学习博客：【MyBatis】动态SQL

时间：2022-08-18 04:00:01 xs618b4nal2传感器 xs618b1pbl2传感器

根据不同的条件生成不同的条件SQL语句

本质还是SQL句子，只是在SQL逻辑代码在层面执行

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

1. 搭建环境

导包

<dependencies>               <dependency>         <groupId>org.projectlombokgroupId>         <artifactId>lombokartifactId>         <version>1.18.24version>         <scope>providedscope>     dependency>           <dependency>         <groupId>junitgroupId>         <artifactId>junitartifactId>         <version>4.12version>     dependency>  dependencies>

编制配置文件

 DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">   <configuration>      
    <properties resource="db.properties">
        <property name="username" value="root"/>
        <property name="password" value="yL@98"/>
    properties>

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


    
    <typeAliases>
        
        <package name="com.yl.pojo"/>
    typeAliases>

    <environments default="test">

        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="yL@98"/>
            dataSource>
        environment>

        <environment id="test">
            <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>
        <mapper class="com.yl.dao.BlogMapper"/>
    mappers>

configuration>

编写实体类

import java.util.Date;

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

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

import com.yl.pojo.Blog;

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

public interface BlogMapper { 
          

}


DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.yl.dao.BlogMapper">


mapper>

2. if

接口

    //插入数据
    int addBlog(Blog blog);
    //查询博客
    List<Blog> queryBlogIF(Map map);

Mapper.xml

<insert id="addBlog" parameterType="blog">
    insert into blog(id,title,author,create_time,views)
    values(#{id},#{title},#{author},#{createTime},#{views})
insert>


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

测试

public class MapperTest { 
          

    @Test
    public void addBlogTest() { 
          
        SqlSession sqlSession = MyBatisUntils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis");
        blog.setAuthor("杨杨");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Java");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("微服务");
        mapper.addBlog(blog);

        sqlSession.close();
    }

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

        HashMap map = new HashMap();
        //map.put("title", "Java");
        map.put("author", "杨杨");
        List<Blog> blogs = mapper.queryBlogIF(map);

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

        sqlSession.close();
    }

}

3. choose (when, otherwise)

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

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

    HashMap map = new HashMap();
    //map.put("title", "Java");
    map.put("author", "杨杨");
    map.put("views", 9999);
    List<Blog> blogs = mapper.queryBlogChoose(map);

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

    sqlSession.close();
}

4. trim (where, set)


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

select>

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

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

    HashMap map = new HashMap();
    map.put("title", "JVM");
    //map.put("author", "杨");
    map.put("id", "0c1db0ef618b458f964c6b29401d22c1");
    mapper.updateBlog(map);

    sqlSession.close();
}

5. SQL片段

抽取公共部分方便复用

<sql id="if-title-author">
    <if test="title != null">
        and title = #{title}
    if>
    <if test="author != null">
        and author = #{author}
    if>
sql>


<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <include refid="if-title-author">include>
    where>
select>

最好基于单表定义SQL片段

不设置where标签

6. foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list" open="ID in (" separator="," close=")" nullable="true">
          #{item}
    foreach>
  where>
select>

接口

List<Blog> queryBlogForeach(Map map);

xml

<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 = MyBatisUntils.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)元器件数据手册、IC替代型号，打造电子元器件IC百科大全！

学习博客：【MyBatis】动态SQL

相关文章