springboot+jpa+mybatis 多数据源支持

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * com.ehaoyao.paycenter.job.common.config
 * 数据源配置类
 * @author PF
 * @create 2018-05-10 16:17
 **/
@Configuration
public class DataSourceConfig {
    @Bean(name = "payCenterDataSource")
    @Qualifier("payCenterDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource.paycenter")
    public DataSource paycenterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "erpDataSource")
    @Qualifier("erpDataSource")
    @ConfigurationProperties(prefix="spring.datasource.erp")
    public DataSource erpDataSource() {
        return DataSourceBuilder.create().build();
    }
}

1、配置dataSource，代码如上所述。

 

package com.ehaoyao.paycenter.job.common.config;/**
 * 支付中心数据源配置类
 *
 * @author PF
 * Created by dell on 2018-05-04.
 */

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

/**
 * com.ehaoyao.paycenter.job.common.config
 * 支付中心数据源配置类
 *
 * @author PF
 * @create 2018-05-04 10:26
 **/
@Configuration
@MapperScan(basePackages = "com.ehaoyao.paycenter.persistence.pay.mapper.paycenter", sqlSessionFactoryRef = "payCenterSqlSessionFactory")
@EnableTransactionManagement
public class PayCenterDataSourceConfig {

    static final String MAPPER_LOCATION = "classpath:mappings/com/ehaoyao/paycenter/persistence/pay/mapper/paycenter/*.xml";

    @Autowired
    @Qualifier("payCenterDataSource")
    private DataSource payCenterDataSource;


    @Bean(name = "payCenterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(payCenterDataSource);
    }

