SpringBoot入门建站全系列（四）Mybatis使用进阶篇

作者: 逍遥天扬 | 来源:发表于2019-05-29 16:09 被阅读70次

SpringBoot入门建站全系列（四）Mybatis使用进阶篇:动态SQL与分页

上一篇介绍了Mybatis的配置和基本用法《SpringBoot入门建站全系列（三）Mybatis操作数据库》

这一篇在此基础上进阶使用mybatis。

所以，这里就不说怎么怎么配置了，直接写mybatis的写法，至于调用，自己用service调就可以了。

这里的sql都是面向mysql的哈，oracle用户要适当修改sql。

品茗IT-SpringBoot专题-同步发布

品茗IT 提供在线支持：

一键快速构建Spring项目工具

一键快速构建SpringBoot项目工具

一键快速构建SpringCloud项目工具

一站式Springboot项目生成

Mysql一键生成Mybatis注解Mapper

一、注解版

基本上包含了所有动态Sql。

package com.cff.springbootwork.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.cff.springbootwork.mybatis.domain.UserInfo;

@Mapper
public interface UserInfoDao {
    @Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "WHERE user_name = #{userName,jdbcType=VARCHAR}",
       "</script>"})
    UserInfo findByUserName(@Param("userName") String userName);
    
    @Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "WHERE mobile = #{mobile,jdbcType=VARCHAR}",
            "<if test='userType != null and userType != \"\" '> and user_type = #{userType, jdbcType=VARCHAR} </if>",
       "</script>"})
    List<UserInfo> testIfSql(@Param("mobile") String mobile,@Param("userType") String userType);
    
    @Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
             "     FROM user_info ",
             "    WHERE mobile IN (",
             "   <foreach collection = 'mobileList' item='mobileItem' index='index' separator=',' >",
             "      #{mobileItem}",
             "   </foreach>",
             "   )",
        "</script>"})
    List<UserInfo> testForeachSql(@Param("mobileList") List<String> mobile);
    
    @Update({
        "<script>",
            "   UPDATE user_info",
            "   SET ",
            "   <choose>",
            "   <when test='userType!=null'> user_type=#{user_type, jdbcType=VARCHAR} </when>",
            "   <otherwise> user_type='0000' </otherwise>",
            "   </choose>",
            "   WHERE user_name = #{userName, jdbcType=VARCHAR}",
        "</script>" })
    int testUpdateWhenSql(@Param("userName") String userName,@Param("userType") String userType);
    
    @Select({
        "<script>",
            "<bind name=\"tableName\" value=\"item.getIdentifyTable()\" />",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM ${tableName}",
            "WHERE mobile = #{item.mobile,jdbcType=VARCHAR}",
       "</script>"})
    public List<UserInfo> testBindSql(@Param("item") UserInfo userInfo);
    
    @Select({
        "<script>",
            "<bind name=\"startNum\" value=\"page*pageSize\" />",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "ORDER BY mobile ASC",
            "LIMIT #{pageSize} OFFSET #{startNum}",
       "</script>"})
    public List<UserInfo> testPageSql(@Param("page") int page, @Param("pageSize") int size);
    
    @Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "<trim prefix=\" where \" prefixOverrides=\"AND\">",
                "<if test='item.userType != null and item.userType != \"\" '> and user_type = #{item.userType, jdbcType=VARCHAR} </if>",
                "<if test='item.mobile != null and item.mobile != \"\" '> and mobile = #{item.mobile, jdbcType=VARCHAR} </if>",
            "</trim>",
       "</script>"})
    public List<UserInfo> testTrimSql(@Param("item") UserInfo userInfo);
    
    @Update({
        "<script>",
            "   UPDATE user_info",
            "   <set> ",
            "<if test='item.userType != null and item.userType != \"\" '>user_type = #{item.userType, jdbcType=VARCHAR}, </if>",
            "<if test='item.mobile != null and item.mobile != \"\" '> mobile = #{item.mobile, jdbcType=VARCHAR} </if>",
            "   </set>",
            "   WHERE user_name = #{item.userName, jdbcType=VARCHAR}",
        "</script>" })
    public int testSetSql(@Param("item") UserInfo userInfo);
}

1.1 if-test

下面是字符串类型的一种写法，判断userType是不是空，如果不为空，则加入and条件中。

