Spring Boot 多数据源配置

2019年1月6日 0条评论 656次阅读 0人点赞

前言

在微服务体系搭建中，难免会有需要使用多个数据源的情况。今天米饭屋就给大家介绍一下在Spring Boot 1.xx环境中（2.xx环境中有些区别，后续再介绍）如何配置多个数据源。
因为篇幅的原因（其实就是懒，哈哈），本文使用MySQL和MsSQL两种数据源来示例，其他种类的数据源大家举一反三就行了。

pom.xml 配置

核心配置有三个：jpa，mysql，mssql。

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<groupId>com.microsoft.sqlserver</groupId>

<artifactId>mssql-jdbc</artifactId>

<scope>runtime</scope>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<scope>runtime</scope>

</dependency>

application.yml 配置

spring:
  jpa:
    database-platform: mysql
    generate-ddl: true
    show-sql: true
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    properties:
      hibernate.dialect:  org.hibernate.dialect.MySQLDialect
      hibernate.hbm2ddl.auto: update
  datasource:
    example:
      mysql:
        url: jdbc:mysql://192.168.1.100:3306/Example?characterEncoding=UTF-8&useSSL=false
        username: root
        password: 123456
        driver-class-name: com.mysql.jdbc.Driver
        remove-abandoned: true
        remove-abandoned-timeout: 36000
        validation-query: select 1
        test-on-borrow: true
      mssql:
        url: jdbc:sqlserver://192.168.1.101:1433;DatabaseName=Example
        username: sa
        password: 123456
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        remove-abandoned: true
        remove-abandoned-timeout: 36000
        validation-query: select 1
        test-on-borrow: true

spring:

jpa:

database-platform: mysql

generate-ddl: true

show-sql: true

hibernate:

naming:

physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

properties:

hibernate.dialect: org.hibernate.dialect.MySQLDialect

hibernate.hbm2ddl.auto: update

datasource:

example:

mysql:

url: jdbc:mysql://192.168.1.100:3306/Example?characterEncoding=UTF-8&useSSL=false

username: root

password: 123456

driver-class-name: com.mysql.jdbc.Driver

remove-abandoned: true

remove-abandoned-timeout: 36000

validation-query: select 1

test-on-borrow: true

mssql:

url: jdbc:sqlserver://192.168.1.101:1433;DatabaseName=Example

username: sa

password: 123456

driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

remove-abandoned: true

remove-abandoned-timeout: 36000

validation-query: select 1

test-on-borrow: true

Spring Boot 配置

连接属性配置

package com.example.mulitdatasource.configuration;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfiguration {
    @Primary
    @Bean(name = "ExampleMySQLDataSource")
    @Qualifier("ExampleMySQLDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.example.mysql")
    public DataSource ExampleMySQLDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ExampleMsSQLDataSource")
    @Qualifier("ExampleMsSQLDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.example.mssql")
    public DataSource VideoDataSource(){
        return DataSourceBuilder.create().build();
    }

    //如果还有其他数据源，在此添加相应的连接属性即可
    //......
}

package com.example.mulitdatasource.configuration;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration

public class DataSourceConfiguration {

@Primary

@Bean(name = "ExampleMySQLDataSource")

@Qualifier("ExampleMySQLDataSource")

@ConfigurationProperties(prefix = "spring.datasource.example.mysql")

public DataSource ExampleMySQLDataSource(){

return DataSourceBuilder.create().build();

}

@Bean(name = "ExampleMsSQLDataSource")

@Qualifier("ExampleMsSQLDataSource")

@ConfigurationProperties(prefix = "spring.datasource.example.mssql")

public DataSource VideoDataSource(){

return DataSourceBuilder.create().build();

}

//如果还有其他数据源，在此添加相应的连接属性即可

//......

}

mysql数据源配置

package com.example.mulitdatasource.configuration.mysql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
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;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "ExampleMySQLEntityManagerFactory",
        transactionManagerRef = "ExampleMySQLTransactionManager",
        basePackages = {"com.example.mulitdatasource.repositories.mysql"}
)
public class ExampleMySQLDataSourceConfiguration {
    private final JpaProperties jpaProperties;

