有时候我们需要在程序中加载多个数据源,用spring jpa可以实现,网上有许多资料,要指定哪个包下面的类使用哪个数据源,个人觉得这种方式不够灵活,不是我想要的。我是想通过多个jdbcTemplate的实例,不同实例操作不同的数据源,甚至不同的实例操作同一类路径的实体对象。下面记录下,实现加载读写分离两个数据源的过程。
pom文件如下:
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.1.RELEASE</version> </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-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.6</version> </dependency> </dependencies>
application.properties配置文件:
spring.datasource.readwrite.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.readwrite.url=jdbc:mysql://10.1.1.190:3306/ubi?useUnicode=true&characterEncoding=utf-8&autoReconnect=true
spring.datasource.readwrite.username=root
spring.datasource.readwrite.password=park
spring.datasource.readwrite.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.read.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.read.url=jdbc:mysql://10.1.1.190:3306/ubi?useUnicode=true&characterEncoding=utf-8&autoReconnect=true
spring.datasource.read.username=root
spring.datasource.read.password=park
spring.datasource.read.driver-class-name=com.mysql.jdbc.Driver
程序启动时候,加载不同数据源,注入不同的jdbcTemplate实例:
package com.yame.ubi.config;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
@Configuration
public class MultiDataSourceConfig {
@Bean
@Primary
@ConfigurationProperties("spring.datasource.readwrite")
public DataSourceProperties firstDataSourceProperties() {
return new DataSourceProperties();
}
@Bean("dataSource")
@Primary
@ConfigurationProperties("spring.datasource.readwrite")
public DataSource firstDataSource() {
return firstDataSourceProperties().initializeDataSourceBuilder().build();
}
@Bean
@ConfigurationProperties("spring.datasource.read")
public DataSourceProperties secondDataSourceProperties() {
return new DataSourceProperties();
}
@Bean(name="readDatasource")
@ConfigurationProperties("spring.datasource.read")
public DataSource secondDataSource() {
return secondDataSourceProperties().initializeDataSourceBuilder().build();
}
@Bean
@Primary
public JdbcTemplate primaryJdbcTemplate(@Qualifier("dataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "readJdbcTemplate")
public JdbcTemplate secondJdbcTemplate(@Qualifier("readDatasource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
声明bean的时候,主数据源强烈建议加上 @Primary注解,同时两个datasource,也要加上bean名称加以区分,否则会报各种各样的错误,如:@Bean("dataSource")和@Bean(name="readDatasource")
使用jdbcTemplate的时候,加上@Qualifier标明使用的是哪个数据源的连接:
package com.yame.ubi.dao;
import java.util.Date;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class MyBeanDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Qualifier("readJdbcTemplate")
@Autowired
private JdbcTemplate jdbcTemplateReadOnly;
public Map<String, Object> getRepacket(long id) {
String sql = "SELECT * FROM ubi_receive_record WHERE id = ?";
Map<String, Object> map = jdbcTemplateReadOnly.queryForMap(sql, id);
return map;
}
public int update(long userId, int bind) {
String sql = "update ubi_receive_record set obd_bind = ?, update_time = ? where user_id = ?";
return jdbcTemplate.update(sql, bind, new Date(), userId);
}
}
代码:https://github.com/breezylee/lby/tree/master/ubi-cron
(忽略工程里面的定时任务,rabbitmq等配置)