Spring Boot:(八)数据存储篇-SQL关系型数据库之JdbcTemplate的使用
前言
前面几章介绍了一些基础,但都是静态的,还不足以构建一个动态的应用。本篇开始就要介绍数据交互了,基于Bootstrap3的ACE模板,并实现一个基本的增删改查分页功能。
正文
本文介绍在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); } private Environment env; //destroy-method="close"的作用是当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用. "close") (destroyMethod = 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 "/learn") (public class LearnController { private LearnService learnService; private Logger logger = LoggerFactory.getLogger(this.getClass()); "") ( public String learn(){ return "learn-resource"; } "/queryLeanList",method = RequestMethod.POST,produces="application/json;charset=UTF-8") (value = 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 */ "/add",method = RequestMethod.POST) (value = 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 */ "/update",method = RequestMethod.POST) (value = 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 */ "/delete",method = RequestMethod.POST) (value= 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 { LearnDao learnDao; public int add(LearnResouce learnResouce) { return this.learnDao.add(learnResouce); } public int update(LearnResouce learnResouce) { return this.learnDao.update(learnResouce); } public int deleteByIds(String ids) { return this.learnDao.deleteByIds(ids); } public LearnResouce queryLearnResouceById(Long id) { return this.learnDao.queryLearnResouceById(id); } 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{ private JdbcTemplate jdbcTemplate; public int add(LearnResouce learnResouce) { return jdbcTemplate.update("insert into learn_resource(author, title,url) values(?, ?, ?)",learnResouce.getAuthor(),learnResouce.getTitle(),learnResouce.getUrl()); } 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()}); } public int deleteByIds(String ids){ return jdbcTemplate.update("delete from learn_resource where id in("+ids+")"); } 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; } } 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干货系列总纲