Spring - 配置支持多数据源

SpringBoot整合多数据源

在SpringBoot工程中整合多数据源，和在SSM工程中整合稍微有一点区别。

整合步骤

具体整合步骤如下：

1、在application.properties中配置出多个数据源

spring.datasource.test1.jdbc-url=jdbc:mysql://yourmysqlurl/yourdb
spring.datasource.test1.username=yourusername
spring.datasource.test1.password=yourpassword
spring.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.test2.jdbc-url=jdbc:sqlserver://yoursqlserverurl;database=yourdb;
spring.datasource.test2.username=yourusername
spring.datasource.test2.password=yourpassword
spring.datasource.test2.driver.class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

2、在代码中创建出mapper目录，在mapper目录下创建出不同数据源的目录

创建出目录

MySQL数据源的Mapper

			@Repository
			public interface MUserTableMapper {
    			  public User getOneByMySQLId(Integer id);
			}

SQL Server数据源的Mapper

		@Repository
		public interface UserTableMapper {
		    public User getOneBySQLServerId(Integer id);
		}

3、创建config package目录，在config目录下创建出两个数据源的配置类

创建出目录

MySqlConfig

package com.test.multipledatasource.config;

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 javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.test.multipledatasource.mapper.mysql",sqlSessionTemplateRef = "mysqlSqlSessionTemplate")
public class DataSourceMysqlConfig {
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    @Primary
    public DataSource test1DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "mysqlTransactionMananger")
    @Primary
    public DataSourceTransactionManager test2TransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mysqlSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate test2SqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
//        sqlsessionTemplate：接受sqlsessionfactory中的数据来创建sqlsession
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

SQLServerConfig

package com.test.multipledatasource.config;

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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.test.multipledatasource.mapper.sqlserver",sqlSessionTemplateRef = "sqlserverSqlSessionTemplate")
public class DataSourceSqlServerConfig {

    //    @Bean：将方法生命成对象交个spring来管理。spring只引用一次该方法，然后就交由springIOC来管理
    @Bean(name="sqlserverDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
//    当一个类中有多个@Bean时，加@Primary注解的可以优先加载
    public DataSource test2DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlserverSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactroy(@Qualifier("sqlserverDataSource") DataSource dataSource) throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "sqlserverTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("sqlserverDataSource") DataSource dataSource) throws Exception{
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlserverSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("sqlserverSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return  new SqlSessionTemplate(sqlSessionFactory);
    }
}

4、在Resource下生成Mapper.xml文件

MySQL 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="com.test.multipledatasource.mapper.mysql.MUserTableMapper">

    <select id="getOneByMySQLId" resultType="com.test.multipledatasource.bean.User">
        select * from user_info
        WHERE id=#{id} 
    </select>
</mapper>

SQL Server 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="com.test.multipledatasource.mapper.sqlserver.UserTableMapper">

    <select id="getOneBySQLServerId" resultType="com.test.multipledatasource.bean.User">
        select * from user_info
        WHERE id=#{id} 
    </select>
</mapper>

验证

创建实体类

@Data
@NoArgsConstructor
@JsonSerialize(include = JsonSerialize.Inclusion.NON_NULL)
@EqualsAndHashCode(callSuper = false)
public class User {
	private String name ;
	private String passwd;
	...
}

创建Controller，编写测试接口

@RestController
public class UserInfoController {

	//My SQL mapper
    @Autowired
    MUserTableMapper mUserTableMapper;
    //SQL Server mapper
    @Autowired
    UserTableMapper userTableMapper;


    @GetMapping("/v1/api/mysql/user")
    public Result<User> getMySQLUserById(@RequestParam Integer userid) {
        //mysql数据源
        User user = mUserTableMapper.getOneByMySQLId(userid);
        return getResult(200,"Success",user);
    }

    @GetMapping("/v1/api/sqlserver/user")
    public Result<User> getSQLServerUserById(@RequestParam Integer userid) {
        //sql server数据源
        User user = userTableMapper.getOneBySQLServerId(userid);
        return getResult(200,"Success",user);
    }
}

Postman接口测试

查看接口返回正确。

SSM整合多数据源

在SSM工程中整合多数据源，基本和Spring Boot工程一致，稍微有一点区别。在Config配置类中需要手动的指定DataSource的信息。否则会报错：Cause: java.lang.IllegalArgumentException: one of either dataSource or dataSourceClassName must be specified

package com.test.config;

import com.zaxxer.hikari.HikariDataSource;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.test.mapper.mysql",sqlSessionTemplateRef = "mysqlSqlSessionTemplate")
public class DataSourceMysqlConfig {

    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    @Primary
    public DataSource test1DataSource(){
        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .driverClassName(properties.determineDriverClassName())
                .url(properties.determineUrl())
                .username(properties.determineUsername())
                .password(properties.determinePassword())
                .build();
    }

    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/test/mapper/mysql/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "mysqlTransactionMananger")
    @Primary
    public DataSourceTransactionManager test2TransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mysqlSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate test2SqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
//        sqlsessionTemplate：接受sqlsessionfactory中的数据来创建sqlsession
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}