SpringBoot入门建站全系列(四)Mybatis使用进阶篇:动态SQL与分页
上一篇介绍了Mybatis的配置和基本用法《SpringBoot入门建站全系列(三)Mybatis操作数据库》
这一篇在此基础上进阶使用mybatis。
所以,这里就不说怎么怎么配置了,直接写mybatis的写法,至于调用,自己用service调就可以了。
这里的sql都是面向mysql的哈,oracle用户要适当修改sql。
品茗IT 提供在线支持:
如果大家正在寻找一个java的学习环境,或者在开发中遇到困难,可以加入我们的java学习圈,点击即可加入,共同学习,节约学习时间,减少很多在学习中遇到的难题。
一、注解版
基本上包含了所有动态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技术吧!