MyBatis 03 动态SQL

众生云海，一念初见

已于 2022-04-26 12:28:06 修改

阅读量1.6k

点赞数 3

分类专栏： SSM MyBatis JavaWeb 文章标签： java mysql 后端 idea

于 2022-03-25 08:20:51 首次发布

本文链接：https://blog.csdn.net/qq_24431059/article/details/123726396

版权

JavaWeb 同时被 3 个专栏收录

23 篇文章 2 订阅

订阅专栏

SSM

13 篇文章 0 订阅

订阅专栏

MyBatis

4 篇文章 0 订阅

订阅专栏

MyBatis 03 动态SQL

文章目录

MyBatis 03 动态SQL

一、学习目标

1、了解MyBatis动态SQL应用场景
2、掌握MyBatis实现动态SQL的常用标签
3、熟练使用MyBatis动态SQL实现数据库查询和修改操作

二、动态SQL

一、MyBatis通过标签的配合使用，可实现如下功能
语句的动态拼接
前后缀格式处理
复杂参数处理
二、常用标签
if
where
choose
foreach
set
trim

if 标签2-1

问题： 查询出角色id值为2且用户姓名包含"赵"的用户信息
实践： 测试方法传入角色id参数为null的时候，为什么检索结果为空？

原因： 分析原因：
将控制台输出的SQL语句与参数拼接，会发现其中包含过滤语句 and u.roleId = null，表示查询要满足条件roleId = null的数据。显然不符合最初的需求。
select u.*,r.roleName from t_sys_user u, t_sys_role r
where u.roleId = r.id and u.roleId = null
and u.realName like concat('%','赵','%')
如何解决？
if标签

if 标签2-2

动态SQL技术中最常用的标签之一
类似于Java中的if语句

语法：

<if  test = "条件判断，返回true或false" >
	SQL语句
</if>

<!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
<resultMap type="SysUser" id="SysUserResult">
		<result property="id" column="id"/>
		<result property="account" column="account"/>
		<result property="realName" column="realName"/>
		<result property="phone" column="phone"/>
		<result property="birthday" column="birthday"/>
		<result property="sex" column="sex"/>
		<result property="roleId" column="roleId"/>
		<result property="userRoleName" column="roleName"/>
