Spring Boot-JDBC

Spring Boot & JDBC

1. Spring Boot整合JDBC操作数据库

JDBC操作数据库流程

  1. 加载数据库驱动
  2. 建立数据库连接
  3. 创建数据库操作对象
  4. 定义操作的SQL语句
  5. 执行数据库操作
  6. 获取并操作结果集
  7. 关闭对象,回收资源

不建议使用JDBC

将Spring JDBC整合到Spring Boot

  1. pom.xml引入依赖
1
2
3
4
5
6
7
8
9
10
11
12
13
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.1.8.RELEASE</version>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
  1. 修改application.yml
1
2
3
4
5
6
spring:
datasource:
url: jdbc:mysql://localhost:3306/jdbc
username: root
password: Thwf1858
driver-class-name: com.mysql.jdbc.Driver
  1. DAO层代码
  • jdbcTemplate.update适用于insert, update和delete操作

  • jdbcTemplate.queryForObject用于查询单条记录并返回结果

  • jdbcTemplate.query用于查询结果列表

  • BeanPropertyRowMapper可以将数据库字段的值向数据库映射,满足驼峰标识也可以自动映射

    e.x. 数据库create_time映射到createTime属性

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
package com.haven.dao;

import com.fasterxml.jackson.databind.BeanProperty;
import com.haven.model.Article;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
* @author HavenTong
* @date 2019/10/30 10:39 下午
*/
@Repository // @Repository标注持久层
public class ArticleJDBCDAO {

@Resource
private JdbcTemplate jdbcTemplate;

// 保存文章
public void save(Article article){
jdbcTemplate.update("INSERT INTO article(author, title, content, create_time) values (?,?,?,?)",
article.getAuthor(),
article.getTitle(),
article.getContent(),
article.getCreateTime());
}

// 删除文章
// 传参可以用 new Object[]{}传,也可以一个一个设置
public void deleteById(int id){
jdbcTemplate.update("DELETE FROM article WHERE id=?", new Object[]{id});
}

// 更新文章
public void updateById(Article article){
jdbcTemplate.update("UPDATE article SET author=?, title=?, content=?, create_time=? WHERE id=?",
article.getAuthor(),
article.getTitle(),
article.getContent(),
article.getCreateTime(),
article.getId());
}

// 根据id查找文章
public Article findById(int id){
return (Article)jdbcTemplate.queryForObject("SELECT * FROM article WHERE id = ?",
new Object[]{id},
new BeanPropertyRowMapper(Article.class));
}

// 查询所有
public List<Article> findAll(){
return (List<Article>) jdbcTemplate.query("SELECT * FROM article", new BeanPropertyRowMapper(Article.class));
}

}

2. JDBC多数据源

(1) application.yml配置两个数据源,第一个叫primary, 第二个叫secondar,也可以自己取名:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
server:
port: 8080
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
datasource:
primary:
jdbc-url: jdbc:mysql://localhost:3306/jdbc
username: root
password: Thwf1858
driver-class-name: com.mysql.jdbc.Driver
secondary:
jdbc-url: jdbc:mysql://localhost:3306/test
username: root
password: Thwf1858
driver-class-name: com.mysql.jdbc.Driver

(2) 通过Java Config将数据源注入到Spring上下文

primaryJdbcTemplate使用primaryDataSource数据源操作数据库jdbc

secondaryJdbcTemplate使用secondaryDataSource数据源操作数据库test

DataSourceConfig.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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.haven.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import javax.xml.crypto.Data;

/**
* @author HavenTong
* @date 2019/10/31 12:08 上午
*/
@Configuration
public class DataSourceConfig {
@Primary
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource(){
return DataSourceBuilder.create().build();
}

@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource(){
return DataSourceBuilder.create().build();
}

@Bean(name = "primaryJdbcTemplate")
public JdbcTemplate primaryJdbcTemplate(
@Qualifier("primaryDataSource") DataSource dataSource ){
return new JdbcTemplate(dataSource);
}

@Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryTemplate(
@Qualifier("secondaryDataSource") DataSource dataSource){
return new JdbcTemplate(dataSource);
}
}

(3) 之后修改dao层代码:

ArticleJDBCDAO.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
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
package com.haven.dao;

import com.fasterxml.jackson.databind.BeanProperty;
import com.haven.model.Article;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
* @author HavenTong
* @date 2019/10/30 10:39 下午
*/
@Repository
public class ArticleJDBCDAO {

// 保存文章
public void save(Article article, JdbcTemplate jdbcTemplate){
jdbcTemplate.update("INSERT INTO article(author, title, content, create_time) values (?,?,?,?)",
article.getAuthor(),
article.getTitle(),
article.getContent(),
article.getCreateTime());
}

// 删除文章
public void deleteById(int id, JdbcTemplate jdbcTemplate){

jdbcTemplate.update("DELETE FROM article WHERE id=?", new Object[]{id});
}

// 更新文章
public void updateById(Article article, JdbcTemplate jdbcTemplate){
jdbcTemplate.update("UPDATE article SET author=?, title=?, content=?, create_time=? WHERE id=?",
article.getAuthor(),
article.getTitle(),
article.getContent(),
article.getCreateTime(),
article.getId());
}

// 根据id查找文章
public Article findById(int id, JdbcTemplate jdbcTemplate){
return (Article)jdbcTemplate.queryForObject("SELECT * FROM article WHERE id = ?",
new Object[]{id},
new BeanPropertyRowMapper(Article.class));
}

// 查询所有
public List<Article> findAll(JdbcTemplate jdbcTemplate){
return (List<Article>) jdbcTemplate.query("SELECT * FROM article", new BeanPropertyRowMapper(Article.class));
}

}

