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 DataSourceprivate 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提供了强大的连接池管理功能，包括设置最大连接数、最小空闲连接数、连接验证等。这样配置不仅能提高性能，还能有效管理数据库资源。