SpringBoot multi-data source configuration

Please indicate the original source, thank you!

HappyFeet blog

Recently, two mysql databases need to be connected in the project, that is, multiple data sources need to be configured.

There is a lot of information about the configuration of multiple data sources on the web. After a search, we started to configure. Although the configuration process also encountered some pits, but generally still relatively simple.

The general steps are as follows :(the github address of the project is attached at the end of the article)

A,application.ymlSQL > alter database (primary, secondary);

spring:
  datasource:
    primary:
      hikari:
        driver-class-name: com.mysql.cj.jdbc.Driver
        connection-test-query: SELECT 1 FROM DUAL
        minimum-idle: 1
        maximum-pool-size: 5
        pool-name: bosPoolName
        max-lifetime: 180000000
        jdbcUrl: jdbc:mysql://${mysqlHost1:localhost}:3306/test1? useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: ${mysqlUsername1:root}
        password: ${mysqlPassword1:123456}
    secondary:
      hikari:
        driver-class-name: com.mysql.cj.jdbc.Driver
        connection-test-query: SELECT 1 FROM DUAL
        minimum-idle: 1
        maximum-pool-size: 5
        pool-name: bosPoolName
        max-lifetime: 180000000
        jdbcUrl: jdbc:mysql://${mysqlHost2:localhost}:3306/test2? useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: ${mysqlUsername2:root}
        password: ${mysqlPassword2:123456}
Copy the code

Second, addPrimaryDataSourceConfig 和 SecondaryDataSourceConfigThe configuration class

PrimaryDataSourceConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager",
        basePackages = {"com.yhh.primary.**.dao"} // The package where the primary database corresponds to the DAO
)
public class PrimaryDataSourceConfig {

    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.primary.hikari") // Primary database configuration
    public DataSource getDataSource(a) {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean
    public JdbcTemplate getJdbcTemplate(a) {
        return new JdbcTemplate(getDataSource());
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                       @Qualifier("vendorProperties")Map<String, ? > vendorProperties){ // Self-defined Bean: vendorProperties
        return builder
                .dataSource(getDataSource())
                .properties(vendorProperties)
                .packages("com.yhh.primary.**.entity") // The primary database corresponds to the package of the Entity
                .persistenceUnit("primary") // Persistence unit
                .build();
    }

    @Primary
    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return newJpaTransactionManager(entityManagerFactory); }}Copy the code

SecondaryDataSourceConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager",
        basePackages = "com.yhh.secondary.**.dao" // The package where the DAO belongs to the secondary database
)
public class SecondaryDataSourceConfig {

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary.hikari") // secondary Database configuration
    public DataSource secondaryDataSource(a) {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }


    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(a) {
        return new JdbcTemplate(secondaryDataSource());
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                       @Qualifier("vendorProperties")Map<String, ? > vendorProperties){ // Self-defined Bean: vendorProperties
        return builder
                .dataSource(secondaryDataSource())
                .properties(vendorProperties)
                .packages("com.yhh.secondary.**.entity") // Secondary Specifies the package of the entity in the database
                .persistenceUnit("secondary") // Persistence unit
                .build();
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("secondaryEntityManagerFactory") EntityManagerFactory entityManagerFactory
    ) {
        return newJpaTransactionManager(entityManagerFactory); }}Copy the code

Three,vendorPropertiesBean configuration

    @Autowired
    private JpaProperties jpaProperties;

    @Bean(name = "vendorProperties")
    public Map<String, Object> getVendorProperties(a) {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
Copy the code

The following three properties are actually configured:

"hibernate.id.new_generator_mappings" -> "true"
Copy the code

"hibernate.physical_naming_strategy" -> "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy"
Copy the code

"hibernate.implicit_naming_strategy" -> "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy"
Copy the code

There are two reference links at the end of this article that basically configure it, although there are problems when database field names are named using underscore naming. The exceptions are as follows:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1054, SQLState: 42S22
o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'teacherdo0_.teacherName' in 'field list'
Copy the code

The reason is that the code uses the hump naming method, while the database uses the underline naming method. The two cannot be mapped to each other, and an Unknown column exception will be reported. It took a long time to solve this problem, which was later resolved by adding vendorProperties.

Iv. Add Entity and DAO (It should be noted that the package location of Entity and DAO should be consistent with the previous configuration)

StudentDO.java

package com.yhh.primary.entity;

import lombok.Data;

import javax.persistence.*;


@Data
@Entity
@Table(name = "student")
public class StudentDO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String studentName;
    private Integer age;
}

Copy the code

StudentDao.java

package com.yhh.primary.dao;

import com.yhh.primary.entity.StudentDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface StudentDao extends JpaRepository<StudentDO.Integer> {}Copy the code

TeacherDO.java

package com.yhh.secondary.entity;

import lombok.Data;

import javax.persistence.*;

@Entity
@Data
@Table(name = "teacher")
public class TeacherDO {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String teacherName;
    private Integer age;
}

Copy the code

TeacherDao.java

package com.yhh.secondary.dao;

import com.yhh.secondary.entity.TeacherDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface TeacherDao extends JpaRepository<TeacherDO.Integer> {}Copy the code

5. Test and verify (the database needs to have the corresponding table and data, there are ready-made SQL statements in the README file of github project)

You can verify that the configuration is successful by adding IT or Controller.

1.IT(Integration test), run integration test, both DAOs can find data.

package com.yhh.dao;

import com.yhh.primary.dao.StudentDao;
import com.yhh.primary.entity.StudentDO;
import com.yhh.secondary.dao.TeacherDao;
import com.yhh.secondary.entity.TeacherDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
@EnableAutoConfiguration
public class MutiDaoIT {

    @Autowired
    private StudentDao studentDao;

    @Autowired
    private TeacherDao teacherDao;

    @Test
    public void muti_dao_IT(a) { List<TeacherDO> teacherDOList = teacherDao.findAll(); List<StudentDO> studentDOList = studentDao.findAll(); Assert.assertFalse(teacherDOList.isEmpty()); Assert.assertFalse(studentDOList.isEmpty()); }}Copy the code

2,ControllerRequest by starting the SpringBoot applicationhttp://localhost:8888/api/muti-dataYou’re going to get ajsonAn array with four pieces of data in it.

package com.yhh.rest;

import com.yhh.primary.dao.StudentDao;
import com.yhh.secondary.dao.TeacherDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping(value = "/api")
@Slf4j
public class ShowController {

    private final StudentDao studentDao;
    private final TeacherDao teacherDao;

    public ShowController(StudentDao studentDao, TeacherDao teacherDao) {
        this.studentDao = studentDao;
        this.teacherDao = teacherDao;
    }

    @GetMapping(value = "/muti-data")
    public ResponseEntity queryMutiData(a) {
        log.info("query muti-data.");

        List result = new ArrayList<>();

        result.addAll(studentDao.findAll());
        result.addAll(teacherDao.findAll());

        log.info("result size is {}.", result.size());

        returnResponseEntity.ok(result); }}Copy the code

All of the above code can be found on Github: spring-Muti-datasource -config

References:

(1) Spring Boot Configure and Use Two DataSources

(2) Using multiple datasources with Spring Boot and Spring Data