Spring Boot学习(七)之Web应用使用jdbctemplate多数据源配置

一时失言乱红尘 2022-06-05 07:43 186阅读 0赞

上篇文章中说到单数据原连接访问,插入数据;但是往往随着业务量发展，我们通常会进行数据库拆分或是引入其他数据库，从而我们需要配置多个数据源，下面基于之前的JdbcTemplate和Spring-data-jpa例子分别介绍两种多数据源的配置方式。

先看pom文件 引用的jar

<?xml version="1.0" encoding="UTF-8"?>
    <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.xiaojingg</groupId>
       <artifactId>springbootstudy-demo7-jdbctemplate-more</artifactId>
       <version>0.0.1-SNAPSHOT</version>
       <packaging>jar</packaging>
    
       <name>springbootstudy-demo7-jdbctemplate-more</name>
       <description>Demo project for Spring Boot</description>
    
       <parent>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-parent</artifactId>
          <version>1.5.8.RELEASE</version>
          <relativePath/> 
       </parent>
    
       <properties>
          <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
          <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
          <java.version>1.8</java.version>
       </properties>
    
       <dependencies>
          <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter</artifactId>
          </dependency>
    
          <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>
             <version>5.1.21</version>
          </dependency>
    
          <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-jdbc</artifactId>
          </dependency>
    
       </dependencies>
    
       <build>
          <plugins>
             <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
             </plugin>
          </plugins>
       </build>
    
    </project>

**多数据源配置**

创建一个Spring配置类，定义两个DataSource用来读取application.properties中的不同配置。如下例子中，主数据源配置为spring.datasource.primary开头的配置，第二数据源配置为spring.datasource.secondary开头的配置。

package com.xiaojingg;
    
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    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;
    
    /**
     * 筱进GG
     */
    @Configuration
    public class DataSourceConfig {
    
        @Bean(name = "primaryDataSource")
        @Qualifier("primaryDataSource")
        @ConfigurationProperties(prefix="spring.datasource.primary")
        public DataSource primaryDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "secondaryDataSource")
        @Qualifier("secondaryDataSource")
        @Primary
        @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 secondaryJdbcTemplate(
                @Qualifier("secondaryDataSource") DataSource dataSource) {
            return new JdbcTemplate(dataSource);
        }
    
    }

对应的application.properties配置如下：

spring.datasource.primary.url=jdbc:mysql://localhost:3306/test1
    spring.datasource.primary.username=root
    spring.datasource.primary.password=root
    spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
    
    spring.datasource.secondary.url=jdbc:mysql://localhost:3306/test2
    spring.datasource.secondary.username=root
    spring.datasource.secondary.password=root
    spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver

接下来通过测试用例来演示如何使用这两个针对不同数据源的JdbcTemplate:

package com.xiaojingg;
    
    import org.junit.Assert;
    import org.junit.Before;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.test.context.junit4.SpringRunner;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class SpringbootstudyDemo7JdbctemplateMoreApplicationTests {
    
       @Autowired
       @Qualifier("primaryJdbcTemplate")
       protected JdbcTemplate jdbcTemplate1;
    
       @Autowired
       @Qualifier("secondaryJdbcTemplate")
       protected JdbcTemplate jdbcTemplate2;
    
       @Before
       public void setUp() {
          jdbcTemplate1.update("DELETE  FROM  USER ");
          jdbcTemplate2.update("DELETE  FROM  USER ");
       }
    
       @Test
       public void test() throws Exception {
    
          // 往第一个数据源中插入两条数据
          jdbcTemplate1.update("insert into user(id,name,age) values(?, ?, ?)", 1, "aaa", 20);
          jdbcTemplate1.update("insert into user(id,name,age) values(?, ?, ?)", 2, "bbb", 30);
    
          // 往第二个数据源中插入一条数据，若插入的是第一个数据源，则会主键冲突报错
          jdbcTemplate2.update("insert into user(id,name,age) values(?, ?, ?)", 1, "aaa", 20);
    
          // 查一下第一个数据源中是否有两条数据，验证插入是否成功
          Assert.assertEquals("2", jdbcTemplate1.queryForObject("select count(1) from user", String.class));
    
          // 查一下第一个数据源中是否有两条数据，验证插入是否成功
          Assert.assertEquals("1", jdbcTemplate2.queryForObject("select count(1) from user", String.class));
    
       }
    
    }

运行截图:

![Center][]

![Center 1][]

[Center]: /images/20220605/b8c1d9778f5a43bd988eaf3126369d6c.png
[Center 1]: /images/20220605/ce8bbd2ee2964e54a633c64daace9848.png