配置类:
application.properties:
spring:
datasource:
default: //默认数据源
username: ${MYSQL_USER}
password: ${MYSQL_PASSWORD}
jdbc-url: jdbc:mysql://${MYSQL_ADDRESS}/${MYSQL_DB}?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
filters: slf4j
mlbmysql: //mysql
username: ${MYSQL_USER2}
password: ${MYSQL_PASSWORD2}
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://${MYSQL_ADDRESS2}/${MYSQL_DB2}?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true
mlboracle: //oracle
jdbc-url: jdbc:oracle:thin:@${ORACLE_ADDRESS}/MLBDB
username: ${ORACLE_USER}
password: ${ORACLE_PASSWORD}
driver-class-name: oracle.jdbc.OracleDriver
type: com.alibaba.druid.pool.DruidDataSource
data:
mongodb1:
uri: mongodb://${MONGODB_USER}:${MONGODB_PASSWORD}@${MONGODB_ADDRESS}/MLB?authSource=admin
mongodb2:
uri: mongodb://${MONGODB_USER2}:${MONGODB_PASSWORD2}@${MONGODB_ADDRESS2}/MLB?authSource=admin
jpa:
properties:
hibernate:
format_sql: true
database: mysql
hibernate:
ddl-auto: none
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# show-sql: true
配置数据源Config:
目录结构:
DatabaseConfig:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
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.HashMap;
import java.util.Map;
/**
* @author aric
* @create 2021-07-15-15:55
* @fun
*/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactoryPrimary",
transactionManagerRef = "defaultTransactionManager",
//设置Repository所在位置-设置扫包范围
basePackages = {"com.fii.eodc.intellect.decistionbrain.repository"})
public class DatabaseConfig {
public static final String DEFAULT_T_M = "defaultTransactionManager";
public static final String MLB_MYSQL_T_M = "mlbmysqlTransactionManager";
public static final String MLB_ORACLE_T_M = "mlboracleTransactionManager";
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
@Bean(name = "default")
@ConfigurationProperties(prefix = "spring.datasource.default")
public DataSource defaultDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "mlbmysql")
@ConfigurationProperties(prefix = "spring.datasource.mlbmysql")
public DataSource mlbmysqlDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "mlboracle")
@ConfigurationProperties(prefix = "spring.datasource.mlboracle")
public DataSource mlboracleDataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource(@Qualifier("default") DataSource defaultDataSource,
@Qualifier("mlbmysql") DataSource mlbmysql,
@Qualifier("mlboracle") DataSource mlboracle){
DynamicDatabase dynamicDataSource = new DynamicDatabase();
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);
// 配置多数据源
Map<Object, Object> dsMap = new HashMap<>();
dsMap.put("default", defaultDataSource);
dsMap.put("mlbmysql", mlbmysql);
dsMap.put("mlboracle", mlboracle);
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}
@Primary
@Bean(name = "entityManagerPrimary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder,@Qualifier("default") DataSource defaultDataSource) {
return entityManagerFactoryPrimary(builder,defaultDataSource).getObject().createEntityManager();
}
@Primary
@Bean(name = "entityManagerFactoryPrimary")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder,@Qualifier("default") DataSource defaultDataSource) {
return builder
// 设置数据源
.dataSource(defaultDataSource)
// 设置jpa配置
.properties(jpaProperties.getProperties())
// 设置hibernate配置
.properties(getVendorProperties())
//设置实体类所在位置
.packages("com.fii.eodc.intellect.decistionbrain.model")
// 设置持久化单元名,用于@PersistenceContext注解获取EntityManager时指定数据源
.persistenceUnit("primaryPersistenceUnit")
.build();
}
private Map getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
@Primary
@Bean(name = DEFAULT_T_M)
public PlatformTransactionManager defaultTransactionManager(EntityManagerFactoryBuilder builder,@Qualifier("default") DataSource defaultDataSource) {
return new JpaTransactionManager(entityManagerFactoryPrimary(builder,defaultDataSource).getObject());
}
// 每个事务管理器需要在不同类下按上述操作重写设置,不然只能查找,不能增删改
// @Bean(name = MLB_MYSQL_T_M)
// public PlatformTransactionManager mlbmysqlTransactionManager(@Qualifier("mlbmysql") DataSource mlbmysql) {
// return new DataSourceTransactionManager(mlbmysql);
// }
//
// @Bean(name = MLB_ORACLE_T_M)
// public PlatformTransactionManager mlboracleTransactionManager(@Qualifier("mlboracle") DataSource mlboracle) {
// return new DataSourceTransactionManager(mlboracle);
// }
//mongoDB连接配置
@Value("${spring.data.mongodb1.uri}")
private String mongodb1Url;
@Value("${spring.data.mongodb2.uri}")
private String mongodb2Url;
@Primary
@Bean
public MongoTemplate mongoTemplate1(MongoDatabaseFactory mongoDatabaseFactory1){
return new MongoTemplate(mongoDatabaseFactory1);
}
@Bean
public MongoTemplate mongoTemplate2(@Qualifier("mongoDatabaseFactory2") MongoDatabaseFactory mongoDatabaseFactory2){
return new MongoTemplate(mongoDatabaseFactory2);
}
@Primary
@Bean
public MongoDatabaseFactory mongoDatabaseFactory1(){
return new SimpleMongoClientDatabaseFactory(new ConnectionString(mongodb1Url));
}
@Bean
public MongoDatabaseFactory mongoDatabaseFactory2(){
return new SimpleMongoClientDatabaseFactory(new ConnectionString(mongodb2Url));
}
}
附:配置另一个数据源的事务管理器,以MLB_MYSQL为例:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
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.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
/**
* @author aric
* @create 2021-07-19-14:58
* @fun
*/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactoryForMLB",
transactionManagerRef = "mlbmysqlTransactionManager",
//设置Repository所在位置-设置扫包范围
basePackages = {"com.fii.eodc.intellect.decistionbrain.mlbRepository"})
public class MlbDatabaseConfig {
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
public static final String MLB_MYSQL_T_M = "mlbmysqlTransactionManager";
/*****************B次Mysql数据源*******************/
@Bean(name = "entityManagerForMLB")
public EntityManager entityManagerForMLB(EntityManagerFactoryBuilder builder, @Qualifier("mlbmysql") DataSource mlbmysql) {
return entityManagerFactoryForMLB(builder,mlbmysql).getObject().createEntityManager();
}
@Bean(name = "entityManagerFactoryForMLB")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryForMLB(EntityManagerFactoryBuilder builder, @Qualifier("mlbmysql") DataSource mlbmysql) {
return builder
// 设置数据源
.dataSource(mlbmysql)
// 设置jpa配置
.properties(jpaProperties.getProperties())
// 设置hibernate配置
.properties(getVendorProperties())
//设置实体类所在位置
.packages("com.fii.eodc.intellect.decistionbrain.model")
// 设置持久化单元名,用于@PersistenceContext注解获取EntityManager时指定数据源
.persistenceUnit("mlbPersistenceUnit")
.build();
}
private Map getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
@Bean(name = MLB_MYSQL_T_M)
public PlatformTransactionManager mlbmysqlTransactionManager(@Qualifier("mlbmysql") DataSource mlbmysql) {
return new DataSourceTransactionManager(mlbmysql);
}
}
DatabaseHolder:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
/**
* @author aric
* @create 2021-07-15-16:07
* @fun
*/
public class DatabaseHolder {
// ThreadLocal每个线程都独有的保存其线程所属的变量值
private static ThreadLocal<String> holder = new ThreadLocal<>();
public static void setDatabaseSource(String ds) {
holder.set(ds);
}
public static String getDatabaseSource() {
return holder.get();
}
public static void clearDatabaseSource() {
holder.remove();
}
}
DatasourceAspect:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* @author aric
* @create 2021-07-15-17:12
* @fun
*/
@Order(1)
@Aspect
@Component
public class DatasourceAspect {
@Pointcut("@annotation(com.fii.eodc.intellect.decistionbrain.config.datasource.MLBDataSource)")
public void annotationPointCut() {
}
@Before(value = "annotationPointCut()&&@annotation(datasource)")
public void beforeSwitchDS(JoinPoint point, MLBDataSource datasource) {
DatabaseHolder.setDatabaseSource(datasource.value().getData());
}
@After(value = "annotationPointCut()")
public void afterSwitchDS(JoinPoint point) {
DatabaseHolder.clearDatabaseSource();
}
}
DynamicDatabase:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author aric
* @create 2021-07-15-16:06
* @fun
*/
public class DynamicDatabase extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DatabaseHolder.getDatabaseSource();
}
}
MLBDataCode:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
/**
* @author aric
* @create 2021-07-15-17:08
* @fun
*/
public enum MLBDataCode {
DEFAULT("default"),
MLBMYSQL("mlbmysql"),
MLBORACLE("mlboracle"),
;
private String data;
MLBDataCode(String data) {
this.data = data;
}
public String getData() {
return data;
}
public void setData(String data) {
this.data = data;
}
}
MLBDataSource:
package com.fii.eodc.intellect.decistionbrain.config.datasource;
import java.lang.annotation.*;
/**
* @author aric
* @create 2021-07-15-16:22
* @fun
* <p>多数据源标识</p>
* <p>使用方式:必须用在方法上</p>
*/
@Target(ElementType.METHOD)
@Documented
@Retention(RetentionPolicy.RUNTIME)
public @interface MLBDataSource {
MLBDataCode value() default MLBDataCode.DEFAULT;
}
编写测试用例:
DemoController:
package com.fii.eodc.intellect.decistionbrain.controller;
import com.fii.eodc.intellect.decistionbrain.common.response.Result;
import com.fii.eodc.intellect.decistionbrain.model.vo.DemoVO;
import com.fii.eodc.intellect.decistionbrain.service.DemoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @author aric
* @create 2021-06-09-14:09
* @fun
*/
@Api(tags = "demo")
@RestController
@RequestMapping("/demo")
public class DemoController {
@Autowired
private DemoService demoService;
@ApiOperation(value = "demo",httpMethod = "GET",notes = "demo")
@GetMapping("/getDemo")
public Result<List<DemoVO>> getDemo(){
return Result.success(demoService.getDemo());
}
}
DemoService:
package com.fii.eodc.intellect.decistionbrain.service;
import com.fii.eodc.intellect.decistionbrain.config.datasource.MLBDataCode;
import com.fii.eodc.intellect.decistionbrain.config.datasource.MLBDataSource;
import com.fii.eodc.intellect.decistionbrain.model.dto.DemoDTO;
import com.fii.eodc.intellect.decistionbrain.model.vo.DemoVO;
import com.fii.eodc.intellect.decistionbrain.repository.DemoRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @author aric
* @create 2021-06-09-14:16
* @fun
*/
@Service
public class DemoService {
@Autowired
private DemoRepo demoRepo;
@MLBDataSource(value = MLBDataCode.MLBMYSQL) //使用注解方式导入动态数据库
public List<DemoVO> getDemo() {
List<DemoDTO> demo = demoRepo.getDemo();
return demo.stream().map(DemoVO::new).collect(Collectors.toList());
}
}
DemoRepostity:
@Transactional(transactionManager = DatabaseConfig.DEFAULT_T_M, rollbackFor = { Exception.class }) //注入事务管理器,不然只能查找,不能增删改
public interface DemoRepo extends JpaRepository<DemoEntity,Long> {
@Query(nativeQuery = true,value = "select * from tb_xuyu")
List<DemoDTO> getDemo();
}
MongoDemo:
@Repository
public class OeeYieldMongo {
private final MongoTemplate mongoTemplate1;
private final MongoTemplate mongoTemplate2;
public OeeYieldMongo(
MongoTemplate mongoTemplate1,
@Qualifier("mongoTemplate2") MongoTemplate mongoTemplate2
) {
this.mongoTemplate1 = mongoTemplate1;
this.mongoTemplate2 = mongoTemplate2;
}
//查询1
public Object getSpiFail(String series) {
MongoTemplate mongoTemplate = mongoTemplate1;
if (databaseSelect.equals(series)) {
mongoTemplate = mongoTemplate2;
}
Object Resuslt = mongoTemplate.aggregate(param1, "param2", Object.class);
}
//条件查询2
long getmongo(String startTime, String endTime, String line) {
Criteria c = Criteria.where("Time").gte(startTime.replace("-", "")).lt(endTime.replace("-", ""))
.and("LineName").is(line)
.and("CheckResult").is("fail");
Query q = Query.query(c);
long count = mongoTemplate.count(q, "AutoSPIResult");
return count;
}
//sql语句查询
Object getmongo(String collection, String type, String startTime, String endTime) {
String sql[] = new String[4];
sql[0] = String.format("{" +
" \"$match\": {" +
" \"FileCreationTime\": {" +
" \"$gte\": '%s'," +
" \"$lt\": '%s'" +
" }," +
" \"Line\": {" +
" \"$in\": [\"C104F01BS\", \"C104F01TS\", \"C10402BS\", \"C10402TS\", \"test\"]" +
" }" +
" }" +
" }", startTime, endTime);
sql[1] = String.format("{" +
" \"$project\": {" +
" \"line\": \"$Line\"," +
" \"machine\": '%s'," +
" \"_id\": 0," +
" }" +
" }", "SPI".equals(type) ? "$Machine" : "$MachineId");
sql[2] = String.format("{" +
" \"$group\":{\"_id\":{\"line\":\"$line\",\"machine\":\"$machine\"}, \"count\":{\"$sum\":1}}" +
" }");
sql[3] = String.format("{" +
" \"$project\": {" +
" \"_id\": 0," +
" \"line\": \"$_id.line\"," +
" \"machine\": \"$_id.machine\"," +
" \"number\": \"$count\"," +
" \"type\": '%s'" +
" }" +
" }", type);
List<Bson> l = new ArrayList<Bson>();
l.add(BsonDocument.parse(sql[0]));
l.add(BsonDocument.parse(sql[1]));
l.add(BsonDocument.parse(sql[2]));
l.add(BsonDocument.parse(sql[3]));
AggregateIterable<Document> iter = mongoTemplate.getCollection(collection).aggregate(l);
}
}
启动类:
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class xuyuApplication {
public static void main(String[] args) {
System.out.println("Hello World!");
SpringApplication.run(xuyuApplication .class, args);
}
}