Spring Boot配置多数据源

业务发展后，一些查询类型的请求需要切换到备用数据源，这个时候需要使用多数据源配置，公司的项目使用的不是主备，而是需要去查询postgresql库（业务库是mysql，所以不是简单的主从，而且语句也可能变化，因为mysql是做了分库的）

整体文件结构

代码文件结构

多数据源配置

• primary数据源配置
  扫描com.tenmao.multidatasource.mapper.primary下的mapper接口，注意需要配置sqlSessionFactoryRef = "primarySqlSessionFactory"

@Configuration
@MapperScan(basePackages = {"com.tenmao.multidatasource.mapper.primary"}, sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfiguration {

    @Bean("primary")
    //从配置文件中读取前缀为spring.datasource.primary的配置作为primary数据源
    @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);
        //配置xml文件
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }
}

• second数据源配置
  扫描com.tenmao.multidatasource.mapper.second下的mapper接口，注意需要配置sqlSessionFactoryRef = "secondSqlSessionFactory"

@Configuration
@MapperScan(basePackages = {"com.tenmao.multidatasource.mapper.second"}, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfiguration {

    @Bean("second")
   //从配置文件中读取前缀为spring.datasource.second的配置作为second数据源 
  @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);
       //配置xml文件
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
       return bean.getObject();
   }
}

领域类和Mapper类

• 领域类: User是primary库的表信息

@Data
public class User {
   private Integer uid;
   private String name;
}

• 领域类: Phone是second库的表信息

@Data
public class Phone {
   private String model;
   private Integer price;
}

• MyBatis的映射类: UserMapper

@Mapper
public interface UserMapper {
   @Select("SELECT * FROM user WHERE id=#{id}")
   User selectOne(int id);
}

• MyBatis的映射类: PhoneMapper

@Mapper
public interface PhoneMapper {
   @Select("SELECT * FROM phone WHERE id=#{id}")
   Phone selectOne(int id);
}

启动类和配置文件

• 启动类

@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);
   }
}

• 配置文件：application.yml
  需要注意的是: 这里的配置实际上都是Hikari数据源的配置项

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

其他特性

• 数据源只读: 如果其中一个数据源是只读的话， 可以在配置中设置readonly

   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
     # 设置second数据源为只读
     read-only: true

如果尝试修改只读数据源则会抛出异常：java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

• 使用MyBatis-Plus
  需要在数据源配置的时候把SqlSessionFactoryBean替换为MybatisSqlSessionFactoryBean

@Bean("primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primary") DataSource dataSource) throws Exception {
    //如果使用MyBatis-Plus，则需要把SqlSessionFactoryBean替换为MybatisSqlSessionFactoryBean
    MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    //配置xml文件
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
    return bean.getObject();
}

常见问题

• org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.tenmao.multidatasource.mapper.second.PhoneMapper.selectById: 这是使用MyBatis-Plus的内置函数，但是使用的是标准版的SqlSessionFactory。解决办法是用SqlSessionFactoryBean替换为MybatisSqlSessionFactoryBean