前言
在微服务体系搭建中，难免会有需要使用多个数据源的情况。今天米饭屋就给大家介绍一下在Spring Boot 1.xx环境中（2.xx环境中有些区别，后续再介绍）如何配置多个数据源。
因为篇幅的原因（其实就是懒，哈哈），本文使用MySQL和MsSQL两种数据源来示例，其他种类的数据源大家举一反三就行了。
pom.xml 配置
核心配置有三个：jpa，mysql，mssql。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<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> |
application.yml 配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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 配置
连接属性配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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数据源配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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数据源配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
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); } } |
自定义方言配置（这一步是可选的）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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中
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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中
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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; } |
数据库操作接口
用户操作接口
|
1 2 3 4 5 6 7 8 9 10 |
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); } |
角色操作接口
|
1 2 3 4 5 6 7 8 9 10 |
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，复杂分页之类的操作，虽然能实现，但我个人还是更喜欢直接面向存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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()); } } |
使用示例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
@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中存储过程调用的一种方法。因为篇幅和精力的问题，这里我并不是针对新手的一步一步教程及原理解析，本文的目的主两有两个，一个是方便我自己记录一下思路，另一个是在配置的过程中为大家介绍一些关键点。如果大家确实觉得有必要将细节一步一步解释清除，可以给我留言讨论。
