Jdbi3|SpringBoot中Jdbi3使用多数据源详解 boot|java|spring|spring

在SpringBoot集成Jdbi3的项目中,如果想使用多数据源,可以按照以下方式来实现:
编辑属性文件,例如: application.yaml

spring: datasource: master: driver-class-name: "com.mysql.jdbc.Driver" jdbc-url: "jdbc:mysql://127.0.0.1:3306/mt?useSSL=false&useUnicode=true&characterEncoding=utf-8" username: "root" password: "111111" minimum-idle: 5 maximum-pool-size: 10slave: driver-class-name: "com.mysql.jdbc.Driver" jdbc-url: "jdbc:mysql://127.0.0.1:3307/snr?useSSL=false&useUnicode=true&characterEncoding=utf-8" username: "root" password: "111111" minimum-idle: 5 maximum-pool-size: 10

提示: datasource下面的属性配置这块,由于SpringBoot2默认使用Hikari,因此属性要跟Hikari的属性一致才行.例如要用jdbc-url而不能使用url .
属性详情请参考SpringBoot Application properties指南里的spring.datasource.hikari.项

文章图片

【Jdbi3|SpringBoot中Jdbi3使用多数据源详解】Hikari常用配置:

# Hikari pool https://github.com/brettwooldridge/HikariCP spring.datasource.type=com.zaxxer.hikari.HikariDataSource # 连接池中允许的最小连接数。缺省值：10 spring.datasource.hikari.minimum-idle=10 # 连接池中允许的最大连接数。缺省值：10 spring.datasource.hikari.maximum-pool-size=100 # 自动提交 spring.datasource.hikari.auto-commit=true # 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟 spring.datasource.hikari.idle-timeout=30000 # 连接池名字 spring.datasource.hikari.pool-name=FlyduckHikariCP # 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒 spring.datasource.hikari.max-lifetime=1800000 # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException，缺省:30秒 spring.datasource.hikari.connection-timeout=30000 # 数据库连接测试语句 spring.datasource.hikari.connection-test-query=SELECT 1

编写多数据源配置类:

package wjw.test.jdbi; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties; 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; //多数据源配置类 @Configuration public class DatasourceConfiguration { @Bean(name = "masterDatasource") @Primary @ConfigurationProperties(prefix = "spring.datasource.master") public DataSource primaryDataSource() { DataSource ds =DataSourceBuilder.create().build(); return ds; }@Bean(name = "slaveDatasource") @ConfigurationProperties(prefix = "spring.datasource.slave") public DataSource secondDataSource() { DataSource ds =DataSourceBuilder.create().build(); return ds; } }

编写Jdbi配置类:

package wjw.test.jdbi; import javax.sql.DataSource; import org.jdbi.v3.core.Jdbi; import org.jdbi.v3.sqlobject.SqlObjectPlugin; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; /** * Bean生产配置类 * @author White Stone * * 2021年5月13日 */ @Configuration public class JdbiConfiguration { @Bean(name = "jdbi") @Primary public Jdbi jdbi(DataSource dataSource) { return Jdbi.create(dataSource) .installPlugin(new SqlObjectPlugin()); }@Bean(name = "slaveJdbi") public Jdbi secondJdbi(@Qualifier("slaveDatasource") DataSource dataSource) { return Jdbi.create(dataSource) .installPlugin(new SqlObjectPlugin()); }}

测试类:

package wjw.test.jdbi; import java.math.BigDecimal; import java.util.List; import java.util.Map; import java.util.Optional; import org.jdbi.v3.core.Handle; import org.jdbi.v3.core.Jdbi; import org.jdbi.v3.core.mapper.reflect.ConstructorMapper; import org.jdbi.v3.core.mapper.reflect.FieldMapper; import org.jdbi.v3.core.statement.Slf4JSqlLogger; import org.jdbi.v3.core.transaction.TransactionIsolationLevel; import org.junit.jupiter.api.AfterEach; import org.junit.jupiter.api.BeforeEach; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.ActiveProfiles; @SpringBootTest @ActiveProfiles("dev") //标明激活哪个profile public class JdbiTests1 { private final Slf4JSqlLogger log; @Autowired private Jdbi jdbi3; @Autowired @Qualifier("slaveJdbi") private Jdbi secondJdbi3; public JdbiTests1() { this.log = new Slf4JSqlLogger(org.slf4j.LoggerFactory.getLogger(this.getClass())); }@BeforeEach public void setup() { jdbi3.setSqlLogger(log); secondJdbi3.setSqlLogger(log); }@AfterEach public void stop() { }/** * 测试 setMaxRows & mapToMap */ @Test void testSelect1() { List> list1 = jdbi3.withHandle(handle -> { return handle.createQuery("SELECT * FROM store_detail_info").setMaxRows(3).mapToMap().list(); }); System.out.println(list1); }