学习笔记 : MyBatis之动态SQL
简介 : MyBatis提供的对SQL语句动态组装的功能解决了开发人员在使用JDBC或其他的框架进行数据库开发时,需要手动拼装SQL的繁琐问题.
动态SQL元素
动态SQL是MyBatis强大特性之一,MyBatis 3采用了功能强大的基于OGNL的表达式来完成动态SQL,其主要元素如下.
<if>: 判断语句,用于单条分支判断.<choose>(<when>,<otherwise>): 相当于Java中的swith…case…default语句,用于多条件分支判断.<where>,<trim>,<set>: 辅助元元素,用于处理一些SQL拼装,特殊字符问题.<foreach>: 循环语句,常用于in语句等列举条件中.<bind>: 从OGNL表达式中创建一个变量,并将其绑定到上下文,常用于模糊查询的sql中.
- 扩展 :
OGNL是Object-Graph Navigation Language的缩写,它是一种功能强大的表达式语言,通过它简单一致的表达式语法,可以存取对象的任意属性,调用对象的方法,遍历整个对象的结构图,实现字段类型转化等功能.它使用相同的表达式去存取对象的属性.这样可以更好的取得数据.
应用案例
下面通过一个操作客户信息的综合案例来学习SQL动态元素 (っ•̀ω•́)っ✎⁾⁾ ~
客户信息表结构
1
2
3
4
5
6
7
8create table t_customer (
id INT(32) PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
jobs VARCHAR(45) NOT NULL,
phone VARCHAR(16) NOT NULL
)db.properties : 数据库配置文件
1
2
3
4
5#database configuration information
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://LOCALHOST/MyBatis?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=GoodTimemybatis-config.xml : MyBatis核心配置文件
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
<configuration>
<!-- 数据库配置文件 -->
<properties resource="db.properties" />
<!-- 配置环境.默认环境id为MySQL -->
<environments default="MySQL">
<environment id="MySQL">
<!-- 使用JDBC事务管理 -->
<transactionManager type="JDBC" />
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 将SQL映射文件注册到全局配置文件中 -->
<mappers>
<mapper resource="pers/huangyuhui/mybatis/mapper/CustomerMapper.xml" />
</mappers>
</configuration>Customer.java : 封装客户信息
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
54package pers.huangyuhui.mybatis.bean;
/**
* @ClassName: Customer
* @Description: 客户信息实体表
* @author: HuangYuhui
* @date: May 18, 2019 11:37:59 AM
*
*/
public class Customer {
private Integer id;
private String username;
private String job;
private String phone;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String toString() {
return "Customer [id=" + id + ", username=" + username + ", job=" + job + ", phone=" + phone + "]";
}
}CustomerMapper.xml : 操作’t_customer’数据表的映射文件
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
<mapper namespace="pers.huangyuhui.mybatis.mapper.CustomerMapper">
<!-- (<if>)将客户名和职业组合作为查询客户信息列表的条件 -->
<select id="findCustomerByNameAndJob" parameterType="pers.huangyuhui.mybatis.bean.Customer"
resultType="pers.huangyuhui.mybatis.bean.Customer">
select id,username,job,phone from t_customer where 1=1
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</select>
<!-- (<where>+<if>)将客户名和职业组合作为查询客户信息列表的条件 -->
<select id="findCustomerByNameAndJob_where" parameterType="pers.huangyuhui.mybatis.bean.Customer"
resultType="pers.huangyuhui.mybatis.bean.Customer">
select id,username,job,phone from t_customer
<where>
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</where>
</select>
<!-- (<trim>+<if>)将客户名和职业组合作为查询客户信息列表的条件 -->
<select id="findCustomerByNameAndJob_trim" parameterType="pers.huangyuhui.mybatis.bean.Customer"
resultType="pers.huangyuhui.mybatis.bean.Customer">
select id,username,job,phone from t_customer
<trim prefix="where" prefixOverrides="and">
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</trim>
</select>
<!-- (<where>+<when>+<otherwise>)将客户名或职业其中一项作为查询客户信息的条件 -->
<select id="findCustomerByNameOrJob" parameterType="pers.huangyuhui.mybatis.bean.Customer"
resultType="pers.huangyuhui.mybatis.bean.Customer">
select id,username,job,phone from t_customer
<where>
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<when test="job!=null and job!=''">
and job = #{job}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</where>
</select>
<!-- (<set>+<if>)根据id更新客户信息 -->
<update id="updateCustomer" parameterType="pers.huangyuhui.mybatis.bean.Customer">
update t_customer
<set>
<if test="username !=null and username !=''">
username=#{username}
</if>
<if test="job !=null and job !=''">
job=#{job}
</if>
<if test="phone !=null and phone !=''">
phone=#{phone}
</if>
</set>
where id = #{id}
</update>
<!-- (<foreach>)根据客户id批量查询客户信息 -->
<select id="findCustomerByIds" parameterType="List" resultType="pers.huangyuhui.mybatis.bean.Customer">
select id,username,job,phone from t_customer where id in
<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<!-- (<bind>)替换${},防止SQL注入问题.根据客户名模糊查询客户信息 -->
<select id="findCustomerByName" parameterType="pers.huangyuhui.mybatis.bean.Customer"
resultType="pers.huangyuhui.mybatis.bean.Customer">
<bind name="pattern_username" value=" '%' + _parameter.getUsername + '%' "/>
select id,username,job,phone from t_customer where username like #{pattern_username}
</select>
</mapper>MyBatisUtils.java : MyBatis工具类
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
40package pers.huangyuhui.mybatis.util;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
* @ClassName: MyBatisUtils
* @Description: MyBatis工具类
* @author: HuangYuhui
* @date: May 18, 2019 11:43:31 AM
*
*/
public class MyBatisUtils {
private MyBatisUtils() {
}
private static SqlSessionFactory sqlSessionFactory = null;
static {
// 使用MyBatis提供的Resources类加载MyBatis的配置文件
try (Reader reader = Resources.getResourceAsReader("mybatis-config.xml");) {
// 构建SqlSessionFactory工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
}
// 获取SqlSession对象的静态方法
public static SqlSession getSession() {
return sqlSessionFactory.openSession();
}
}MyBatisTest.java : 测试类
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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133package pers.huangyuhui.mybatis.test;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.BeforeClass;
import org.junit.Ignore;
import org.junit.Test;
import pers.huangyuhui.mybatis.bean.Customer;
import pers.huangyuhui.mybatis.util.MyBatisUtils;
/**
* @ClassName: MyBatisTest
* @Description: 测试
* @author: HuangYuhui
* @date: May 18, 2019 8:41:39 AM
*
*/
public class MyBatisTest {
private static Customer customer;
private static String namespace = "pers.huangyuhui.mybatis.mapper.CustomerMapper.";
public static void init() throws IOException {
customer = new Customer();
}
// 将客户名和职业组合作为查询客户信息列表的条件
public void findCustomerByNameAndJobTest() {
// 获取SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSession();
// 封装需要组合查询的条件
customer.setUsername("YUbuntu0109");
customer.setJob("programmer");
// 执行SqlSession的查询方法,并返回结果集
List<Customer> customers = sqlSession.selectList(namespace + "findCustomerByNameAndJob", customer);
// 输出查询结果信息
for (Customer customer : customers) {
System.out.println(customer);
}
// 关闭SqlSession
sqlSession.close();
}
// 将客户名或职业其中一项作为查询客户信息的条件
public void findCustomerByNameOrJobTest() {
SqlSession sqlSession = MyBatisUtils.getSession();
customer.setUsername("YUbuntu0109");
customer.setJob("student");
List<Customer> customers = sqlSession.selectList(namespace + "findCustomerByNameOrJob", customer);
for (Customer customer : customers) {
System.out.println(customer);
}
sqlSession.close();
}
// 根据客户id更新客户信息
public void updateCustomerTest() {
SqlSession sqlSession = MyBatisUtils.getSession();
customer.setId(1);
customer.setPhone("15711111111");
int rows = sqlSession.update(namespace + "updateCustomer", customer);
if (rows > 0) {
System.out.println("成功更新了 " + rows + " 条数据哟 ~");
} else {
System.out.println("数据更新操作失败 !");
}
sqlSession.commit();
sqlSession.close();
}
// 根据客户id批量查询客户信息
public void findCustomerByIdsTest() {
SqlSession sqlSession = MyBatisUtils.getSession();
// 创建List集合,封装查询id
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
// 查询id为1,2,3的客户信息
List<Customer> customers = sqlSession.selectList(namespace + "findCustomerByIds", ids);
for (Customer customer : customers) {
System.out.println(customer);
}
sqlSession.close();
}
// 根据客户名模糊查询客户信息
public void findCustomerByNameTest() {
SqlSession sqlSession = MyBatisUtils.getSession();
customer.setUsername("Y");
List<Customer> customers = sqlSession.selectList(namespace + "findCustomerByName", customer);
for (Customer customer : customers) {
System.out.println(customer);
}
sqlSession.close();
}
}log4j.properties : 日志配置文件
1
2
3
4
5
6
7
8#Global configuration
log4j.rootLogger=DEBUG,stdout
#Log configuration
log4j.logger.pers.huangyuhui.mybatis.test=TRACE
#Console configuration
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.layout.ConversionPattern=%5p [%t] - %m%n
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
wechat
Related Articles
Comment