[Java知识库]Spring boot：Spring boot配置多数据源

?
关于Spring boot配置多数据源问题
1、首先需要在application.yml文件中添加两个数据源。具体格式如下：

datasource:
    ip: localhost
    port: 5432
    dbName: Test
    test1:
     username: postgres
     password: postgres
     jdbc-url:     
jdbc:postgresql://${spring.datasource.ip}:${spring.datasource.port}/${spring.datasource.dbName}
     driverClassName: org.postgresql.Driver
    test2:
     username: postgres
     password: 123456
     jdbc-url: jdbc:postgresql://192.168.1.103:5432/Student
     driverClassName: org.postgresql.Driver

2、由于Spring boot的自动装载不适用于多数据源，所以应该为每一个数据源各创建一个手动配置的配置文件。

注意：默认数据源配置文件里的每个方法都需要加@Primary注解，表示此数据源为默认数据源，不加的话Spring boot找不到默认的数据源

**默认数据源：**

package com.test.demo.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 javax.sql.DataSource;
 
@Configuration
//数据源mapper接口所在包
@MapperScan(basePackages = "com.test.demo.dao.test1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {

    @Primary 
    @Bean("db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1") 
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //数据源的mapper.xml所在位置(具体到xml文件)
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:**/com/test/**/dao/test1/mapping/*Mapper.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

其他数据源：

package com.test.demo.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 javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.test.demo.dao.test2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {

    @Bean("db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:**/com/test/**/dao/test2/mapping/*Mapper.xml"));
        return bean.getObject();
    }

    @Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3、由于Spring boot启动类的@Mapperscan和各数据源的@Mapperscan只能生效一种,且同时存在时以启动类优先,故应取消启动类的@Mapperscan注解

package com.test.demo;

import java.io.UnsupportedEncodingException;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;

@SpringBootApplication(scanBasePackages = "com.test")
@EnableScheduling
//@MapperScan("com.test.demo.dao")
public class DemoApplication {
	public static void main(String[] args) throws UnsupportedEncodingException {	
		SpringApplication.run(DemoApplication.class, args);  
	}
}

4、在为每个数据源编写Mapper接口及xml时，应按数据源将这些文件区分在不同的包下。

区分好包后，多数据源就配好了。写两个测试类：
Controller
test1

@RequestMapping(value="/getStudentList",method=RequestMethod.GET)
public List<Map<String,Object>> getStudentList() {
	return studentService.getStudentList();
}

test2

@RequestMapping(value="/getStudentsList",method=RequestMethod.GET)
	public List<Map<String,Object>> getStudentsList() {
		return userService.getStudentsList();
	}

ServiceImpl
test1：

@Override
public List<Map<String, Object>> getStudentList() {
	List<Map<String, Object>> studentList = null;
	try {
		studentList = studentMapper.getStudentList();
		return studentList;
	}catch(Exception e){
		log.error("getStudentList error ,errorMessage:{}", e.getMessage());
		return studentList;
	}
}

test2：

	@Override
public List<Map<String, Object>> getStudentsList() {
	List<Map<String, Object>> studentList = null;
	try {
		studentList = studentsMapper.getStudentsList();
		return studentList;
	}catch(Exception e){
		System.out.println(e);
		return studentList;
	}
}

Test1Mapper.xml

<mapper namespace="com.test.demo.dao.test1.StudentMapper">
<select id="getStudentList" resultType="java.util.LinkedHashMap" >
		SELECT
			tbl_student_info.student_id,
			student_name AS NAME,
			student_sex AS sex,
			student_age AS age,
			tbl_grade_info.grade_id AS gradeId,
			tbl_class_info.class_id AS classId,
		CASE 
			WHEN student_post = '' 
				THEN '' 
			WHEN student_post IS NULL 
				THEN '' 
			ELSE concat ( grade_name, class_name, student_post ) 
		END AS post 
		FROM
			tbl_student_conn
			LEFT JOIN tbl_student_info ON tbl_student_conn.student_id = tbl_student_info.student_id
			LEFT JOIN tbl_class_info ON tbl_student_conn.class_id = tbl_class_info.class_id
			LEFT JOIN tbl_grade_info ON tbl_student_conn.grade_id = tbl_grade_info.grade_id
		WHERE 
			tbl_student_info.deleted=0
		ORDER BY tbl_student_info.student_id ASC
	</select>
</mapper>

Test2Mapper.xml

<mapper namespace="com.test.demo.dao.test2.StudentMapper">
<select id="getStudentList" resultType="java.util.LinkedHashMap" >
		SELECT
			tbl_student_info.student_id,
			student_name AS NAME,
			student_sex AS sex,
			student_age AS age,
			tbl_grade_info.grade_id AS gradeId,
			tbl_class_info.class_id AS classId,
		CASE 
			WHEN student_post = '' 
				THEN '' 
			WHEN student_post IS NULL 
				THEN '' 
			ELSE concat ( grade_name, class_name, student_post ) 
		END AS post 
		FROM
			tbl_student_conn
			LEFT JOIN tbl_student_info ON tbl_student_conn.student_id = tbl_student_info.student_id
			LEFT JOIN tbl_class_info ON tbl_student_conn.class_id = tbl_class_info.class_id
			LEFT JOIN tbl_grade_info ON tbl_student_conn.grade_id = tbl_grade_info.grade_id
		WHERE 
			tbl_student_info.deleted=0
		ORDER BY tbl_student_info.student_id ASC
	</select>
</mapper>

test1的运行结果

test2的运行结果