springboot项目，使用原生JDBC、Druid实现多数据源

1. 引入Druid依赖

首先，确保在pom.xml中已经添加了Druid的依赖。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.9</version>
</dependency>

2. 在application.properties中配置Druid数据源

为每个数据源设置Druid连接池相关的属性，包括最大连接数、初始连接数等。

# DataSource 1
spring.datasource.url=jdbc:mysql://localhost:3306/db1
spring.datasource.username=user1
spring.datasource.password=pass1
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initial-size=5
spring.datasource.min-idle=5
spring.datasource.max-active=20
spring.datasource.max-wait=60000
spring.datasource.time-between-eviction-runs-millis=60000
spring.datasource.min-evictable-idle-time-millis=300000
spring.datasource.validation-query=SELECT 1 FROM DUAL
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
spring.datasource.pool-prepared-statements=true
spring.datasource.max-pool-prepared-statement-per-connection-size=20

# DataSource 2
spring.datasource2.url=jdbc:mysql://localhost:3306/db2
spring.datasource2.username=user2
spring.datasource2.password=pass2
spring.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource2.initial-size=5
spring.datasource2.min-idle=5
spring.datasource2.max-active=20
spring.datasource2.max-wait=60000
spring.datasource2.time-between-eviction-runs-millis=60000
spring.datasource2.min-evictable-idle-time-millis=300000
spring.datasource2.validation-query=SELECT 1 FROM DUAL
spring.datasource2.test-while-idle=true
spring.datasource2.test-on-borrow=false
spring.datasource2.test-on-return=false
spring.datasource2.pool-prepared-statements=true
spring.datasource2.max-pool-prepared-statement-per-connection-size=20

# DataSource 3
spring.datasource3.url=jdbc:mysql://localhost:3306/db3
spring.datasource3.username=user3
spring.datasource3.password=pass3
spring.datasource3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource3.initial-size=5
spring.datasource3.min-idle=5
spring.datasource3.max-active=20
spring.datasource3.max-wait=60000
spring.datasource3.time-between-eviction-runs-millis=60000
spring.datasource3.min-evictable-idle-time-millis=300000
spring.datasource3.validation-query=SELECT 1 FROM DUAL
spring.datasource3.test-while-idle=true
spring.datasource3.test-on-borrow=false
spring.datasource3.test-on-return=false
spring.datasource3.pool-prepared-statements=true
spring.datasource3.max-pool-prepared-statement-per-connection-size=20

3. 使用Druid配置多数据源

我们将创建一个配置类DataSourceConfig，使用Druid连接池来管理多数据源。

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@Configuration
public class DataSourceConfig {

    @Value("${spring.datasource.url}")
    private String db1Url;

    @Value("${spring.datasource.username}")
    private String db1Username;

    @Value("${spring.datasource.password}")
    private String db1Password;

    @Value("${spring.datasource2.url}")
    private String db2Url;

    @Value("${spring.datasource2.username}")
    private String db2Username;

    @Value("${spring.datasource2.password}")
    private String db2Password;

    @Value("${spring.datasource3.url}")
    private String db3Url;

    @Value("${spring.datasource3.username}")
    private String db3Username;

    @Value("${spring.datasource3.password}")
    private String db3Password;

    // 配置 Druid DataSource 1
    @Bean(name = "db1DataSource")
    public DataSource db1DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(db1Url);
        dataSource.setUsername(db1Username);
        dataSource.setPassword(db1Password);
        configureDataSource(dataSource);
        return dataSource;
    }

    // 配置 Druid DataSource 2
    @Bean(name = "db2DataSource")
    public DataSource db2DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(db2Url);
        dataSource.setUsername(db2Username);
        dataSource.setPassword(db2Password);
        configureDataSource(dataSource);
        return dataSource;
    }

    // 配置 Druid DataSource 3
    @Bean(name = "db3DataSource")
    public DataSource db3DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(db3Url);
        dataSource.setUsername(db3Username);
        dataSource.setPassword(db3Password);
        configureDataSource(dataSource);
        return dataSource;
    }

    // 通用配置 Druid DataSource
    private void configureDataSource(DruidDataSource dataSource) {
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(20);
        dataSource.setMaxWait(60000);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("SELECT 1 FROM DUAL");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
    }

    @Bean(name = "db1Connection")
    public Connection db1Connection() throws SQLException {
        return db1DataSource().getConnection();
    }

    @Bean(name = "db2Connection")
    public Connection db2Connection() throws SQLException {
        return db2DataSource().getConnection();
    }

    @Bean(name = "db3Connection")
    public Connection db3Connection() throws SQLException {
        return db3DataSource().getConnection();
    }
}

4. 使用原始的JDBC操作

和之前一样，你可以在你的业务逻辑中注入并使用这些配置好的Connection实例。

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Service
public class MyService {

    @Autowired
    @Qualifier("db1Connection")
    private Connection db1Connection;

    @Autowired
    @Qualifier("db2Connection")
    private Connection db2Connection;

    @Autowired
    @Qualifier("db3Connection")
    private Connection db3Connection;

    public void performDb1Operations() throws SQLException {
        String sql = "SELECT COUNT(*) FROM some_table";
        try (PreparedStatement pstmt = db1Connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("DB1 Count: " + count);
            }
        }
    }

    public void performDb2Operations() throws SQLException {
        String sql = "SELECT COUNT(*) FROM another_table";
        try (PreparedStatement pstmt = db2Connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("DB2 Count: " + count);
            }
        }
    }

    public void performDb3Operations() throws SQLException {
        String sql = "SELECT COUNT(*) FROM yet_another_table";
        try (PreparedStatement pstmt = db3Connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("DB3 Count: " + count);
            }
        }
    }
}

总结

通过以上配置，你现在可以使用Druid连接池管理多个数据源，并通过原始的JDBC代码进行数据库操作。Druid提供了强大的连接池管理功能，包括设置最大连接数、最小空闲连接数、连接验证等。这样配置不仅能提高性能，还能有效管理数据库资源。