Spring Boot & JDBC

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

JDBC操作数据库流程

加载数据库驱动
建立数据库连接
创建数据库操作对象
定义操作的SQL语句
执行数据库操作
获取并操作结果集
关闭对象，回收资源

不建议使用JDBC

将Spring JDBC整合到Spring Boot

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>

修改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

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);
    }

}

配置事务管理器(TM) 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;
    }
}

Spring Boot & JDBC#

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

JDBC操作数据库流程#

将Spring JDBC整合到Spring Boot#

2. JDBC多数据源#

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

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