</resultMap>
<!-- 查询用户列表-关联角色表 -->
<select id="selectList" resultMap="SysUserResult">
	select u.*,r.roleName from t_sys_user u,t_sys_role r 
       where u.roleId = r.id
	<if test="roleId != null">
		and u.roleId = #{roleId}
	</if>
	<if test="realName != null and realName != ''">
		and u.realName like concat ('%',#{realName},'%') 
	</if>
</select>

where标签2-1

问题： 不考虑关联查询角色名称，根据角色id和用户姓名查询用户测试方法只传入用户姓名，不传入角色id参数时，控制台为什么会报SQL异常错误？

原因：
select * from t_sys_user where and u.realName like concat('%','赵','%');
与之前的代码相比，此次查询将多表关联查询改为单表查询，删除了用于消除笛卡尔积的过滤语句u.roleId = r.id，并根据MySQL语法规则去掉了where关键字后第一个过滤语句前的 and 关键字

演示案例3中存在的问题描述：
①若只传入一个参数：realName，控制台报SQL异常错误
查看日志中SQL语句：
select * from t_sys_user where and u.realName like concat('%','赵','%')
②若不传入任何参数，控制台报SQL异常错误
查看日志中SQL语句：
select * from t_sys_user where
如何解决？
where标签

where 标签2-2

作用： 简化SQL语句中where子句处理，智能处理and、or等关键字

语法：

<where>
	<if test="条件判断">
		SQL语句
	</if>
	…
</where>

<!--简单改造getUserList：
若 只传入一个参数：roleId，而不传入参数：realName的时候，控制台报sql异常错误
 查看日志中sql语句：select * from t_sys_user where and roleId = ?
 若 不传入任何参数：控制台报sql异常错误
 查看日志中sql语句：select * from t_sys_user where-->
 <select id="selectList" resultType="SysUser">
		select * from t_sys_user where
		<if test="roleId != null">
			roleId = #{roleId}
		</if>
		<if test="realName != null and realName != ''">
			and realName like CONCAT ('%',#{realName},'%')
		</if>
</select>

<!--where标签的作用演示 -->
<select id="selectList" resultType="SysUser">
     select * from t_sys_user
        <where>
            <if test="roleId != null">
                and roleId = #{roleId}
            </if>
            <if test="realName != null and realName != ''">
                and realName like CONCAT ('%',#{realName},'%')
            </if>
        </where>
   </select>

choose(when、otherwise)标签

是一个组合标签，通常与when、otherwise标签配合使用，类似于Java中switch语句

/**
 * 查询用户列表(choose)
 * @param realName
 * @param roleId
 * @param account
 * @param createdTime
 * @return
*/
 public List<SysUser> selectListByChoose(
     @Param("realName")String realName,
        @Param("roleId")Integer roleId,
        @Param("account")String account,
        @Param("createdTime")Date createdTime);

<!-- 查询用户列表(choose) -->
<select id="selectListByChoose" resultType="SysUser">
		select * from t_sys_user
		<where>
			<choose>
				<when test="realName != null and realName != ''">
					and realName like CONCAT ('%',#{realName},'%')
				</when>
				<when test="roleId != null">
					and roleId=#{roleId}
				</when>
				<when test="account != null and account != ''">
					and account like CONCAT ('%',#{account},'%')
				</when>
				<otherwise>
					<!-- and YEAR(createdTime) = YEAR(NOW()) -->
					and YEAR(createdTime) = YEAR(#{createdTime})
				</otherwise>
			</choose>
		</where>
</select>

foreach标签4-1

   /**
 * 根据角色id 数组 查询用户列表信息
 * @param roleIds
 * @return
 */
public List<SysUser> getUserByRoleIdArray(Integer[] roleIds);

/**
 * 根据角色id 集合 查询用户列表信息
 * @param roleList
 * @return
 */
public List<SysUser> getUserByRoleIdList(List<Integer> roleList);

/**
 * 根据角色id 集合 查询用户列表信息(集合存在Map中)
 * @param roleMap
 * @return
 */
public List<SysUser> getUserByRoleIdMap(Map<String,Object> roleMap);

<?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="cn.cvs.dao.sysUser.SysUserMapper">
	<!-- 根据角色id 数组 查询用户列表信息 -->
	<select id="getUserByRoleIdArray" resultType="SysUser">
		select * from t_sys_user where roleId in
			<foreach collection="array" item="item" 
                     open="(" separator="," close=")">
				#{item}
			</foreach>
	</select>

	<!-- 根据角色id 集合 查询用户列表信息 -->
	<select id="getUserByRoleIdList" resultType="SysUser">
		select * from t_sys_user where roleId in
			<foreach collection="list" item="item" 
                     open="(" separator="," close=")">
				#{item}
			</foreach>
	</select>

	<!-- 根据角色id 集合 查询用户列表信息(集合存在Map中) -->
	<select id="getUserByRoleIdMap" resultType="SysUser">
		select * from t_sys_user where roleId in
		<foreach collection="roleIdList" item="item" 
                 open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<!-- 根据用户角色列表和性别(多参数)，获取该角色列表下并指定性别的用户列表信息-参数类型为Map -->
	<select id="getUserByRoleIdSet" resultType="SysUser">
		select * from t_sys_user where roleId in
		<foreach collection="roleIdSet" item="item" 
                 open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
</mapper>

set标签2-1

要求： 更新用户id为16的用户的真实姓名，其他用户属性保持不变

/**
* 修改用户信息
* @param sysUser
* @return
*/
public int update(SysUser sysUser);

<!-- 修改用户信息 -->
<update id="update" parameterType="SysUser">
	update t_sys_user set account=#{account}, realName=#{realName}, password=#{password}, sex=#{sex}, birthday=#{birthday}, phone=#{phone}, address=#{address}, roleId=#{roleId}, updatedUserId=#{updatedUserId}, updatedTime=#{updatedTime}
    where id = #{id}
</update>

package cn.cvs.dao.sysUser;

import cn.cvs.pojo.SysUser;
import cn.cvs.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Before;
import org.junit.Test;

import java.text.SimpleDateFormat;
import java.util.*;

public class SysUserMapperTest {
	
	private Logger logger = Logger.getLogger(SysUserMapperTest.class);
    
	@Test
	public void testUpdate(){
		SqlSession sqlSession = null;
		Integer userId = 16;
		int count = 0;
		try {
			SysUser user = new SysUser();
			user.setId(userId);
			user.setRealName("测试用户修改");
			user.setUpdatedUserId(1);
			user.setUpdatedTime(new Date());
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(SysUserMapper.class).update(user);
			sqlSession.commit();
		} catch (Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.info("修改Id=" + userId + "的用户修改" + (count== 0?"失败":"成功"));
	}
}

问题：更新用户表数据时，除重新赋值的属性外，其他属性的值都更新为null

分析： 分析SQL语句
如何解决？ set标签、 if标签

set标签2-2

作用： 简化SQL语句中set子句处理，智能忽略更新语句尾部多出来的逗号

语法：

<set>
	<if test="条件判断">
		SQL语句
	</if>
	…
</set>

<!-- 修改用户信息-使用set标签 -->
<update id="update" parameterType="SysUser">
		update t_sys_user
		<set>
			<if test="account != null">account=#{account},</if>
			<if test="realName != null">realName=#{realName},</if>
			<if test="password != null">password=#{password},</if>
			<if test="sex != null">sex=#{sex},</if>
			<if test="birthday != null">birthday=#{birthday},</if>
			<if test="phone != null">phone=#{phone},</if>
			<if test="address != null">address=#{address},</if>
			<if test="roleId != null">roleId=#{roleId},</if>
			<if test="updatedUserId != null">updatedUserId=#{updatedUserId},</if>
			<if test="updatedTime != null">updatedTime=#{updatedTime},</if>
		</set>
		where id = #{id}
</update>

trim标签3-1

作用： 动态地为SQL语句添加前后缀，智能忽略标签前后多余的and、or或逗号等字符
<trim prefix = "前缀"  suffix = "后缀"  
   prefixOverrides = "忽略前缀" suffixOverrides = "忽略后缀" >
   	…
</trim>

trim标签3-2

示例： 使用trim标签替换前面示例中的where标签,实现示例中相同的功能

<!--trim标签替换where -->
<select id="selectList" resultType="SysUser">
		select * from t_sys_user
		<trim prefix="where" prefixOverrides="and|or" >
			<if test="roleId != null">
				and roleId = #{roleId}
			</if>
			<if test="realName != null and realName != ''">
				and realName like CONCAT ('%',#{realName},'%')
			</if>
		</trim>
</select>

trim标签3-3

示例： 使用trim标签替换前面示例中的set标签,实现示例中相同的功能

<!--trim标签替换where -->
<update id="update" parameterType="SysUser">
		update t_sys_user
		<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
			<if test="account != null">account=#{account},</if>
			<if test="realName != null">realName=#{realName},</if>
			<if test="password != null">password=#{password},</if>
			<if test="sex != null">sex=#{sex},</if>
			<if test="birthday != null">birthday=#{birthday},</if>
			<if test="phone != null">phone=#{phone},</if>
			<if test="address != null">address=#{address},</if>
			<if test="roleId != null">roleId=#{roleId},</if>
			<if test="updatedUserId != null">updatedUserId=#{updatedUserId},</if>
			<if test="updatedTime != null">updatedTime=#{updatedTime},</if>
		</trim>
</update>

三、MyBatis 简单版分页功能实现

需求： 查询用户列表增加分页实现

DAO层 limit(起始位置，页面容量)
查询用户列表方法增加两个参数
pageIndex
pageSize

/**
 * 查询用户表记录数
 * @return
*/
 public int selectCount(@Param("realName")String realName,
					 @Param("roleId")Integer roleId);

/**
 * 分页查询用户列表
 * @param realName
 * @param roleId
 * @return
*/
 public List<SysUser> selectPageList(@Param("realName")String realName,
								 @Param("roleId")Integer roleId,
								 @Param("pageIndex")Integer pageIndex,
								 @Param("pageSize")Integer pageSize);

<!-- 查询分页数据 -->
<select id="selectPageList" resultType="SysUser">
		select * from t_sys_user
		<trim prefix="where" prefixOverrides="and |or" >
			<if test="realName != null and realName != ''">
				and realName like concat ('%',#{realName},'%')
			</if>
			<if test="roleId != null">
				and roleId = #{roleId}
			</if>
		</trim>
		order by createdTime desc
		limit #{pageIndex}, #{pageSize}
</select>

@Test
public void selectPageList(){
		SqlSession sqlSession = null;
		List<SysUser> userList = null;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			String realName = "";
			Integer roleId = 2;
			Integer pageIndex = 1;
			Integer pageSize = 5;
			pageIndex = (pageIndex - 1) * pageSize;	//计算查询起始位置
          //查询分页数据
   			userList = sqlSession.getMapper(SysUserMapper.class)
					.selectPageList(realName,roleId,pageIndex,pageSize);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.info("查询到用户数量：" + userList.size());
		for(SysUser user: userList){
			logger.info("查询到用户信息：" + user);
		}
}

四、MyBatis分页PageHelper

原文引用：https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md

第一步：添加Maven依赖,在 pom.xml 中添加如下依赖

 <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.3.0</version>
</dependency>

第二步：在mybatis-config.xml核心配置文件中配置拦截器插件（注意节点顺序）

<!-- 
    plugins在配置文件中的位置必须符合要求，否则会报错，顺序如下:
    properties?, settings?, 
    typeAliases?, typeHandlers?, 
    objectFactory?,objectWrapperFactory?, 
    plugins?, 
    environments?, databaseIdProvider?, mappers?
-->
<plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
            <property name="param1" value="value1"/>
        </plugin>
 </plugins>

第三步：定义mapper接口

List<Supplier> findByPage(@Param("supName")String supName);

第四步：mapper接口对应的配置文件中定义select查询

<select id="findByPage" resultType="com.aiden.pojo.Supplier">
	select * from t_supplier 
    <where>
        <if text="name !=null and name!=''">
        	SupName LIKE CONCAT('%',#{supName},'%')
        </if>
    </where>
</select>

第五步：测试调用 Mapper接口方式的调用，推荐这种使用方式

@Test
public void testPage() {
    SqlSession sqlSession = null;
    try {
        sqlSession = MyBatisUtils.getSqlSession();
        SupplierMapper supplierMapper = sqlSession.getMapper(SupplierMapper.class);
        Page<Supplier> page = PageHelper.startPage(1, 2);
        supplierMapper.findByPage("科泰");
        List<Supplier> supplierList = page.getResult();
        System.out.println("当前页：" + page.getPageNum());
        System.out.println("总页数" + page.getPages());
        System.out.println("总条数：" + page.getTotal());
        System.out.println("分页数据列表：");
        supplierList.forEach(s -> {
            System.out.println(s.getId() + "\t" + s.getSupCode() + "\t" + s.getSupName());
        });
    } catch (Exception exception) {
        exception.printStackTrace();
    } finally {
        MyBatisUtils.closeSqlSession(sqlSession);
    }
    
}