java实现多数据源切换_java切换数据源

---

前言

本文主要介绍在QA人员测试过程中使用到ava多数据源切换，主要会涉及到springboot的自定义注解，动态数据源切面（AOP）、

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据源配置

db.propertise设置数据库相关配置

#新系统数据库配置
new.mysql.host=192.168.xxx.xxx:3306
new.mysql.user=root
new.mysql.pass=xxxxx
1
2
3
4

读取propertise配置信息

import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.util.Properties;

public class DBUtil {
    public static final String CONFIG_FILE_PATH="conf/db.properties";

    //通过key直接获取对应的值
    public static String getValue(String key)  {
        Properties props=new Properties();
        try {
            ClassPathResource resource=new ClassPathResource(CONFIG_FILE_PATH);
            InputStream in=resource.getInputStream();
            BufferedReader bf=new BufferedReader(new InputStreamReader(in,"UTF-8"));
            props.load(bf);
        }catch (FileNotFoundException e){
            e.printStackTrace();
        }catch (IOException e){
            e.printStackTrace();

        }
        return props.getProperty(key);

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

多数据源配置DBConfig

import com.alibaba.druid.pool.DruidDataSource;
import com.test.qa.util.DBUtil;
import org.junit.Test;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DBConfig {
    private static final String newDbHost  = DBUtil.getValue("new.mysql.host");
    private static final String newDbname = DBUtil.getValue("new.mysql.user");
    private static final String newDbpass = DBUtil.getValue("new.mysql.pass");

    public DruidDataSource userDb() throws SQLException {
        return DruidDataSourceFactory.getSimpleDruidDataSource("jdbc:mysql://"+newDbHost+"/finance_user?useSSL=false", newDbname, newDbpass);
    }

    public DruidDataSource loanDb() throws SQLException {
        return DruidDataSourceFactory.getSimpleDruidDataSource("jdbc:mysql://"+newDbHost+"/finance_loan?useSSL=false", newDbname, newDbpass);
    }

    @Bean(name = "dynamicDataSource")
    public DataSource dataSource() throws Exception {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(aggDb());
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap<>();
        dsMap.put("aggDb", aggDb());
        dsMap.put("userDb", userDb());
        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }



}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

动态数据源DruidDataSourceFactory

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.SQLException;

/**
 * 动态数据源
 */
public class DruidDataSourceFactory {


    /**
     * 构建一个简单的数据源
     * @param args
     * @return
     */
    public static DruidDataSource getSimpleDruidDataSource(String ... args) throws SQLException{
        DruidDataSource dataSource = new DruidDataSource();
        if (args.length < 3){
            throw  new IllegalArgumentException("参数错误，请至少输入url，username，password三个配置!");
        }else if (args.length == 3){
            dataSource.setUrl(args[0]);
            dataSource.setUsername(args[1]);
            dataSource.setPassword(args[2]);
        }else {
            dataSource.setUrl(args[0]);
            dataSource.setUsername(args[1]);
            dataSource.setPassword(args[2]);
            dataSource.setMaxActive(Integer.parseInt(args[3]));

        }
        dataSource.setInitialSize(2);
        dataSource.setMinIdle(2);
        dataSource.setTimeBetweenEvictionRunsMillis(10000);
        dataSource.setMinEvictableIdleTimeMillis(60000);
        dataSource.setValidationQuery("SELECT 'x'");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setRemoveAbandoned(true);
        dataSource.setRemoveAbandonedTimeout(1800);
        dataSource.setLogAbandoned(true);
        dataSource.setFilters("stat");
        dataSource.setConnectionProperties("druid.stat.slowSqlMillis=100");
        return dataSource;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

动态数据源切换DruidDataSourceFactory

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态数据源切换
 */
public class DynamicDataSource extends AbstractRoutingDataSource {


    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

DataSourceContextHolder类

/**
 * 动态数据源
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    /**
     * @param dataSourceType 数据库类型
     * @Description: 设置数据源类型
     */
    public static void setDataSourceType(String dataSourceType) {
        contextHolder.set(dataSourceType);
    }

    /**
     * @Description: 获取数据源类型
     */
    public static String getDataSourceType() {
        return contextHolder.get();
    }

    /**
     * @Description: 清除数据源类型
     */
    public static void clearDataSourceType() {
        contextHolder.remove();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

动态数据源切面

/**
 * 动态数据源切面
 */
@Aspect
@Order(1)
public class DynamicDataSourceAspect {



    /**
     * 切面选择需要切换的数据库
     * @param joinPoint
     */
    @Around("@annotation(com.test.qa.anotations.UseDataBase)")
    public Object intercept(ProceedingJoinPoint joinPoint) throws Throwable{
        Object target = joinPoint.getTarget();
        Object[] args = joinPoint.getArgs();
        Method method = getMethod(joinPoint, args);
        //获取数据库名称参数
        UseDataBase chooseDataSource = method.getAnnotation(UseDataBase.class);
        if(chooseDataSource != null){
            String dataSourceName = chooseDataSource.value();
            DataSourceContextHolder.setDataSourceType(dataSourceName);
            //System.out.println("当前数据库为:"+dataSourceName);
        }
        try {
            return joinPoint.proceed();
        }finally {
            DataSourceContextHolder.clearDataSourceType();
        }
    }


    /**
     * 获取切面的方法
     * @param joinPoint
     * @param args
     * @return
     * @throws NoSuchMethodException
     */
    private Method getMethod(ProceedingJoinPoint joinPoint, Object[] args) throws NoSuchMethodException {
        String methodName = joinPoint.getSignature().getName();
        Class clazz = joinPoint.getTarget().getClass();
        Method[] methods = clazz.getMethods();
        for(Method method : methods) {
            if (methodName.equals(method.getName())) {
                return method;
            }
        }
        return null;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

二、自定义注解

通过自定义注解，方便数据源的切换

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * 用于数据源的切换
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface UseDataBase {

    /**
     * 使用的数据库名称
     * @return
     */
    String value();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

监听

/**
 * 开启整个测试框架的监听
 */
@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Configuration
@Import({DynamicDataSourceAspect.class})
public @interface EnableTestingListener {

    String value() default "";

}
1
2
3
4
5
6
7
8
9
10
11
12
13

三 、启动类设置

/**
 * springBoot启动器
 */
@SpringBootApplication
@EnableTestingListener
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}
1
2
3
4
5
6
7
8
9
10

四、Dao

@Repository
@Slf4j
public class dbDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
	//通过UseDataBase注解切换数据源
    @UseDataBase("loanDb")
    public  Map<String,Object> find(String id){
        String querySQL = "SELECT * FROM loan_bill WHERE id = '%s';";
        querySQL = String.format(querySQL,id);
        log.info("执行SQL==>{}",querySQL);
        try {
            return jdbcTemplate.queryForMap(querySQL);
        }catch (Exception e){
            log.error("执行SQL失败,信息为==>{}",e.toString());
            return null;
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

五、测试

测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class test1 {

    @Autowired
    private dbDao dbDao;
    @Test
    public void test(){
        System.out.println(dbDao.find2("27").get("user_id"));
    }
}
1
2
3
4
5
6
7
8
9
10
11

执行结果结果

这一块介绍完了，喜欢的可以点赞哦，谢谢