(4) 修改service层代码

ArticleRestJDBCServiceImpl.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
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
package com.haven.service;

import com.haven.dao.ArticleJDBCDAO;
import com.haven.model.Article;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

/**
* @author HavenTong
* @date 2019/10/30 10:56 下午
*/
@Slf4j
@Service
public class ArticleRestJDBCServiceImpl implements ArticleRestService {

@Resource
ArticleJDBCDAO articleJDBCDAO;

@Resource
JdbcTemplate primaryJdbcTemplate;

@Resource
JdbcTemplate secondaryJdbcTemplate;

@Transactional
@Override
public Article saveArticle(Article article) {
articleJDBCDAO.save(article, primaryJdbcTemplate);
articleJDBCDAO.save(article, secondaryJdbcTemplate);
return article;

}

@Override
public void deleteArticle(int id) {
articleJDBCDAO.deleteById(id, primaryJdbcTemplate);
articleJDBCDAO.deleteById(id, secondaryJdbcTemplate);
}

@Override
public void updateArticle(Article article) {
articleJDBCDAO.updateById(article, primaryJdbcTemplate);
}

@Override
public Article getArticle(int id) {
return articleJDBCDAO.findById(id, primaryJdbcTemplate);
}

@Override
public List<Article> getAll() {
return articleJDBCDAO.findAll(primaryJdbcTemplate);
}
}

3. Spring JDBC JTA实现分布式事务

@Transactional无法跨库完成分布式事务

通过整合JTA实现分布式事务

  • 引入maven依赖
1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jta-atomikos -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
<version>2.2.0.RELEASE</version>
</dependency>
  • 修改application.yml配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
primarydb:
uniqueResourceName: primary
xaDataSourceClassName: com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
xaProperties:
url: jdbc:mysql://localhost:3306/jdbc
user: root
password: Thwf1858
exclusiveConnectionMode: true
minPoolSize: 3
maxPoolSize: 10
testQuery: SELECT 1 FROM dual
secondarydb:
uniqueResourceName: secondary
xaDataSourceClassName: com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
xaProperties:
url: jdbc:mysql://localhost:3306/test
user: root
password: Thwf1858
exclusiveConnectionMode: true
minPoolSize: 3
maxPoolSize: 10
testQuery: SELECT 1 FROM dual
  • 编写配置类DataSourceConfig.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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.haven.config;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import javax.xml.crypto.Data;

/**
* @author HavenTong
* @date 2019/10/31 12:08 上午
*/
@Configuration
public class DataSourceConfig {
// 多数据源,分布式
@Bean(initMethod = "init", destroyMethod = "close", name = "primaryDataSource")
@Primary
@ConfigurationProperties(prefix = "primarydb")
public DataSource primaryDataSource(){
return new AtomikosDataSourceBean();
}

@Bean(initMethod = "init", destroyMethod = "close", name = "secondaryDataSource")
@ConfigurationProperties(prefix = "secondarydb")
public DataSource secondaryDataSource(){
return new AtomikosDataSourceBean();
}

@Bean
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource")
DataSource primaryDataSource){
return new JdbcTemplate(primaryDataSource);
}

@Bean
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource")
DataSource secondaryDataSource){
return new JdbcTemplate(secondaryDataSource);
}

}
  • 配置事务管理器™ TransactionManagerConfig.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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package com.haven.config;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.jta.JtaTransactionManager;

import javax.transaction.SystemException;
import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;

/**
* @author HavenTong
* @date 2019/10/31 3:57 下午
*/
@Configuration
public class TransactionManagerConfig {

@Bean
public UserTransaction userTransaction() throws SystemException {
UserTransactionImp userTransactionImp = new UserTransactionImp();
userTransactionImp.setTransactionTimeout(10000);
return userTransactionImp;
}

@Bean(name = "atomikosTransactionManager", initMethod = "init", destroyMethod = "close")
public TransactionManager atomikosTransactionManager() throws Throwable{
UserTransactionManager userTransactionManager = new UserTransactionManager();
userTransactionManager.setForceShutdown(false);
return userTransactionManager;
}

@Bean(name = "transactionManager")
@DependsOn({"userTransaction", "atomikosTransactionManger"})
public PlatformTransactionManager transactionManager() throws Throwable{
UserTransaction userTransaction = userTransaction();
JtaTransactionManager manager = new JtaTransactionManager(userTransaction, atomikosTransactionManager());
return manager;
}
}

Kommentare