    private final DataSource exampleMySQLDataSource;

    @Autowired
    public ExampleMySQLDataSourceConfiguration(JpaProperties jpaProperties, @Qualifier("ExampleMySQLDataSource") DataSource dataSource) {
        this.jpaProperties = jpaProperties;
        this.exampleMySQLDataSource = dataSource;
    }

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

    @Primary
    @Bean(name = "ExampleMySQLEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean exampleMySQLEntityManagerFactory(EntityManagerFactoryBuilder builder){
        LocalContainerEntityManagerFactoryBean persistenceUnit = builder
                .dataSource(exampleMySQLDataSource)
                .properties(getVendorProperties(exampleMySQLDataSource))
                .packages("com.example.mulitdatasource.entities.mysql")
                .persistenceUnit("MySQLPersistenceUnit")
                .build();

        Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
        persistenceUnit.setJpaProperties(properties);

        return persistenceUnit;
    }

    @Primary
    @Bean(name = "ExampleMySQLTransactionManager")
    public PlatformTransactionManager transactionManagerMonitor(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(exampleMySQLEntityManagerFactory(builder).getObject());
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }
}

package com.example.mulitdatasource.configuration.mysql;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;

import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

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;

import javax.persistence.EntityManager;

import javax.sql.DataSource;

import java.util.Map;

import java.util.Properties;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

entityManagerFactoryRef = "ExampleMySQLEntityManagerFactory",

transactionManagerRef = "ExampleMySQLTransactionManager",

basePackages = {"com.example.mulitdatasource.repositories.mysql"}

)

public class ExampleMySQLDataSourceConfiguration {

private final JpaProperties jpaProperties;

private final DataSource exampleMySQLDataSource;

@Autowired

public ExampleMySQLDataSourceConfiguration(JpaProperties jpaProperties, @Qualifier("ExampleMySQLDataSource") DataSource dataSource) {

this.jpaProperties = jpaProperties;

this.exampleMySQLDataSource = dataSource;

}

@Primary

@Bean(name = "ExampleMySQLEntityManager")

public EntityManager entityManager(EntityManagerFactoryBuilder builder){

return exampleMySQLEntityManagerFactory(builder).getObject().createEntityManager();

}

@Primary

@Bean(name = "ExampleMySQLEntityManagerFactory")

public LocalContainerEntityManagerFactoryBean exampleMySQLEntityManagerFactory(EntityManagerFactoryBuilder builder){

LocalContainerEntityManagerFactoryBean persistenceUnit = builder

.dataSource(exampleMySQLDataSource)

.properties(getVendorProperties(exampleMySQLDataSource))

.packages("com.example.mulitdatasource.entities.mysql")

.persistenceUnit("MySQLPersistenceUnit")

.build();

Properties properties = new Properties();

properties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");

persistenceUnit.setJpaProperties(properties);

return persistenceUnit;

}

@Primary

@Bean(name = "ExampleMySQLTransactionManager")

public PlatformTransactionManager transactionManagerMonitor(EntityManagerFactoryBuilder builder) {

return new JpaTransactionManager(exampleMySQLEntityManagerFactory(builder).getObject());

}

private Map<String, String> getVendorProperties(DataSource dataSource) {

return jpaProperties.getHibernateProperties(dataSource);

}

mssql数据源配置

package com.example.mulitdatasource.configuration.mssql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "ExampleMsSQLEntityManagerFactory",
        transactionManagerRef = "ExampleMsSQLTransactionManager",
        basePackages = {"com.example.mulitdatasource.repositories.mssql"}
)
public class ExampleMsSQLDataSourceConfiguration {
    private final JpaProperties jpaProperties;

    private final DataSource exampleMsSQLDataSource;

    @Autowired
    public ExampleMsSQLDataSourceConfiguration(JpaProperties jpaProperties, @Qualifier("ExampleMsSQLDataSource") DataSource dataSource) {
        this.jpaProperties = jpaProperties;
        this.exampleMsSQLDataSource = dataSource;
    }

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

