Springboot 之 JDBC 多数据源实现

简介

Springboot 中使用 JdbcTemplate 多数据源的实现相对简单。查看 JdbcTemplate 源代码；可以找到 JdbcTemplate 提供了传入 DataSource 以不同的方式构建 JdbcTemplate 实例。多数据源可以通过这种方式实现。

public JdbcTemplate() {}public JdbcTemplate(DataSource dataSource) {    this.setDataSource(dataSource);    this.afterPropertiesSet();}public JdbcTemplate(DataSource dataSource, boolean lazyInit) {    this.setDataSource(dataSource);    this.setLazyInit(lazyInit);    this.afterPropertiesSet();}

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.olive</groupId><artifactId>jdbc-multip-datasource</artifactId><version>0.0.1-SNAPSHOT</version><packaging>jar</packaging><name>jdbc-multip-datasource</name><url>http://maven.apache.org</url><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.5.14</version><relativePath /> <!-- lookup parent from repository --></parent><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency>    <groupId>org.springframework.boot</groupId>    <artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency></dependencies></project>

第一个主数据源分别是第一个(primary)，第二数据源(second)，具体配置如下：

# server基本配置:  port: 8080# Spring数据库:  datasource:    primary:      driver-class-name: com.mysql.jdbc.Driver      jdbc-url: jdbc:mysql://127.0.0.1:3306/db01?characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true      username: root      password: root    second:      driver-class-name: com.mysql.cj.jdbc.Driver      jdbc-url: jdbc:mysql://127.0.0.1:3306/crm72?characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true      username: root      password: root

DataSourceconfit配置

package com.olive.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 javax.sql.DataSource;/** * @Description: 数据源配置 */@Configurationpublic class DataSourceConfig {    @Bean(name = "primaryDataSource")    @Qualifier("primaryDataSource")    @ConfigurationProperties(prefix = "spring.datasource.primary")    @Primary    public DataSource primaryDataSource() {        return DataSourceBuilder.create().build();    }    @Bean(name = "secondDataSource")    @Qualifier("secondDataSource")    @ConfigurationProperties(prefix = "spring.datasource.second")    public DataSource secondDataSource() {        return DataSourceBuilder.create().build();    }}

构建 JdbcTemplateConfig 类

package com.olive.config;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.jdbc.core.JdbcTemplate;import javax.sql.DataSource;/** * @Description: 数据源配置 * @since */@Configurationpublic class JdbcTemplateConfig {    @Bean(name = "primaryJdbcTemplate")    public JdbcTemplate primaryJdbcTemplate(            @Qualifier("primaryDataSource") DataSource dataSource) {        return new JdbcTemplate(dataSource);    }    @Bean(name = "secondJdbcTemplate")    public JdbcTemplate secondaryJdbcTemplate(            @Qualifier("secondDataSource") DataSource dataSource) {        return new JdbcTemplate(dataSource);    }}

StudentDO 实体类

package com.olive.entity;import java.io.Serializable;import lombok.Data;@Datapublic class StudentDO implements Serializable{    private Long id;    private String name;    private int sex;        private String grade;}

TeacherDO 实体类

package com.olive.entity;import java.io.Serializable;import lombok.Data;@Datapublic class TeacherDO implements Serializable {private Long id;private String name;private int sex;private String office;}

StudentRepository 类

package com.olive.dao;import com.olive.entity.TeacherDO;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.stereotype.Repository;import com.olive.entity.StudentDO;@Repositorypublic class StudentRepository {    @Autowired    @Qualifier("primaryJdbcTemplate")    private JdbcTemplate jdbcTemplate;    public boolean save(StudentDO studentDO) {        int result =jdbcTemplate.update("INSERT INTO t_student (`user_name`, `sex`, `grade`) VALUES ( ?, ?, ?, ?);",                studentDO.getName(),                studentDO.getSex(),                studentDO.getGrade());        return result>0;    }    public TeacherDO getById(Long id) {        return jdbcTemplate.queryForObject("select * from t_student where id = ?",                new BeanPropertyRowMapper<TeacherDO>(TeacherDO.class), new Object[]{id});    }}

TeacherRepository 类

package com.olive.dao;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.stereotype.Repository;import com.olive.entity.TeacherDO;@Repositorypublic class TeacherRepository {    @Autowired    @Qualifier("secondJdbcTemplate")    private JdbcTemplate jdbcTemplate;    public boolean save(TeacherDO teacherDO) {       int result = jdbcTemplate.update("INSERT INTO t_teacher (`user_name`, `sex`, `office`) VALUES ( ?, ?, ?, ?);",                teacherDO.getName(),                teacherDO.getSex(),                teacherDO.getOffice());       return result>0;    }    public TeacherDO getById(Long id) {        return jdbcTemplate.queryForObject("select * from t_teacher where id = ?",                new BeanPropertyRowMapper<TeacherDO>(TeacherDO.class), new Object[]{id});    }}

package com.olive;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;/** * @author 2230 * */@SpringBootApplicationpublic class Application {    public static void main(String[] args) {        SpringApplication.run(Application.class);    }}

package com.olive;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import com.olive.entity.StudentDO;import com.olive.entity.TeacherDO;import com.olive.dao.StudentRepository;import com.olive.dao.TeacherRepository;@SpringBootTestpublic class JdbcTest {@AutowiredStudentRepository studentRepository;@AutowiredTeacherRepository teacherRepository;@Testpublic void userSave() {StudentDO studentDO = new StudentDO();studentDO.setName(“BUG弄潮儿”);studentDO.setSex(1);studentDO.setGrade(一年级);studentRepository.save(studentDO);TeacherDO teacherDO = new TeacherDO();teacherDO.setName(Java乐园);teacherDO.setSex(2);teacherDO.setOffice(语文);teacherRepository.save(teacherDO);}}