springboot整合mybatis(映射文件方式和注解方式)
</h1>
<div class="clear"></div>
<div class="postBody">
springboot作为一个微服务框架,给我们开发人员提供极大的便利,秉着约定大于配置的原则,通过starter包的形式为我们做了许多默认的配置,在进行数据持久化到关系型数据库时,我们一般都会首选spring data jpa,springboot为我们提供了starter包,只需配置很少的参数,就能满足我们的需求,非常方便。但是当我们遇到一些比较复杂的查询、多表关联查询及动态sql时,mybatis则在这方面更出色,并且在使用mybatis时我们可以通过sql优化来提高查询效率,springboot并没有给我们提供整合mybatis的starter包,因此我们需要自己整合。记录一下我的整合过程,以供后续参考。
本人使用的是sts,新建springboot项目加入web、mysql、mybatis模块,springboot版本2.0.1。
1、pom.xml
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
|
<?xml version=
"1.0"
encoding=
"UTF-8"
?>
<project xmlns=
"http://maven.apache.org/POM/4.0.0"
xmlns:xsi=
"http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation=
"http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"
>
<modelVersion>
4.0
.
0
</modelVersion>
<groupId>com.example</groupId>
<artifactId>springboot-mybatis</artifactId>
<version>
0.0
.
1
-SNAPSHOT</version>
<packaging>jar</packaging>
<name>springboot-mybatis</name>
<description>Demo project
for
Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>
2.1
.
0
.BUILD-SNAPSHOT</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-
8
</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-
8
</project.reporting.outputEncoding>
<java.version>
1.8
</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>
1.3
.
2
</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>
1.2
.
5
</version>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>
1.1
.
9
</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https:
//repo.spring.io/snapshot</url>
<snapshots>
<enabled>
true
</enabled>
</snapshots>
</repository>
<repository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https:
//repo.spring.io/milestone</url>
<snapshots>
<enabled>
false
</enabled>
</snapshots>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https:
//repo.spring.io/snapshot</url>
<snapshots>
<enabled>
true
</enabled>
</snapshots>
</pluginRepository>
<pluginRepository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https:
//repo.spring.io/milestone</url>
<snapshots>
<enabled>
false
</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
</project>
|
2、实体类UserDomain.java
1
2
3
4
5
6
7
8
9
10
11
12
13
|
package
com.example.demo.model;
public
class
UserDomain {
private
Integer userId;
private
String userName;
private
String password;
private
String phone;
//get和set方法
}
|
3、持久化接口
1
2
3
4
5
6
7
8
9
10
11
12
13
|
package
com.example.demo.dao;
import
java.util.List;
import
com.example.demo.model.UserDomain;
public
interface
UserDao {
int
insert(UserDomain record);
List<UserDomain> selectUsers();
}
|
4、建表语句,注意数据库的ip和名称
1
2
3
4
5
6
|
CREATE
TABLE
t_user(
user_id
INT
NOT
NULL
PRIMARY
KEY
AUTO_INCREMENT,
user_name
VARCHAR
(255)
NOT
NULL
,
password
VARCHAR
(255)
NOT
NULL
,
phone
VARCHAR
(255)
NOT
NULL
) ENGINE=INNODB AUTO_INCREMENT=1000
DEFAULT
CHARSET=utf8;
|
5、service层
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
|
package
com.example.demo.service;
import
com.example.demo.model.UserDomain;
import
com.github.pagehelper.PageInfo;
public
interface
UserService {
int
addUser(UserDomain user);
PageInfo<UserDomain> findAllUser(
int
pageNum,
int
pageSize);
}
package
com.example.demo.service.impl;
import
java.util.List;
import
org.springframework.beans.factory.annotation.Autowired;
import
org.springframework.stereotype.Service;
import
com.example.demo.dao.UserDao;
import
com.example.demo.model.UserDomain;
import
com.example.demo.service.UserService;
import
com.github.pagehelper.PageHelper;
import
com.github.pagehelper.PageInfo;
@Service
(value =
"userService"
)
public
class
UserServiceImpl
implements
UserService {
@Autowired
private
UserDao userDao;
@Override
public
int
addUser(UserDomain user) {
return
userDao.insert(user);
}
//分页查询
@Override
public
PageInfo<UserDomain> findAllUser(
int
pageNum,
int
pageSize) {
PageHelper.startPage(pageNum, pageSize);
List<UserDomain> userDomains = userDao.selectUsers();
PageInfo result =
new
PageInfo(userDomains);
return
result;
}
}
|
6、控制层
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
|
package
com.example.demo.controller;
import
org.springframework.beans.factory.annotation.Autowired;
import
org.springframework.stereotype.Controller;
import
org.springframework.web.bind.annotation.GetMapping;
import
org.springframework.web.bind.annotation.PostMapping;
import
org.springframework.web.bind.annotation.RequestMapping;
import
org.springframework.web.bind.annotation.RequestParam;
import
org.springframework.web.bind.annotation.ResponseBody;
import
com.example.demo.model.UserDomain;
import
com.example.demo.service.UserService;
@Controller
@RequestMapping
(value =
"/user"
)
public
class
UserController {
@Autowired
private
UserService userService;
@ResponseBody
@PostMapping
(
"/add"
)
public
int
addUser(UserDomain user){
return
userService.addUser(user);
}
@ResponseBody
@GetMapping
(
"/all"
)
public
Object findAllUser(
@RequestParam
(name =
"pageNum"
, required =
false
, defaultValue =
"1"
)
int
pageNum,
@RequestParam
(name =
"pageSize"
, required =
false
, defaultValue =
"10"
)
int
pageSize){
return
userService.findAllUser(pageNum,pageSize);
}
}
|
7、application.yml
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
|
server:
port:
8080
spring:
datasource:
name: mysql_test
type: com.alibaba.druid.pool.DruidDataSource
#druid相关配置
druid:
#监控统计拦截的filters
filters: stat
driver-
class
-name: com.mysql.jdbc.Driver
#基本属性
url: jdbc:mysql:
//127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
username: root
password: root
#配置初始化大小/最小/最大
initial-size:
1
min-idle:
1
max-active:
20
#获取连接等待超时时间
max-wait:
60000
#间隔多久进行一次检测,检测需要关闭的空闲连接
time-between-eviction-runs-millis:
60000
#一个连接在池中最小生存的时间
min-evictable-idle-time-millis:
300000
validation-query: SELECT
'x'
test-
while
-idle:
true
test-on-borrow:
false
test-on-
return
:
false
#打开PSCache,并指定每个连接上PSCache的大小。oracle设为
true
,mysql设为
false
。分库分表较多推荐设置为
false
pool-prepared-statements:
false
max-pool-prepared-statement-per-connection-size:
20
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-
package
: com.example.demo.model
#pagehelper
pagehelper:
helperDialect: mysql
reasonable:
true
supportMethodsArguments:
true
params: count=countSql
returnPageInfo: check
|
8、UserMapper.xml
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
|
<?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.example.demo.dao.UserDao"
>
<sql id=
"BASE_TABLE"
>
t_user
</sql>
<sql id=
"BASE_COLUMN"
>
userId,userName,password,phone
</sql>
<insert id=
"insert"
parameterType=
"com.example.demo.model.UserDomain"
>
INSERT INTO
<include refid=
"BASE_TABLE"
/>
<trim prefix=
"("
suffix=
")"
suffixOverrides=
","
>
userName,password,
<
if
test=
"phone != null"
>
phone,
</
if
>
</trim>
<trim prefix=
"VALUES("
suffix=
")"
suffixOverrides=
","
>
#{userName, jdbcType=VARCHAR},#{password, jdbcType=VARCHAR},
<
if
test=
"phone != null"
>
#{phone, jdbcType=VARCHAR},
</
if
>
</trim>
</insert>
<select id=
"selectUsers"
resultType=
"com.example.demo.model.UserDomain"
>
SELECT
<include refid=
"BASE_COLUMN"
/>
FROM
<include refid=
"BASE_TABLE"
/>
</select>
</mapper>
|
9、项目目录结构如下
在项目启动类SpringbootMybatisApplication上加上@MapperScan("com.example.demo.dao")注解,以便spring容器可以扫描到持久化接口,创建对应的代理类。springboot默认为mybatis、jpa、jdbc等开启了事务,如果需要开启事务,则在service层对应的方法上加上@Transactional即可。启动项目,访问localhost:8080/user/add,添加用户;访问localhost:8080/user/all,可以看到分页查询效果。
补充:相比与映射文件方式,注解方式会更加方便,但是功能有限,使用注解方式只需做如下修改即可
1、持久化接口修改为如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
package
com.example.demo.dao;
import
java.util.List;
import
org.apache.ibatis.annotations.Insert;
import
org.apache.ibatis.annotations.Mapper;
import
org.apache.ibatis.annotations.Param;
import
org.apache.ibatis.annotations.Select;
import
com.example.demo.model.UserDomain;
@Mapper
public
interface
UserDao {
@Insert
(
"INSERT INTO t_user(name, password, phone) VALUES(#{name}, #{password}, #{phone})"
)
int
insert(
@Param
(
"name"
) String name,
@Param
(
"password"
) String password,
@Param
(
"phone"
) String phone);
@Select
(
"SELECT * FROM t_user "
)
List<UserDomain> selectUsers();
}
|
2、删除掉UserMappper.xml文件即可。