Configuring multiple data sources with spring boot

Time:2022-5-9

After business development, some query type requests need to be switched to standby data sources. At this time, multi data source configuration needs to be used. The company’s projects do not use master-slave, but need to query the PostgreSQL Library (the business library is mysql, so it is not a simple master-slave, and the statements may also change, because MySQL is a sub database)

Overall document structure

Configuring multiple data sources with spring boot

Code file structure

Multi data source configuration

  • Primary data source configuration
    scanningcom.tenmao.multidatasource.mapper.primaryThe mapper interface under. Note that it needs to be configuredsqlSessionFactoryRef = "primarySqlSessionFactory"
@Configuration
@MapperScan(basePackages = {"com.tenmao.multidatasource.mapper.primary"}, sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfiguration {

    @Bean("primary")
    //Read from the configuration file with the prefix spring datasource. The primary configuration is used as the primary data source
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("primarySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primary") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //Configuration XML file
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }
}
  • Second data source configuration
    scanningcom.tenmao.multidatasource.mapper.secondThe mapper interface under. Note that it needs to be configuredsqlSessionFactoryRef = "secondSqlSessionFactory"
@Configuration
@MapperScan(basePackages = {"com.tenmao.multidatasource.mapper.second"}, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfiguration {

    @Bean("second")
   //Read from the configuration file with the prefix spring datasource. The configuration of second is used as the second data source 
  @ConfigurationProperties(prefix = "spring.datasource.second")
   public DataSource primaryDataSource() {
       return DataSourceBuilder.create().build();
   }

   @Bean("secondSqlSessionFactory")
   public SqlSessionFactory secondSqlSessionFactory(@Qualifier("second") DataSource dataSource) throws Exception {
       SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
       bean.setDataSource(dataSource);
       //Configuration XML file
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
       return bean.getObject();
   }
}

Domain class and mapper class

  • Domain class: user is the table information of the primary library
@Data
public class User {
   private Integer uid;
   private String name;
}
  • Domain class: phone is the table information of the second library
@Data
public class Phone {
   private String model;
   private Integer price;
}
  • Mapping class of mybatis:UserMapper
@Mapper
public interface UserMapper {
   @Select("SELECT * FROM user WHERE id=#{id}")
   User selectOne(int id);
}
  • Mapping class of mybatis:PhoneMapper
@Mapper
public interface PhoneMapper {
   @Select("SELECT * FROM phone WHERE id=#{id}")
   Phone selectOne(int id);
}

Startup classes and configuration files

  • Startup class
@RestController
@SpringBootApplication
public class MultidatasourceApplication {
   @Resource
   private UserMapper userMapper;

   @Resource
   private PhoneMapper phoneMapper;

   public static void main(String[] args) {
       SpringApplication.run(MultidatasourceApplication.class, args);
   }

   @GetMapping("getOneUser")
   public User getOneUser(@RequestParam Integer id) {
       return userMapper.selectOne(id);
   }

   @GetMapping("getOnePhone")
   public Phone getOnePhone(@RequestParam Integer id) {
       return phoneMapper.selectOne(id);
   }
}
  • Profile:application.yml
    It should be noted that the configurations here are actually configuration items of Hikari data source
spring:
 datasource:
   primary:
     jdbc-url: jdbc:mysql://localhost:3306/test?useAffectedRows=true&autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&serverTimezone=GMT%2B8
     username: root
     password: root
   second:
     jdb-url: jdbc:mysql://192.168.1.101:3306/tenmao?useAffectedRows=true&autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&serverTimezone=GMT%2B8
     username: tenmao
     password: tenmao

Other features

  • Read only data source: if one of the data sources is read-only, you can set readonly in the configuration
second:
     jdb-url: jdbc:mysql://192.168.1.101:3306/tenmao?useAffectedRows=true&autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&serverTimezone=GMT%2B8
     username: tenmao
     password: tenmao
     #Set the second data source to read-only
     read-only: true

If you try to modify a read-only data source, an exception is thrown:java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

  • Using mybatis plus
    You need to replace sqlsessionfactorybean with mybatissqlsessionfactorybean during data source configuration
@Bean("primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primary") DataSource dataSource) throws Exception {
    //If you use mybatis plus, you need to replace sqlsessionfactorybean with mybatissqlsessionfactorybean
    MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    //Configuration XML file
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
    return bean.getObject();
}

common problem

  • org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.tenmao.multidatasource.mapper.second.PhoneMapper.selectById: This is a built-in function using mybatis plus, but the standard version of sqlsessionfactory is used. The solution is to useSqlSessionFactoryBeanReplace withMybatisSqlSessionFactoryBean