Spring Boot干货系列:(八)数据存储篇-SQL关系型数据库之JdbcTemplate的使用

前言

前面几章介绍了一些基础,但都是静态的,还不足以构建一个动态的应用。本篇开始就要介绍数据交互了,为了演示效果更加好,博主花了大把时间整合了一个后端模板框架,基于Bootstrap3的ACE模板,并实现了一个基本的增删改查分页功能。让我们一起动手,学技术的同时,顺便把我们的项目完善起来,这样跟着博主学到最后,你就有了一个属于自己的Spring Boot项目啦。

正文

本文介绍在Spring Boot基础下配置数据源和通过JdbcTemplate编写数据访问的示例。

添加依赖

这里需要添加spring-boot-starter-jdbc依赖跟mysql依赖

1
2
3
4
5
6
7
8
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

数据源配置

在src/main/resources/application.properties中配置数据源信息。

1
2
3
4
spring.datasource.url = jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driver-class-name = com.mysql.jdbc.Driver

自定义数据源

spring-boot-starter-jdbc 默认使用tomcat-jdbc数据源,如果你想使用其他的数据源,比如这里使用了阿里巴巴的数据池管理,你应该额外添加以下依赖:

1
2
3
4
5
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.19</version>
</dependency>

修改Application.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
@SpringBootApplication
public class Application {

public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}

@Autowired
private Environment env;

//destroy-method="close"的作用是当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.
@Bean(destroyMethod = "close")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setInitialSize(2);//初始化时建立物理连接的个数
dataSource.setMaxActive(20);//最大连接池数量
dataSource.setMinIdle(0);//最小连接池数量
dataSource.setMaxWait(60000);//获取连接时最大等待时间,单位毫秒。
dataSource.setValidationQuery("SELECT 1");//用来检测连接是否有效的sql
dataSource.setTestOnBorrow(false);//申请连接时执行validationQuery检测连接是否有效
dataSource.setTestWhileIdle(true);//建议配置为true,不影响性能,并且保证安全性。
dataSource.setPoolPreparedStatements(false);//是否缓存preparedStatement,也就是PSCache
return dataSource;
}
}

ok这样就算自己配置了一个DataSource,Spring Boot会智能地选择我们自己配置的这个DataSource实例。

脚本初始化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE DATABASE /*!32312 IF NOT EXISTS*/`spring` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `spring`;
DROP TABLE IF EXISTS `learn_resource`;

CREATE TABLE `learn_resource` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`author` varchar(20) DEFAULT NULL COMMENT '作者',
`title` varchar(100) DEFAULT NULL COMMENT '描述',
`url` varchar(100) DEFAULT NULL COMMENT '地址链接',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1029 DEFAULT CHARSET=utf8;


insert into `learn_resource`(`id`,`author`,`title`,`url`) values (999,'官方SpriongBoot例子','官方SpriongBoot例子','https://github.com/spring-projects/spring-boot/tree/master/spring-boot-samples');
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (1000,'龙果学院','Spring Boot 教程系列学习','http://www.roncoo.com/article/detail/124661');
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (1001,'嘟嘟MD独立博客','Spring Boot干货系列','http://tengj.top/');
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (1002,'后端编程嘟','Spring Boot视频教程','http://www.toutiao.com/m1559096720023553/');

开始使用JdbcTemplate

Spring的JdbcTemplate是自动配置的,你可以直接使用@Autowired来注入到你自己的bean中来使用。这里博主做了一套基本的增删改查操作。

实体对象

1
2
3
4
5
6
7
public class LearnResouce {
private Long id;
private String author;
private String title;
private String url;
// SET和GET方法
}

