mybatis

每日格言

无所畏惧,坚持到底,决不放弃。干兄弟们

动态sql

什么是动态sql,动态sql就是根据不同的条件产生不同的sql语句

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。

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

搭建环境

CREATE TABLE `mybatis`.`blog`  (
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `title` VARCHAR(30) NOT NULL COMMENT '博客标题',
  `author` VARCHAR(30) NOT NULL COMMENT '博客作者',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `views` INT(30) NOT NULL COMMENT '浏览量',
  PRIMARY KEY (`id`)
)

创建一个基础工程

  1. 导包

  2. 编写配置文件

    <?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>
    <!--    外部配置文件-->
        <properties resource="com/hyc/dao/db.properties"/>
    <!--    可以给实体类起别名-->
        <settings>
            <setting name="logImpl" value="LOG4J"/>
        </settings>
        <typeAliases>
    <!--        <typeAlias type="com.hyc.pojo.user" alias="user"/>-->
    <package name="com.hyc.pojo"/>
        </typeAliases>
        <environments default="development">
            <environment id="development">
                <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 resource="com/hyc/dao/BlogMapper.xml"/>
    
        </mappers>
    </configuration>
    
  3. 编写实体类

    ​ 注意编写日期要用 util包下的date

    package com.hyc.pojo;
    
    import lombok.Data;
    
    import java.util.Date;
    @Data
    public class Blog {
         
        private int id;
        private String title;
        private String author;
        private Date createTime;
        private  int views;
    }
    
    
  4. 编写实体类对应的Mapper接口和对应的Mapper.xml文件

if

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

choose

    <select id="queryBlogChoose" resultType="blog" parameterType="map">

        select * from blog
      <where>
          <choose>
              <when test="title != null">
                  and title = #{
   title}
              </when>
              <when test="author != null">
                  and  author = #{
   author}
              </when>
        <otherwise>
            and  views = #{
   views}
        </otherwise>
          </choose>
      </where>

    </select>

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 blog
        <set>
            <if test="title != null">
             title = #{title},
             </if>
             <if test="author != null">
                 author = #{author}
             </if>
        </set>
        where id = #{id}
    </update>

所谓的动态sqL,本质上就是sql语句,只是我们可以在sql层面,去执行一个逻辑代码

sql片段

有的时候,我们可能会将一些公共的部分抽取出来,方便使用

通过include标签的refid属性来调用sql片段

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

设置id来让sql片段可以被调用,

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

注意事项:

  • 最好基于单表来定义sql片段
  • 最好不在sql片段存在where

Foreach

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

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

<!-- 就是拼接sql-->
<!-- select * from blog where 1=1 and (id = 1 or id=2 or id=3) -->
    <select id="queryBlogForeach" resultType="blog" parameterType="map">
        select  * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

建议:

  • 现在mysql写出完整的sql,再对应的修改我们的动态sql再使用,