    @Bean(name = "ExampleMsSQLEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean exampleMsSQLEntityManagerFactory(EntityManagerFactoryBuilder builder){
        LocalContainerEntityManagerFactoryBean persistenceUnit = builder
                .dataSource(exampleMsSQLDataSource)
                .properties(getVendorProperties(exampleMsSQLDataSource))
                .packages("com.example.mulitdatasource.entities.mssql")
                .persistenceUnit("ExampleMsSQLPersistenceUnit")
                .build();

        Properties properties = new Properties();
        //如果不需要扩展方言，直接使用以下代码
        //properties.setProperty("hibernate.dialect", "org.hibernate.dialect.SQLServer2008Dialect");
        //这里我添加了一个自定义言配置，是为了兼容对于某些像nchar这类字段名不兼容的情况
        properties.setProperty("hibernate.dialect", "com.example.mulitdatasource.configuration.dialect.CustomSQLServerDialect");
        persistenceUnit.setJpaProperties(properties);

        return persistenceUnit;
    }

    @Bean(name = "ExampleMsSQLTransactionManager")
    public PlatformTransactionManager transactionManagerMonitor(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(exampleMsSQLEntityManagerFactory(builder).getObject());
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }
}

package com.example.mulitdatasource.configuration.mssql;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;

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;

import javax.persistence.EntityManager;

import javax.sql.DataSource;

import java.util.Map;

import java.util.Properties;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

entityManagerFactoryRef = "ExampleMsSQLEntityManagerFactory",

transactionManagerRef = "ExampleMsSQLTransactionManager",

basePackages = {"com.example.mulitdatasource.repositories.mssql"}

)

public class ExampleMsSQLDataSourceConfiguration {

private final JpaProperties jpaProperties;

private final DataSource exampleMsSQLDataSource;

@Autowired

public ExampleMsSQLDataSourceConfiguration(JpaProperties jpaProperties, @Qualifier("ExampleMsSQLDataSource") DataSource dataSource) {

this.jpaProperties = jpaProperties;

this.exampleMsSQLDataSource = dataSource;

}

@Bean(name = "ExampleMsSQLEntityManager")

public EntityManager entityManager(EntityManagerFactoryBuilder builder){

return exampleMsSQLEntityManagerFactory(builder).getObject().createEntityManager();

}

@Bean(name = "ExampleMsSQLEntityManagerFactory")

public LocalContainerEntityManagerFactoryBean exampleMsSQLEntityManagerFactory(EntityManagerFactoryBuilder builder){

LocalContainerEntityManagerFactoryBean persistenceUnit = builder

.dataSource(exampleMsSQLDataSource)

.properties(getVendorProperties(exampleMsSQLDataSource))

.packages("com.example.mulitdatasource.entities.mssql")

.persistenceUnit("ExampleMsSQLPersistenceUnit")

.build();

Properties properties = new Properties();

//如果不需要扩展方言，直接使用以下代码

//properties.setProperty("hibernate.dialect", "org.hibernate.dialect.SQLServer2008Dialect");

//这里我添加了一个自定义言配置，是为了兼容对于某些像nchar这类字段名不兼容的情况

properties.setProperty("hibernate.dialect", "com.example.mulitdatasource.configuration.dialect.CustomSQLServerDialect");

persistenceUnit.setJpaProperties(properties);

return persistenceUnit;

}

@Bean(name = "ExampleMsSQLTransactionManager")

public PlatformTransactionManager transactionManagerMonitor(EntityManagerFactoryBuilder builder) {

return new JpaTransactionManager(exampleMsSQLEntityManagerFactory(builder).getObject());

}

private Map<String, String> getVendorProperties(DataSource dataSource) {

return jpaProperties.getHibernateProperties(dataSource);

}

自定义方言配置（这一步是可选的）

package com.example.mulitdatasource.configuration.dialect;

import org.hibernate.dialect.SQLServer2008Dialect;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.context.annotation.Configuration;

import java.sql.Types;

@Configuration
public class CustomSQLServerDialect extends SQLServer2008Dialect {
    public CustomSQLServerDialect(){
        super();
        registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 1, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 255, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());
        registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName());
    }
}

