oracle+jpa和mysql+mybatis的混合多数据源配置例子

最近在学习的时候看到了多数据源这一章，回想以前做的所有项目用到了MySQL，Oracle，Mybatis，SpringJpa等等。

这里不评论各自的优缺点，也不推荐该使用哪一种。大部分的文章都是单一技术配置多个库，这几样混合使用的比较少。

经过多次尝试和资料参考后终于能出来结果了，这里记录一下。

---

主体技术框架如下：

spring boot 2.0，thymeleaf，Oracle 12c + spring jpa，MySQL + mybatis。

---

主要的配置文件

1.MySqlDataSourceConfig

package com.example.demo.config;

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * @author qsky on 2018/7/2
 */
@Configuration
@MapperScan(basePackages = "com.example.demo.mapper", sqlSessionFactoryRef = "mySqlSessionFactory")
@EnableTransactionManagement
public class MySqlDataSourceConfig {

	@Bean(name = "mySqlDataSource")
	@Qualifier("mySqlDataSource")
	@Primary
	@ConfigurationProperties(prefix = "spring.datasource.hikari.mysql")
	public DataSource mySqlDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean
	@Primary
	public DataSourceTransactionManager masterManager() {
		return new DataSourceTransactionManager(mySqlDataSource());
	}

	@Bean
	@Primary
	public SqlSessionFactory mySqlSessionFactory() throws Exception {
		final SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(mySqlDataSource());
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/*.xml"));
		return bean.getObject();
	}

	@Bean
	@Primary
	public SqlSessionTemplate mySqlSessionTemplate() throws Exception {
		return new SqlSessionTemplate(mySqlSessionFactory());
	}
}

1.1 mapper包扫描路径：com.example.demo.mapper

1.2 mapper xml文件路径：classpath:mybatis/mapper/*.xml

2.OracleDataSourceConfig

package com.example.demo.config;

import java.util.Map;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * @author qsky on 2018/7/2
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "oracleEntityManagerFactory", transactionManagerRef = "oracleTransactionManager", basePackages = {
		"com.example.demo.repository"})
public class OracleDataSourceConfig {

	@Bean(name = "oracleDataSource")
	@Qualifier("oracleDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.hikari.oracle")
	public DataSource oracleDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Resource
	private JpaProperties jpaProperties;

	@Bean(name = "entityManager")
	public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
		return oracleEntityManagerFactory(builder).getObject().createEntityManager();
	}

	/**
	 * 设置实体类所在位置
	 */
	@Bean(name = "oracleEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory(EntityManagerFactoryBuilder builder) {
		return builder.dataSource(oracleDataSource())
				.packages("com.example.demo.entity")
				.persistenceUnit("oraclePersistenceUnit")
				.properties(getProperties())
				.build();
	}

	private Map<String, Object> getProperties() {
		return jpaProperties.getHibernateProperties(new HibernateSettings());
	}

	@Bean(name = "oracleTransactionManager")
	public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
		return new JpaTransactionManager(oracleEntityManagerFactory(builder).getObject());
	}
}

2.1 repository包路径：com.example.demo.repository

2.2 实体类包路径：com.example.demo.entity

3.application.properties

spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
mybatis.type-aliases-package=com.example.demo.model
mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
spring.datasource.hikari.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.mysql.jdbc-url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8
spring.datasource.hikari.mysql.username=root
spring.datasource.hikari.mysql.password=123456
spring.datasource.hikari.oracle.jdbc-url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.hikari.oracle.username=c##dev
spring.datasource.hikari.oracle.password=123456

spring.jpa.show-sql=true
#数据库方言设置，避免出现limit查询给oracle
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect

注意配置前缀要带上hikari

---

编写repository和mapper时无需考虑使用哪个数据源，像正常一样使用，因为上面在配置时已经分包了。

具体demo代码见GitHub：混合多数据源demo