动态SQL

一、环境准备

表blog

实体类

（使用了Lombok）

package com.zy.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;
 ​
 }

 

jdbc.properties

driver=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/testmybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
 username=root
 password=123456

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核心配置文件-->
 <configuration>
     
     <properties resource="jdbc.properties"/>
     
     <settings>
         <setting name="logImpl" value="STDOUT_LOGGING"/>
         <!--是否开启自动驼峰命名规则(camel case)映射-->
         <setting name="mapUnderscoreToCamelCase" value="true"/>
     </settings>
 ​
     <typeAliases>
         <!-- 方法1、定义一个alias别名，缺点在于需要一个实体类分别指定
         <typeAlias type="com.zy.pojo.User" alias="user" />-->
         <!-- 方法2、也可以使用package来给某个包下面的所有实体类自动创建别名，
         自动创建的别名规则是类的类名并将首字母改为小写 -->
         <package name="com.zy.pojo"/>
     </typeAliases>
 ​
     <!--配置环境-->
     <environments default="mysql">
         <!--有多个环境时，通过修改default="id"来实现选择-->
 ​
         <environment id="mysql">
             <!--JDBC事务管理-->
             <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>
 ​
     <!--每一个Mapper.XML都需要Mybatis核心配置文件中注册-->
     <!--如果文件放在resources下，则这么写-->
     <mappers>
         <mapper resource="mapper/BlogMapper.xml"></mapper>
     </mappers>
 ​
 </configuration>

 

 

二、< where >

当查询时，select * from xxx where id=#{id}

如果出现多条件时，比如 select * from xxx where id=#{id} and name=#{name}

但是当 id 或者 name 未知的情况下，就没有办法实现查询

所以使用动态SQL中的 < where > 进行查询

 

< if >

BlogMapper

package com.zy.mapper;

import com.zy.pojo.Blog;

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

public interface BlogMapper {

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

}

BlogMapper.xml：

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

测试类

@Test
     public void queryBlogIF() {
 ​
         SqlSession sqlSession = MybatisUtils.getSqlSession();
         BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 ​
         HashMap map = new HashMap();
         map.put("title","45325");
         map.put("author","efg");
       
         List<Blog> blogs = blogMapper.queryBlogIF(map);
 ​
         for (Blog blog : blogs) {
             System.out.println(blog);
         }
 ​
     }

 

结果

 

如果不添加任何元素进入map

@Test
     public void queryBlogIF() {
     
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 ​
     HashMap map = new HashMap();
   
     List<Blog> blogs = blogMapper.queryBlogIF(map);
 ​
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
 ​
 }

结果

 

< choose >

BlogMapper

package com.zy.mapper;
 ​
 import com.zy.pojo.Blog;
 ​
 import java.util.List;
 import java.util.Map;
 ​
 public interface BlogMapper {
 ​
     List<Blog> queryBlogChoose(Map map);
 ​
 }

BlogMapper.xml

<select id="queryBlogChoose" parameterType="map" resultType="blog">
     select * from 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 = MybatisUtils.getSqlSession();
     BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 ​
     HashMap map = new HashMap();
     map.put("title","45325");
     map.put("author","efg");
     map.put("views","322");
     
     List<Blog> blogs = blogMapper.queryBlogChoose(map);
    
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
 ​
 }

结果

 

存在多个成立的条件

比如：这个sql中，不使用choose的话会查出3条记录

但只取choose中的第一个

 

使用 < trim >代替 < where > + SQL片段使用

BlogMapper.xml

<sql id="sql-title-author-views">
     <if test="title != null">
         title = #{title}
     </if>
     <if test="author != null">
         AND author = #{author}
     </if>
     <if test="views != null">
         AND views = #{views}
     </if>
 </sql>
 <select id="queryBlogIF" parameterType="map" resultType="blog">
     select * from blog
     <trim prefix="where" prefixOverrides="AND">
         <include refid="sql-title-author-views"></include>
     </trim>
 </select>

 

< set >

BlogMapper

package com.zy.mapper;
 ​
 import com.zy.pojo.Blog;
 ​
 import java.util.List;
 import java.util.Map;
 ​
 public interface BlogMapper {
 ​
     //更新博客
     int updateBlog(Map map);
 ​
 }

BlogMapper.xml

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

测试类

@Test
 public void queryBlogSet() {
 ​
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 ​
     HashMap map = new HashMap();
     map.put("title","6546");
     map.put("author","fasfd");
     map.put("views","6243");
     map.put("id","d4303d0f-43f2-422e-8dbf-9a61ad872ff9");
 ​
     blogMapper.updateBlog(map);
 ​
     List<Blog> blogs = blogMapper.queryBlogIF(map);
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
 ​
 }

原本的数据库

更新后

 

 

使用 < trim >代替 < set >

BlogMapper.xml

<update id="updateBlogSet" parameterType="map">
     update blog
     <trim prefix="set" suffixOverrides=",">
         <if test="title != null">
             title = #{title},
         </if>
         <if test="author != null">
             author = #{author},
         </if>
         <if test="views != null">
             views = #{views},
         </if>
     </trim>
     where id = #{id}
 </update>

SQL片段

有时候，可以将一些功能相同的部分抽出，方便使用

抽取例子：

抽取前

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

抽取后

<sql id="if-title-author-views">
     <if test="title != null">
         title = #{title}
     </if>
     <if test="author != null">
         and author = #{author}
     </if>
     <if test="views != null">
         and views = #{views}
     </if>
 </sql>
 ​
 <!--where标签 if-->
 <select id="queryBlogIF" parameterType="map" resultType="blog">
     select * from blog
     <where>
         <include refid="if-title-author-views"></include>
     </where>
 </select>

测试

依旧能正常查出数据

注意点

　　1.最好基于单表定义SQL片段，尽量不要太复杂

　　2.不要存在 < where >

 

< ForEach >

对集合进行遍历使用 ForEach

对 blog表 的 id 修改一下

BlogMapper

 package com.zy.mapper;
 ​
 import com.zy.pojo.Blog;
 ​
 import java.util.List;
 import java.util.Map;
 ​
 public interface BlogMapper {
 ​
     //查询1-2-3号记录的博客
     List<Blog> queryBlogForeach(Map map);
 ​
 }

BlogMapper.xml

<select id="queryBlogForeach" parameterType="map" resultType="blog">
         select * from blog
         <trim prefix="where" prefixOverrides="AND">
             <foreach collection="ids" item="id" open="AND (" close=")" separator="or">
                 id = #{id}
             </foreach>
         </trim>
     </select>

测试类

@Test
 public void queryBlogForEach(){
 ​
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 ​
     HashMap map = new HashMap();
 ​
     ArrayList<Integer> ids = new ArrayList<Integer>();
 ​
     map.put("ids",ids);
 ​
     List<Blog> blogs = blogMapper.queryBlogForeach(map);
 ​
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
 ​
     sqlSession.close();
 }

结果

 

通过给ArrayList添加元素来实现想要的元素查询

比如想查 id 为1，2，3的

@Test
 public void queryBlogForEach(){
 ​
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper blogMapper = 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 = blogMapper.queryBlogForeach(map);
 ​
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
 ​
     sqlSession.close();
 }

结果