package com.example.mulitdatasource.configuration.dialect;

import org.hibernate.dialect.SQLServer2008Dialect;

import org.hibernate.type.StandardBasicTypes;

import org.springframework.context.annotation.Configuration;

import java.sql.Types;

@Configuration

public class CustomSQLServerDialect extends SQLServer2008Dialect {

public CustomSQLServerDialect(){

super();

registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName());

registerHibernateType(Types.NCHAR, 1, StandardBasicTypes.CHARACTER.getName());

registerHibernateType(Types.NCHAR, 255, StandardBasicTypes.STRING.getName());

registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());

registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());

registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName());

}

至此，多数据源的配置已经完成。如果还有其他的数据源配置，可以参数上面的示例自行添加。关于自定义方言的配置，不是必须的，这个是在某些时候数据库里的字段定义了一些不常见的类型，jpa提示“No Dialect mapping for JDBC type: -9”这样的错误时，可以自己注入对这些类型的映射关系。
EnableJpaRepositories中的basePackages，这里如果在工程目录中有多个包需要包含，可以直接添加多个路径。如：basePackages = {“com.example.mulitdatasource.repositories.mssql”,”com.example.mulitdatasource.repositories.mssql1″,…}，这样。同理LocalContainerEntityManagerFactoryBean中的packages也是相同的操作。
配置完成后，接下来向大家介绍一下如何使用。

实体类

用户实体

这里我将用户实体配置在MySQL中

package com.example.mulitdatasource.entities.mysql;

import lombok.AllArgsConstructor;
import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Data
@Entity
@AllArgsConstructor
public class UserInfo {
    @Id
    @GeneratedValue
    @Column(name = "UserId")
    private int userId;
    @Column(name = "UserName")
    private String userName;
    @Column(name = "Password")
    private String password;
    @Column(name = "NickName")
    private String nickName;
    @Column(name = "roleId")
    private int roleId;
}

package com.example.mulitdatasource.entities.mysql;

import lombok.AllArgsConstructor;

import lombok.Data;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

@Data

@Entity

@AllArgsConstructor

public class UserInfo {

@Id

@GeneratedValue

@Column(name = "UserId")

private int userId;

@Column(name = "UserName")

private String userName;

@Column(name = "Password")

private String password;

@Column(name = "NickName")

private String nickName;

@Column(name = "roleId")

private int roleId;

}

角色实体

这里我将角色实体配置在MsSQL中

package com.example.mulitdatasource.entities.mssql;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class UserRoleInfo {
    @Id
    @GeneratedValue
    @Column(name = "RoleID")
    private int roleId;
    @Column(name = "RoleName")
    private String roleName;
}

package com.example.mulitdatasource.entities.mssql;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

@Entity

public class UserRoleInfo {

@Id

@GeneratedValue

@Column(name = "RoleID")

private int roleId;

@Column(name = "RoleName")

private String roleName;

}

数据库操作接口

用户操作接口

package com.example.mulitdatasource.repositories.mysql;

import com.example.mulitdatasource.entities.mysql.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<UserInfo, Integer> {
    UserInfo findByUserName(String name);
}

package com.example.mulitdatasource.repositories.mysql;

import com.example.mulitdatasource.entities.mysql.UserInfo;

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<UserInfo, Integer> {

UserInfo findByUserName(String name);

}

角色操作接口

package com.example.mulitdatasource.repositories.mssql;

import com.example.mulitdatasource.entities.mssql.UserRoleInfo;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRoleRepository extends JpaRepository<UserRoleInfo, Integer> {
    UserRoleInfo findByRoleId(int roleId);
}

package com.example.mulitdatasource.repositories.mssql;

import com.example.mulitdatasource.entities.mssql.UserRoleInfo;

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRoleRepository extends JpaRepository<UserRoleInfo, Integer> {

UserRoleInfo findByRoleId(int roleId);

}

