文章目录

1. 1. 环境依赖
2. 2. 数据源3. 脚本初始化
   1. 2.1. 方案一 使用 Spring Boot 默认配置
   2. 2.2. 方案二 手动创建
3. 4. 使用JdbcTemplate操作5. 总结
   1. 4.1. 实体对象
   2. 4.2. DAO相关
   3. 4.3. Service相关
   4. 4.4. Controller相关
4. 6. 源代码

本文讲解 Spring Boot 基础下，如何使用 JDBC，配置数据源和通过 JdbcTemplate 编写数据访问。

环境依赖

修改 POM 文件，添加spring-boot-starter-jdbc依赖。

1. <dependency>
2. <groupId>org.springframework.boot</groupId>
3. <artifactId>spring-boot-starter-jdbc</artifactId>
4. </dependency>

添加mysql依赖。

1. <dependency>
2. <groupId>mysql</groupId>
3. <artifactId>mysql-connector-java</artifactId>
4. <version>5.1.35</version>
5. </dependency>
6. <dependency>
7. <groupId>com.alibaba</groupId>
8. <artifactId>druid</artifactId>
9. <version>1.0.14</version>
10. </dependency>

数据源

方案一 使用 Spring Boot 默认配置

使用 Spring Boot 默认配置，不需要在创建 dataSource 和 jdbcTemplate 的 Bean。

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

1. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
2. spring.datasource.url=jdbc:mysql://localhost:3307/springboot_db
3. spring.datasource.username=root
4. spring.datasource.password=root

方案二 手动创建

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

1. # mysql
2. source.driverClassName = com.mysql.jdbc.Driver
3. source.url = jdbc:mysql://localhost:3306/springboot_db
4. source.username = root
5. source.password = root

通过 Java Config 创建 dataSource 和jdbcTemplate。

1. @Configuration
2. @EnableTransactionManagement
3. @PropertySource(value = {"classpath:config/source.properties"})
4. public class BeanConfig {
5. @Autowired
6. private Environment env;
7. @Bean(destroyMethod = "close")
8. public DataSource dataSource() {
9. DruidDataSource dataSource = new DruidDataSource();
10. dataSource.setDriverClassName(env.getProperty("source.driverClassName").trim());
11. dataSource.setUrl(env.getProperty("source.url").trim());
12. dataSource.setUsername(env.getProperty("source.username").trim());
13. dataSource.setPassword(env.getProperty("source.password").trim());
14. return dataSource;
15. }
16. @Bean
17. public JdbcTemplate jdbcTemplate() {
18. JdbcTemplate jdbcTemplate = new JdbcTemplate();
19. jdbcTemplate.setDataSource(dataSource());
20. return jdbcTemplate;
21. }
22. }

脚本初始化

先初始化需要用到的SQL脚本。

1. CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
2. USE `springboot_db`;
3. DROP TABLE IF EXISTS `t_author`;
4. CREATE TABLE `t_author` (
5. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
6. `real_name` varchar(32) NOT NULL COMMENT '用户名称',
7. `nick_name` varchar(32) NOT NULL COMMENT '用户匿名',
8. PRIMARY KEY (`id`)
9. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

使用JdbcTemplate操作

实体对象

1. public class Author {
2. private Long id;
3. private String realName;
4. private String nickName;
5. // SET和GET方法
6. }

DAO相关

1. public interface AuthorDao {
2. int add(Author author);
3. int update(Author author);
4. int delete(Long id);
5. Author findAuthor(Long id);
6. List<Author> findAuthorList();
7. }

我们来定义实现类，通过JdbcTemplate定义的数据访问操作。

1. @Repository
2. public class AuthorDaoImpl implements AuthorDao {
3. @Autowired
4. private JdbcTemplate jdbcTemplate;
5. @Override
6. public int add(Author author) {
7. return jdbcTemplate.update("insert into t_author(real_name, nick_name) values(?, ?)",
8. author.getRealName(), author.getNickName());
9. }
10. @Override
11. public int update(Author author) {
12. return jdbcTemplate.update("update t_author set real_name = ?, nick_name = ? where id = ?",
13. new Object[]{author.getRealName(), author.getNickName(), author.getId()});
14. }
15. @Override
16. public int delete(Long id) {
17. return jdbcTemplate.update("delete from t_author where id = ?", id);
18. }
19. @Override
20. public Author findAuthor(Long id) {
21. List<Author> list = jdbcTemplate.query("select * from t_author where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Author.class));
22. if(null != list && list.size()>0){
23. Author auhtor = list.get(0);
24. return auhtor;
25. }else{
26. return null;
27. }
28. }
29. @Override
30. public List<Author> findAuthorList() {
31. List<Author> list = jdbcTemplate.query("select * from t_author", new Object[]{}, new BeanPropertyRowMapper<Author>(Author.class));
32. return list;
33. }
34. }

Service相关

1. public interface AuthorService {
2. int add(Author author);
3. int update(Author author);
4. int delete(Long id);
5. Author findAuthor(Long id);
6. List<Author> findAuthorList();
7. }

我们来定义实现类，Service层调用Dao层的方法，这个是典型的套路。

1. @Service("authorService")
2. public class AuthorServiceImpl implements AuthorService {
3. @Autowired
4. private AuthorDao authorDao;
5. @Override
6. public int add(Author author) {
7. return this.authorDao.add(author);
8. }
9. @Override
10. public int update(Author author) {
11. return this.authorDao.update(author);
12. }
13. @Override
14. public int delete(Long id) {
15. return this.authorDao.delete(id);
16. }
17. @Override
18. public Author findAuthor(Long id) {
19. return this.authorDao.findAuthor(id);
20. }
21. @Override
22. public List<Author> findAuthorList() {
23. return this.authorDao.findAuthorList();
24. }
25. }

Controller相关

为了展现效果，我们先定义一组简单的　RESTful API 接口进行测试。

1. @RestController
2. @RequestMapping(value="/data/jdbc/author")
3. public class AuthorController {
4. @Autowired
5. private AuthorService authorService;
6. /**
7. * 查询用户列表
8. */
9. @RequestMapping(method = RequestMethod.GET)
10. public Map<String,Object> getAuthorList(HttpServletRequest request) {
11. List<Author> authorList = this.authorService.findAuthorList();
12. Map<String,Object> param = new HashMap<String,Object>();
13. param.put("total", authorList.size());
14. param.put("rows", authorList);
15. return param;
16. }
17. /**
18. * 查询用户信息
19. */
20. @RequestMapping(value = "/{userId:\\d+}", method = RequestMethod.GET)
21. public Author getAuthor(@PathVariable Long userId, HttpServletRequest request) {
22. Author author = this.authorService.findAuthor(userId);
23. if(author == null){
24. throw new RuntimeException("查询错误");
25. }
26. return author;
27. }
28. /**
29. * 新增方法
30. */
31. @RequestMapping(method = RequestMethod.POST)
32. public void add(@RequestBody JSONObject jsonObject) {
33. String userId = jsonObject.getString("user_id");
34. String realName = jsonObject.getString("real_name");
35. String nickName = jsonObject.getString("nick_name");
36. Author author = new Author();
37. if (author!=null) {
38. author.setId(Long.valueOf(userId));
39. }
40. author.setRealName(realName);
41. author.setNickName(nickName);
42. try{
43. this.authorService.add(author);
44. }catch(Exception e){
45. e.printStackTrace();
46. throw new RuntimeException("新增错误");
47. }
48. }
49. /**
50. * 更新方法
51. */
52. @RequestMapping(value = "/{userId:\\d+}", method = RequestMethod.PUT)
53. public void update(@PathVariable Long userId, @RequestBody JSONObject jsonObject) {
54. Author author = this.authorService.findAuthor(userId);
55. String realName = jsonObject.getString("real_name");
56. String nickName = jsonObject.getString("nick_name");
57. author.setRealName(realName);
58. author.setNickName(nickName);
59. try{
60. this.authorService.update(author);
61. }catch(Exception e){
62. e.printStackTrace();
63. throw new RuntimeException("更新错误");
64. }
65. }
66. /**
67. * 删除方法
68. */
69. @RequestMapping(value = "/{userId:\\d+}", method = RequestMethod.DELETE)
70. public void delete(@PathVariable Long userId) {
71. try{
72. this.authorService.delete(userId);
73. }catch(Exception e){
74. throw new RuntimeException("删除错误");
75. }
76. }
77. }

总结

通过，上面这个简单的案例，我们发现 Spring Boot 仍然秉承了 Spring 框架的一贯套路，并简化 Spring 应用的初始搭建以及开发过程。

源代码

相关示例完整代码： springboot-action

(完)