Springboot2.x Data JPA multi-data source crawl pit

Environment Version:

SpringBoot 2.1.3

JPA official documentation

The official documentation

5.1.2. The Annotation – -based Configuration

The Spring Data JPA repositories support can be activated not only through an XML namespace but also by using an annotation through JavaConfig, as shown in the following example:

Example 51. Spring Data JPA repositories using JavaConfig

@Configuration
@EnableJpaRepositories
@EnableTransactionManagement
class ApplicationConfig {

  @Bean
  public DataSource dataSource(a) {

    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    return builder.setType(EmbeddedDatabaseType.HSQL).build();
  }

  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(a) {

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(true);

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.acme.domain");
    factory.setDataSource(dataSource());
    return factory;
  }

  @Bean
  public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {

    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory);
    returntxManager; }}Copy the code

As documented, the annotation-based configuration contains three key beans:

PlatformTransactionManager— Transaction Management
LocalContainerEntityManagerFactoryBean— Entity class management
DataSource– data source

There is also a key note @EnableJpaRepositories

Official SpringBoot document

The official documentation

Multiple data sources use @primary to indicate priority

You can even go further by leveraging what data properties does for You — that is, by providing a default embedded database with a sensible username and password if no URL is provided. You can easily initialize a DataSourceBuilder from the state of any DataSourceProperties object, so you could also inject the DataSource that Spring Boot creates automatically. However, that would split your configuration into two namespaces: url, username, password, type, and driver on spring.datasource and the rest on your custom namespace (app.datasource). To avoid that, you can redefine a custom DataSourceProperties on your custom namespace, as shown in the following example:
@Bean
@Primary
@ConfigurationProperties("app.datasource")
public DataSourceProperties dataSourceProperties(a) {
	return new DataSourceProperties();
}

@Bean
@ConfigurationProperties("app.datasource.configuration")
public HikariDataSource dataSource(DataSourceProperties properties) {
	return properties.initializeDataSourceBuilder().type(HikariDataSource.class)
			.build();
}
Copy the code
84.2 Configure Two DataSources

If you need to configure multiple data sources, you can apply the same tricks that are described in the previous section. You must, however, mark one of the DataSource instances as @Primary, because various auto-configurations down the road expect to be able to get one by type.

