本文已参与「新人创作礼」活动，一起开启掘金创作之路。
多数据源
在一个项目里，可以同时可以访问多个不同的数据库。
单个数据源时我们只需要给项目配置一套配置。但是，当我们项目中需要用到多个数据源时，不同的数据源就需要绑定不同的配置。简单的思路就是让不同的数据源扫描不同的包，让不同的包下的 dao 层接口对应连接不同的数据源去处理逻辑。
虽然听起来简单，但是在实际实现过程中还是有不少的问题。
全局配置
项目依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--druid用起来比较简便-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
多数据源
自定义yml配置。当配置多数据源时，就不能直接使用Spring 进行自动配置了。
spring:
datasource:
one:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql:///springboot_vue?serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
two:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql:///springboot_vue2?serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
代码配置
项目注入多数据源
@Configuration
public class DaoConfiguration {
@Primary
@Bean
@ConfigurationProperties("spring.datasource.one")
DataSource dsOne() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.two")
DataSource dsTwo() {
return DruidDataSourceBuilder.create().build();
}
}
表结构
如果项目中有JPA ORM框架，设置了update或create，无需手动建表。
create table book
(
id int auto_increment
primary key,
create_by varchar(255) null,
create_time datetime null,
last_modified_by varchar(255) null,
last_modified_time datetime null,
author varchar(255) null,
name varchar(255) null,
price float null
);
Domain
对物理表结构的映射。
@Getter
@Setter
@ToString
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Book extends AuditModel {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer id;
private String name;
private String author;
private Float price;
@Transient
private String description;
}
JDBC多数据源
配置
一个数据源映射到一个JdbcTemplate。
@Configuration
public class JdbcConfig {
@Primary
@Bean
JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean
JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
使用测试
我们通过 Spring JdbcTemplate 来简化 CRUD 操作。
JdbcTemplate1
@Repository
public class BookJdbcDao {
@Resource
private JdbcTemplate jdbcTemplate;
public int addBook(Book book) {
return jdbcTemplate.update("INSERT INTO book(id, name, author) VALUES (?, ?, ?)", book.getId(), book.getName(), book.getAuthor());
}
}
JdbcTemplate2
@Repository
public class BookJdbcDao2 {
@Resource(name = "jdbcTemplateTwo")
private JdbcTemplate jdbcTemplate;
public int addBook(Book book) {
return jdbcTemplate.update("INSERT INTO book(id, name, author) VALUES (?, ?, ?)", book.getId(), book.getName(), book.getAuthor());
}
}
Mybatis多数据源
配置
Mapper接口
@Mapper
public interface BookMapper1 {
Integer addBook(Book book);
}
Mapper.xml接口实现
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.lmmarise.vue.persistent.dao.mybatis.mapper1.BookMapper1">
<insert id="addBook" useGeneratedKeys="true" keyProperty="id" parameterType="org.lmmarise.vue.persistent.org.domain.Book">
<selectKey keyProperty="id" resultType="Integer" order="BEFORE">
select if(max(id) is null, 1, max(id) + 1) as newId from book
</selectKey>
INSERT INTO book(id, name, author) VALUES (#{id}, #{name}, #{author})
</insert>
</mapper>
类似JdbcTemplate，取而代之的是SqlSessionTemplate。
@MapperScan(value = {"org.lmmarise.vue.persistent.dao.mybatis.mapper"}, // xml与dao接口所在位置
sqlSessionFactoryRef = "sqlSessionFactoryBean") // 指定 SqlSessionFactory 的 beanName
@MapperScan(value = {"org.lmmarise.vue.persistent.dao.mybatis.mapper1"}, // mapper 不能共用，接口相同但复制一份分开定义
sqlSessionFactoryRef = "sqlSessionFactoryBean1")
@Configuration
public class MybatisConfig {
@Resource
DataSource dsOne;
@Resource
DataSource dsTwo;
@Bean
SqlSessionFactory sqlSessionFactoryBean() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dsOne);
return factoryBean.getObject();
}
@Bean
SqlSessionFactory sqlSessionFactoryBean1() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dsTwo);
return factoryBean.getObject();
}
@Bean
SqlSessionTemplate sqlSessionTemplate() throws Exception {
return new SqlSessionTemplate(sqlSessionFactoryBean());
}
@Bean
SqlSessionTemplate sqlSessionTemplate1() throws Exception {
return new SqlSessionTemplate(sqlSessionFactoryBean1());
}
}
使用测试
@Service
public class BookMybatisService {
@Resource
private BookMapper bookDao; // 源1
@Resource
private BookMapper1 bookDao1; // 源2
public int addBook(Book book) {
return bookDao.addBook(book);
}
public int addBook1(Book book) {
return bookDao1.addBook(book);
}
}
JPA多数据源
配置
文件配置，新增
spring:
jpa: # 注意，这里只配置了JPA，Hibernate配置看下面代码（代码中配置）
show-sql: true
database: mysql
hibernate:
ddl-auto: update
properties:
hibernate:
dialect: org.hibernate.dialect.MySQL55Dialect
代码配置，与Mybatis相似，domain部分可以共用，动态代理实现的repository需要分开。Hibernate的persistenceUnit对应Mybatis的SqlSessionFactory。
@Configuration
public class JpaConfig {
@Resource
JpaProperties jpaProperties;
@Resource
private HibernateProperties properties;
@EnableJpaRepositories(basePackages = "org.lmmarise.vue.persistent.dao.jpa.repository", // 指定 Repository 所在的包【多个 persistenceUnit 不可共用】
entityManagerFactoryRef = "entityManagerFactoryBeanOne",
transactionManagerRef = "platformTransactionManagerOne")
@EnableTransactionManagement
@Configuration
class PersistenceUnit {
@Resource(name = "dsOne")
DataSource dsOne;
@Primary
@Bean
LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(dsOne)
.properties(
// 将 JPA 和 Hibernate 的配置组合并合并【避免项目 yml 中 JPA 配置不对 Hibernate 生效】
properties.determineHibernateProperties(
jpaProperties.getProperties(), new HibernateSettings()
)
)
.packages("org.lmmarise.vue.persistent.org") // 指定 @Entity 所在的包名
.persistenceUnit("pu")
.build();
}
@Primary
@Bean
PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean factoryOne = entityManagerFactoryBeanOne(builder);
return new JpaTransactionManager(Objects.requireNonNull(factoryOne.getObject()));
}
}
@EnableJpaRepositories(basePackages = "org.lmmarise.vue.persistent.dao.jpa.repository1",
entityManagerFactoryRef = "entityManagerFactoryBeanTwo",
transactionManagerRef = "platformTransactionManagerTwo")
@EnableTransactionManagement
@Configuration
class PersistenceUnit1 {
@Resource
DataSource dsTwo;
@Bean
LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanTwo(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(dsTwo)
.properties(
properties.determineHibernateProperties(
jpaProperties.getProperties(),
new HibernateSettings()
)
)
.packages("org.lmmarise.vue.persistent.org")
.persistenceUnit("pu1")
.build();
}
@Bean
PlatformTransactionManager platformTransactionManagerTwo(EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean factoryOne = entityManagerFactoryBeanTwo(builder);
return new JpaTransactionManager(Objects.requireNonNull(factoryOne.getObject()));
}
}
}
使用测试
@Service
public class BookJpaService {
@Resource
private BookJpaRepository bookJpaRepository;
@Resource
private BookJpaRepository1 bookJpaRepository1;
public Book addBook(Book book) {
return bookJpaRepository.save(book);
}
public Page<Book> getBookByPage1(PageRequest pageable) {
return bookJpaRepository1.findAll(pageable);
}
}
配置都很简单，踩坑可能会很久。
