Mybatis（5）---动态sql

SSM

发布日期: 2020-10-27

动态sql

（1）if

满足条件拼接语句，不满足不拼接

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null and title !=''">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null and author.name != ''">
    AND author_name like #{author.name}
  </if>
</select>

（2）choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

（3）where

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

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

（4）trim

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

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

trim是更灵活的去处多余关键字的标签，他可以实践where和set的效果。

trim代替where

<!-- 5.1if/trim代替where(判断参数) -将实体类不为空的属性作为where条件-->
<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity">
    SELECT ST.STUDENT_ID,
           ST.STUDENT_NAME,
           ST.STUDENT_SEX,
           ST.STUDENT_BIRTHDAY,
           ST.STUDENT_PHOTO,
           ST.CLASS_ID,
           ST.PLACE_ID
      FROM STUDENT_TBL ST
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="studentName !=null ">
            ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
        </if>
        <if test="studentSex != null and studentSex != '' ">
            AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}
        </if>
        <if test="studentBirthday != null ">
            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}
        </if>
        <if test="classId != null and classId!= '' ">
            AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}
        </if>
        <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">
            AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}
        </if>
        <if test="placeId != null and placeId != '' ">
            AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}
        </if>
        <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">
            AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
        </if>
        <if test="studentId != null and studentId != '' ">
            AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
        </if>
    </trim>
</select>

trim代替set

<!-- 5.2 if/trim代替set(判断参数) - 将实体类不为空的属性更新 -->
<update id="updateStudent_if_trim" parameterType="liming.student.manager.data.model.StudentEntity">
    UPDATE STUDENT_TBL
    <trim prefix="SET" suffixOverrides=",">
        <if test="studentName != null and studentName != '' ">
            STUDENT_TBL.STUDENT_NAME = #{studentName},
        </if>
        <if test="studentSex != null and studentSex != '' ">
            STUDENT_TBL.STUDENT_SEX = #{studentSex},
        </if>
        <if test="studentBirthday != null ">
            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
        </if>
        <if test="studentPhoto != null ">
            STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},
        </if>
        <if test="classId != '' ">
            STUDENT_TBL.CLASS_ID = #{classId},
        </if>
        <if test="placeId != '' ">
            STUDENT_TBL.PLACE_ID = #{placeId}
        </if>
    </trim>
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId}
</update>

（5）set

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

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

（6）foreach

使用@Param注解自定义keyName

List<UserList> getUserInfo(@Param("userName") List<String> userName);

 <select id="getUserInfo" resultType="com.test.UserList">
         SELECT
         *
         FROM user_info
         <where>
             <if test="userName!= null and userName.size() >0">
                 USERNAME IN
                 <foreach collection="userName" item="value" separator="," open="(" close=")">
                     #{value}
                 </foreach>
             </if>
          </where>
</select>

使用默认属性值list作为keyname

public List<User> selectByIds(List<Integer> userIds);

<select id="selectByIds" resultType="com.olive.pojo.User">
        select * from t_user where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
</select>

使用默认属性值array作为keyname

public List<User> selectByIds(int[] userIds);

<select id="selectByIds" resultType="com.olive.pojo.User">
    select * from t_user where id in
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

collection属性值类型为Map

List<UserList> getUserInfo(@Param("user") Map<String,String> user);

获取Map的键值对

多字段组合条件情况下，一定要注意书写格式：括号（）