If you create your own DataSource, the auto-configuration backs off. In the following example, we provide the exact same feature set as the auto-configuration provides on the primary data source:
@Bean
@Primary
@ConfigurationProperties("app.datasource.first")
public DataSourceProperties firstDataSourceProperties(a) {
	return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("app.datasource.first.configuration")
public HikariDataSource firstDataSource(a) {
	return firstDataSourceProperties().initializeDataSourceBuilder()
			.type(HikariDataSource.class).build();
}

@Bean
@ConfigurationProperties("app.datasource.second")
public BasicDataSource secondDataSource(a) {
	return DataSourceBuilder.create().type(BasicDataSource.class).build();
}
Copy the code

Or use two EntityManagers and configure them in conjunction with @EnableJpaRepositories

84.10 Use Two EntityManagers

Even if the default EntityManagerFactory works fine, you need to define a new one. Otherwise, the presence of the second bean of that type switches off the default. To make it easy to do, you can use the convenient EntityManagerBuilder provided by Spring Boot. Alternatively, you can just theLocalContainerEntityManagerFactoryBean directly from Spring ORM, as shown in the following example:
// add two data sources configured as above

@Bean
public LocalContainerEntityManagerFactoryBean customerEntityManagerFactory( EntityManagerFactoryBuilder builder) {
	return builder
			.dataSource(customerDataSource())
			.packages(Customer.class)
			.persistenceUnit("customers")
			.build();
}

@Bean
public LocalContainerEntityManagerFactoryBean orderEntityManagerFactory( EntityManagerFactoryBuilder builder) {
	return builder
			.dataSource(orderDataSource())
			.packages(Order.class)
			.persistenceUnit("orders")
			.build();
}
Copy the code
The configuration above almost works on its own. To complete the picture, you need to configure TransactionManagers for the two EntityManagers as well. If you mark one of them as @Primary, it could be picked up by the default JpaTransactionManager in Spring Boot. The other would have to be explicitly injected into a new instance. Alternatively, you might be able to use a JTA transaction manager that spans both.

If you use Spring Data, you need to configure @EnableJpaRepositories accordingly, as shown in the following example:
@Configuration
@EnableJpaRepositories(basePackageClasses = Customer.class,
		entityManagerFactoryRef = "customerEntityManagerFactory")
public class CustomerConfiguration {... }@Configuration
@EnableJpaRepositories(basePackageClasses = Order.class,
		entityManagerFactoryRef = "orderEntityManagerFactory")
public class OrderConfiguration {... }Copy the code

Source code analysis

The auto-assembly class declares configuration beans and conditional assembly sequences

package org.springframework.boot.autoconfigure.data.jpa;

/ * * * {@link EnableAutoConfiguration Auto-configuration} for Spring Data's JPA Repositories.
 * <p>
 * Activates when there is a bean of type {@link javax.sql.DataSource} configured in the
 * context, the Spring Data JPA
 * {@link org.springframework.data.jpa.repository.JpaRepository} type is on the classpath,
 * and there is no other, existing
 * {@link org.springframework.data.jpa.repository.JpaRepository} configured.
 * <p>
 * Once in effect, the auto-configuration is the equivalent of enabling JPA repositories
 * using the {@link org.springframework.data.jpa.repository.config.EnableJpaRepositories}
 * annotation.
 * <p>
 * This configuration class will activate <em>after</em> the Hibernate auto-configuration.
 *
 * @author Phillip Webb
 * @author Josh Long
 * @see EnableJpaRepositories
 */
@Configuration
@ConditionalOnBean(DataSource.class)
@ConditionalOnClass(JpaRepository.class)
@ConditionalOnMissingBean({ JpaRepositoryFactoryBean.class,
		JpaRepositoryConfigExtension.class })
@ConditionalOnProperty(prefix = "spring.data.jpa.repositories", name = "enabled", havingValue = "true", matchIfMissing = true)
@Import(JpaRepositoriesAutoConfigureRegistrar.class)
@AutoConfigureAfter({ HibernateJpaAutoConfiguration.class,
		TaskExecutionAutoConfiguration.class })
public class JpaRepositoriesAutoConfiguration {

	@Bean
	@Conditional(BootstrapExecutorCondition.class)
	public EntityManagerFactoryBuilderCustomizer entityManagerFactoryBootstrapExecutorCustomizer( Map
       
         taskExecutors)
       ,> {
		return (builder) -> {
			AsyncTaskExecutor bootstrapExecutor = determineBootstrapExecutor(
					taskExecutors);
			if(bootstrapExecutor ! =null) { builder.setBootstrapExecutor(bootstrapExecutor); }}; }private AsyncTaskExecutor determineBootstrapExecutor( Map
       
         taskExecutors)
       ,> {
		if (taskExecutors.size() == 1) {
			return taskExecutors.values().iterator().next();
		}
		return taskExecutors
				.get(TaskExecutionAutoConfiguration.APPLICATION_TASK_EXECUTOR_BEAN_NAME);
	}

	private static final class BootstrapExecutorCondition extends AnyNestedCondition {

		BootstrapExecutorCondition() {
			super(ConfigurationPhase.REGISTER_BEAN);
		}

		@ConditionalOnProperty(prefix = "spring.data.jpa.repositories", name = "bootstrap-mode", havingValue = "deferred", matchIfMissing = false)
		static class DeferredBootstrapMode {}@ConditionalOnProperty(prefix = "spring.data.jpa.repositories", name = "bootstrap-mode", havingValue = "lazy", matchIfMissing = false)
		static class LazyBootstrapMode {}}}Copy the code

The EnableJpaRepositories annotation is declared in the imported configuration class

package org.springframework.boot.autoconfigure.data.jpa;

class JpaRepositoriesAutoConfigureRegistrar
		extends AbstractRepositoryConfigurationSourceSupport {

	private BootstrapMode bootstrapMode = null;

	@Override
	protected Class<? extends Annotation> getAnnotation() {
		return EnableJpaRepositories.class;
	}

	@Override
	protectedClass<? > getConfiguration() {return EnableJpaRepositoriesConfiguration.class;
	}

	@Override
	protected RepositoryConfigurationExtension getRepositoryConfigurationExtension(a) {
		return new JpaRepositoryConfigExtension();
	}

	@Override
	protected BootstrapMode getBootstrapMode(a) {
		return (this.bootstrapMode == null)?super.getBootstrapMode()
				: this.bootstrapMode;
	}

	@Override
	public void setEnvironment(Environment environment) {
		super.setEnvironment(environment);
		configureBootstrapMode(environment);
	}

	private void configureBootstrapMode(Environment environment) {
		String property = environment
				.getProperty("spring.data.jpa.repositories.bootstrap-mode");
		if (StringUtils.hasText(property)) {
			this.bootstrapMode = BootstrapMode .valueOf(property.toUpperCase(Locale.ENGLISH)); }}@EnableJpaRepositories
	private static class EnableJpaRepositoriesConfiguration {}}Copy the code

EnableJpaRepositories Annotation definition

package org.springframework.data.jpa.repository.config;

/**
 * Annotation to enable JPA repositories. Will scan the package of the annotated configuration class for Spring Data
 * repositories by default.
 *
 * @author Oliver Gierke
 * @author Thomas Darimont
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
@Import(JpaRepositoriesRegistrar.class)
public @interface EnableJpaRepositories {

	String[] value() default {};

	String[] basePackages() default{}; Class<? >[] basePackageClasses()default {};

	Filter[] includeFilters() default {};

	Filter[] excludeFilters() default {};

	String repositoryImplementationPostfix(a) default "Impl";

	String namedQueriesLocation(a) default "";

	Key queryLookupStrategy(a) default Key.CREATE_IF_NOT_FOUND; Class<? > repositoryFactoryBeanClass()defaultJpaRepositoryFactoryBean.class; Class<? > repositoryBaseClass()default DefaultRepositoryBaseClass.class;

	/** * Entity manages factory *@return* /
	String entityManagerFactoryRef(a) default "entityManagerFactory";

	/** * transaction management *@return* /
	String transactionManagerRef(a) default "transactionManager";

	boolean considerNestedRepositories(a) default false;

	boolean enableDefaultTransactions(a) default true;

	/**
	 * Configures when the repositories are initialized in the bootstrap lifecycle. {@link BootstrapMode#DEFAULT}
	 * (default) means eager initialization except all repository interfaces annotated with {@link Lazy},
	 * {@linkBootstrapMode#LAZY} means lazy by default including injection of lazy-initialization proxies into client * beans so that  those can be instantiated but will only trigger the initialization upon first repository usage (i.e a * method invocation on it). This means repositories can still be uninitialized when the application context has * completed its bootstrap. {@link BootstrapMode#DEFERRED} is fundamentally the same as {@link BootstrapMode#LAZY},
	 * but triggers repository initialization when the application context finishes its bootstrap.
	 * 
	 * @return
	 * @since2.1 * /
	BootstrapMode bootstrapMode(a) default BootstrapMode.DEFAULT;
}
Copy the code

Find the timing of registration for key beans

package org.springframework.boot.autoconfigure.orm.jpa;

@Configuration
@ConditionalOnClass({ LocalContainerEntityManagerFactoryBean.class, EntityManager.class })
@Conditional(HibernateEntityManagerCondition.class)
@EnableConfigurationProperties(JpaProperties.class)
@AutoConfigureAfter({ DataSourceAutoConfiguration.class })
@Import(HibernateJpaConfiguration.class)
public class HibernateJpaAutoConfiguration {}
Copy the code

Enter the configuration Bean

package org.springframework.boot.autoconfigure.orm.jpa;

@Configuration
@EnableConfigurationProperties(HibernateProperties.class)
@ConditionalOnSingleCandidate(DataSource.class)
class HibernateJpaConfiguration extends JpaBaseConfiguration {}
Copy the code

Assembly timing and conditions are declared in its parent JpaBaseConfiguration

package org.springframework.boot.autoconfigure.orm.jpa;

/** * This class was refactored in version 2.0 *@since2.0.0 * /
@Configuration
@EnableConfigurationProperties(JpaProperties.class)
@Import(DataSourceInitializedPublisher.Registrar.class)
public abstract class JpaBaseConfiguration implements BeanFactoryAware {
    
    @Bean
	@ConditionalOnMissingBean
	public PlatformTransactionManager transactionManager(a) {
		JpaTransactionManager transactionManager = new JpaTransactionManager();
		if (this.transactionManagerCustomizers ! =null) {
            // The default method name
			this.transactionManagerCustomizers.customize(transactionManager);
		}
		return transactionManager;
	}

	@Bean
	@ConditionalOnMissingBean
	public JpaVendorAdapter jpaVendorAdapter(a) {
		AbstractJpaVendorAdapter adapter = createJpaVendorAdapter();
		adapter.setShowSql(this.properties.isShowSql());
		adapter.setDatabase(this.properties.determineDatabase(this.dataSource));
		adapter.setDatabasePlatform(this.properties.getDatabasePlatform());
		adapter.setGenerateDdl(this.properties.isGenerateDdl());
		return adapter;
	}

	@Bean
	@ConditionalOnMissingBean
	public EntityManagerFactoryBuilder entityManagerFactoryBuilder( JpaVendorAdapter jpaVendorAdapter, ObjectProvider
       
         persistenceUnitManager, ObjectProvider
        
          customizers)
        
        {
		EntityManagerFactoryBuilder builder = new EntityManagerFactoryBuilder(
				jpaVendorAdapter, this.properties.getProperties(),
				persistenceUnitManager.getIfAvailable());
		customizers.orderedStream()
				.forEach((customizer) -> customizer.customize(builder));
		return builder;
	}

	@Bean
	@Primary
	@ConditionalOnMissingBean({ LocalContainerEntityManagerFactoryBean.class,
			EntityManagerFactory.class })
	public LocalContainerEntityManagerFactoryBean entityManagerFactory( EntityManagerFactoryBuilder factoryBuilder) {
		Map<String, Object> vendorProperties = getVendorProperties();
		customizeVendorProperties(vendorProperties);
		return factoryBuilder.dataSource(this.dataSource).packages(getPackagesToScan()) .properties(vendorProperties).mappingResources(getMappingResources()) .jta(isJta()).build(); }}Copy the code

test

After crawling the documentation and source code, you have a basic understanding of the beans that need to be configured, and create a project for multi-source testing

Test with the latest version of SpringBoot

maven

<?xml version="1.0" encoding="UTF-8"? >
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3. RELEASE</version>
        <relativePath/> <! -- lookup parent from repository -->
    </parent>
    <groupId>top.fjy8018</groupId>
    <artifactId>jpadatasource</artifactId>
    <version>0.0.1 - the SNAPSHOT</version>
    <name>jpadatasource</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
Copy the code

Entity class

The primary and backup libraries are distinguished by the package path

Main library entity class

package top.fjy8018.jpadatasource.entity.primary;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;

/ * * *@authorJia Yang * F@dateBut in 2019-03-30 * /
@Data
@Entity
public class Product {

    @Id
    private Integer id;

    private String name;

    private Integer price;
}
Copy the code

Backup library entity class

package top.fjy8018.jpadatasource.entity.backup;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 joppa * /
@Data
@Entity(name = "tb_order")
public class Order {

    @Id
    private Integer id;

    private String orderName;

    private Integer price;
}
Copy the code

Pit 1: Table name keyword conflicts

If the table name tb_ORDER is not specified, the table fails to be created because it conflicts with the MySQL keyword

Error:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL " create table Order ( id integer not null, orderName varchar(255), price integer, primary key (id) ) engine=InnoDB" via JDBC Statement
    ...
Copy the code

DAO

package top.fjy8018.jpadatasource.repository.backup;

import org.springframework.data.jpa.repository.JpaRepository;
import top.fjy8018.jpadatasource.entity.backup.Order;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 9:12 * /
public interface OrderRepository extends JpaRepository<Order.Integer> {}Copy the code

package top.fjy8018.jpadatasource.repository.primary;

import org.springframework.data.jpa.repository.JpaRepository;
import top.fjy8018.jpadatasource.entity.primary.Product;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 9:12 * /
public interface ProductRepository extends JpaRepository<Product.Integer> {}Copy the code

Configuration Bean

Official configuration example:

Both data sources are also bound for advanced customizations. For instance, you could configure them as follows:

app.datasource.first.url=jdbc:mysql://localhost/first
app.datasource.first.username=dbuser
app.datasource.first.password=dbpass
app.datasource.first.configuration.maximum-pool-size=30

app.datasource.second.url=jdbc:mysql://localhost/second
app.datasource.second.username=dbuser
app.datasource.second.password=dbpass
app.datasource.second.max-total=30
Copy the code

You can apply the same concept to the secondary DataSource as well, as shown in the following example:

@Bean
@Primary
@ConfigurationProperties("app.datasource.first")
public DataSourceProperties firstDataSourceProperties(a) {
	return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("app.datasource.first.configuration")
public HikariDataSource firstDataSource(a) {
	return firstDataSourceProperties().initializeDataSourceBuilder()
			.type(HikariDataSource.class).build();
}

@Bean
@ConfigurationProperties("app.datasource.second")
public DataSourceProperties secondDataSourceProperties(a) {
	return new DataSourceProperties();
}

@Bean
@ConfigurationProperties("app.datasource.second.configuration")
public BasicDataSource secondDataSource(a) {
	return secondDataSourceProperties().initializeDataSourceBuilder()
			.type(BasicDataSource.class).build();
}
Copy the code

The preceding example configures two data sources on custom namespaces with the same logic as Spring Boot would use in auto-configuration. Note that each configuration sub namespace provides advanced settings based on the chosen implementation.

See writing configuration files to note the driver changes for SpringBoot 2.x

Pit 2: mysql driver class name changed

For details, see SpringBoot mysql driver problems

spring:
  datasource:
    first:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: financial_adminer
      password: financial_adminer_pass
      url: jdbc:mysql://localhost/jpa_test? useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      configuration:
        maximum-pool-size: 30
    second:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: financial_adminer
      password: financial_adminer_pass
      url: jdbc:mysql://localhost/jpa_test_bak? useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      configuration:
        maximum-pool-size: 20

  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    database-platform: org.hibernate.dialect.MySQL55Dialect
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true
Copy the code

The configuration class

package top.fjy8018.jpadatasource.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 * / earth
@Configuration
public class DataAccessConfig {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first")
    public DataSourceProperties firstDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first.configuration")
    public HikariDataSource firstDataSource(a) {
        return firstDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second")
    public DataSourceProperties secondDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second.configuration")
    public HikariDataSource secondDataSource(a) {
        returnsecondDataSourceProperties().initializeDataSourceBuilder() .type(HikariDataSource.class).build(); }}Copy the code

After the configuration is complete, it is found that only the tables are automatically created in the primary database and the backup database is unchanged, which is expected because only one of the three required beans is currently configured

Continue to configure entity management and transaction management beans

Official document example

84.10 Use Two EntityManagers

Even if the default EntityManagerFactory works fine, you need to define a new one. Otherwise, the presence of the second bean of that type switches off the default. To make it easy to do, you can use the convenient EntityManagerBuilder provided by Spring Boot. Alternatively, you can just theLocalContainerEntityManagerFactoryBean directly from Spring ORM, as shown in the following example:
// add two data sources configured as above

@Bean
public LocalContainerEntityManagerFactoryBean customerEntityManagerFactory( EntityManagerFactoryBuilder builder) {
	return builder
			.dataSource(customerDataSource())
			.packages(Customer.class)
			.persistenceUnit("customers")
			.build();
}

@Bean
public LocalContainerEntityManagerFactoryBean orderEntityManagerFactory( EntityManagerFactoryBuilder builder) {
	return builder
			.dataSource(orderDataSource())
			.packages(Order.class)
			.persistenceUnit("orders")
			.build();
}
Copy the code
The configuration above almost works on its own. To complete the picture, you need to configure TransactionManagers for the two EntityManagers as well. If you mark one of them as @Primary, it could be picked up by the default JpaTransactionManager in Spring Boot. The other would have to be explicitly injected into a new instance. Alternatively, you might be able to use a JTA transaction manager that spans both.

If you use Spring Data, you need to configure @EnableJpaRepositories accordingly, as shown in the following example:
@Configuration
@EnableJpaRepositories(basePackageClasses = Customer.class,
		entityManagerFactoryRef = "customerEntityManagerFactory")
public class CustomerConfiguration {... }@Configuration
@EnableJpaRepositories(basePackageClasses = Order.class,
		entityManagerFactoryRef = "orderEntityManagerFactory")
public class OrderConfiguration {... }Copy the code

Project configuration

package top.fjy8018.jpadatasource.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 top.fjy8018.jpadatasource.entity.backup.Order;
import top.fjy8018.jpadatasource.entity.primary.Product;
import top.fjy8018.jpadatasource.repository.backup.OrderRepository;
import top.fjy8018.jpadatasource.repository.primary.ProductRepository;

import javax.sql.DataSource;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 * / earth
@Configuration
public class DataAccessConfig {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first")
    public DataSourceProperties firstDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first.configuration")
    public HikariDataSource firstDataSource(a) {
        return firstDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second")
    public DataSourceProperties secondDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second.configuration")
    public HikariDataSource secondDataSource(a) {
        return secondDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("firstDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages(Product.class)
                .persistenceUnit("first")
                .build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("secondDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages(Order.class)
                .persistenceUnit("second")
                .build();
    }

    @Bean
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("firstEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    @Bean
    public PlatformTransactionManager backupTransactionManager(
            @Qualifier("secondEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    @EnableJpaRepositories(basePackageClasses = ProductRepository.class,
            entityManagerFactoryRef = "firstEntityManagerFactory")
    @Primary
    public class PrimaryConfiguration {}@EnableJpaRepositories(basePackageClasses = OrderRepository.class,
            entityManagerFactoryRef = "secondEntityManagerFactory")
    public class secondConfiguration {}}Copy the code

Pit 3: The official document sample is incorrect

An error occurs when the configuration is started directly

Method requestMappingHandlerMapping in org.springframework.boot.autoconfigure.web.servlet.WebMvcAutoConfiguration$EnableWebMvcConfiguration required a single bean, but 2 were found:
	- firstEntityManagerFactory: defined by method 'firstEntityManagerFactory' in class path resource [top/fjy8018/jpadatasource/config/DataAccessConfig.class]
	- secondEntityManagerFactory: defined by method 'secondEntityManagerFactory' in class path resource [top/fjy8018/jpadatasource/config/DataAccessConfig.class]


Action:

Consider marking one of the beans as @Primary, updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed

Copy the code

This means that two beans of the same type are found while autologiing, and the priority is mainly specified. The official documentation does not indicate a possible problem with this

Gives priority to the Bean specified firstEntityManagerFactory, sample document at the same time created the transaction manager but not configured to @ EnableJpaRepositories annotations, here also configuration

The final configuration class is as follows

package top.fjy8018.jpadatasource.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 top.fjy8018.jpadatasource.entity.backup.Order;
import top.fjy8018.jpadatasource.entity.primary.Product;
import top.fjy8018.jpadatasource.repository.backup.OrderRepository;
import top.fjy8018.jpadatasource.repository.primary.ProductRepository;

import javax.sql.DataSource;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 * / earth
@Configuration
public class DataAccessConfig {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first")
    public DataSourceProperties firstDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first.configuration")
    public HikariDataSource firstDataSource(a) {
        return firstDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second")
    public DataSourceProperties secondDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second.configuration")
    public HikariDataSource secondDataSource(a) {
        return secondDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("firstDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages(Product.class)
                .persistenceUnit("first")
                .build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("secondDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages(Order.class)
                .persistenceUnit("second")
                .build();
    }

    @Bean
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("firstEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    @Bean
    public PlatformTransactionManager backupTransactionManager(
            @Qualifier("secondEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    @EnableJpaRepositories(basePackageClasses = ProductRepository.class,
            entityManagerFactoryRef = "firstEntityManagerFactory", transactionManagerRef = "primaryTransactionManager")
    @Primary
    public class PrimaryConfiguration {}@EnableJpaRepositories(basePackageClasses = OrderRepository.class,
            entityManagerFactoryRef = "secondEntityManagerFactory", transactionManagerRef = "backupTransactionManager")
    public class secondConfiguration {}}Copy the code

Pit 4: JPA will not automatically build tables

Start after the configuration is complete

If no SQL output is generated for the created table in the log, check the database to ensure that the table is not created

But the official documentation for configuring multiple data sources has ended up confusing

The assumption that the auto-build sentence is not generated may be due to the unconfigured ddL-related attributes

Enter the configuration file and check the DDL configuration. The default value is false

After it is set to true, the total configuration file is as follows

spring:
  datasource:
    first:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: financial_adminer
      password: financial_adminer_pass
      url: JDBC: mysql: / / 120.79.226.26 / jpa_test? useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      configuration:
        maximum-pool-size: 30
    second:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: financial_adminer
      password: financial_adminer_pass
      url: JDBC: mysql: / / 120.79.226.26 / jpa_test_bak? useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      configuration:
        maximum-pool-size: 20

  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    database-platform: org.hibernate.dialect.MySQL55Dialect
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true
    generate-ddl: true
Copy the code

Start project discovery automatic table creation takes effect

Check the database status. As expected, the primary database contains only tables of the primary database, and the backup database contains only tables of the backup database. Multiple data configurations are complete

Pit 5: Hibernate default configuration overridden

If this parameter is not required in the single-source configuration and the policy that maps the hump attribute name to _ concicing name by default is invalid, the default configuration is overridden or invalid when you customize the source configuration

Hibernate configuration is found in the HibernateProperties configuration class

package org.springframework.boot.autoconfigure.orm.jpa;

/**
 * Configuration properties for Hibernate.
 *
 * @author Stephane Nicoll
 * @since 2.1.0
 * @see JpaProperties
 */
@ConfigurationProperties("spring.jpa.hibernate")
public class HibernateProperties {}
Copy the code

And the class in HibernateJpaConfiguration injection, but the class of the conditions of assembly annotations @ ConditionalOnSingleCandidate annotation, cause in the case of multiple source does not meet the configuration, the class has not been assembled into a Spring Bean

package org.springframework.boot.autoconfigure.orm.jpa;

/ * * * {@link JpaBaseConfiguration} implementation for Hibernate.
 * @since2.0.0 * /
@Configuration
@EnableConfigurationProperties(HibernateProperties.class)
@ConditionalOnSingleCandidate(DataSource.class)
class HibernateJpaConfiguration extends JpaBaseConfiguration {

	private final HibernateProperties hibernateProperties;

	HibernateJpaConfiguration(DataSource dataSource, JpaProperties jpaProperties,
			ConfigurableListableBeanFactory beanFactory,
			ObjectProvider<JtaTransactionManager> jtaTransactionManager,
			ObjectProvider<TransactionManagerCustomizers> transactionManagerCustomizers,
			HibernateProperties hibernateProperties,
			ObjectProvider<Collection<DataSourcePoolMetadataProvider>> metadataProviders,
			ObjectProvider<SchemaManagementProvider> providers,
			ObjectProvider<PhysicalNamingStrategy> physicalNamingStrategy,
			ObjectProvider<ImplicitNamingStrategy> implicitNamingStrategy,
			ObjectProvider<HibernatePropertiesCustomizer> hibernatePropertiesCustomizers) {
		super(dataSource, jpaProperties, jtaTransactionManager,
				transactionManagerCustomizers);
		this.hibernateProperties = hibernateProperties;
		this.defaultDdlAutoProvider = new HibernateDefaultDdlAutoProvider(providers);
		this.poolMetadataProvider = new CompositeDataSourcePoolMetadataProvider(
				metadataProviders.getIfAvailable());
		this.hibernatePropertiesCustomizers = determineHibernatePropertiesCustomizers( physicalNamingStrategy.getIfAvailable(), implicitNamingStrategy.getIfAvailable(), beanFactory, hibernatePropertiesCustomizers.orderedStream() .collect(Collectors.toList())); }... }Copy the code

Note that the current configuration is valid based on the latest version 2.1.3, and does not necessarily mean compatibility with subsequent versions

Project configuration

package top.fjy8018.jpadatasource.config;

import com.zaxxer.hikari.HikariDataSource;
import org.hibernate.boot.model.naming.ImplicitNamingStrategy;
import org.hibernate.boot.model.naming.PhysicalNamingStrategy;
import org.hibernate.cfg.AvailableSettings;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.config.ConfigurableListableBeanFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernatePropertiesCustomizer;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.EmbeddedDatabaseConnection;
import org.springframework.boot.jdbc.SchemaManagement;
import org.springframework.boot.jdbc.SchemaManagementProvider;
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.hibernate5.SpringBeanContainer;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.util.ClassUtils;
import top.fjy8018.jpadatasource.entity.backup.Order;
import top.fjy8018.jpadatasource.entity.primary.Product;
import top.fjy8018.jpadatasource.repository.backup.OrderRepository;
import top.fjy8018.jpadatasource.repository.primary.ProductRepository;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Supplier;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;

/ * * *@authorJia Yang * F@dateThe 2019-03-30 * / earth
@Configuration
public class DataAccessConfig {

    private final ObjectProvider<SchemaManagementProvider> providers;

    private final HibernateProperties hibernateProperties;

    private final JpaProperties properties;

    private final ObjectProvider<PhysicalNamingStrategy> physicalNamingStrategy;

    private final ObjectProvider<ImplicitNamingStrategy> implicitNamingStrategy;

    private final ConfigurableListableBeanFactory beanFactory;

    private final ObjectProvider<HibernatePropertiesCustomizer> hibernatePropertiesCustomizers;

    @Autowired
    public DataAccessConfig(ObjectProvider<SchemaManagementProvider> providers, HibernateProperties hibernateProperties, JpaProperties properties, ObjectProvider<PhysicalNamingStrategy> physicalNamingStrategy, ObjectProvider<ImplicitNamingStrategy> implicitNamingStrategy, ConfigurableListableBeanFactory beanFactory, ObjectProvider<HibernatePropertiesCustomizer> hibernatePropertiesCustomizers) {
        this.providers = providers;
        this.hibernateProperties = hibernateProperties;
        this.properties = properties;
        this.physicalNamingStrategy = physicalNamingStrategy;
        this.implicitNamingStrategy = implicitNamingStrategy;
        this.beanFactory = beanFactory;
        this.hibernatePropertiesCustomizers = hibernatePropertiesCustomizers;
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first")
    public DataSourceProperties firstDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first.configuration")
    public HikariDataSource firstDataSource(a) {
        return firstDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second")
    public DataSourceProperties secondDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.second.configuration")
    public HikariDataSource secondDataSource(a) {
        return secondDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("firstDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                // Add protocol configuration
                .properties(getVendorProperties(dataSource))
                .packages(Product.class)
                .persistenceUnit("first")
                .build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("secondDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .properties(getVendorProperties(dataSource))
                .packages(Order.class)
                .persistenceUnit("second")
                .build();
    }

    @Bean
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("firstEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    @Bean
    public PlatformTransactionManager backupTransactionManager(
            @Qualifier("secondEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    @EnableJpaRepositories(basePackageClasses = ProductRepository.class,
            entityManagerFactoryRef = "firstEntityManagerFactory", transactionManagerRef = "primaryTransactionManager")
    @Primary
    public class PrimaryConfiguration {}@EnableJpaRepositories(basePackageClasses = OrderRepository.class,
            entityManagerFactoryRef = "secondEntityManagerFactory", transactionManagerRef = "backupTransactionManager")
    public class secondConfiguration {}/** * Obtain configuration file information **@param dataSource
     * @return* /
    private Map<String, Object> getVendorProperties(DataSource dataSource) {
        List<HibernatePropertiesCustomizer> hibernatePropertiesCustomizers = determineHibernatePropertiesCustomizers(
                physicalNamingStrategy.getIfAvailable(),
                implicitNamingStrategy.getIfAvailable(), beanFactory,
                this.hibernatePropertiesCustomizers.orderedStream()
                        .collect(Collectors.toList()));
        Supplier<String> defaultDdlMode = () -> new HibernateDefaultDdlAutoProvider(providers)
                .getDefaultDdlAuto(dataSource);
        return new LinkedHashMap<>(this.hibernateProperties.determineHibernateProperties(
                properties.getProperties(),
                new HibernateSettings().ddlAuto(defaultDdlMode)
                        .hibernatePropertiesCustomizers(
                                hibernatePropertiesCustomizers)));
    }

    /** * The naming policy automatically determines **@param physicalNamingStrategy
     * @param implicitNamingStrategy
     * @param beanFactory
     * @param hibernatePropertiesCustomizers
     * @return* /
    private List<HibernatePropertiesCustomizer> determineHibernatePropertiesCustomizers( PhysicalNamingStrategy physicalNamingStrategy, ImplicitNamingStrategy implicitNamingStrategy, ConfigurableListableBeanFactory beanFactory, List
       
         hibernatePropertiesCustomizers)
        {
        List<HibernatePropertiesCustomizer> customizers = new ArrayList<>();
        if (ClassUtils.isPresent(
                "org.hibernate.resource.beans.container.spi.BeanContainer",
                getClass().getClassLoader())) {
            customizers
                    .add((properties) -> properties.put(AvailableSettings.BEAN_CONTAINER,
                            new SpringBeanContainer(beanFactory)));
        }
        if(physicalNamingStrategy ! =null|| implicitNamingStrategy ! =null) {
            customizers.add(new NamingStrategiesHibernatePropertiesCustomizer(
                    physicalNamingStrategy, implicitNamingStrategy));
        }
        customizers.addAll(hibernatePropertiesCustomizers);
        return customizers;
    }

    /** * automatically create table */
    class HibernateDefaultDdlAutoProvider implements SchemaManagementProvider {

        private final Iterable<SchemaManagementProvider> providers;

        HibernateDefaultDdlAutoProvider(Iterable<SchemaManagementProvider> providers) {
            this.providers = providers;
        }

        public String getDefaultDdlAuto(DataSource dataSource) {
            if(! EmbeddedDatabaseConnection.isEmbedded(dataSource)) {return "none";
            }
            SchemaManagement schemaManagement = getSchemaManagement(dataSource);
            if (SchemaManagement.MANAGED.equals(schemaManagement)) {
                return "none";
            }
            return "create-drop";

        }

        @Override
        public SchemaManagement getSchemaManagement(DataSource dataSource) {
            return StreamSupport.stream(this.providers.spliterator(), false) .map((provider) -> provider.getSchemaManagement(dataSource)) .filter(SchemaManagement.MANAGED::equals).findFirst() .orElse(SchemaManagement.UNMANAGED); }}private static class NamingStrategiesHibernatePropertiesCustomizer
            implements HibernatePropertiesCustomizer {

        private final PhysicalNamingStrategy physicalNamingStrategy;

        private final ImplicitNamingStrategy implicitNamingStrategy;

        NamingStrategiesHibernatePropertiesCustomizer(
                PhysicalNamingStrategy physicalNamingStrategy,
                ImplicitNamingStrategy implicitNamingStrategy) {
            this.physicalNamingStrategy = physicalNamingStrategy;
            this.implicitNamingStrategy = implicitNamingStrategy;
        }

        /** * Database naming mapping policy **@param hibernateProperties the JPA vendor properties to customize
         */
        @Override
        public void customize(Map<String, Object> hibernateProperties) {
            if (this.physicalNamingStrategy ! =null) {
                hibernateProperties.put("hibernate.physical_naming_strategy".this.physicalNamingStrategy);
            }
            if (this.implicitNamingStrategy ! =null) {
                hibernateProperties.put("hibernate.implicit_naming_strategy".this.implicitNamingStrategy); }}}}Copy the code

After configuring the Bean, the protocol configuration takes effect, eliminating the need to display the configuration DDL statement execution switch in the configuration file

The startup log also shows that the results are as expected

conclusion

As a result, the SpringBoot version migration process is not necessarily compatible with each other, and the official documentation, although comprehensive, can be inaccurate as the version changes. And if you rely entirely on crawling source code copy source code configuration is also unable to achieve the most simplified configuration, the need to combine documents, source code, log analysis one by one

The project source code has been opened, pay attention to configure their own database connection address, the project connection address has been invalid

Making the source code