SpringBoot下多数据源JPA与Mybatis完整配置

在一段时间里,我们在一些项目的架构中,使用了JPA作为持久化框架来操作数据库,并且在项目中使用JPA还是非常方便的,省去了如Mybatis中的Mapper.xml文件,同时我们可以通过在程序中定义的类型来生成和更新数据库。可是在新的团队和项目中发现在复杂的业务查询操作中,JPA对于开发人员的熟悉API及掌握情况还是比较高的,这对于之前一直使用Mybatis的团队来说增加了开发难度,于是我们又将Mybatis也集成到了项目当中。同时业务上要求从多个数据库中操作数据,这样一来,我们就要考虑需要支持JPA和Mybatis两种框架同时可以操作多个数据源。于是,就有了这篇。

Pom.xml及配置文件

首先我们需要引入相关的依赖,这里我们在Spring Boot 2.*下,直接引用。

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>1.1.10</version>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>1.3.0</version>
</dependency>

多数据源情况下,我们要设置主数据源和从数据源。

spring:
  datasource:
    primary:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.71.88.23/story-db1?useSSL=false&allowMultiQueries=true&characterEncoding=utf8
      username: root
      password: 123456
    secondary:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.71.88.23/story-db2?useSSL=false&allowMultiQueries=true&characterEncoding=utf8
      username: root
      password: 123456
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        dialect: org.hibernate.dialect.MySQL5Dialect
        enable_lazy_load_no_trans: true

Druid配置

Druid是常用的数据库连接池之一,可以根据不同的需求做响应的配置,详细的配置可以参考相关的文档。

@ServletComponentScan
@Configuration
public class DruidDBConfig {
    private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
	 
    @Value("${spring.datasource.primary.url}")
    private String dbUrl1;
 
    @Value("${spring.datasource.primary.username}")
    private String username1;
 
    @Value("${spring.datasource.primary.password}")
    private String password1;
 
    @Value("${spring.datasource.secondary.username}")
    private String username2;
 
    @Value("${spring.datasource.secondary.password}")
    private String password2;
 
    @Value("${spring.datasource.secondary.url}")
    private String dbUrl2;
 
    @Value("com.mysql.jdbc.Driver")
    private String driverClassName;
 
    @Value("5")
    private int initialSize;
 
    @Value("5")
    private int minIdle;
 
    @Value("20")
    private int maxActive;
 
    @Value("60000")
    private int maxWait;
 
    /**
     * 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
     */
    @Value("60000")
    private int timeBetweenEvictionRunsMillis;
    /**
     * 配置一个连接在池中最小生存的时间,单位是毫秒
     */
    @Value("300000")
    private int minEvictableIdleTimeMillis;
 
    @Value("SELECT 1 FROM DUAL")
    private String validationQuery;
 
    @Value("true")
    private boolean testWhileIdle;
 
    @Value("false")
    private boolean testOnBorrow;
 
    @Value("false")
    private boolean testOnReturn;
 
    /**
     * 打开PSCache,并且指定每个连接上PSCache的大小
     */
    @Value("true")
    private boolean poolPreparedStatements;
 
    @Value("20")
    private int maxPoolPreparedStatementPerConnectionSize;
    /**
     * 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
     */
    @Value("stat,wall,log4j")
    private String filters;
    /**
     * 通过connectProperties属性来打开mergeSql功能;慢SQL记录
     */
    @Value("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500")
    private String connectionProperties;
 
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @Primary
    public DataSource dataSource() {
        return getDruidDataSource(username1, password1, dbUrl1);
    }
 
    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    public DataSource secondaryDataSource() {
        return getDruidDataSource(username2, password2, dbUrl2);
    }
 
    private DruidDataSource getDruidDataSource(String username, String password, String url) {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter : {0}", e);
        }
        datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }
}

JPA 多数据源配置

这里配置第一个数据源。由于我们配置了两个数据源,primary和secondary库,因此,我们持久化的类也需要加以区分,放在不同的包里。
primaryDataSourcesecondaryDataSource 分别使用上面Druid配置中注册的Bean,同时我们也在代码中,配置了我们实体对应的包名com.story.storyweb.domain.primary.po

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.story.storyweb.repository.dao.primary"})
public class DataSourcePrimayConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;
 
    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }
 
    @Autowired
    private JpaProperties jpaProperties;
 
    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
 
    /**
     * 设置实体类所在位置
     */
    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.story.storyweb.domain.primary.po")
                .persistenceUnit("primaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
    }
 
    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

第二个数据源的配置。

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.story.storyweb.repository.dao.secondary"})
public class DataSourceSecondaryConfig {
    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;
 
    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }
 
    @Autowired
    private JpaProperties jpaProperties;
 
    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
 
    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .packages("com.story.storyweb.domain.secondary.po")
                .persistenceUnit("secondaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
    }
 
    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

Mybatis多数据源配置

Mybatis的配置,更多的是要注意mapper类的名命空间,以及Mapper.xml的路径。同时,我们把原来在Application启动类中的@MapperScan去掉,分别放在了相应的配置类中,更加清晰。

@Configuration
@MapperScan(basePackages = "com.story.storyweb.repository.mapper.primary", sqlSessionTemplateRef  = "primarySqlSessionTemplate")
public class MapperPrimaryConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/primary/**/*.xml"));
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager primaryTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "primarySqlSessionTemplate")
    public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

第二个数据源的配置。

@Configuration
@MapperScan(basePackages = "com.story.storyweb.repository.mapper.secondary", sqlSessionTemplateRef  = "secondarySqlSessionTemplate")
public class MapperSecondaryConfig {	
    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);        
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/secondary/*.xml"));
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    @Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager secondaryTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "secondarySqlSessionTemplate")
    public SqlSessionTemplate secondarySqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

到这里其实就已经配置好了,除了我们的实体类,数据库操作的DAO类,Mapper的xml文件,都分别分成了两个包,其他的都与我们在单数据源下开发一样了。这样在我们的业务中,使用不同的DAO类就会对应到相应的数据源上,实现多数据源的操作。

发表评论

电子邮件地址不会被公开。 必填项已用*标注