多数据源配置后,由于entityManage 与DataSource是绑定的,@EnableJpaRepositories注解绑定repository目录与entityManage,所以使用Jparepository时会自动使用对应的数据源。暂时交叉库查询没有,可以使用原生本地sql查询,下面有测试用例
注意,主数据源配置@Primary,不然启动报错
//启动类上面
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
//application-dev.yml
datasource1:
url: jdbc:mysql://192.168.1.196:3306/voice?useOldAliasMetadataBehavior=true&useSSL=false
username: root
password: 123456
driver-class: com.mysql.jdbc.Driver
datasource2:
url: jdbc:mysql://192.168.1.196:3306/bat?useOldAliasMetadataBehavior=true&useSSL=false
username: root
password: 123456
driver-class: com.mysql.jdbc.Driver
logging.level.org.hibernate.type.descriptor.sql.BasicBinder: TRACE # show bind values
hibernate.show_sql: true
hibernate.ddl-auto: validate # warn if column is not same with entity type
hibernate.format_sql: true
hibernate.dialect: org.hibernate.dialect.MySQL5InnoDBDialect
package com.offerPrice.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.text.RandomStringGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import static org.apache.commons.text.CharacterPredicates.LETTERS;
/**
* 数据库配置
* @author zhanghui
* @date 2019/5/7
*/
@Configuration
public class DbConfig {
@Autowired
private Environment env;
/**
* EnableJpaRepositories 注解关联repository与entityManage,entityManage决定数据源
*/
@EnableJpaRepositories(
basePackages = "com.offerPrice.repository",
entityManagerFactoryRef = "entityManage1",
transactionManagerRef = "transactionManager1"
)
@Configuration
class db1{
@Bean("datasource1")
@Primary
public DataSource getDataSource1() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(env.getProperty("datasource1.url"));
dataSource.setUsername(env.getProperty("datasource1.username"));
dataSource.setPassword(env.getProperty("datasource1.password"));
dataSource.setDriverClassName(env.getProperty("datasource1.driverClass"));
return dataSource;
}
/**
* entityManage 在用jpa原生sql 查询时,(非JpaRepository),注意注入的EntityManage的qualifier值
* @param dataSource
* @return
*/
@Bean("entityManage1")
@Primary
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean1(@Qualifier("datasource1")DataSource dataSource) {
return getFactory(dataSource,"com.offerPrice.entity");
}
@Bean("transactionManager1")
@Primary
public PlatformTransactionManager transactionManager1(@Qualifier("entityManage1")LocalContainerEntityManagerFactoryBean entityManageFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(entityManageFactory.getObject());
return transactionManager;
}
}
@EnableJpaRepositories(
basePackages = "com.offerPrice.repository2",
entityManagerFactoryRef = "entityManage2",
transactionManagerRef = "transactionManager2"
)
@Configuration
class db2{
@Bean("datasource2")
public DataSource getDataSource2() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(env.getProperty("datasource2.url"));
dataSource.setUsername(env.getProperty("datasource2.username"));
dataSource.setPassword(env.getProperty("datasource2.password"));
dataSource.setDriverClassName(env.getProperty("datasource2.driverClass"));
return dataSource;
}
@Bean("entityManage2")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean2(@Qualifier("datasource2")DataSource dataSource) {
return getFactory(dataSource,"com.offerPrice.entity2");
}
@Bean("transactionManager2")
public PlatformTransactionManager transactionManager2(@Qualifier("entityManage2")LocalContainerEntityManagerFactoryBean entityManageFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(entityManageFactory.getObject());
return transactionManager;
}
}
private LocalContainerEntityManagerFactoryBean getFactory(DataSource dataSource,String baseEntityPackage){
LocalContainerEntityManagerFactoryBean em
= new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan(baseEntityPackage);
em.setPersistenceUnitName(new RandomStringGenerator.Builder()
.withinRange('a', 'z')
.filteredBy(LETTERS)
.build().generate(5));
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
properties.put("hibernate.ddl-auto", env.getProperty("hibernate.ddl-auto"));
properties.put("hibernate.format_sql", env.getProperty("hibernate.format_sql"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
em.setJpaPropertyMap(properties);
return em;
}
}
//测试用例
package com.offerPrice.common;
import com.offerPrice.entity.WxCarInfoEntity;
import com.offerPrice.entity2.TCarSourceEntity;
import com.offerPrice.model.UserRole;
import com.offerPrice.repository.WxCarInfoRepo;
import com.offerPrice.repository2.CarSourceRepo;
import com.offerPrice.utils.DbUtil;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.persistence.EntityManager;
import java.util.List;
/**
* 常用功能测试类
*
* @author zhanghui
* @date 2019/6/5
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class DbTest {
@Autowired
private CarSourceRepo carSourceRepo;
@Autowired
private WxCarInfoRepo carInfoRepo;
@Test
public void db1(){
WxCarInfoEntity entity = carInfoRepo.findById(14);
System.out.println(entity);
}
@Test
public void db2(){
List<TCarSourceEntity> tCarSourceEntities = carSourceRepo.findByInsurance("123456");
System.out.println(tCarSourceEntities);
}
@Test
public void rawSql(){
String sql="select uu.ID, uu.`USER`,ur.`NAME` from t_uac_users uu LEFT JOIN t_uac_user_role uur on uu.ID=uur.USERID LEFT JOIN t_uac_role ur on uur.ROLEID=ur.id ";
// list result
List<UserRole> userRoles = DbUtil.sqlObj(sql,"entityManage1", UserRole.mapping);
System.out.println(userRoles.size());
//one result, if no result will NoResultException
sql+="LIMIT 1";
UserRole userRole = DbUtil.sqlObjOne(sql,"entityManage1",UserRole.mapping);
System.out.println(userRole.getUser());
}
}
//UserRole
package com.offerPrice.model;
import lombok.Data;
import lombok.experimental.Accessors;
import java.util.function.Function;
/**
* @author zhanghui
* @date 2019/5/29
*/
@Data
@Accessors(chain = true)
public class UserRole {
private int id;
private String user;
private String name;
//lambda函数式,转换jpa原生sql查询结果,一行结果是一个Object[],转为一个对象
public static Function<Object[], UserRole> mapping = e->new UserRole().setId((Integer) e[0]).setUser((String) e[1]).setName((String) e[2]);
}
//DbUtil
package com.offerPrice.utils;
import org.springframework.beans.factory.annotation.BeanFactoryAnnotationUtils;
import javax.persistence.EntityManager;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;
/**
* jpa原生sql查询工具
*
* @author zhanghui
* @date 2019/5/29
*/
public class DbUtil {
/**
* jpa原生sql查询后转换为对象
* @param sql 查询语句
* @param func 转换一条结果为一个对象,定义在每个接收的model内
* @param <T> 接收的类型
* @return 返回list 或 null
*/
public static <T> T sqlObj(String sql, String entityManagerQualifier, Function func){
List res = getEntityManager(entityManagerQualifier).createNativeQuery(sql).getResultList();
if (res == null || res.isEmpty())
return null;
List<T> resList = new ArrayList<>();
((List<Object[]>) res).forEach(e->resList.add((T)func.apply(e)));
return (T) resList;
}
private static EntityManager getEntityManager(String entityManagerQualifier){
if(Objects.isNull(entityManagerQualifier)){
return SpringUtil.getBean(EntityManager.class);
}
/**
* 根据bean的类型与Qualifier值取bean,直接SpringUtil.getbean(QualifierName)取不到
*/
return BeanFactoryAnnotationUtils.qualifiedBeanOfType(
SpringUtil.getApplicationContext().getAutowireCapableBeanFactory(),
EntityManager.class,
entityManagerQualifier);
}
/**
* 方便对sql只有一条结果,直接返回对象,不然需要sqlObj获取list,然后list.get(0)
* @param sql
* @param func
* @param <T>
* @return
*/
public static <T> T sqlObjOne(String sql, String entityManagerQualifier, Function func){
List res = sqlObj(sql,entityManagerQualifier, func);
if (res == null)
return null;
if (res.size()>1)
throw new RuntimeException("DbUtil.sqlObjOne expect only one result, find "+res.size());
return (T)res.get(0);
}
/**
* sql查询结果只有一列的,一个总数,一个字符等等
* @param sql
* @param T
* @param <T>
* @return
*/
public static <T> T sqlCast(String sql, String entityManagerQualifier, Class<T> T){
Object o = getEntityManager(entityManagerQualifier).createNativeQuery(sql).getSingleResult();
//对一些类型做特殊处理
//count(*)返回BigInteger类型
if (o instanceof BigInteger){
Long res = ((BigInteger) o).longValue();
return (T)res;
}
return (T)o;
}
}
//SpringUtil
package com.offerPrice.utils;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
/**
* spring工具类,静态工具方法获取bean
*
* @author zhanghui
* @date 2019/5/29
*/
@Component
public class SpringUtil implements ApplicationContextAware {
private static ApplicationContext applicationContext;
// 获取applicationContext
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
// 通过name获取 Bean.
public static Object getBean(String name) {
return getApplicationContext().getBean(name);
}
// 通过class获取Bean.
public static <T> T getBean(Class<T> clazz) {
return getApplicationContext().getBean(clazz);
}
// 通过name,以及Clazz返回指定的Bean
public static <T> T getBean(String name, Class<T> clazz) {
return getApplicationContext().getBean(name, clazz);
}
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if (SpringUtil.applicationContext == null) {
SpringUtil.applicationContext = applicationContext;
}
}
}