springboot项目多数据源配置

有时候同步数据，需要操作不一样类型的数据库，或者一个服务操作不同的数据源，所以需要用到多数据源。

1、多数据源，首先需要有对应的依赖，如双数据源是mysql和Oracle数据库，那么就需要mysql的依赖和Oracle的依赖。

Oracle依赖

<!-- oracle -->
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>RELEASE</version>
		</dependency>

mysql依赖

<!-- mysql -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.46</version>
		</dependency>

其他（mybatis，druid）数据库依赖：

<!-- durid连接池 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>
		<!-- mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>

2、添加不同数据源的配置，在application.properties中添加

Oracle数据源配置

spring.datasource.oracle.jdbc-url=jdbc:oracle:thin:@127.0.0.1:1521/orcl
spring.datasource.oracle.username=test
spring.datasource.oracle.password=123456
spring.datasource.oracle.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.oracle.type=com.alibaba.druid.pool.DruidDataSource

mysql数据源配置

spring.pdcy.mysql.url=jdbc:mysql://127.0.0.1:3306/test_base?useSSL=true&useUnicode=true&characterEncoding=UTF-8&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=CTT
spring.pdcy.mysql.username=root
spring.pdcy.mysql.password=123456
spring.pdcy.mysql.driverClassName=com.mysql.jdbc.Driver
spring.pdcy.mysql.validationQuery=SELECT 1
spring.pdcy.mysql.testWhileIdle=true

3、项目结构如图所示：

在resource下新增mybatis配置文件：
mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<settings>
		<setting name="cacheEnabled" value="true" />
		<setting name="lazyLoadingEnabled" value="true" />
		<setting name="multipleResultSetsEnabled" value="true" />
		<setting name="useColumnLabel" value="true" />
		<setting name="mapUnderscoreToCamelCase" value="true" />
		<setting name="defaultExecutorType" value="REUSE" />
		<setting name="defaultStatementTimeout" value="25000" />
		<setting name="callSettersOnNulls" value="true" />
    	 <setting name="logImpl" value="STDOUT_LOGGING" />
		<setting name="jdbcTypeForNull" value="VARCHAR"/>
	</settings>

	<typeHandlers>
		<typeHandler handler="com.znv.data.config.mybatis.oracle.MyObjectTypeHandle"/>
	</typeHandlers>
</configuration>

在DataSource目录下新建配置文件
mysqlDataSource配置如下：

@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@MapperScan(basePackages = "com.znv.data.mapper.mysql", sqlSessionTemplateRef = "mysqlSqlSessionTemplate")
public class MysqlDataSourceConfig {

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

    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory SqlSessionFactory(@Qualifier("mysqlDataSource") DataSource mysqlDataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(mysqlDataSource);
        sqlSessionFactory.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mysql/*.xml"));
        return sqlSessionFactory.getObject();
    }

    @Bean(name = "mysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager TransactionManager(@Qualifier("mysqlDataSource") DataSource mysqlDataSource) {
        return new DataSourceTransactionManager(mysqlDataSource);
    }

    @Bean(name = "mysqlSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate rdsSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory mysqlSqlSessionFactory) {
        return new SqlSessionTemplate(mysqlSqlSessionFactory);
    }

    @Bean(name = "mysqlNamedParameterJdbcTemplate")
    @Primary
    public NamedParameterJdbcTemplate NamedParameterJdbcTemplate(@Qualifier("mysqlDataSource") DataSource mysqlDataSource) {
        return new NamedParameterJdbcTemplate(mysqlDataSource);
    }
}

接下来是OracleDataSource文件，

@Configuration
@MapperScan(basePackages = "com.znv.data.mapper.oracle", sqlSessionTemplateRef = "oracleSqlSessionTemplate")
public class OracleDataSourceConfig {

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

    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory OracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource adsDataSource) throws Exception {

        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(adsDataSource);
        sqlSessionFactory.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/oracle/*.xml"));
        return sqlSessionFactory.getObject();
    }

    @Bean(name = "oracleTransactionManager")
    public DataSourceTransactionManager adsTransactionManager(@Qualifier("oracleDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "oracleSqlSessionTemplate")
    public SqlSessionTemplate oracleSqlSessionTemplate(@Qualifier("oracleSqlSessionFactory") SqlSessionFactory SqlSessionFactory) {
        return new SqlSessionTemplate(SqlSessionFactory);
    }

    @Bean(name = "oracleNamedParameterJdbcTemplate")
    @Primary
    public NamedParameterJdbcTemplate oracleNamedParameterJdbcTemplate(@Qualifier("oracleDataSource") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
}

这里为了防止报错需要添加，Oracle的类型转换代码
MyObjectTypeHandle.java

@MappedTypes({Object.class})
@MappedJdbcTypes(value = {JdbcType.TIMESTAMP})
@Slf4j
public class MyObjectTypeHandle extends BaseTypeHandler<Object> {
    public MyObjectTypeHandle() {
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setObject(i, parameter);
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Object result = rs.getObject(columnName);
        return rs.wasNull() ? null : dealResult(result);
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Object result = rs.getObject(columnIndex);
        return rs.wasNull() ? null : dealResult(result);
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Object result = cs.getObject(columnIndex);
        return cs.wasNull() ? null : dealResult(result);
    }

    /**
     * 为了解决错误：
     * 26-Sep-2018 14:21:06.634 WARNING [http-apr-8080-exec-6] org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver.handleHttpMessageNotWritable Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException:
     * Could not write JSON: No serializer found for class java.io.ByteArrayInputStream and no properties discovered to create BeanSerializer
     * (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS);
     * nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class java.io.ByteArrayInputStream and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS)
     * (through reference chain: java.util.HashMap["pageData"]->java.util.ArrayList[0]->java.util.HashMap["UPDATE_TIME"]->oracle.sql.TIMESTAMP["stream"])
     * @param result
     * @return
     * @throws SQLException
     */
    private Object dealResult(Object result) throws SQLException {
        if (result instanceof TIMESTAMP) {
            return new Date(((TIMESTAMP) result).dateValue().getTime());
        } else if (result instanceof DATE) {
            return new Date(((DATE) result).dateValue().getTime());
        } else if (result instanceof TIMESTAMPLTZ) {
            return new Date(((TIMESTAMPLTZ) result).dateValue().getTime());
        } else if (result instanceof TIMESTAMPTZ) {
            return new Date(((TIMESTAMPTZ) result).dateValue().getTime());
        } else{
            return result;
        }
    }
}