mybatis多数据源配置_SpringBoot多数据源配置详解

开发环境：JDK1.8+SpringBoot2.1.4.RELEASE+Oracle

这里我们假设要使用两个数据源分别为：master和slave。

• pom.xml 依赖包

org.springframework.bootspring-boot-starterorg.springframework.bootspring-boot-starter-weborg.springframework.bootspring-boot-starter-data-jpacom.github.norauiojdbc712.1.0.2org.apache.commonscommons-lang3org.mybatis.spring.bootmybatis-spring-boot-starter1.1.1

• application.yml配置

server:  port: 50000 ---spring:  jpa:     hibernate:        ddlAuto: update    openInView: true    showSql: false    databasePlatform: org.hibernate.dialect.Oracle10gDialect---# 第一个数据源      master:   datasource:    driverClassName: oracle.jdbc.driver.OracleDriver    url: jdbc:oracle:thin:@localhost:1521/orcl    username: t0    password: t0    type: com.zaxxer.hikari.HikariDataSource    hikari:      minimumIdle: 10      maximumPoolSize: 200      autoCommit: true      idleTimeout: 30000      poolName: MasterDatabookHikariCP      maxLifetime: 1800000      connectionTimeout: 30000      connectionTestQuery: SELECT 1 FROM DUAL# 第二个数据源      slave:  datasource:    driverClassName: oracle.jdbc.driver.OracleDriver    url: jdbc:oracle:thin:@localhost:1521/orcl    username: t1    password: t1    type: com.zaxxer.hikari.HikariDataSource    hikari:      minimumIdle: 10      maximumPoolSize: 200      autoCommit: true      idleTimeout: 30000      poolName: SlaveDatabookHikariCP      maxLifetime: 1800000      connectionTimeout: 30000      connectionTestQuery: SELECT 1 FROM DUAL      ---# mybatis 配置，分表对应到不同的包中        master:  mybatis:    config-location: classpath:/MyBatis-conf.xml    type-aliases-package: com.pack.domain #master数据源对应的包    mapper-locations:    - classpath:/com/pack/mapper/oracle/*.xml #master数据源对应mapper文件slave:  mybatis:    config-location: classpath:/MyBatis-conf.xml    type-aliases-package: com.pack.slave.domain #slave数据源对应的包    mapper-locations:    - classpath:/com/pack/slave/mapper/oracle/*.xml    #slave数据源对应mapper文件---# jpa相关的配置master:  jpa:    repos: com.pack.base.repository #master数据源对应的包配置    domain: com.pack.domain #master对应的实体包slave:  jpa:    repos: com.pack.slave.repository #salve数据源对应的包配置    domain: com.pack.slave.domain  #slave对应的实体包

以上就是两个数据源对应相关的配置了，大家注意看里面的注释。接下来我们看mabatis和jpa对应的类相关的配置了，都是固定的配置。

• 数据源属性配置对应的java类配置

BaseProperties类

