在MyBatis框架中，if标签用于在构建SQL语句时，根据参数条件判断的结果，动态地选择加入或不加where条件中。
一 常见使用
在使用MyBatis处理查询逻辑的时候，常用的是判断一些参数是否为空，列举常用的几种情况展示
1.1 数据准备
1.1.1 创建表模型
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE `approval_info` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`info_name` VARCHAR(128) DEFAULT NULL,
`info_type` VARCHAR(32) DEFAULT NULL,
`info_size` VARCHAR(32) DEFAULT NULL,
`approval_time` DATETIME DEFAULT NULL,
`create_time` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`)
)
|
1.1.2 创建实体类
|
1
2
3
4
5
6
7
8
9
10
11
|
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ApprovalInfo {
private Long id;
private String infoName;
private String infoType;
private String infoSize;
private Date approvalTime;
private Date createTime;
}
|
1.1.3 创建mapper层
|
1
2
3
|
public interface ApprovalInfoMapper {
}
|
|
1
2
3
4
5
6
7
|
<?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.mango.mapper.ApprovalInfoMapper">
</mapper>
|
1.1.4 插入测试数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
@Test
public void insertBatchObject() {
List<ApprovalInfo> approvalInfoList = new ArrayList<>();
// 使用工具类往集合中添加数据
Collections.addAll(approvalInfoList,
new ApprovalInfo(null, "cultureProjectOne", "culture", "A", assembleDate("2023-12-05 10:10:10"), assembleDate("2023-11-15 08:08:10")),
new ApprovalInfo(null, "tourismProjectOne", "tourism", "A", assembleDate("2023-12-06 09:10:11"), assembleDate("2023-11-19 15:13:11")),
new ApprovalInfo(null, "gameProjectOne", "game", "A", assembleDate("2023-12-08 17:10:13"), assembleDate("2023-11-21 01:03:16")),
new ApprovalInfo(null, "cultureProjectTwo", "culture", "B", assembleDate("2023-12-11 01:07:15"), assembleDate("2023-11-25 20:23:40")),
new ApprovalInfo(null, "cultureProjectThree", "culture", "B", assembleDate("2023-12-17 07:12:07"), assembleDate("2023-12-29 13:41:11")),
new ApprovalInfo(null, "tourismProjectTwo", "tourism", "C", assembleDate("2023-12-21 13:01:19"), assembleDate("2023-12-01 11:31:19")),
new ApprovalInfo(null, "gameProjectTwo", "game", "C", assembleDate("2023-12-25 19:13:10"), assembleDate("2023-12-03 17:51:15")),
new ApprovalInfo(null, "gameProjectThree", "game", "B", assembleDate("2023-12-27 02:11:10"), assembleDate("2023-12-04 12:10:21")),
new ApprovalInfo(null, "tourismProjectThree", "tourism", "B", assembleDate("2023-12-29 06:19:10"), assembleDate("2023-12-05 15:43:21")),
new ApprovalInfo(null, "cultureProjectFour", "culture", "C", assembleDate("2023-12-31 01:09:10"), assembleDate("2023-12-08 20:21:37")));
int effectLineNumber = approvalInfoMapper.saveBatchApprovalInfo(approvalInfoList);
System.out.println("effectLineNumber is: " + effectLineNumber);
}
private Date assembleDate(String strDate) {
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return simpleDateFormat.parse(strDate);
} catch (ParseException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
}
|
|
1
2
3
|
public interface ApprovalInfoMapper {
int saveBatchApprovalInfo(@Param("approvalInfoList") List<ApprovalInfo> approvalInfoList);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<?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.mango.mapper.ApprovalInfoMapper">
<insert id="saveBatchApprovalInfo">
insert into approval_info(info_name, info_type, info_size, approval_time, create_time)
values
<foreach collection="approvalInfoList" item="approvalInfo" separator=",">
(#{approvalInfo.infoName}, #{approvalInfo.infoType}, #{approvalInfo.infoSize},
#{approvalInfo.approvalTime}, #{approvalInfo.createTime})
</foreach>
</insert>
</mapper>
|
表中数据如下：
1.2 mapper方法参数是String类型
mapper方法参数是String类型，如果在查询条件中infoType的值不为空，那么就加上infoType的判断条件：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
@Test
public void testQueryApprovalInfosByType() {
//==> Preparing: select id, info_name, info_type, info_size, approval_time, create_time from approval_info
//==> Parameters:
//<== Total: 10
// String infoType = null;
// String infoType = "";
//==> Preparing: select id, info_name, info_type, info_size, approval_time, create_time from approval_info WHERE info_type = ?
//==> Parameters: (String)
//<== Total: 0
// String infoType = " ";
//==> Preparing: select id, info_name, info_type, info_size, approval_time, create_time from approval_info WHERE info_type = ?
//==> Parameters: culture(String)
//<== Columns: id, info_name, info_type, info_size, approval_time, create_time
//<== Row: 1, cultureProjectOne, culture, A, 2023-12-05 10:10:10, 2023-11-15 08:08:10
//<== Row: 4, cultureProjectTwo, culture, B, 2023-12-11 01:07:15, 2023-11-25 20:23:40
//<== Row: 5, cultureProjectThree, culture, B, 2023-12-17 07:12:07, 2023-12-29 13:41:11
//<== Row: 10, cultureProjectFour, culture, C, 2023-12-31 01:09:10, 2023-12-08 20:21:37
//<== Total: 4
//[
// ApprovalInfo(id=1, infoName=cultureProjectOne, infoType=culture, infoSize=A, approvalTime=Tue Dec 05 10:10:10 CST 2023, createTime=Wed Nov 15 08:08:10 CST 2023),
// ApprovalInfo(id=4, infoName=cultureProjectTwo, infoType=culture, infoSize=B, approvalTime=Mon Dec 11 01:07:15 CST 2023, createTime=Sat Nov 25 20:23:40 CST 2023),
// ApprovalInfo(id=5, infoName=cultureProjectThree, infoType=culture, infoSize=B, approvalTime=Sun Dec 17 07:12:07 CST 2023, createTime=Fri Dec 29 13:41:11 CST 2023),
// ApprovalInfo(id=10, infoName=cultureProjectFour, infoType=culture, infoSize=C, approvalTime=Sun Dec 31 01:09:10 CST 2023, createTime=Fri Dec 08 20:21:37 CST 2023)
// ]
String infoType = "culture";
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfosByType(infoType);
System.out.println(approvalInfoList);
}
|
如果infoType的值为null，那么info_type = #{infoType}就不会加入到where条件中，查询出全部的数据；
如果infoType的值为""，那么info_type = #{infoType}就不会加入到where条件中，查询出全部的数据；
如果infoType的值为" "，那么info_type = #{infoType}就会加入到where条件中，且查询出来的结果当然是空的，MyBatis不会去除空格再进行计算；
如果infoType的值为culture，那么info_type = #{infoType}就会加入到where条件中，且只查询符合条件的结果，且从数据库中查出来的顺序就是往List有序集合中依次添加的顺序；
|
1
2
3
|
public interface ApprovalInfoMapper {
List<ApprovalInfo> queryApprovalInfosByType(@Param("infoType") String infoType);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="queryApprovalInfosByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="infoType != null and infoType != ''">
info_type = #{infoType}
</if>
</where>
</select>
</mapper>
|
在mapper映射文件中，要写resultMap标签用于在查询语句中，将表中的字段映射到Java对象的属性中；
在mapper映射文件中，where条件后面只有一个if标签，那么要写成<where>标签嵌套<if>标签；
mapper方法参数是String类型，那么在mapper映射文件中，就需要判断参数是否等于null，和是否等于空字符串；
1.3 mapper方法参数是Long类型
mapper方法参数是包装类Long类型或是long类型的区别；
如果在查询条件中(Long类型)id的值不为空，那么就加上id的判断条件；
|
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test
public void testSelectApprovalInfoByBaseId() {
// Long targetId = 5L;
// long targetId = 5L;
Long targetId = null;
List<ApprovalInfo> approvalInfoList1 = approvalInfoMapper.queryApprovalInfoListByBaseId(targetId);
List<ApprovalInfo> approvalInfoList2 = approvalInfoMapper.queryApprovalInfoListByWrapperId(targetId);
System.out.println(approvalInfoList1);
System.out.println(approvalInfoList2);
System.out.println("集合中第一条数据: " + approvalInfoList1.get(0));
System.out.println("集合中最后一条数据: " + approvalInfoList1.get(approvalInfoList1.size() - 1));
}
|
在服务层调用mapper层的queryApprovalInfoListByBaseId(@Param("id") long id)方法，对方法参数传值描述如下：
如果对mapper层的方法参数id传基本数据类型，可以正常使用；
如果对mapper层的方法参数id传包装类型，那么会先进行拆箱(由Long-->long)，传null值会抛异常；
在服务层调用mapper层queryApprovalInfoListByWrapperId(@Param("id") Long id)方法，对方法参数传值描述如下：
如果对mapper层的方法参数id传基本数据类型，可以正常使用；
如果对mapper层的方法参数id传包装类型，可以正常使用；
小结：mapper层接口参数有动态sql条件时，最好使用包装类型，而不是基本数据类型；
|
1
2
3
4
5
6
7
|
public interface ApprovalInfoMapper {
/**
* mapper层方法参数有参与到动态sql条件, 最好使用包装类型, 而不是基本数据类型
*/
List<ApprovalInfo> queryApprovalInfoListByBaseId(@Param("id") long id);
List<ApprovalInfo> queryApprovalInfoListByWrapperId(@Param("id") Long id);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="queryApprovalInfoListByBaseId" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="id != null">
id >= #{id}
</if>
</where>
</select>
<select id="queryApprovalInfoListByWrapperId" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="id != null">
id >= #{id}
</if>
</where>
</select>
</mapper>
|
mapper方法参数是Long类型，那么在mapper映射文件中，就只需要判断参数是否等于null；
番外篇
篇一：关于表中记录为空时，查询结果是null，譬如查询表中的最值，在mapper层的方法返回值中使用基本数据类型还是包装类型
|
1
2
3
4
5
6
7
|
@Test
public void testMaxIndex() {
Long maxIndexWrapperType = approvalInfoMapper.getMaxIndexWrapperType();
System.out.println("maxIndexWrapperType is: " + maxIndexWrapperType);
long maxIndexBaseType = approvalInfoMapper.getMaxIndexBaseType();
System.out.println("maxIndexBaseType is: " + maxIndexBaseType);
}
|
如果表中有数据，那么方法返回值使用long类型或是Long类型都可以；
如果表中没有数据，那么方法返回值使用long类型接收null值会报错，Long类型可以接收null值；
小结：如果表中记录为空，且查询结果为null，譬如mapper层获取表中字段最值时，方法返回值类型使用包装类型；
|
1
2
3
4
5
6
7
|
public interface ApprovalInfoMapper {
/**
* 获取表中字段的最值使用包装类型, long类型不能接收null值
*/
long getMaxIndexBaseType();
Long getMaxIndexWrapperType();
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<?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.mango.mapper.ApprovalInfoMapper">
<select id="getMaxIndexBaseType" resultType="long">
select MAX(id) from approval_info
</select>
<select id="getMaxIndexWrapperType" resultType="java.lang.Long">
select MAX(id) from approval_info
</select>
</mapper>
|
篇二：关于表中记录为空时，查询结果是0，譬如统计表中记录的数量，在mapper层的方法返回值中使用基本数据类型还是包装类型
|
1
2
3
4
5
6
7
|
@Test
public void testCount() {
long baseCount = approvalInfoMapper.queryCountBaseType();
System.out.println("基本数据类型接收的值: " + baseCount);
Long wrapperCount = approvalInfoMapper.queryCountWrapperType();
System.out.println("包装数据类型接收的值: " + wrapperCount);
}
|
如果表中没有数据，那么方法返回值0，使用long类型或是Long类型都可以接收；
如果表中有数据，那么方法返回值10，使用long类型或是Long类型都可以接收；
小结：如果表中记录为空，且查询结果为0，譬如mapper层统计表中记录的数量，方法返回值类型使用基本数据类型和包装类型都可以；
|
1
2
3
4
5
6
7
|
public interface ApprovalInfoMapper {
/**
* 获取表中记录的数量
*/
long queryCountBaseType();
Long queryCountWrapperType();
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<?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.mango.mapper.ApprovalInfoMapper">
<select id="queryCountBaseType" resultType="long">
select count(*) from approval_info
</select>
<select id="queryCountWrapperType" resultType="java.lang.Long">
select count(*) from approval_info
</select>
</mapper>
|
1.4 mapper方法参数是Date类型
mapper方法参数是包装类Date类型，如果在查询条件中date的值不为空，那么就加上date的判断条件：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@Test
public void testSelectApprovalInfoByDate() {
// Date date = null;
Date date = assembleDate("2023-12-03 17:51:15");
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfoListByDate(date);
System.out.println(approvalInfoList);
System.out.println("====");
System.out.println(approvalInfoList.get(0));
System.out.println(approvalInfoList.get(approvalInfoList.size() - 1));
}
private Date assembleDate(String strDate) {
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return simpleDateFormat.parse(strDate);
} catch (ParseException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
}
|
|
1
2
3
4
5
6
|
public interface ApprovalInfoMapper {
/**
* 使用date类型筛选数据
*/
List<ApprovalInfo> queryApprovalInfoListByDate(@Param("date") Date date);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<!-- 方式一 -->
<select id="queryApprovalInfoListByDate" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="date != null">
create_time >= #{date}
</if>
</where>
</select>
<!-- 方式二 -->
<select id="queryApprovalInfoListByDate" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="date != null">
create_time >= #{date}
</if>
</where>
</select>
<!-- 方式三 -->
<select id="queryApprovalInfoListByDate" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="date != null">
create_time <![CDATA[>=]]> #{date}
</if>
</where>
</select>
</mapper>
|
mapper方法参数是Date类型，那么在mapper映射文件中，就只需要判断参数是否等于null；
1.5 小结
如果MyBatis的mapper层方法参数是String类型，那么在mapper映射文件需要判断参数不等于null，和不等于空字符串；
如果MyBatis的mapper层方法参数是Long类型等基本数据类型对应的包装类型，那么在mapper映射文件只需要判断参数不等于null；
如果MyBatis的mapper层方法参数是Date类型，那么在mapper映射文件只需要判断参数不等于null；
在MyBatis表示关系运算符的方式有如下三种：
方式一，可以使用>关系运算符和>=关系运算符，不可以使用<关系运算符或<=关系运算符；
方式二，可以将关系运算符进行转义，使用&开头，和使用;结尾的方式
| < |
< |
| <= |
<= |
| > |
> |
| >= |
>= |
| & |
& |
| ' |
' |
| " |
" |
方式三，可以使用<![CDATA[ ]]>嵌套关系运算符，<![CDATA[>]]>，<![CDATA[>=]]>，<![CDATA[<]]>，和<![CDATA[<=]]>
二 其他使用
2.1 if标签指定的单个字符
如果infoSize的值是指定字符A，那就查询指定字符A的ApprovalInfo集合，如果不是那就查询全部的ApprovalInfo集合：
|
1
2
3
4
5
6
|
@Test
public void testSelectApprovalInfoBySize() {
String size = "A";
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfoListBySize(size);
System.out.println(approvalInfoList);
}
|
|
1
2
3
4
5
6
|
public interface ApprovalInfoMapper {
/**
* 当参数值是单字符时, 对应着的动态sql标签的判断条件如何处理
*/
List<ApprovalInfo> queryApprovalInfoListBySize(@Param("size") String size);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="queryApprovalInfoListBySize" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="size = 'A'">
info_size = #{size}
</if>
</where>
</select>
</mapper>
|
2.2 if标签指定的多个字符
如果infoType的值是指定的字符串culture，那就查询指定的字符串culture的ApprovalInfo集合，如果不是那就查询全部的ApprovalInfo集合：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
@Test
public void testSelectApprovalInfoByType() {
//==> Preparing: select id, info_name, info_type, info_size, approval_time, create_time from approval_info
//==> Parameters:
//<== Total: 10
// String type = null;
// String type = "culture12306";
//==> Preparing: select id, info_name, info_type, info_size, approval_time, create_time from approval_info WHERE info_type = ?
//==> Parameters: culture(String)
//<== Total: 4
String type = "culture";
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfoListByType(type);
System.out.println(approvalInfoList);
System.out.println("====");
System.out.println(approvalInfoList.get(0));
System.out.println(approvalInfoList.get(approvalInfoList.size() - 1));
}
|
|
1
2
3
4
5
6
7
|
public interface ApprovalInfoMapper {
/**
* 当参数值是多字符时, 对应着的动态sql标签的判断条件如何处理
* mapper接口中的方法参数, 只是用于mapper映射文件中的条件判断和组装sql字段值, 不一定要和表中字段名保持一致
*/
List<ApprovalInfo> queryApprovalInfoListByType(@Param("infoType") String infoType);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<!-- 方式一 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="infoType == 'culture'">
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式二 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test='infoType == "culture"'>
info_type = #{infoType}
</if>
</where>
</select>
</mapper>
|
在mapper映射文件中，判断方法参数是否等于指定的值，使用<if>标签的test属性值可以写如下两种书写的格式：
<if test=" infoType == 'culture' ">，双引号和单引号配合着使用；
<if test=' infoType == "culture" '>，单引号和双引号配合着使用；
2.3 if标签指定的数字
如果id的值是指定长整型5，那就查询infoType是culture的ApprovalInfo集合；
如果id的值是指定长整型7，那就查询infoType是tourism的ApprovalInfo集合；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
@Test
public void testQueryApprovalInfoListById() {
// Long id = 5L;
// Long id = 6L;
Long id = 7L;
String type = assembleMapData().get(id);
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfoListById(id, type);
System.out.println(approvalInfoList);
}
private Map<Long, String> assembleMapData() {
Map<Long, String> map = new HashMap<>();
map.put(5L, "culture");
map.put(7L, "tourism");
return map;
}
|
|
1
2
3
4
5
6
|
public interface ApprovalInfoMapper {
/**
* 当参数值是数字时, 对应着的动态sql标签的判断条件如何处理
*/
List<ApprovalInfo> queryApprovalInfoListById(@Param("id") Long id, @Param("infoType") String infoType);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<!-- 方式一 -->
<select id="queryApprovalInfoListById" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="id == 5">
and info_type = #{infoType}
</if>
<if test="id == 7">
and info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式二 -->
<select id="queryApprovalInfoListById" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test='id == 5'>
and info_type = #{infoType}
</if>
<if test='id == 7'>
and info_type = #{infoType}
</if>
</where>
</select>
</mapper>
|
2.4 if标签的test属性介绍
在MyBatis中，<if>标签的test属性值用于设置条件判断：
如果test属性值为true，那么if标签的条件会加到where中，
如果test属性值为false，那么if标签的条件不会加到where中，
<if>标签的test属性值的条件判断的内容可以写基本数据类型，字符串常量，关系表达式和引用类型对象；
|
1
2
3
4
5
6
|
@Test
public void testSelectApprovalInfoByType() {
String type = "culture";
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfoListByType(type);
System.out.println(approvalInfoList);
}
|
|
1
2
3
|
public interface ApprovalInfoMapper {
List<ApprovalInfo> queryApprovalInfoListByType(@Param("infoType") String infoType);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<!-- 方式一: boolean类型 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="true">
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式二: 数字(byte short int long float double)类型, 0为false, 非0(哪怕是0.0)为true -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="1">
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式三: char类型, 任意char类型都表示为true -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="'a'">
info_type = #{infoType}
</if>
</where>
</select>
<!--
单引号+单字符会解析成char类型;
双引号+单字符会解析成String类型;
单引号+多字符会解析成String类型;
多引号+多字符会解析成String类型;
-->
<!-- 方式四: String类型, String类型都表示为true -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="'abc'">
info_type = #{infoType}
</if>
</where>
</select>
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test='"abc"'>
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式五: 关系表达式, 相等表达式 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="'abc'=='abc'">
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式六: 关系表达式, 不相等表达式 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="'abc' != 'cba'">
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式七: 引用类型对象, infoType是String类型 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="infoType">
info_type = #{infoType}
</if>
</where>
</select>
<!-- 方式八: 引用类型对象, infoType用在关系表达式中 -->
<select id="queryApprovalInfoListByType" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="infoType == 'culture'">
info_type = #{infoType}
</if>
</where>
</select>
</mapper>
|
小结：在if标签的test属性值表达式中，引用类型对象不加引号(双引号或单引号)，字符和字符串类型需要加引号(双引号或单引号)；
在mapper映射文件中，if标签的标签体是表字段、运算符和值表达式，其中值表达式和if标签的test属性值表达式的内容是一致的，只不过在使用引用类型时需要使用#{}或${}给包括起来；
在mapper接口的方法中，方法参数可以自定义需要的参数，不一定非得和表中字段一样或是匹配，只要能表述清楚就可以了；
|
1
2
3
4
5
6
7
|
@Test
public void testSelectApprovalInfoByCustomize() {
String type = "culture";
String code = "en";
List<ApprovalInfo> approvalInfoList = approvalInfoMapper.queryApprovalInfoListByCustomize(type, code);
System.out.println(approvalInfoList);
}
|
|
1
2
3
4
5
6
|
public interface ApprovalInfoMapper {
List<ApprovalInfo> queryApprovalInfoListByCustomize(
@Param("customizeType") String customizeType,
@Param("customizeCode") String customizeCode
);
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<?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.mango.mapper.ApprovalInfoMapper">
<resultMap id="approvalInfoMap" type="com.mango.domain.ApprovalInfo">
<id property="id" column="id"/>
<result property="infoName" column="info_name"/>
<result property="infoType" column="info_type"/>
<result property="infoSize" column="info_size"/>
<result property="approvalTime" column="approval_time"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="queryApprovalInfoListByCustomize" resultMap="approvalInfoMap">
select id, info_name, info_type, info_size, approval_time, create_time
from approval_info
<where>
<if test="customizeCode == 'en'">
and info_type = #{customizeType}
</if>
</where>
</select>
</mapper>
|
2.5 MyBatis中#{}常用属性
MyBatis中#{}占位符的常用属性，javaType、jdbcType、mode、numericScale、resultMap、typeHandler、jdbcTypeName、expression。
javaType属性：指定参数的Java类型；
jdbcType属性：指定参数的JDBC类型；
typeHandler属性：指定自定义的类型处理器，用于处理特定类型的参数或结果；
MyBatis在预编译(parsing)阶段会读取SQL中的字段类型，以确保预编译SQL的字段类型正确；
MyBatis在预编译(parsing)阶段由SqlSourceBuilder解析#{}参数，将#{}替换为?号(占位)，并将#{}中的内容解析为ParameterMapping的封装，ParameterMapping包含了参数的各个属性，解析是除了typeHandler和javaType外其它属性都直接从配置中获取到然后设定，若未指定则为null，ParameterMapping中的typeHandler和javaType是必须要的，因为在后面通过setter方法设置参数值时，从#{}中javaType属性指定的类型信息来选择对应的setter方法进行参数设定，最后执行sql；
在执行sql时，但是有时候在执行的时会报ClassCastException，譬如表中字段是单字符就会报String cannot be cast to Integer的错误，需要手动设置参数的Java类型为：info_size = #{size, javaType=String}；
|
