一、处理简单多参数查询
MyBatis框架主要通过标签的配合使用实现SQL语句的动态拼接、前后缀格式化处理、复杂参数处理功能
1.1 什么是动态SQL
动态SQL就是指根据不同的条件生成不同的SQL语句
作用:动态 SQL,可以彻底摆脱不同条件拼接 SQL 语句这种痛苦。
1.2 动态SQL的常用标签
①if:条件判断,与java中的 if 语句类似
②where:为SQL语句动态添加where关键字
③choose:条件判断,这是一个组合标签,需要与when、otherwise标签搭配
使用。可实现与Java中的switch语句类似的功能
④foreach:以遍历方式处理集合类型的参数
⑤set:为SQL语句动态添加set关键字,实现动态实现数据更新功能
⑥trim:为SQL语句进行格式化处理,添加或移除前后缀
1.2.1 if 标签(动态拼接功能)
动态SQL技术重要切日常的标签之一,与 Java中的 if 语句基本相同,用法相似
语法:
< if test="条件判断,返回true或false">
SQL语句……
</if>
代码: MyBatis框架把所有的SQL语句操作都放在了配置文件中,使代码更加清晰,便于维护
<!--在 mapper.SysUserMapper3 名字域中,mybatis-config.xml文件中-->
<mapper namespace="mapper.SysUserMapper3">
<select id="selectList" resultType="sysuser">
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>
</mapper>
1.2.2 where标签【所有的查询条件都放在where标签中】
where标签的主要作用是对SQL语句中的where关键字进行简化处理,并可以
智能地处理其内部and、or等关键字,避免多余字符带来的语法错误。
把SQL语句中的where关键字替换为where标签,并将过滤条件放置于标签
内部即可
语法:
<where>
< if test="条件判断,返回true或false">
SQL语句
</if>
……
</where>
代码:where可以自动剔除标签内多余的and关键字,并可以在没有过滤条件时忽略where关键字,从而简化SQL语句并智能处理where、and、or关键字的作用
<mapper namespace="mapper.SysUserMapper3">
<select id="selectList" resultType="sysuser">
select * from t_sys_user
<where>
<if test="realName != null and realName != '' ">
realName like concat('%',#{realName},'%')
</if>
<if test="roleId != null">
and roleId = #{roleId}
</if>
</where>
</select>
</mapper>
1.2.3 choose(when、otherwise)标签
choose标签是一个组合标签,通常与when、otherwise标签配合使用,实现类似switch语句的功能。只会进入test属性判断为true的第一个when标签,执行之后便会跳出choose标签,如果所有的test属性判断都为false,则进入otherwise
语法:
<choose>
<when test="条件判断,返回true或false">
< if test="条件判断,返回true或false">
SQL语句
</if>
……
</where></choose>
结论:
1、choose(when、otherwise)是一个组合标签,when和otherwise标签写在choose标签中
2、当when标签中的test属性判断为true,就会执行它所包含的语句
3、choose中的多个when标签指挥执行匹配成功的第一个,执行后跳出choose标签
4、当所有的when标签中的test属性判断为false时,进入otherwise标签
代码:
<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(#{createdTime})
</otherwise>
</choose>
</where>
</select>
2.1 MyBatis框架动态SQL处理集合参数
MySql通过in语句来处理多个值的参数。
如:select * from t_sys_user where roleId in (1,2);
语法:
<foreach collection="参数名称" item="元素别名" open="(" separactor="," close=")" index="当前元素位置下标">
#{元素别名}
</foreach>
属性介绍:
①item:遍历数组时,为数组或List集合中的元素起的别名
②open:起始位置的拼接字符,表示 in 语句以“(”左括号开始
③close:结束位置的拼接字符,表示 in 语句以“)”右括号结束
④separator:元素直接的连接符,表示 in 语句中的元素之间以“,”逗号连接
⑤collection:参数名称。当参数为数组类型时,默认参数名为array。当参数
类型为List集合时,默认参数名为list。当参数类型为Map时,参数名为Map中
集合元素所在键值对的key
1、foreach标签处理数组类型参数
<select id="getUserByRoleIdArray" resultType="sysuser">
select * from t_sys_user where roleId in
<foreach collection="array" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
2、foreach标签处理List类型参数
<select id="getUserByRoleIdList" resultType="sysuser">
select * from t_sys_user where roleId in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
3、foreach标签处理Map类型参数
<select id="getUserByRoleIdMap" resultType="pojo.SysUser">
select * from t_sys_user where roleId in
<foreach collection="roleIdList" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
3.1 MyBatis框架动态SQL处理集合参数
【MyBatis动态更新数据的主要功能主要通过set+if标签实现】
3.1.1 set标签
作用:主要用于数据的更新操作,if标签则用于控制更新的字段,与where标签
非常相似,在包裹的语句之前拼接一个set关键字,并能忽略更新语句尾部多余
出来的逗号。配合 if 标签灵活使用set标签,就可以在拼接SQL时忽略不需要
修改的字段,从而实现数据部分更新的功能
语法:
<set>
< if test="条件判断,返回true或false">
SQL语句
</if>
</set>
代码:(与trim区别:后面还有where关键字)
<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>
4.1 MyBatis框架动态SQL知识扩展
4.1.1 trim标签
动态的添加、删除SQL语句的前后缀
语法:
<trim prefix="前缀" suffix="后缀" prefixOverrides="忽略前缀" suffixOverrides="忽略后缀">
……
</trim>
属性:
①prefix:前缀,可以自动对trim标签所包含的语句是否有返回值进行判断。
如果有返回值,则为SQL语句拼接相应前缀。
②suffix:后缀,在trim标签包含的语句末尾拼接后缀。
③prefixOverrides:忽略的前缀,忽略trim标签内部首部指定的内容
④suffixOverrides:忽略的后缀,忽略trim标签包含内容尾部指定的内容
代码:
【与set区别,where写在trim后缀(suffix)中,会自动在后面给SQL语句添加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>
5.1 MyBatis框架的分页功能
5.1.1 基于MySQL的分页查询
MySQL数据库分页关键字为limit
开始位置=(当前页面-1)*每页的数据量:
【分页计算:pageBegin=(pageIndex-1)*pageSize 】
mapper接口的方法:
package mapper;
import org.apache.ibatis.annotations.Param;
import pojo.Supplier;
import pojo.SysRole;
import pojo.SysUser;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* SysUserMapper3
*
* @version 2023/9/22
*/
public interface SysUserMapper3 {
/**
* 分页查询用户列表
* @param realName
* @param roleId
* @param pageBegin
* @param pageSize
* @return
*/
List<SysUser> selectPageList(@Param("realName")String realName,
@Param("roleId")Integer roleId,
@Param("pageBegin")Integer pageBegin,
@Param("pageSize")Integer pageSize);
}
对应配置文件【SysUserMapper3.xml】的代码:
<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">
and roleId = #{roleId}
</if>
</trim>
order by createdTime desc limit #{pageBegin},#{pageSize}
</select>
测试方法:
package mapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import pojo.SysRole;
import pojo.SysUser;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class SysUserMapper3Test{
// 记录日志
private Logger logger = Logger.getLogger(SysUserMapper3Test.class);
SqlSession sqlSession = null;
/**
* 在执行测试之前执行的代码
* @throws Exception
*/
@Before
public void setUp() throws Exception{
String resource = "mybatis-config.xml";// 获取资源
InputStream is = Resources.getResourceAsStream(resource);// 加载资源
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);// 加工
sqlSession = factory.openSession();// 打开
assert true;
}
@Test
public void testGetUserPage(){
List<SysUser> userList = new ArrayList<SysUser>();
String realName = "";
Integer roleId = 2;
Integer pageIndex = 2;
Integer pageSize = 2;
Integer pageBegin = (pageIndex - 1) * pageSize;// 计算查询起始位置
userList = sqlSession.getMapper(SysUserMapper3.class).selectPageList(realName, roleId, pageBegin, pageSize);
logger.info("查询到用户数量:" + userList.size());
for(SysUser user : userList){
logger.info("查询到用户信息:" + user);
}
}
@After
public void tearDown() throws Exception{
if (sqlSession!=null){
sqlSession.close();// 关闭
}
assert true;
}
}