public class BaseDataSourceProperties implements BeanClassLoaderAware, InitializingBean {private ClassLoader classLoader;/** * Name of the datasource. Default to "testdb" when using an embedded database. */private String name;/** * Whether to generate a random datasource name. */private boolean generateUniqueName;/** * Fully qualified name of the connection pool implementation to use. By default, it * is auto-detected from the classpath. */private Class extends DataSource> type;/** * Fully qualified name of the JDBC driver. Auto-detected based on the URL by default. */private String driverClassName;/** * JDBC URL of the database. */private String url;/** * Login username of the database. */private String username;/** * Login password of the database. */private String password;/** * JNDI location of the datasource. Class, url, username & password are ignored when * set. */private String jndiName;/** * Initialize the datasource with available DDL and DML scripts. */private DataSourceInitializationMode initializationMode = DataSourceInitializationMode.EMBEDDED;/** * Platform to use in the DDL or DML scripts (such as schema-${platform}.sql or * data-${platform}.sql). */private String platform = "all";/** * Schema (DDL) script resource references. */private List schema;/** * Username of the database to execute DDL scripts (if different). */private String schemaUsername;/** * Password of the database to execute DDL scripts (if different). */private String schemaPassword;/** * Data (DML) script resource references. */private List data;/** * Username of the database to execute DML scripts (if different). */private String dataUsername;/** * Password of the database to execute DML scripts (if different). */private String dataPassword;/** * Whether to stop if an error occurs while initializing the database. */private boolean continueOnError = false;/** * Statement separator in SQL initialization scripts. */private String separator = ";";/** * SQL scripts encoding. */private Charset sqlScriptEncoding;private EmbeddedDatabaseConnection embeddedDatabaseConnection = EmbeddedDatabaseConnection.NONE;private Xa xa = new Xa();private String uniqueName;@Overridepublic void setBeanClassLoader(ClassLoader classLoader) {this.classLoader = classLoader;}@Overridepublic void afterPropertiesSet() throws Exception {this.embeddedDatabaseConnection = EmbeddedDatabaseConnection.get(this.classLoader);}/** * Initialize a {@link DataSourceBuilder} with the state of this instance. * @return a {@link DataSourceBuilder} initialized with the customizations defined on * this instance */public DataSourceBuilder> initializeDataSourceBuilder() {return DataSourceBuilder.create(getClassLoader()).type(getType()).driverClassName(determineDriverClassName()).url(determineUrl()).username(determineUsername()).password(determinePassword());}public String getName() {return this.name;}public void setName(String name) {this.name = name;}public boolean isGenerateUniqueName() {return this.generateUniqueName;}public void setGenerateUniqueName(boolean generateUniqueName) {this.generateUniqueName = generateUniqueName;}public Class extends DataSource> getType() {return this.type;}public void setType(Class extends DataSource> type) {this.type = type;}/** * Return the configured driver or {@code null} if none was configured. * @return the configured driver * @see #determineDriverClassName() */public String getDriverClassName() {return this.driverClassName;}public void setDriverClassName(String driverClassName) {this.driverClassName = driverClassName;}/** * Determine the driver to use based on this configuration and the environment. * @return the driver to use * @since 1.4.0 */public String determineDriverClassName() {if (StringUtils.hasText(this.driverClassName)) {Assert.state(driverClassIsLoadable(),() -> "Cannot load driver class: " + this.driverClassName);return this.driverClassName;}String driverClassName = null;if (StringUtils.hasText(this.url)) {driverClassName = DatabaseDriver.fromJdbcUrl(this.url).getDriverClassName();}if (!StringUtils.hasText(driverClassName)) {driverClassName = this.embeddedDatabaseConnection.getDriverClassName();}if (!StringUtils.hasText(driverClassName)) {throw new DataSourceBeanCreationException("Failed to determine a suitable driver class", this,this.embeddedDatabaseConnection);}return driverClassName;}private boolean driverClassIsLoadable() {try {ClassUtils.forName(this.driverClassName, null);return true;}catch (UnsupportedClassVersionError ex) {// Driver library has been compiled with a later JDK, propagate errorthrow ex;}catch (Throwable ex) {return false;}}/** * Return the configured url or {@code null} if none was configured. * @return the configured url * @see #determineUrl() */public String getUrl() {return this.url;}public void setUrl(String url) {this.url = url;}/** * Determine the url to use based on this configuration and the environment. * @return the url to use * @since 1.4.0 */public String determineUrl() {if (StringUtils.hasText(this.url)) {return this.url;}String databaseName = determineDatabaseName();String url = (databaseName != null)? this.embeddedDatabaseConnection.getUrl(databaseName) : null;if (!StringUtils.hasText(url)) {throw new DataSourceBeanCreationException("Failed to determine suitable jdbc url", this,this.embeddedDatabaseConnection);}return url;}/** * Determine the name to used based on this configuration. * @return the database name to use or {@code null} * @since 2.0.0 */public String determineDatabaseName() {if (this.generateUniqueName) {if (this.uniqueName == null) {this.uniqueName = UUID.randomUUID().toString();}return this.uniqueName;}if (StringUtils.hasLength(this.name)) {return this.name;}if (this.embeddedDatabaseConnection != EmbeddedDatabaseConnection.NONE) {return "testdb";}return null;}/** * Return the configured username or {@code null} if none was configured. * @return the configured username * @see #determineUsername() */public String getUsername() {return this.username;}public void setUsername(String username) {this.username = username;}/** * Determine the username to use based on this configuration and the environment. * @return the username to use * @since 1.4.0 */public String determineUsername() {if (StringUtils.hasText(this.username)) {return this.username;}if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {return "sa";}return null;}/** * Return the configured password or {@code null} if none was configured. * @return the configured password * @see #determinePassword() */public String getPassword() {return this.password;}public void setPassword(String password) {this.password = password;}/** * Determine the password to use based on this configuration and the environment. * @return the password to use * @since 1.4.0 */public String determinePassword() {if (StringUtils.hasText(this.password)) {return this.password;}if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {return "";}return null;}public String getJndiName() {return this.jndiName;}/** * Allows the DataSource to be managed by the container and obtained via JNDI. The * {@code URL}, {@code driverClassName}, {@code username} and {@code password} fields * will be ignored when using JNDI lookups. * @param jndiName the JNDI name */public void setJndiName(String jndiName) {this.jndiName = jndiName;}public DataSourceInitializationMode getInitializationMode() {return this.initializationMode;}public void setInitializationMode(DataSourceInitializationMode initializationMode) {this.initializationMode = initializationMode;}public String getPlatform() {return this.platform;}public void setPlatform(String platform) {this.platform = platform;}public List getSchema() {return this.schema;}public void setSchema(List schema) {this.schema = schema;}public String getSchemaUsername() {return this.schemaUsername;}public void setSchemaUsername(String schemaUsername) {this.schemaUsername = schemaUsername;}public String getSchemaPassword() {return this.schemaPassword;}public void setSchemaPassword(String schemaPassword) {this.schemaPassword = schemaPassword;}public List getData() {return this.data;}public void setData(List data) {this.data = data;}public String getDataUsername() {return this.dataUsername;}public void setDataUsername(String dataUsername) {this.dataUsername = dataUsername;}public String getDataPassword() {return this.dataPassword;}public void setDataPassword(String dataPassword) {this.dataPassword = dataPassword;}public boolean isContinueOnError() {return this.continueOnError;}public void setContinueOnError(boolean continueOnError) {this.continueOnError = continueOnError;}public String getSeparator() {return this.separator;}public void setSeparator(String separator) {this.separator = separator;}public Charset getSqlScriptEncoding() {return this.sqlScriptEncoding;}public void setSqlScriptEncoding(Charset sqlScriptEncoding) {this.sqlScriptEncoding = sqlScriptEncoding;}public ClassLoader getClassLoader() {return this.classLoader;}public Xa getXa() {return this.xa;}public void setXa(Xa xa) {this.xa = xa;}/** * XA Specific datasource settings. */public static class Xa {/** * XA datasource fully qualified name. */private String dataSourceClassName;/** * Properties to pass to the XA data source. */private Map properties = new LinkedHashMap<>();public String getDataSourceClassName() {return this.dataSourceClassName;}public void setDataSourceClassName(String dataSourceClassName) {this.dataSourceClassName = dataSourceClassName;}public Map getProperties() {return this.properties;}public void setProperties(Map properties) {this.properties = properties;}}static class DataSourceBeanCreationException extends BeanCreationException {/** *  */private static final long serialVersionUID = 1L;private final BaseDataSourceProperties properties;private final EmbeddedDatabaseConnection connection;DataSourceBeanCreationException(String message, BaseDataSourceProperties properties,EmbeddedDatabaseConnection connection) {super(message);this.properties = properties;this.connection = connection;}public BaseDataSourceProperties getProperties() {return this.properties;}public EmbeddedDatabaseConnection getConnection() {return this.connection;}}}

mybatis对应的base属性文件

public class BaseMybatisProperties {/** * Config file path. */private String configLocation;/** * Location of mybatis mapper files. */private String[] mapperLocations;/** * Package to scan domain objects. */private String typeAliasesPackage;/** * Package to scan handlers. */private String typeHandlersPackage;/** * Check the config file exists. */private boolean checkConfigLocation = false;/** * Execution mode for {@link org.mybatis.spring.SqlSessionTemplate}. */private ExecutorType executorType;/** * A Configuration object for customize default settings. If * {@link #configLocation} is specified, this property is not used. */private Configuration configuration;/** * @since 1.1.0 * @return */public String getConfigLocation() {return this.configLocation;}/** * @since 1.1.0 * @return */public void setConfigLocation(String configLocation) {this.configLocation = configLocation;}@Deprecatedpublic String getConfig() {return this.configLocation;}@Deprecatedpublic void setConfig(String config) {this.configLocation = config;}public String[] getMapperLocations() {return this.mapperLocations;}public void setMapperLocations(String[] mapperLocations) {this.mapperLocations = mapperLocations;}public String getTypeHandlersPackage() {return this.typeHandlersPackage;}public void setTypeHandlersPackage(String typeHandlersPackage) {this.typeHandlersPackage = typeHandlersPackage;}public String getTypeAliasesPackage() {return this.typeAliasesPackage;}public void setTypeAliasesPackage(String typeAliasesPackage) {this.typeAliasesPackage = typeAliasesPackage;}public boolean isCheckConfigLocation() {return this.checkConfigLocation;}public void setCheckConfigLocation(boolean checkConfigLocation) {this.checkConfigLocation = checkConfigLocation;}public ExecutorType getExecutorType() {return this.executorType;}public void setExecutorType(ExecutorType executorType) {this.executorType = executorType;}public Configuration getConfiguration() {return configuration;}public void setConfiguration(Configuration configuration) {this.configuration = configuration;}public Resource[] resolveMapperLocations() {List resources = new ArrayList();if (this.mapperLocations != null) {for (String mapperLocation : this.mapperLocations) {Resource[] mappers;try {mappers = new PathMatchingResourcePatternResolver().getResources(mapperLocation);resources.addAll(Arrays.asList(mappers));} catch (IOException e) {}}}Resource[] mapperLocations = new Resource[resources.size()];mapperLocations = resources.toArray(mapperLocations);return mapperLocations;}}

因为我们使用的是Hikari数据源，所以这里我是直接copy默认系统Hikari的属性文件。

也就是这个文件：org.springframework.boot.autoconfigure.jdbc.DataSourceProperties 为啥我不直接继承这个类而是在自己的项目中新建这么一个类，是因为我发现这个类有这个注解

@ConfigurationProperties(prefix = "spring.datasource")

怕的是它的这个注解会覆盖我接下来两个类的注解(我主要是懒得测试，所以直接copy一份无所谓了)。

接下来看看具体master和slave两个数据源的属性文件：

• Properties(jpa和mybatis)文件

@Component@ConfigurationProperties(prefix = "master.datasource")public class MasterDataSourceProperties extends BaseDataSourceProperties {}

@Component@ConfigurationProperties(prefix = "slave.datasource")public class SlaveDataSourceProperties extends BaseDataSourceProperties {}

@Component@ConfigurationProperties(prefix = "master.mybatis")public class MasterMybatisProperties extends BaseMybatisProperties {}

@Component@ConfigurationProperties(prefix = "slave.mybatis")public class SlaveMybatisProperties extends BaseMybatisProperties {}

接下来是数据源的配置了。

• 数据源配置类

@Configurationpublic class HikariDataSourceConfig {@Bean@Primarypublic HikariDataSource masterDataSource(MasterDataSourceProperties properties) {HikariDataSource dataSource = createDataSource(properties,HikariDataSource.class);if (StringUtils.hasText(properties.getName())) {dataSource.setPoolName(properties.getName());}return dataSource;}@Beanpublic HikariDataSource slaveDataSource(SlaveDataSourceProperties properties) {HikariDataSource dataSource = createDataSource(properties,HikariDataSource.class);if (StringUtils.hasText(properties.getName())) {dataSource.setPoolName(properties.getName());}return dataSource;}@SuppressWarnings("unchecked")protected static  T createDataSource(BaseDataSourceProperties properties,Class extends DataSource> type) {return (T) properties.initializeDataSourceBuilder().type(type).build();}}

因为我们配置的是多个数据源所有其中一个数据源必须加入这个注解@Primary

接下来是jpa的EntityManagerFactory工厂的配置了

• EntityManagerFactory配置

public class EntityManagerFactoryConfig {@Configuration@EnableJpaRepositories(basePackages = {"${master.jpa.repos}" }, entityManagerFactoryRef = "masterEntityManagerFactory", transactionManagerRef = "masterJPATransactionManager")static class MasterEntityManagerFactory {@Resource(name = "masterDataSource")private DataSource masterDataSource;@Value("${master.jpa.domain}")private String masterDomainPkg;@Bean@Primarypublic LocalContainerEntityManagerFactoryBean masterEntityManagerFactory(EntityManagerFactoryBuilder builder) {Map properties = new HashMap<>();properties.put("hibernate.hbm2ddl.auto", "update");properties.put("hibernate.id.new_generator_mappings", true);properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ;return builder.dataSource(masterDataSource).packages(masterDomainPkg).persistenceUnit("master").properties(properties).build();}@Bean@Primarypublic PlatformTransactionManager masterJPATransactionManager(EntityManagerFactoryBuilder builder) {JpaTransactionManager tm = new JpaTransactionManager(masterEntityManagerFactory(builder).getObject());return tm;}}@Configuration@EnableJpaRepositories(basePackages = {"${slave.jpa.repos}" }, entityManagerFactoryRef = "slaveEntityManagerFactory", transactionManagerRef = "slaveJPATransactionManager")@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")static class SlaveEntityManagerFactory {@Resource(name = "slaveDataSource")private DataSource slaveDataSource;@Value("${slave.jpa.domain}")private String slaveDomainPkg;@Beanpublic LocalContainerEntityManagerFactoryBean slaveEntityManagerFactory(EntityManagerFactoryBuilder builder) {Map properties = new HashMap<>();properties.put("hibernate.hbm2ddl.auto", "update");properties.put("hibernate.id.new_generator_mappings", true);properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ;return builder.dataSource(slaveDataSource).packages(slaveDomainPkg).persistenceUnit("slave").properties(properties).build();}@Beanpublic PlatformTransactionManager slaveJPATransactionManager(EntityManagerFactoryBuilder builder) {JpaTransactionManager tm = new JpaTransactionManager(slaveEntityManagerFactory(builder).getObject());return tm;}}}

• mybatis SqlSessionFactory工厂配置

public class SqlSessionFactoryConfig {@Configurationstatic class MasterSqlSessionFactory {@Resourceprivate MasterMybatisProperties properties;@Autowired(required = false)private Interceptor[] interceptors;@Autowiredprivate ResourceLoader resourceLoader = new DefaultResourceLoader();@Autowired(required = false)private DatabaseIdProvider databaseIdProvider;@Beanpublic SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource)throws Exception {SqlSessionFactoryBean factory = new SqlSessionFactoryBean();factory.setDataSource(dataSource);factory.setVfs(SpringBootVFS.class);if (StringUtils.hasText(this.properties.getConfigLocation())) {factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));}factory.setConfiguration(properties.getConfiguration());if (!ObjectUtils.isEmpty(this.interceptors)) {factory.setPlugins(this.interceptors);}if (this.databaseIdProvider != null) {factory.setDatabaseIdProvider(this.databaseIdProvider);}if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());}if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());}if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {factory.setMapperLocations(this.properties.resolveMapperLocations());}return factory.getObject();}@Bean    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {        return new DataSourceTransactionManager(dataSource);    }@Beanpublic SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory")SqlSessionFactory sqlSessionFactory) {ExecutorType executorType = this.properties.getExecutorType();if (executorType != null) {return new SqlSessionTemplate(sqlSessionFactory, executorType);} else {return new SqlSessionTemplate(sqlSessionFactory);}}}@Configuration@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")static class SlaveSqlSessionFactory {@Resourceprivate SlaveMybatisProperties properties;@Autowired(required = false)private Interceptor[] interceptors;@Autowiredprivate ResourceLoader resourceLoader = new DefaultResourceLoader();@Autowired(required = false)private DatabaseIdProvider databaseIdProvider;@Beanpublic SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource)throws Exception {SqlSessionFactoryBean factory = new SqlSessionFactoryBean();factory.setDataSource(dataSource);factory.setVfs(SpringBootVFS.class);if (StringUtils.hasText(this.properties.getConfigLocation())) {factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));}factory.setConfiguration(properties.getConfiguration());if (!ObjectUtils.isEmpty(this.interceptors)) {factory.setPlugins(this.interceptors);}if (this.databaseIdProvider != null) {factory.setDatabaseIdProvider(this.databaseIdProvider);}if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());}if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());}if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {factory.setMapperLocations(this.properties.resolveMapperLocations());}return factory.getObject();}@Bean    public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {        return new DataSourceTransactionManager(dataSource);    }@Beanpublic SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory")SqlSessionFactory sqlSessionFactory) {ExecutorType executorType = this.properties.getExecutorType();if (executorType != null) {return new SqlSessionTemplate(sqlSessionFactory, executorType);} else {return new SqlSessionTemplate(sqlSessionFactory);}}}}

