动态sql语句
MyBatis的映射文件中,前面我们的SQL都是比较简单的,有些时候业务逻辑复杂时,我们的SQL是动态变化的,此时在前面的学习中我们的SQL就不能满足要求了。
先使用之前的进行精准查询
UserMapper.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.zg.mapper.UserMapper" >
<select id="findByCondition" parameterType="user" resultType="user">
select * from user where id=#{id} and username=#{username} and password=#{password}
</select>
</mapper>
UserMapper接口
package com.zg.mapper;
import com.zg.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findByCondition(User user);
}
测试
package com.zg.test;
import com.zg.domain.User;
import com.zg.mapper.UserMapper;
import jdk.nashorn.internal.runtime.JSONFunctions;
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.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MapperTest {
@Test
public void test1() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//模拟条件user
User condition = new User();
condition.setId(1);
condition.setUsername("bb");
condition.setPassword("222");
List<User> userList = mapper.findByCondition(condition);
System.out.println(userList);
}
}
动态SQL—> <if>
当在真正业务中,模拟条件中的东西由筛选表单提供,所以筛选的条件我们是不能确定的,会根据条件的不同,导致UserMapper.xml的语句也不同。
根据实体类的不同取值,使用不同的SQL语句进行查询,比如在id如果不为空时可以根据id查询,如果username不为空时还要加入用户名作为条件,这种情况在我们的多条件组合查询中经常会碰到
<?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.zg.mapper.UserMapper" >
<select id="findByCondition" parameterType="user" resultType="user">
select * from user/* where 1=1*/
/* 可以把where 1=1使用mybatis的where标签进行替换
但是下面若没有条件where标签可以不写*/
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
<!-- //这里意思就是说,当user传递进来的时候,如果有任何一个查询条件都可-->
</mapper>
mabatis的动态SQL中if标签的test属性里写的代码
MyBatis的动态SQL中if标签的test属性里写的代码是一个表达式,用于判断是否执行该if标签下面的SQL语句。这个表达式可以使用OGNL表达式语言进行编写,OGNL是一个Java中的表达语言,用于访问和操作Java中的对象。在MyBatis的if标签中,可以使用OGNL表达式访问当前的参数对象和结果对象,并进行逻辑判断。
<select id="getUserList" parameterType="map" resultType="User">
SELECT * FROM users
<where>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
</where>
</select>
在这个示例中,if标签的test属性里的表达式判断了两个参数,如果他们不为空或空字符串,则执行该if标签下面的SQL语句。这里使用MyBatis的占位符(#{})来防止SQL注入攻击,同时也避免了手动进行类型转换的问题
OGNL如何写
当我们使用MyBatis的动态SQL中的if标签时,需要使用OGNL表达式来判断条件是否成立。下面是OGNL表达式的写法和用法的一些详细说明:
1、基本语法
OGNL表达式的基本语法与Java的语法类似,使用".“来访问属性,使用”[“和”]“来访问数组或集合,使用”#"访问上下文对象。例如
#username // 访问上下文中的username对象
user.username // 访问user对象的username属性
userList[0] // 访问userList集合的第一个元素
2、操作符
OGNL支持多种操作符,包括算术、比较、逻辑和位运算等。常用的操作符如下:
+、-、*、/、% // 算术运算符
==、!=、<、>、<=、>= // 比较运算符
&&、||、! // 逻辑运算符
&、|、^、~、<<、>>、>>> // 位运算符
3、函数调用
OGNL还支持函数调用,可以直接调用java类中的方法,语法为:类名@方法名(参数列表)。例如:
@java.lang.Math@abs(-1) // 调用Math类中的abs方法
@java.lang.String@valueOf(123) // 调用String类中的valueOf方法
4、特殊符号
OGNL还有一些特殊符号,用于表示一些特殊的含义
#this // 当前对象
#root // 根对象
#context // 上下文对象
动态SQL—> <choose>
<choose>
标签类似于Java 中的 switch 语句,用于根据不同的条件生成不同的 SQL 语句。它包含多个 <when>
标签和一个 <otherwise>
标签,其中 <when>
标签用于判断条件是否成立,如果成立则执行其中的 SQL 语句,而 <otherwise>
标签则类似于 Java 中的 default 语句,表示所有条件都不成立时执行其中的 SQL 语句。语法如下:
<choose>
<when test="条件表达式1">
SQL 语句1
</when>
<when test="条件表达式2">
SQL 语句2
</when>
...
<otherwise>
SQL 语句n
</otherwise>
</choose>
案例:netValTp是02查询的是收益权层级的净值,01查询的是项目层级的净值,但很多项目没有受益权层级
<!--已披露净值分页查询接口-->
<select id="pageQuery1" resultMap="BaseSearchPageMap">
select
s.PRJ_ID,
s.PRJ_NM,
s.EVAL_PD_CD,
s.CLAF_PD_CD,
s.CLAF_PD_NM,
s.ANC_STAT_CD,
s.SHOW_TP,
s.NET_VAL_DT,
s.UNIT_NET_VAL,
s.UNIT_NET_VAL_GROW_RATE,
s.ACM_NET_VAL,
s.ACM_NET_VAL_GROW_RATE,
s.MICP_PFT,
s.SEVEN_DAYS_ANUL_YELD,
s.THIRTY_DAYS_ANUL_YELD,
s.IS_CURRENCY
from NETVAL_DISC_DETAIL_INFO s
<if test = "netValQueryInfo.selTp != null and netValQueryInfo.selTp == '01'.toString()">
<choose>
<when test="netValQueryInfo.netValTp != null and netValQueryInfo.netValTp == '02'.toString()">
inner join (SELECT m.CLAF_PD_CD, MAX(NET_VAL_DT) AS NET_VAL_DT FROM
(SELECT * FROM NETVAL_DISC_DETAIL_INFO
WHERE
ANC_STAT_CD = '1'
<if test="netValQueryInfo.publChnl !=null and netValQueryInfo.publChnl != ''">
and PUBL_CHNL_CD = #{netValQueryInfo.publChnl}
</if>) m
where CLAF_PD_CD is not null
GROUP BY CLAF_PD_CD) m on s.CLAF_PD_CD=m.CLAF_PD_CD and s.NET_VAL_DT=m.NET_VAL_DT
</when>
<otherwise>
inner join (SELECT m.PRJ_ID, MAX(NET_VAL_DT) AS NET_VAL_DT FROM
(SELECT * FROM NETVAL_DISC_DETAIL_INFO
WHERE
ANC_STAT_CD = '1'
<if test="netValQueryInfo.publChnl !=null and netValQueryInfo.publChnl != ''">
and PUBL_CHNL_CD = #{netValQueryInfo.publChnl}
</if>) m
<if test="netValQueryInfo.netValTp != null and netValQueryInfo.netValTp == '01'.toString()">
where CLAF_PD_CD is null
</if>
GROUP BY PRJ_ID) m on s.PRJ_ID=m.PRJ_ID and s.NET_VAL_DT=m.NET_VAL_DT
</otherwise>
</choose>
</if>
<where>
s.use_state='1'
<if test = "netValQueryInfo.netValTp != null and netValQueryInfo.netValTp == '01'.toString()">
and s.CLAF_PD_CD is null
</if>
<if test = "netValQueryInfo.netValTp != null and netValQueryInfo.netValTp == '02'.toString()">
and s.CLAF_PD_CD is not null
</if>
<if test="netValQueryInfo.publChnl !=null and netValQueryInfo.publChnl != ''">
and s.PUBL_CHNL_CD = #{netValQueryInfo.publChnl}
</if>
and s.ANC_STAT_CD='1'
<if test="netValQueryInfo.clafPdCode !=null and netValQueryInfo.clafPdCode != '' " >
and s.CLAF_PD_CD = #{netValQueryInfo.clafPdCode}
</if>
<if test="netValQueryInfo.netValStartDt !=null ">
<![CDATA[ and s.NET_VAL_DT >= #{netValQueryInfo.netValStartDt} ]]>
</if>
<if test="netValQueryInfo.netValEndDt !=null ">
<![CDATA[ and s.NET_VAL_DT <= #{netValQueryInfo.netValEndDt} ]]>
</if>
<if test="netValQueryInfo.prjIdArray !=null and netValQueryInfo.prjIdArray.size() > 0 ">
and s.PRJ_ID in
<foreach collection="netValQueryInfo.prjIdArray" item="prjId" open="(" separator="," close=")">
#{prjId}
</foreach>
</if>
</where>
order by s.NET_VAL_DT desc nulls last
</select>
动态SQL—> <trim>
<trim>
标签也用于动态生成 SQL 语句中的 WHERE 子句,与 <where>
标签不同的是,它可以控制 WHERE 子句前后的空格和 AND/OR
关键字的生成。语法如下:
<select id="getUserByNameAndEmail" resultType="User">
select * from user
<where>
<trim prefix="where" prefixOverrides="
MyBatis 中的 <trim>
标签用于从生成的 SQL 语句中删除不必要的 SQL 片段。 <trim>
标签允许您指定要删除的 SQL 片段,同时保留关键字和 SQL 片段之间的空格。
<trim>
标签包含以下属性:
prefix:将在 SQL 语句开始位置添加前缀。
suffix:将在 SQL 语句结尾位置添加后缀。
prefixOverrides:将在 SQL 语句开始位置删除指定的前缀。
suffixOverrides:将在 SQL 语句结尾位置删除指定的后缀。
标签还包含以下子标签:
<where>
:用于将生成的 SQL 语句嵌套在 WHERE 语句中。
<set>
:用于将生成的 SQL 语句嵌套在 SET 语句中。
<foreach>
:用于将生成的 SQL 语句嵌套在 FOREACH 语句中。
下面是一个使用 <trim>
标签的例子,假设有一个查询条件可以为空,如果为空,则不应该在查询中使用:
<select id="getUser" parameterType="User" resultType="User">
SELECT * FROM users
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="name != null">
AND name = #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
</trim>
</select>
在这个例子中,如果传递的 User 对象中的 name 或 age 属性为空,则不会在 SQL 查询中使用这些属性。<trim>
标签使用 prefixOverrides 属性删除任何不需要的 AND 或 OR 子句。同时,如果 name 或 age 属性不为空,则使用 <if>
标签添加相应的子句。
使用trim标签去除多余的and关键字
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
如果这些条件没有一个能匹配上会发生什么?最终这条 SQL 会变成这样:
SELECT * FROM BLOG
WHERE
这会导致查询失败。如果仅仅第二个条件匹配又会怎样?这条 SQL 最终会是这样:
SELECT * FROM BLOG
WHERE
AND title like 'someTitle'
你可以使用where标签来解决这个问题,where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且,若语句的开头为“AND”或“OR”,where 元素也会将它们去除。
select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
trim标签也可以完成相同的功能,写法如下:
<trim prefix="WHERE" prefixOverrides="AND">
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</trim>
使用trim标签去除多余的逗号
如果红框里面的条件没有匹配上,sql语句会变成如下:
INSERT INTO role(role_name,) VALUES(roleName,)
插入将会失败。使用trim标签可以解决此问题,只需做少量的修改,suffixOverrides=","
如下所示:
动态SQL—> <where>
<where>
标签用于动态生成 WHERE 子句,如果 WHERE 子句中有多个条件,这些条件之间用 AND 连接,如果其中有条件不成立,则不生成该条件。语法如下
<select id="getUserByNameAndEmail" resultType="User">
select * from user
<where>
<if test="name != null">
and name = #{name}
</if>
<if test="email != null">
and email = #{email}
</if>
</where>
</select>
动态SQL—> <foreach>
当查询为id中的某一个select * from user where id=1 or id=2 or id=3;
或select * from user where in IN {1,2,3}
时可以使用foreach
<!--foreach-->
<select id="findByIds" parameterType="list" resultType="user">
/*这里的list是由业务层传递进来的,所以(1,2,3)是变的,如果传递的list为空,则where也是用不到的*/
select * from user
<where>
<!--collection="list"表示当前传递的封装数据为list集合,如果是数组则array
open="id in(":表示以哪块开始,close=")":表示以哪块结束,item="id":id去接收list集合中的每一项值,separator=",":分隔符
foreach内部写每一个对应的值#{id},因为这里使用item="id"封装-->
<foreach collection="list" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
dao接口类
package com.zg.mapper;
import com.zg.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findByCondition(User user);
//使用foreach查询时,参数就要进行封装
public List<User> findByIds(List<Integer> ids);
}
测试类
@Test
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//模拟ids的数据
List<Integer> ids = new ArrayList<>();
ids.add(1);
List<User> userList = mapper.findByIds(ids);
System.out.println(userList);
}
foreach标签的属性含义:
<foreach>
标签用于遍历集合,他的属性有:
collection:代表要遍历的集合元素,注意编写时不要写#{}
open:代表语句的开始部分
close:代表结束部分
item:代表遍历集合的每个元素,生成的变量名
sperator:代表分隔符
SQL片段抽取
SQL中可将重复的sql抽取出来,使用时用include
引用即可,最终达到sql重用的目的
<?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.itheima.mapper.UserMapper">
<!--sql语句抽取-->
<sql id="selectUser">select * from user</sql>
<select id="findByCondition" parameterType="user" resultType="user">
<include refid="selectUser"></include>
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
<select id="findByIds" parameterType="list" resultType="user">
<include refid="selectUser"></include>
<where>
<foreach collection="list" open="id`在这里插入代码片` in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
MyBatis映射文件配置
标签名 | 作用 |
---|---|
<select> | 查询 |
<insert> | 插入 |
<update> | 修改 |
<delete> | 删除 |
<where> | where条件 |
<if> | if判断 |
<foreach> | 循环 |
<sql> | sql片段抽取 |
在Mybatis的xml文件中,很多特殊符号是无法直接使用的,需要使用实体引用,假如在 XML 文档中放置了一个类似 “<” 字符,那么这个文档会产生一个错误,这是因为解析器会把它解释为新元素的开始。
原符号、实体引用、CDATA对照表
原符号 | 实体引用 | CDATA | 说明 |
---|---|---|---|
< | < | <![CDATA[ < ]]> | 小于 |
<= | <= | <![CDATA[ <= ]]> | 小于等于 |
> | > | <![CDATA[ > ]]> | 大于 |
>= | >= | <![CDATA[ >= ]]> | 大于等于 |
& | & | <![CDATA[ & ]]> | 和号 |
’ | ' | <![CDATA[ ' ]]> | 省略号 |
" | " | <![CDATA[ " ]]> | 引号 |
使用模糊查询的案例
<!-- 已披露净值分页查询 -->
<select id="page" resultMap="BaseResultModelMapStr">
select
info.PUBL_NET_VAL_NO,
info.PRJ_ID,
info.PRJ_NM,
info.NET_VAL_DT,
info.EVAL_PD_CD,
info.CLAF_PD_CD,
info.CLAF_PD_NM,
info.IS_CURRENCY,
info.UNIT_NET_VAL,
info.UNIT_NET_VAL_GROW_RATE,
info.ACM_NET_VAL,
info.ACM_NET_VAL_GROW_RATE,
info.MICP_PFT,
info.SEVEN_DAYS_ANUL_YELD,
info.THIRTY_DAYS_ANUL_YELD,
info.PUBL_CHNL_CD,
info.ANC_STAT_CD,
info.NET_VAL_DATA_SRC_CD,
info.USE_STATE,
info.CREATE_USER_NO,
info.CREATE_TIME,
info.UPDATE_USER_NO,
info.UPDATE_TIME,
info.DATA_SOURCE,
info.RMRK
from NETVAL_DISC_INFO info
inner join pm_project_info pm on info.prj_id = pm.prj_id
<include refid="com.boot.dao.AuthCommonMapper.AuthData_Limits_Queries">
<property name="alias" value="pm"/>
<property name="aliasParam" value="netvalDiscInfo"/>
</include>
<where>
<if test="netvalDiscInfo.prjNm !=null and netvalDiscInfo.prjNm != ''">
and info.PRJ_NM like concat(concat('%',#{netvalDiscInfo.prjNm}),'%')
</if>
<if test="netvalDiscInfo.prjId !=null and netvalDiscInfo.prjId != ''">
and info.PRJ_ID = #{netvalDiscInfo.prjId,jdbcType=VARCHAR}
</if>
<if test="netvalDiscInfo.netValStartDt !=null ">
<![CDATA[ and info.NET_VAL_DT >= #{netvalDiscInfo.netValStartDt} ]]>
</if>
<if test="netvalDiscInfo.netValEndDt !=null ">
<![CDATA[ and info.NET_VAL_DT <= #{netvalDiscInfo.netValEndDt} ]]>
</if>
<if test="netvalDiscInfo.publChnlCd !=null and netvalDiscInfo.publChnlCd != ''">
and info.PUBL_CHNL_CD like concat(concat('%',#{netvalDiscInfo.publChnlCd}),'%')
</if>
<if test="netvalDiscInfo.isCurrency != null and netvalDiscInfo.isCurrency != ''">
and info.IS_CURRENCY = #{netvalDiscInfo.isCurrency}
</if>
</where>
order by NET_VAL_DT desc nulls last,PRJ_NM desc nulls last
</select>
解释
特殊符号为什么不能在xml文件中使用
“<” 会产生错误,因为解析器会把该字符解释为新元素的开始。
“>” 会产生错误,因为解析器会把该字符解释为新元素的结束。
“&” 也会产生错误,因为解析器会把该字符解释为字符实体的开始。
关于 CDATA 部分的注释:
术语 CDATA 指的是不应由 XML 解析器进行解析的文本数据(Unparsed Character Data)。
CDATA 部分中的所有内容都会被解析器忽略。
CDATA 部分由 “<![CDATA[" 开始,由 "]]>” 结束。
CDATA 部分不能包含字符串 “]]>”。也不允许嵌套的 CDATA 部分。
标记 CDATA 部分结尾的 “]]>” 不能包含空格或折行。
Mapper接口中传递参数需要@Param注解映射的情况
方法有多个参数
接口:
Integer insertUser(@Param("id") Integer id, @Param("username") String username, @Param("password") String password, @Param("age") Integer age);
映射XML文件:
<insert id="insertUser">
INSERT INTO T_USER (ID, USERNAME, PASSWORD, AGE)
VALUES(#{id}, #{username}, #{password}, #{age})
</insert>
方法参数取别名
接口:
User getUserByUsername(@Param("name") String username);
映射XML文件:
<select id="getUserByUsername" resultMap="BaseResultMap" parameterType="String">
SELECT * FROM T_USER WHERE USERNAME = #{name}
</select>
XML中的SQL使用了$
接口:
List<User> getUsersOrderByParam(@Param("order_by") String order_by);
映射XML文件:
<select id="getUsersOrderByParam" resultMap="BaseResultMap" parameterType="String">
SELECT * FROM T_USER
<if test="order_by != null and order_by != ''">
ORDER BY ${order_by} DESC
</if>
</select>
动态SQL中使用参数作为变量
接口:
User getUserById(@Param("id") Integer id);
映射XML文件:
<select id="getUserById" resultMap="BaseResultMap">
SELECT * FROM T_USER
<where>
<if test="id != null">
ID = #{id}
</if>
</where>
</select>