about

Sometimes, with the development of business, the data sources associated with the project will become more and more complex, and the database used will be more dispersed. At this time, multi-data sources will be used to obtain data. In addition, multiple data sources have other advantages, such as the separation of read and write in distributed databases, the integration of multiple databases and so on. Below I share my case of configuring multiple data sources in a real project.

step

1. Configure the database source in the application.yml file. Here primary is the main library, and secondary is from the library.

server:
  port: 8089

# Multiple Data Source Configuration
#primary
spring:
  primary:
    datasource:
      url: jdbc:mysql://127.0.0.1:3306/database1?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
      username: root
      password: ******
      driver-class-name: com.mysql.jdbc.Driver

  #secondary
  secondary:
    datasource:
      url: jdbc:mysql://127.0.0.1:3306/database1?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
      username: root
      password: ******
      driver-class-name: com.mysql.jdbc.Driver

  jpa:
    hibernate:
      primary-dialect: org.hibernate.dialect.MySQL5Dialect
      secondary-dialect: org.hibernate.dialect.MySQL5Dialect
    open-in-view: true
    show-sql: true
//Copy code

2. Create a Spring configuration class where the path of spring.primary.datasource refers to the configuration of the yml file.

@Configuration
public class DataSourceConfig {

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix="spring.primary.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.secondary.datasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

}
//Copy code

3. Create configuration classes of master and slave libraries respectively. Note: The configuration of entity and dao packages and the @Primary annotation specify the main library.

Main Library Configuration Class:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.xxx.xxx.dao.primary"}) //Set Repository Location
public class PrimaryConfig {
    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

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

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties(primaryDataSource))
                .packages("com.infinitus.yunxiao_data.entity.primary") //Set the location of the entity class
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }


    private Map getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}
//Copy code

Configuration classes from libraries:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.infinitus.yunxiao_data.dao.secondary"}) //Set Repository Location
public class SecondaryConfig {
    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

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

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties(secondaryDataSource))
                .packages("com.xxx.xxx.entity.secondary") //Set the location of the entity class
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }


    private Map getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }

}
//Copy code

4. Create master and slave library dao classes respectively. Main dao:

@Repository
public interface PrimaryRepository extends JpaRepository<PrimaryEntity, Long> {

    @Query(value = "SELECT p FROM PrimaryEntity p")
    List<PrimaryEntity> queryList();

}
//Copy code

From dao:

@Repository
public interface SecondaryRepository extends JpaRepository<SecondaryEntity, Long> {

    @Query(value = "SELECT p FROM SecondaryEntity p")
    List<SecondaryEntity> queryList();

}
//Copy code

5. Create master and slave library entity classes respectively. Main entity:

@Entity
@Table(name = "holiday_scheme")
@EntityListeners(AuditingEntityListener.class)
public class PrimaryEntity extends AbstractPersistable<Long> {
    @Column(name = "date")
    public String date;
    @Column(name = "hour")
    public String hour;
    @Column(name = "holiday")
    public String holiday;
    @Column(name = "holiday_explain")
    public String holiday_explain;

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getHour() {
        return hour;
    }

    public void setHour(String hour) {
        this.hour = hour;
    }

    public String getHoliday() {
        return holiday;
    }

    public void setHoliday(String holiday) {
        this.holiday = holiday;
    }

    public String getHoliday_explain() {
        return holiday_explain;
    }

    public void setHoliday_explain(String holiday_explain) {
        this.holiday_explain = holiday_explain;
    }

    @Override
    public String toString() {
        return "PrimaryEntity{" +
                "date='" + date + '\'' +
                ", hour='" + hour + '\'' +
                ", holiday='" + holiday + '\'' +
                ", holiday_explain='" + holiday_explain + '\'' +
                '}';
    }
}
//Copy code

From entity:

@Entity
@Table(name = "active_dashboards")
@EntityListeners(AuditingEntityListener.class)
public class SecondaryEntity extends AbstractPersistable<Long> {

    @Column(name = "dashboard_id")
    public String dashboard_id;
    @Column(name = "user_id")
    public String user_id;
    @Column(name = "order_index")
    public String order_index;

    public String getDashboard_id() {
        return dashboard_id;
    }

    public void setDashboard_id(String dashboard_id) {
        this.dashboard_id = dashboard_id;
    }

    public String getUser_id() {
        return user_id;
    }

    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }

    public String getOrder_index() {
        return order_index;
    }

    public void setOrder_index(String order_index) {
        this.order_index = order_index;
    }

    @Override
    public String toString() {
        return "SecondaryEntity{" +
                "dashboard_id='" + dashboard_id + '\'' +
                ", user_id='" + user_id + '\'' +
                ", order_index='" + order_index + '\'' +
                '}';
    }
}
//Copy code

6.controller requests data from different databases.

@RestController
@RequestMapping("/database")
public class MailController {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    PrimaryRepository primaryRepository;
    @Autowired
    SecondaryRepository secondaryRepository;

    @RequestMapping("/primary")
    @ResponseBody
    public String primary() {
        return primaryRepository.queryList().toString();
    }

    @RequestMapping("/secondary")
    @ResponseBody
    public String secondary() {
        return secondaryRepository.queryList().toString();
    }

}
//Copy code

Be careful

Below are two pits encountered when configuring multiple data sources, which drop out of focus. 1. The Application class does not need to configure the @EnableJpaRepositories annotation, and will report the following error.

A component required a bean named 'entityManagerFactory' that could not be f
//Copy code

2. Pay attention to check the dao class, get data in the correct format, whether there is a field that does not exist in the table, to avoid startup exception. As follows, the job_name field does not exist in the Secondary Entity table, so comments are left out to start successfully, etc.

//@Query(value = "SELECT p FROM SecondaryEntity p where p.job_name = ?1")
//List < Secondary Entity > queryOdcRecord (String job_name); copy code