Controller层

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
133
134
135
136
137
138
139
140
141
142
143
144
@Controller
@RequestMapping("/learn")
public class LearnController {
@Autowired
private LearnService learnService;
private Logger logger = LoggerFactory.getLogger(this.getClass());

@RequestMapping("")
public String learn(){
return "learn-resource";
}

@RequestMapping(value = "/queryLeanList",method = RequestMethod.POST,produces="application/json;charset=UTF-8")
@ResponseBody
public void queryLearnList(HttpServletRequest request ,HttpServletResponse response){
String page = request.getParameter("page"); // 取得当前页数,注意这是jqgrid自身的参数
String rows = request.getParameter("rows"); // 取得每页显示行数,,注意这是jqgrid自身的参数
String author = request.getParameter("author");
String title = request.getParameter("title");
Map<String,Object> params = new HashMap<String,Object>();
params.put("page", page);
params.put("rows", rows);
params.put("author", author);
params.put("title", title);
Page pageObj =learnService.queryLearnResouceList(params);
List<Map<String, Object>> learnList=pageObj.getResultList();
JSONObject jo=new JSONObject();
jo.put("rows", learnList);
jo.put("total", pageObj.getTotalPages());
jo.put("records", pageObj.getTotalRows());
ServletUtil.createSuccessResponse(200, jo, response);
}
/**
* 新添教程
* @param request
* @param response
*/

@RequestMapping(value = "/add",method = RequestMethod.POST)
public void addLearn(HttpServletRequest request , HttpServletResponse response){
JSONObject result=new JSONObject();
String author = request.getParameter("author");
String title = request.getParameter("title");
String url = request.getParameter("url");
if(StringUtil.isNull(author)){
result.put("message","作者不能为空!");
result.put("flag",false);
ServletUtil.createSuccessResponse(200, result, response);
return;
}
if(StringUtil.isNull(title)){
result.put("message","教程名称不能为空!");
result.put("flag",false);
ServletUtil.createSuccessResponse(200, result, response);
return;
}
if(StringUtil.isNull(url)){
result.put("message","地址不能为空!");
result.put("flag",false);
ServletUtil.createSuccessResponse(200, result, response);
return;
}
LearnResouce learnResouce = new LearnResouce();
learnResouce.setAuthor(author);
learnResouce.setTitle(title);
learnResouce.setUrl(url);
int index=learnService.add(learnResouce);
System.out.println("结果="+index);
if(index>0){
result.put("message","教程信息添加成功!");
result.put("flag",true);
}else{
result.put("message","教程信息添加失败!");
result.put("flag",false);
}
ServletUtil.createSuccessResponse(200, result, response);
}
/**
* 修改教程
* @param request
* @param response
*/

@RequestMapping(value = "/update",method = RequestMethod.POST)
public void updateLearn(HttpServletRequest request , HttpServletResponse response){
JSONObject result=new JSONObject();
String id = request.getParameter("id");
LearnResouce learnResouce=learnService.queryLearnResouceById(Long.valueOf(id));
String author = request.getParameter("author");
String title = request.getParameter("title");
String url = request.getParameter("url");
if(StringUtil.isNull(author)){
result.put("message","作者不能为空!");
result.put("flag",false);
ServletUtil.createSuccessResponse(200, result, response);
return;
}
if(StringUtil.isNull(title)){
result.put("message","教程名称不能为空!");
result.put("flag",false);
ServletUtil.createSuccessResponse(200, result, response);
return;
}
if(StringUtil.isNull(url)){
result.put("message","地址不能为空!");
result.put("flag",false);
ServletUtil.createSuccessResponse(200, result, response);
return;
}
learnResouce.setAuthor(author);
learnResouce.setTitle(title);
learnResouce.setUrl(url);
int index=learnService.update(learnResouce);
System.out.println("修改结果="+index);
if(index>0){
result.put("message","教程信息修改成功!");
result.put("flag",true);
}else{
result.put("message","教程信息修改失败!");
result.put("flag",false);
}
ServletUtil.createSuccessResponse(200, result, response);
}
/**
* 删除教程
* @param request
* @param response
*/

@RequestMapping(value="/delete",method = RequestMethod.POST)
@ResponseBody
public void deleteUser(HttpServletRequest request ,HttpServletResponse response){
String ids = request.getParameter("ids");
System.out.println("ids==="+ids);
JSONObject result = new JSONObject();
//删除操作
int index = learnService.deleteByIds(ids);
if(index>0){
result.put("message","教程信息删除成功!");
result.put("flag",true);
}else{
result.put("message","教程信息删除失败!");
result.put("flag",false);
}
ServletUtil.createSuccessResponse(200, result, response);
}
}

Service层

1
2
3
4
5
6
7
public interface LearnService {
int add(LearnResouce learnResouce);
int update(LearnResouce learnResouce);
int deleteByIds(String ids);
LearnResouce queryLearnResouceById(Long learnResouce);
Page queryLearnResouceList(Map<String,Object> params);
}

实现类

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
@Service
public class LearnServiceImpl implements LearnService {

@Autowired
LearnDao learnDao;
@Override
public int add(LearnResouce learnResouce) {
return this.learnDao.add(learnResouce);
}

@Override
public int update(LearnResouce learnResouce) {
return this.learnDao.update(learnResouce);
}

@Override
public int deleteByIds(String ids) {
return this.learnDao.deleteByIds(ids);
}

@Override
public LearnResouce queryLearnResouceById(Long id) {
return this.learnDao.queryLearnResouceById(id);
}

@Override
public Page queryLearnResouceList(Map<String,Object> params) {
return this.learnDao.queryLearnResouceList(params);
}
}