SELECT * FROM user_info WHERE (USERNAME,AGE) IN (('张三','26'),('李四','58'),('王五','27'),......);

 <select id="getUserInfo" resultType="com.test.UserList">
     SELECT
     *
     FROM user_info
     where
     <if test="user!= null and user.size() >0">
         (USERNAME,AGE) IN
         <foreach collection="user.entrySet()" item="value" index="key" separator="," open="(" close=")">
             (#{key},#{value})
         </foreach>
     </if>
</select>

参数Map类型，只需要获取key值或者value值

key

 <select id="getUserInfo" resultType="com.test.UserList">
     SELECT
     *
     FROM user_info
     where
     <if test="user!= null and user.size() >0">
         (USERNAME) IN
         <foreach collection="user.keys" item="key"  separator="," open="(" close=")">
             #{key}
         </foreach>
     </if>
</select>

value

<select id="getUserInfo" resultType="com.test.UserList">
    SELECT
    *
    FROM user_info
    where
    <if test="user!= null and user.size() >0">
        (USERNAME) IN
        <foreach collection="user.values" item="value"  separator="," open="(" close=")">
            #{key}
        </foreach>
    </if>
</select>

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有 item，index，collection，open，separator，close。

item：集合中元素迭代时的别名，
index：集合中元素迭代时的索引
open：常用语where语句中，表示以什么开始，比如以’(‘开始
separator：表示在每次进行迭代时的分隔符，
close 常用语where语句中，表示以什么结束，

在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array .
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key.
可以使用@Param注解自定义keyName，即为collection属性值

提示你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

具体用法如下:

<insert id="insertBatch" parameterType="List">
    INSERT INTO TStudent(name,age)
        <foreach collection="list" item="item" index="index" open="(" close=")" separator="union all">
            SELECT #{item.name} as a, #{item.age} as b FROM DUAL
        </foreach>
</insert>

INSERT INTO users(username,PASSWORD,phone,adress) VALUES('aaa','123','123','123'),('bbb','123','123','123')

（7）script

要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:

@Update({"<script>",
    "update Author",
    "  <set>",
    "    <if test='username != null'>username=#{username},</if>",
    "    <if test='password != null'>password=#{password},</if>",
    "    <if test='email != null'>email=#{email},</if>",
    "    <if test='bio != null'>bio=#{bio}</if>",
    "  </set>",
    "where id=#{id}",
    "</script>"})
void updateAuthorValues(Author author);

（8）bind

bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

（9）sql

抽取可重用sql片段

<sql id="colums">
    id,${name},user_age,created_time
</sql>

<select id="findAll" resultType="com.rewind.datasbase.entity.UserEntity">
    select
    /* include标签: 根据 refid 引用抽取的sql;
    property标签: 可向 sql 标签传递属性，实现更强的扩展性，sql标签用${属性名}引用 */
    <include refid="colums">
        <property name="name" value="user_name"/>
    </include>
    from t_user
</select>

（10）selectKey

在insert语句中，在Oracle经常使用序列、在MySQL中使用函数来自动生成插入表的主键，而且需要方法能返回这个生成主键。使用myBatis的selectKey标签可以实现这个效果。下面例子，使用mysql数据库自定义函数 nextval('student')，用来生成一个key，并把他设置到传入的实体类中的studentId属性上。所以在执行完此方法后，边可以通过这个实体类获取生成的key。

<!-- 插入学生 自动主键-->
<insert id="createStudentAutoKey" parameterType="liming.student.manager.data.model.StudentEntity" keyProperty="studentId">
    <selectKey keyProperty="studentId" resultType="String" order="BEFORE">
        select nextval('student')
    </selectKey>
    INSERT INTO STUDENT_TBL(STUDENT_ID,
                            STUDENT_NAME,
                            STUDENT_SEX,
                            STUDENT_BIRTHDAY,
                            STUDENT_PHOTO,
                            CLASS_ID,
                            PLACE_ID)
    VALUES (#{studentId},
            #{studentName},
            #{studentSex},
            #{studentBirthday},
            #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},
            #{classId},
            #{placeId})
</insert>

调用接口方法，和获取自动生成key

StudentEntity entity = new StudentEntity();
entity.setStudentName("黎明你好");
entity.setStudentSex(1);
entity.setStudentBirthday(DateUtil.parse("1985-05-28"));
entity.setClassId("20000001");
entity.setPlaceId("70000001");
this.dynamicSqlMapper.createStudentAutoKey(entity);
System.out.println("新增学生ID: " + entity.getStudentId());

MyBatis

一、简单排序--冒泡排序

2020-10-28 算法

算法

MyBatis(3)---多表操作

2020-10-25 SSM

MyBatis