SpringBoot项目中JdbcTemplate、Mybatis和JPA配置多数据源

全局配置项目依赖

        
            org.springframework.boot
            spring-boot-starter-jdbc
        
        
            mysql
            mysql-connector-java
            runtime
        
        
        
            com.alibaba
            druid-spring-boot-starter
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
        
        
            org.springframework.boot
            spring-boot-starter-data-jpa

多数据源

自定义yml配置

spring:
  datasource:
    one:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql:///springboot_vue?serverTimezone=UTC
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    two:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql:///springboot_vue2?serverTimezone=UTC
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

代码配置

项目注入多数据源

@Configuration
public class DaoConfiguration {

    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource.one")
    DataSource dsOne() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.two")
    DataSource dsTwo() {
        return DruidDataSourceBuilder.create().build();
    }
}

表结构

如果项目中有JPA ORM框架，设置了update或create，无需手动建表。

create table book
(
    id                 int auto_increment
        primary key,
    create_by          varchar(255) null,
    create_time        datetime     null,
    last_modified_by   varchar(255) null,
    last_modified_time datetime     null,
    author             varchar(255) null,
    name               varchar(255) null,
    price              float        null
);

Domain

@Getter
@Setter
@ToString
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Book extends AuditModel {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer id;
    private String name;
    private String author;
    private Float price;
    @Transient
    private String description;
}

JDBC多数据源配置

一个数据源映射到一个JdbcTemplate。

@Configuration
public class JdbcConfig {

    @Primary
    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

使用测试

JdbcTemplate1

@Repository
public class BookJdbcDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public int addBook(Book book) {
        return jdbcTemplate.update("INSERT INTO book(id, name, author) VALUES (?, ?, ?)", book.getId(), book.getName(), book.getAuthor());
    }
}

JdbcTemplate2

@Repository
public class BookJdbcDao2 {

    @Resource(name = "jdbcTemplateTwo")
    private JdbcTemplate jdbcTemplate;

    public int addBook(Book book) {
        return jdbcTemplate.update("INSERT INTO book(id, name, author) VALUES (?, ?, ?)", book.getId(), book.getName(), book.getAuthor());
    }
}

Mybatis多数据源配置

Mapper接口

@Mapper
public interface BookMapper1 {
    Integer addBook(Book book);
}

Mapper.xml接口实现




    
        
            select if(max(id) is null, 1, max(id) + 1) as newId from book
        
        INSERT INTO book(id, name, author) VALUES (#{id}, #{name}, #{author})

类似JdbcTemplate，取而代之的是SqlSessionTemplate。

@MapperScan(value = {"org.lmmarise.vue.persistent.dao.mybatis.mapper"},  // xml与dao接口所在位置
        sqlSessionFactoryRef = "sqlSessionFactoryBean")		// 指定 SqlSessionFactory 的 beanName
@MapperScan(value = {"org.lmmarise.vue.persistent.dao.mybatis.mapper1"},	// mapper 不能共用，接口相同但复制一份分开定义
        sqlSessionFactoryRef = "sqlSessionFactoryBean1")
@Configuration
public class MybatisConfig {

    @Resource
    DataSource dsOne;

    @Resource
    DataSource dsTwo;

    @Bean
    SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsOne);
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionFactory sqlSessionFactoryBean1() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsTwo);
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBean());
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBean1());
    }
}

使用测试

@Service
public class BookMybatisService {

    @Resource
    private BookMapper bookDao;		// 源1

    @Resource
    private BookMapper1 bookDao1;	// 源2

    public int addBook(Book book) {
        return bookDao.addBook(book);
    }

    public int addBook1(Book book) {
        return bookDao1.addBook(book);
    }
}

JPA多数据源配置

文件配置，新增

spring:
  jpa:	# 注意，这里只配置了JPA，Hibernate配置看下面代码（代码中配置）
    show-sql: true
    database: mysql
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL55Dialect

代码配置，与Mybatis相似，domain部分可以共用，动态代理实现的repository需要分开。Hibernate的persistenceUnit对应Mybatis的SqlSessionFactory。

@Configuration
public class JpaConfig {

    @Resource
    JpaProperties jpaProperties;

    @Resource
    private HibernateProperties properties;

    @EnableJpaRepositories(basePackages = "org.lmmarise.vue.persistent.dao.jpa.repository",   // 指定 Repository 所在的包【多个 persistenceUnit 不可共用】
            entityManagerFactoryRef = "entityManagerFactoryBeanOne",
            transactionManagerRef = "platformTransactionManagerOne")
    @EnableTransactionManagement
    @Configuration
    class PersistenceUnit {

        @Resource(name = "dsOne")
        DataSource dsOne;

        @Primary
        @Bean
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder) {
            return builder
                    .dataSource(dsOne)
                    .properties(
                            // 将 JPA 和 Hibernate 的配置组合并合并【避免项目 yml 中 JPA 配置不对 Hibernate 生效】
                            properties.determineHibernateProperties(
                                    jpaProperties.getProperties(), new HibernateSettings()
                            )
                    )
                    .packages("org.lmmarise.vue.persistent.org")    // 指定 @Entity 所在的包名
                    .persistenceUnit("pu")
                    .build();
        }

        @Primary
        @Bean
        PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder) {
            LocalContainerEntityManagerFactoryBean factoryOne = entityManagerFactoryBeanOne(builder);
            return new JpaTransactionManager(Objects.requireNonNull(factoryOne.getObject()));
        }
    }

    @EnableJpaRepositories(basePackages = "org.lmmarise.vue.persistent.dao.jpa.repository1",
            entityManagerFactoryRef = "entityManagerFactoryBeanTwo",
            transactionManagerRef = "platformTransactionManagerTwo")
    @EnableTransactionManagement
    @Configuration
    class PersistenceUnit1 {

        @Resource
        DataSource dsTwo;

        @Bean
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanTwo(EntityManagerFactoryBuilder builder) {
            return builder
                    .dataSource(dsTwo)
                    .properties(
                            properties.determineHibernateProperties(
                                    jpaProperties.getProperties(),
                                    new HibernateSettings()
                            )
                    )
                    .packages("org.lmmarise.vue.persistent.org")
                    .persistenceUnit("pu1")
                    .build();
        }

        @Bean
        PlatformTransactionManager platformTransactionManagerTwo(EntityManagerFactoryBuilder builder) {
            LocalContainerEntityManagerFactoryBean factoryOne = entityManagerFactoryBeanTwo(builder);
            return new JpaTransactionManager(Objects.requireNonNull(factoryOne.getObject()));
        }
    }
}

使用测试

@Service
public class BookJpaService {

    @Resource
    private BookJpaRepository bookJpaRepository;

    @Resource
    private BookJpaRepository1 bookJpaRepository1;

    public Book addBook(Book book) {
        return bookJpaRepository.save(book);
    }

    public Page getBookByPage1(PageRequest pageable) {
        return bookJpaRepository1.findAll(pageable);
    }
}

配置都很简单，踩坑可能会很久。

示例代码

https://github.com/lmmarisej/SpringBootVue/releases/tag/1.0-SNAPSHOT

SpringBoot项目中JdbcTemplate、Mybatis和JPA配置多数据源

Java相关栏目本月热门文章