    @Bean(name = "payCenterSqlSessionFactory")
    @Primary
    public SqlSessionFactory payCenterSqlSessionFactory(@Qualifier("payCenterDataSource") DataSource payCenterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(payCenterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(PayCenterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

 

2、master数据源的sessionFactory、transactionManager等配置，代码如上。

package com.ehaoyao.paycenter.job.common.config;/**
 * ERP数据源配置类
 *
 * @author PF
 * Created by dell on 2018-05-04.
 */

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

/**
 * com.ehaoyao.paycenter.job.common.config
 * ERP数据源配置类
 * @author PF
 * @create 2018-05-04 10:27
 **/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="erpEntityManagerFactory",
        transactionManagerRef="erpTransactionManager",
        basePackages= { "com.ehaoyao.paycenter.persistence.pay.Repository" })
public class ErpDataSourceConfig {

    @Autowired
    @Qualifier("erpDataSource")
    private DataSource erpDataSource;

    @Bean(name = "entityManager")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return erpEntityManagerFactory(builder).getObject().createEntityManager();
    }

    @Bean(name = "erpEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean erpEntityManagerFactory (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(erpDataSource)
                .properties(getVendorProperties(erpDataSource))
                .packages("com.ehaoyao.paycenter.persistence.pay.entity.erp")
                .persistenceUnit("erpPersistenceUnit")
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;

    private Map getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Bean(name = "erpTransactionManager")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(erpEntityManagerFactory(builder).getObject());
    }


}

 

3、配置slave数据源的sessionFactory、transactionManager等配置，代码如上。

 

自己留着备查用的，master数据源用的mybatis，slave用的jpa（虽然用法很搞，但是主要为了记录多数据源，及springboot下jpa配置相关）

 

碰到的问题，记录自查。

1、指定实体类包的位置。

 

    @Primary
    @Bean(name = "payCenterEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean payCenterEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(payCenterDataSource)
                .properties(getVendorProperties(payCenterDataSource))
                .packages("com.ehaoyao.paycenter.persistence.pay.entity","com.ehaoyao.pay.common.model")
                .persistenceUnit("payCenterPersistenceUnit")
                .build();
    }

 

 

指定多个的话语法为packages("第一个","第二个") 。其中源码如下： String... 为可变长参数，之前看到过。。。搞了半天，哎，还是基础啊。

 

public EntityManagerFactoryBuilder.Builder packages(String... packagesToScan) {
    this.packagesToScan = packagesToScan;
    return this;
}

 

 

 JPA常用注解，及注意事项

1、使用jpa的save新增数据后，有些数据库设置了默认值的字段没有生效，要使其生效可以在对应entity上增加 @DynamicInsert(true)
2、不想持久化的字段，比如有些字段只是实体类中临时存储，或仅为前端展示用，不需要在自动生成的insert、select语句中包含改字段，可以在实体类的改字段上添加 @Transient注解

如下图：

 

3、springboot jpa自带的分页起始页为0 ，但是一般前端显示的时候，都是从1开始的。主动-1，解决方法很low，不知道有没有好的办法。

Pageable pageable = new PageRequest(param.getPageIndex()==0?0:param.getPageIndex()-1, param.getPageSize(), sort);   //jpa自带的分页是从第0页开始的，因此这里将传过来的页码-1

 

4、springboot jpa查询缓存，配置关闭。（待解决）

 5、使用jpa在生产环境需要注意的配置

spring.jpa.hibernate.ddl-auto = update 不要在生成环境下配置，会死人的。

 

（配置解析如下）

ddl-auto:create----每次运行该程序，没有表格会新建表格，表内有数据会清空

ddl-auto:create-drop----每次程序结束的时候会清空表

ddl-auto:update----每次运行程序，没有表格会新建表格，表内有数据不会清空，只会更新

ddl-auto:validate----运行程序会校验数据与数据库的字段类型是否相同，不同会报错

 

 jpa支持时间范围，及动态条件分页查询。
之前的实例查询虽然可以支持动态条件，而且使用方便，但是对于一个字段需要传入多个参数的就不行了，比如 查询某个时间范围内的数据。create between beginDate and endDate 。像这种需求，在真实案例中是很常见的。但是在网上找了一圈之后，大多数是建议用@Query或者specification来实现。但要是这样的话，我还不如用mybatis呢。。。。。（而且基本都是这篇文章的转载，严重吐槽，百度前几页全是这个。。。。。原谅我已经不知道原作者是谁了，随便贴了一个链接，反正都一样。。）

之前实例查询代码如下：

    @Override
    public Page<RefundEntity> getDataList(RefundManEntity param) {

        ExampleMatcher exampleMatcher = ExampleMatcher.matching().withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING).
                withMatcher("createTime", ExampleMatcher.GenericPropertyMatchers.startsWith())
                .withMatcher("orderNume", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("refundStatus", ExampleMatcher.GenericPropertyMatchers.contains());
        RefundEntity refundEntity=new RefundEntity();
        BeanUtils.copyProperties(param,refundEntity);
        Example<RefundEntity> example = Example.of(refundEntity, exampleMatcher);
        Sort sort = new Sort(Sort.Direction.DESC, "id");
        Pageable pageable = new PageRequest(param.getPageIndex()==0?0:param.getPageIndex()-1, param.getPageSize(), sort);   //jpa自带的分页是从第0页开始的，因此这里将传过来的页码-1
        Page<RefundEntity> pages = refundManageRepository.findAll(example, pageable);
        return pages;
    }

 

我自己的low解决方案，代码如下：

 @Override
    public Page<RefundEntity> getDataList(RefundManEntity param) {
        Specification<RefundEntity> querySpecifi = new Specification<RefundEntity>() {
            @Override
            public Predicate toPredicate(Root<RefundEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                List<Predicate> predicates = new ArrayList<>();
                if (StringUtils.isNotBlank(param.getBeginDate())) {
                    //大于或等于传入时间
                    predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime").as(String.class), param.getBeginDate()));
                }
                if (StringUtils.isNotBlank(param.getEndDate())) {
                    //小于或等于传入时间
                    predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createTime").as(String.class), param.getEndDate()));
                }
                if (param != null) {
                    Class<? extends RefundManEntity> clazz = param.getClass();
                    Field[] fields = clazz.getDeclaredFields();
                    for (Field tmpField : fields) {
                        tmpField.setAccessible(true);
                        try {
                            if (tmpField.get(param) != null &&    !tmpField.getName().equals("beginDate") && !tmpField.getName().equals("endDate"))       //不为空的查询参数才拼查询条件,并且要去掉额外加上的时间范围条件
                            {
                                if (tmpField.getType().equals(String.class) &&  StringUtil.isNotBlank((String)tmpField.get(param)))     //只拼字符串查询条件的，因为目前只需要按照 订单号、退款状态来查询
                                {
                                    String name = tmpField.getName();
                                    predicates.add(criteriaBuilder.equal(root.get(name), tmpField.get(param)));
                                }
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
                // and到一起的话所有条件就是且关系，or就是或关系
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        Sort sort = new Sort(Sort.Direction.DESC, "id");
        Pageable pageable = new PageRequest(param.getPageIndex() == 0 ? 0 : param.getPageIndex() - 1, param.getPageSize(), sort);   //jpa自带的分页是从第0页开始的，因此这里将传过来的页码-1
        return refundManageRepository.findAll(querySpecifi, pageable);
    }

 repository需要多加个继承

public interface RefundManageRepository extends JpaRepository<RefundEntity,Integer> ,JpaSpecificationExecutor<RefundEntity>{

//    Page<RefundEntity> findRefundEntities(Specification<RefundEntity> param, Pageable pageable);
}