接下来还需要配置mapper相关的配置

• mapper配置

public class MapperScanConfig {@Configuration@MapperScan(basePackages = {"com.pack.base.mapper"}, sqlSessionTemplateRef  = "masterSqlSessionTemplate")static class MasterMapper {}@Configuration@MapperScan(basePackages = {"com.pack.slave.mapper"}, sqlSessionTemplateRef  = "slaveSqlSessionTemplate")@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")static class SlaveMapper {}}

到这里我们所有的相关配置就完成了，接下来我们只需建立对应的包即可。

MyBatis-conf.xml配置文件内容如下：

测试：

建立com.pack.domain包，然后新建类Users.java

@Entity@Table(name = "T_USERS")@Datapublic class Users {  @Id  private Long id;private String username ;private String password ;private String realName ;private String phone ;private String idNo ;@Column(length=4000)private String authority ;@Column(columnDefinition="int default 0")private Integer status = 0 ;}

建立包com.pack.slave.domain，然后新建类

@Entity@Table(name = "T_PERSON")@Datapublic class Person{  @Id  private Long id;private String name ;private String email ;}

dao我就不写了。我们启动服务器分别在不同的用户下查看表是否建立，如果都建立了就表示成功。

完毕！！！

给个关注，给个赞呗，谢谢！！！

SpringBoot+Atomikos多数据源分布式事务

分布式事务框架Seata之AT模式

SpringCloud zuul 动态网关配置

SpringMVC参数统一验证方法