about

Sometimes, with the development of the business, the data sources associated with the project will become more and more complex, and the database used will be scattered. In this case, the method of multi-data source will be adopted to obtain the data. In addition, multiple data sources have other benefits, such as read and write separation of distributed databases, integration of multiple databases, and so on. The following is a case where I configured multiple data sources in a real project.

steps

1. In the application.yml file, configure the database source. Where primary is the primary library and secondary is the secondary library.

server:
  port: 8089

# Multiple data source configuration
#primarySpring: primary: the 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

  #secondarySecondary: the 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 the code

2. Create a Spring configuration classes, including Spring. The primary. The path of the datasource reference yml file configuration.

@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() {
        returnDataSourceBuilder.create().build(); }}Copy the code

3. Create configuration classes for the primary and secondary libraries respectively. Note: The entity package and DAO package configuration, and the @primary annotation specify the master 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"PersistenceUnit ()"primaryPersistenceUnit")
                .build();
    }


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

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

Configuration classes for the slave library:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.infinitus.yunxiao_data.dao.secondary"}) 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"PersistenceUnit ()"primaryPersistenceUnit")
                .build();
    }


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

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        returnnew JpaTransactionManager(entityManagerFactorySecondary(builder).getObject()); }}Copy the code

4. Create dao classes for the master and slave libraries respectively. Lord dao:

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

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

}
Copy the code

From the dao:

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

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

}
Copy the code

5. Create entity classes for the master and slave libraries respectively. The 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 the code

From the 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 the 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() {
        returnsecondaryRepository.queryList().toString(); }}Copy the code

Pay attention to

The following are two examples of pitfalls encountered when configuring multiple data sources. 1. The @EnableJpaRepositories annotation is not required for the Application class, and the following error is reported.

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

2. Check the DAO class, whether the format of the method to obtain data is correct, and whether a certain field does not exist in the table to avoid abnormal startup. The SecondaryEntity table does not contain the job_name field. Therefore, the SecondaryEntity table can be successfully started only after the comment is deleted.

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