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

原创

品茗IT

修改于 2019-08-05 11:42:15

1.1K0

修改于 2019-08-05 11:42:15

文章被收录于专栏：品茗IT品茗IT

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

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

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

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

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

一、注解版

基本上包含了所有动态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技术吧！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

javascript