本文講解 Spring Boot 基礎下，如何使用 JDBC，配置資料來源和通過 JdbcTemplate 編寫資料訪問。
原文地址：Spring Boot 揭祕與實戰（二） 資料儲存篇 – MySQL
部落格地址：blog.720ui.com/
環境依賴
修改 POM 檔案，新增spring-boot-starter-jdbc依賴。
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>複製程式碼新增mysql依賴。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.14</version>
</dependency>複製程式碼資料來源
方案一 使用 Spring Boot 預設配置
使用 Spring Boot 預設配置，不需要在建立 dataSource 和 jdbcTemplate 的 Bean。
在 src/main/resources/application.properties 中配置資料來源資訊。
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3307/springboot_db
spring.datasource.username=root
spring.datasource.password=root複製程式碼方案二 手動建立
在 src/main/resources/config/source.properties 中配置資料來源資訊。
# mysql
source.driverClassName = com.mysql.jdbc.Driver
source.url = jdbc:mysql://localhost:3306/springboot_db
source.username = root
source.password = root複製程式碼通過 Java Config 建立 dataSource 和jdbcTemplate。
@Configuration
@EnableTransactionManagement
@PropertySource(value = {"classpath:config/source.properties"})
public class BeanConfig {
@Autowired
private Environment env;
@Bean(destroyMethod = "close")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(env.getProperty("source.driverClassName").trim());
dataSource.setUrl(env.getProperty("source.url").trim());
dataSource.setUsername(env.getProperty("source.username").trim());
dataSource.setPassword(env.getProperty("source.password").trim());
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
}複製程式碼指令碼初始化
先初始化需要用到的SQL指令碼。
CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `springboot_db`;
DROP TABLE IF EXISTS `t_author`;
CREATE TABLE `t_author` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT `使用者ID`,
`real_name` varchar(32) NOT NULL COMMENT `使用者名稱稱`,
`nick_name` varchar(32) NOT NULL COMMENT `使用者匿名`,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;複製程式碼使用JdbcTemplate操作
實體物件
public class Author {
private Long id;
private String realName;
private String nickName;
// SET和GET方法
}複製程式碼DAO相關
public interface AuthorDao {
int add(Author author);
int update(Author author);
int delete(Long id);
Author findAuthor(Long id);
List<Author> findAuthorList();
}複製程式碼我們來定義實現類，通過JdbcTemplate定義的資料訪問操作。
@Repository
public class AuthorDaoImpl implements AuthorDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int add(Author author) {
return jdbcTemplate.update("insert into t_author(real_name, nick_name) values(?, ?)",
author.getRealName(), author.getNickName());
}
@Override
public int update(Author author) {
return jdbcTemplate.update("update t_author set real_name = ?, nick_name = ? where id = ?",
new Object[]{author.getRealName(), author.getNickName(), author.getId()});
}
@Override
public int delete(Long id) {
return jdbcTemplate.update("delete from t_author where id = ?", id);
}
@Override
public Author findAuthor(Long id) {
List<Author> list = jdbcTemplate.query("select * from t_author where id = ?", new Object[]{id}, new BeanPropertyRowMapper<Author>(Author.class));
if(null != list && list.size()>0){
Author auhtor = list.get(0);
return auhtor;
}else{
return null;
}
}
@Override
public List<Author> findAuthorList() {
List<Author> list = jdbcTemplate.query("select * from t_author", new Object[]{}, new BeanPropertyRowMapper<Author>(Author.class));
return list;
}
}複製程式碼Service相關
public interface AuthorService {
int add(Author author);
int update(Author author);
int delete(Long id);
Author findAuthor(Long id);
List<Author> findAuthorList();
}複製程式碼我們來定義實現類，Service層呼叫Dao層的方法，這個是典型的套路。
@Service("authorService")
public class AuthorServiceImpl implements AuthorService {
@Autowired
private AuthorDao authorDao;
@Override
public int add(Author author) {
return this.authorDao.add(author);
}
@Override
public int update(Author author) {
return this.authorDao.update(author);
}
@Override
public int delete(Long id) {
return this.authorDao.delete(id);
}
@Override
public Author findAuthor(Long id) {
return this.authorDao.findAuthor(id);
}
@Override
public List<Author> findAuthorList() {
return this.authorDao.findAuthorList();
}
}複製程式碼Controller相關
為了展現效果，我們先定義一組簡單的 RESTful API 介面進行測試。
@RestController
@RequestMapping(value="/data/jdbc/author")
public class AuthorController {
@Autowired
private AuthorService authorService;
/**
* 查詢使用者列表
*/
@RequestMapping(method = RequestMethod.GET)
public Map<String,Object> getAuthorList(HttpServletRequest request) {
List<Author> authorList = this.authorService.findAuthorList();
Map<String,Object> param = new HashMap<String,Object>();
param.put("total", authorList.size());
param.put("rows", authorList);
return param;
}
/**
* 查詢使用者資訊
*/
@RequestMapping(value = "/{userId:\d+}", method = RequestMethod.GET)
public Author getAuthor(@PathVariable Long userId, HttpServletRequest request) {
Author author = this.authorService.findAuthor(userId);
if(author == null){
throw new RuntimeException("查詢錯誤");
}
return author;
}
/**
* 新增方法
*/
@RequestMapping(method = RequestMethod.POST)
public void add(@RequestBody JSONObject jsonObject) {
String userId = jsonObject.getString("user_id");
String realName = jsonObject.getString("real_name");
String nickName = jsonObject.getString("nick_name");
Author author = new Author();
if (author!=null) {
author.setId(Long.valueOf(userId));
}
author.setRealName(realName);
author.setNickName(nickName);
try{
this.authorService.add(author);
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException("新增錯誤");
}
}
/**
* 更新方法
*/
@RequestMapping(value = "/{userId:\d+}", method = RequestMethod.PUT)
public void update(@PathVariable Long userId, @RequestBody JSONObject jsonObject) {
Author author = this.authorService.findAuthor(userId);
String realName = jsonObject.getString("real_name");
String nickName = jsonObject.getString("nick_name");
author.setRealName(realName);
author.setNickName(nickName);
try{
this.authorService.update(author);
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException("更新錯誤");
}
}
/**
* 刪除方法
*/
@RequestMapping(value = "/{userId:\d+}", method = RequestMethod.DELETE)
public void delete(@PathVariable Long userId) {
try{
this.authorService.delete(userId);
}catch(Exception e){
throw new RuntimeException("刪除錯誤");
}
}
}複製程式碼總結
通過，上面這個簡單的案例，我們發現 Spring Boot 仍然秉承了 Spring 框架的一貫套路，並簡化 Spring 應用的初始搭建以及開發過程。
原始碼
相關示例完整程式碼： springboot-action
（完）
更多精彩文章，盡在「服務端思維」微信公眾號！