复杂操作接口

我创建了一个用于复杂查询操作的接口。
某些时候原生JpaRepository操作还是过于简单，在实现join，复杂分页之类的操作，虽然能实现，但我个人还是更喜欢直接面向存储过程

package com.example.mulitdatasource.repositories.mssql;

import com.example.mulitdatasource.entities.mssql.UserRoleInfo;
import com.example.mulitdatasource.entities.mysql.UserInfo;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import java.util.List;
import java.util.stream.Collectors;

@Repository
public class MsSQLDatabase {
    @PersistenceContext(unitName = "ExampleMsSQLPersistenceUnit")
    private EntityManager entityManager;

    public List<UserInfo> findByRoleId(int roleId){
        StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery("produce_get_users");

        procedureQuery.registerStoredProcedureParameter(0, Integer.class, ParameterMode.IN);

        procedureQuery.setParameter(0, roleId);

        if (!procedureQuery.execute()) {
            return null;
        }

        List<Object[]> storedProcedureResults = procedureQuery.getResultList();
        if (storedProcedureResults == null) {
            return null;
        }

        return storedProcedureResults.stream().map(result -> new UserInfo(
                (Integer)result[0],
                (String)result[1],
                (String)result[2],
                (String)result[3],
                (Integer) result[4]
        )).collect(Collectors.toList());
    }
}

package com.example.mulitdatasource.repositories.mssql;

import com.example.mulitdatasource.entities.mssql.UserRoleInfo;

import com.example.mulitdatasource.entities.mysql.UserInfo;

import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;

import javax.persistence.ParameterMode;

import javax.persistence.PersistenceContext;

import javax.persistence.StoredProcedureQuery;

import java.util.List;

import java.util.stream.Collectors;

@Repository

public class MsSQLDatabase {

@PersistenceContext(unitName = "ExampleMsSQLPersistenceUnit")

private EntityManager entityManager;

public List<UserInfo> findByRoleId(int roleId){

StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery("produce_get_users");

procedureQuery.registerStoredProcedureParameter(0, Integer.class, ParameterMode.IN);

procedureQuery.setParameter(0, roleId);

if (!procedureQuery.execute()) {

return null;

}

List<Object[]> storedProcedureResults = procedureQuery.getResultList();

if (storedProcedureResults == null) {

return null;

}

return storedProcedureResults.stream().map(result -> new UserInfo(

(Integer)result[0],

(String)result[1],

(String)result[2],

(String)result[3],

(Integer) result[4]

)).collect(Collectors.toList());

}

使用示例

@Autowired
private UserRepository userRepository;
@Autowired
private UserRoleRepository roleRepository;
@Autowired
MsSQLDatabase msSQLDatabase;

@Test
public void contextLoads() {

        UserInfo userInfo = userRepository.findByUserName("admin");

        UserRoleInfo roleInfo = roleRepository.findByRoleId(userInfo.getRoleId());

        List<UserInfo> userInfos = msSQLDatabase.findByRoleId(userInfo.getRoleId());

    }

@Autowired

private UserRepository userRepository;

@Autowired

private UserRoleRepository roleRepository;

@Autowired

MsSQLDatabase msSQLDatabase;

@Test

public void contextLoads() {

UserInfo userInfo = userRepository.findByUserName("admin");

UserRoleInfo roleInfo = roleRepository.findByRoleId(userInfo.getRoleId());

List<UserInfo> userInfos = msSQLDatabase.findByRoleId(userInfo.getRoleId());

}

写在最后

现在我已经为大家展示了Spring Boot中关于多数据源的配置以及使用示例，并附带了spring boot中存储过程调用的一种方法。因为篇幅和精力的问题，这里我并不是针对新手的一步一步教程及原理解析，本文的目的主两有两个，一个是方便我自己记录一下思路，另一个是在配置的过程中为大家介绍一些关键点。如果大家确实觉得有必要将细节一步一步解释清除，可以给我留言讨论。