if test的条件中使用的是属性名，如果传入是对象，要用：对象.属性名。

@Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "WHERE mobile = #{mobile,jdbcType=VARCHAR}",
            "<if test='userType != null and userType != \"\" '> and user_type = #{userType, jdbcType=VARCHAR} </if>",
       "</script>"})
    List<UserInfo> testIfSql(@Param("mobile") String mobile,@Param("userType") String userType);

如果参数类型是数值型。要注意：

"<if test='valid != null and valid  != 0 '> and valid = #{valid , jdbcType=INTEGER} </if>",

这个地方不能用valid != ""，因为这种写法会导致永远未true。

1.2 foreach

    @Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
             "     FROM user_info ",
             "    WHERE mobile IN (",
             "   <foreach collection = 'mobileList' item='mobileItem' index='index' separator=',' >",
             "      #{mobileItem}",
             "   </foreach>",
             "   )",
        "</script>"})
    List<UserInfo> testForeachSql(@Param("mobileList") List<String> mobile);

这里：

foreach的collection 指定的传入的参数list对象，默认名称是list。
foreach的item是遍历的每个对象，名称是自己写的，item指定的啥，下面就用啥。
index是用来指定用来访问迭代集合下标的名称。如：index="myIndex"，则#{myIndex}用来访问当前迭代的下标。下标从0开始。
open 将该属性指定的值添加到foreach迭代后拼出字符串的开始。如：拼凑in子语句的开始部分“(”。
close 将该属性指定的值添加到foreach迭代拼出字符串的结尾。如：拼凑in子语句的介绍部分"）"。
separator 用来分割foreach元素迭代的每个元素,这里用的逗号。

1.3 choose、when、otherwise

这三个标签是一起用的，跟if-elseif-else的含义一样，看清楚了，不是if-test。

这个没有多么要注意的地方，照着格式写就行。一般很少用。

@Update({
        "<script>",
            "   UPDATE user_info",
            "   SET ",
            "   <choose>",
            "   <when test='userType!=null'> user_type=#{user_type, jdbcType=VARCHAR} </when>",
            "   <otherwise> user_type='0000' </otherwise>",
            "   </choose>",
            "   WHERE user_name = #{userName, jdbcType=VARCHAR}",
        "</script>" })
    int testUpdateWhenSql(@Param("userName") String userName,@Param("userType") String userType);

1.4 bind

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文.

如果我们想使用简单的分表功能，这个元素是很重要的，可以动态指定表名；

而且我们在分页操作的时候也可以用它来计算offset。

    @Select({
        "<script>",
            "<bind name=\"tableName\" value=\"item.getIdentifyTable()\" />",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM ${tableName}",
            "WHERE mobile = #{item.mobile,jdbcType=VARCHAR}",
       "</script>"})
    public List<UserInfo> testBindSql(@Param("item") UserInfo userInfo);

这里：

${}符号：要知道#{} 的参数替换是发生在 DBMS 中，而$ {} 则发生在动态解析过程中。#是一般是preparedStatement中的参数。
bind 的value，这里用的是item.getIdentifyTable()，表示的是getIdentifyTable是对象的方法。如果想使用某个包的方法，要把它写成静态方法，然后使用包路径+类名+方法这样去调用。

1.5 分页排序

    @Select({
        "<script>",
            "<bind name=\"startNum\" value=\"page*pageSize\" />",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "ORDER BY mobile ASC",
            "LIMIT #{pageSize} OFFSET #{startNum}",
       "</script>"})
    public List<UserInfo> testPageSql(@Param("page") int page, @Param("pageSize") int size);

分页排序相对简单，排序只需要按照sql语句的ORDER BY写法来写即可。

分页的话，要用到LIMIT和OFFSET ，LIMIT是限制多少条数据，就是分页的大小。OFFSET是偏移量，就是页码和分页大小相乘即可（页码从0开始）。

这里是使用了bind这种写法去计算偏移量，完全是可以在程序中计算好了再传递过来，这样就不需要bind了。

1.6 trim

trim其实就是删掉前（prefixOverrides）后（suffixOverrides）指定的字符串。

比如我们有时候用if-test动态sql的时候，可能有时候前面的if-test条件判断为false，条件没加上，后面的if-test会多一个and，这时候就会有问题，trim正好解决这问题。

    @Select({
        "<script>",
            "SELECT ",
            "user_name as userName,passwd,name,mobile,valid, user_type as userType",
            "FROM user_info",
            "<trim prefix=\" where \" prefixOverrides=\"AND\">",
                "<if test='item.userType != null and item.userType != \"\" '> and user_type = #{item.userType, jdbcType=VARCHAR} </if>",
                "<if test='item.mobile != null and item.mobile != \"\" '> and mobile = #{item.mobile, jdbcType=VARCHAR} </if>",
            "</trim>",
       "</script>"})
    public List<UserInfo> testTrimSql(@Param("item") UserInfo userInfo);

1.7 set

就是用在update的。类似的用于动态更新语句的解决方案叫做 set。set 元素可以用于动态包含需要更新的列，而舍去其它的。

    @Update({
        "<script>",
            "   UPDATE user_info",
            "   <set> ",
            "<if test='item.userType != null and item.userType != \"\" '>user_type = #{item.userType, jdbcType=VARCHAR}, </if>",
            "<if test='item.mobile != null and item.mobile != \"\" '> mobile = #{item.mobile, jdbcType=VARCHAR} </if>",
            "   </set>",
            "   WHERE user_name = #{item.userName, jdbcType=VARCHAR}",
        "</script>" })
    public int testSetSql(@Param("item") UserInfo userInfo);

二、xml版

和注解版差不多，这里就不一一写出来了，请看代码：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.cff.springbootwork.mybatis.dao.UserInfoDao" >
    <resultMap id="BaseResultMap" type="com.cff.springbootwork.mybatis.domain.UserInfo" >
        <id property="user_name" column="userName" />
        <result column="passwd" jdbcType="VARCHAR" property="passwd" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="mobile" jdbcType="VARCHAR" property="mobile" />
        <result column="valid" jdbcType="INTEGER" property="valid" />
        <result column="user_type" jdbcType="VARCHAR" property="userType" />
    </resultMap>

    <select id="testIfSql" resultMap="BaseResultMap">
        SELECT * FROM user_info WHERE mobile = #{mobile}
         <if test='userType != null and userType != ""'>
            AND user_type = #{userType} 
         </if>
    </select>
    
    <select id="testForeachSql" resultMap="BaseResultMap">
        SELECT * FROM user_info WHERE mobile IN (
        <foreach item="item" index="index" collection="mobileList"
            open="(" separator="," close=")">
                #{item}
        </foreach>
    </select>
    
    <select id="testBindSql" resultMap="BaseResultMap">
        <bind name="tableName" value="item.getIdentifyTable()" />
        SELECT * FROM ${tableName} WHERE mobile = #{item.mobile}
    </select>
    
    <select id="testPageSql" resultMap="BaseResultMap">
        <bind name="startNum" value="page*pageSize" />
        SELECT * FROM user_info ORDER BY mobile ASC LIMIT #{pageSize} OFFSET #{startNum}
    </select>
    
    <select id="testTrimSql" resultMap="BaseResultMap">
        SELECT * FROM user_info 
        <trim prefix="WHERE" prefixOverrides="AND">
          <if test='item.userType != null and item.userType != ""'>
            AND user_type = #{item.userType} 
         </if>
         <if test='item.mobile != null and item.mobile != ""'>
            AND mobile = #{item.mobile} 
         </if>
        </trim>
    </select>

    <update id="testUpdateWhenSql">
      update user_info set
        <choose>
            <when test="userType!=null">
              user_type=#{user_type}
            </when>
            <otherwise>
              user_type='0000'
            </otherwise>
        </choose>
        WHERE user_name = #{userName}
    </update>
    
    <update id="testSetSql">
      update user_info set
        <set>
            <if test='item.userType != null and item.userType != ""'>
            user_type = #{item.userType}, 
            </if>
            <if test='item.mobile != null and item.mobile != ""'>
            mobile = #{item.mobile} 
            </if>
        </set>
      where user_name = #{item.userName}
    </update>
</mapper>

喜欢这篇文章么，喜欢就加入我们一起讨论SpringBoot技术吧！

品茗IT交流群

SpringBoot入门建站全系列（四）Mybatis使用进阶篇