Dao层

1
2
3
4
5
6
7
public interface LearnDao {
int add(LearnResouce learnResouce);
int update(LearnResouce learnResouce);
int deleteByIds(String ids);
LearnResouce queryLearnResouceById(Long id);
Page queryLearnResouceList(Map<String,Object> params);
}

实现类,这里注入我们需要的JdbcTemplate

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
@Repository
public class LearnDaoImpl implements LearnDao{
@Autowired
private JdbcTemplate jdbcTemplate;

@Override
public int add(LearnResouce learnResouce) {
return jdbcTemplate.update("insert into learn_resource(author, title,url) values(?, ?, ?)",learnResouce.getAuthor(),learnResouce.getTitle(),learnResouce.getUrl());
}

@Override
public int update(LearnResouce learnResouce) {
return jdbcTemplate.update("update learn_resource set author=?,title=?,url=? where id = ?",new Object[]{learnResouce.getAuthor(),learnResouce.getTitle(),learnResouce.getUrl(),learnResouce.getId()});
}

@Override
public int deleteByIds(String ids){
return jdbcTemplate.update("delete from learn_resource where id in("+ids+")");
}

@Override
public LearnResouce queryLearnResouceById(Long id) {
List<LearnResouce> list = jdbcTemplate.query("select * from learn_resource where id = ?", new Object[]{id}, new BeanPropertyRowMapper(LearnResouce.class));
if(null != list && list.size()>0){
LearnResouce learnResouce = list.get(0);
return learnResouce;
}else{
return null;
}
}

@Override
public Page queryLearnResouceList(Map<String,Object> params) {
StringBuffer sql =new StringBuffer();
sql.append("select * from learn_resource where 1=1");
if(!StringUtil.isNull((String)params.get("author"))){
sql.append(" and author like '%").append((String)params.get("author")).append("%'");
}
if(!StringUtil.isNull((String)params.get("title"))){
sql.append(" and title like '%").append((String)params.get("title")).append("%'");
}
Page page = new Page(sql.toString(), Integer.parseInt(params.get("page").toString()), Integer.parseInt(params.get("rows").toString()), jdbcTemplate);
return page;
}
}

上面介绍的JdbcTemplate只是最基本的几个操作,更多其他数据访问操作的使用请参考:JdbcTemplate API

到此为止,后端交互代码都写好了,这里博主整合的bootstrap模板就不展示了,各位可以自行下载本篇对应的源码跑起来看看,效果很棒咯,如下:

总结

SpringBoot下访问数据库还是很简单的,只要添加依赖,然后在application.properties中配置连接信息。下一篇博主将介绍下Spring Boot对mybatis的整合。

想要查看更多Spring Boot干货教程,可前往:Spring Boot干货系列总纲

源码下载

( ̄︶ ̄)↗[相关示例完整代码]
想要ace模板源码的话,在博主微信公众号回复关键字:ace


一直觉得自己写的不是技术,而是情怀,一篇篇文章是自己这一路走来的痕迹。靠专业技能的成功是最具可复制性的,希望我的这条路能让你少走弯路,希望我能帮你抹去知识的蒙尘,希望我能帮你理清知识的脉络,希望未来技术之巅上有你也有我,希望大爷你看完打赏点零花钱给我。

订阅博主微信公众号:嘟爷java超神学堂(javaLearn)三大好处:

  • 获取最新博主博客更新信息,首发公众号
  • 获取大量视频,电子书,精品破解软件资源
  • 可以跟博主聊天,欢迎程序媛妹妹来撩我

博主最近发起了《嘟爷电子书互惠组》计划,里面包含了《精通Spring4.X企业应用开发实战》相关书籍在内的至少163本Java相关的电子书,也有博主花钱买的电子书。可谓新手必备之物,详情可前往书单末尾查看: Java后端2017书单推荐

文章目录
  1. 1. 前言
  2. 2. 正文
    1. 2.1. 添加依赖
    2. 2.2. 数据源配置
    3. 2.3. 自定义数据源
    4. 2.4. 脚本初始化
    5. 2.5. 开始使用JdbcTemplate
      1. 2.5.1. 实体对象
      2. 2.5.2. Controller层
      3. 2.5.3. Service层
      4. 2.5.4. Dao层
  3. 3. 总结
  4. 4. 源码下载
|