Mybatis知识小汇（11）——动态sql

动态sql

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

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

搭建环境

sql

 create table blog(id varchar(30) not null comment '博客id',
                   title varchar(100) not null comment '博客标题',
                   author varchar(30) not null comment '博客作者',
                   create_time datetime not null comment '创建时间',
                   views int  not null comment '浏览量'
                  )engine=innodb default charset=utf8;

其余(MybatisUtils,mybatis-config.xml)步骤可参考前面的博客进行搭建

实体类

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

补充：

1. 在utils包中

   public class IDUtil {
       //自动生成id
       public static String getId(){
           return UUID.randomUUID().toString().replace("-", "");
       }
   }
   

2. mybatis-config.xml

   <settings>
   <!--        支持改为驼峰  数据库一般用_连接，但在实体类中要用驼峰命名-->
           <setting name="mapUnderscoreToCamelCase" value="true"/>
       </settings>
   

if

传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形，当都传入时，都匹配

BlogMapper

public interface BlogMapper {
    //查询
    List<Blog> queryByIf(Map map);
}

BlogMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wjq.dao.BlogMapper">
    <select id="queryByIf" parameterType="map" resultType="blog">
        <!--此处的where下面会解决-->
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>
</mapper>

test

public class BlogMapperTest {
    @Test
    public void queryByIf(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "美食");
        map.put("author", "aaa");
        List<Blog> blogs = mapper.queryByIf(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }
}

choose、when、otherwise

传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形,当都传入时，根据choose中的匹配，只能选择一个

先补充： where元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

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

@Test
public void queryByChoose(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    //只会匹配一个，即title
    map.put("views", 2000);
    map.put("title", "美食");
    map.put("author", "aaa");
    List<Blog> blogs = mapper.queryByChoose(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

trim、where、set

set

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

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

test

@Test
public void updateBlogBySet(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    map.put("author", "李四");
    map.put("id", "196f87d37afa4f82a253f06ec0ea3d42");
    mapper.updateBlogBySet(map);
    sqlSession.close();
}

trim

来看看与 set 元素等价的自定义 trim 元素吧：

<!-- 自定义了前缀值     覆盖了后缀值设置，即去掉","-->
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。

sql片段

将一些重复的部分提取出来实现复用

<!--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="queryByIf" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <!--对sql片段的引用-->
    <include refid="if-title-author"></include>
</select>

注意事项：

1. 最好基于单表来定义sql片段
2. 最好不要存在where标签

foreach

BlogMapper

List<Blog> queryByForeach(Map map);

BlogMapper.xml

<!--    select * from blog where id in (1,2,3);-->
<select id="queryByForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="id in (" separator="," close=")">
            #{id}
        </foreach>
    </where>
</select>

test

@Test
public void queryByForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<String> list = new ArrayList();
    list.add("1");
    list.add("2");
    list.add("3");
    map.put("ids", list);
    List<Blog> blogs = mapper.queryByForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

建议：先写好完整的sql语句，在写动态的sql！
上一节—>复杂查询环境搭建 ( 一对多处理)

如有不对的地方欢迎指出，共同进步！