hikari如何切换数据源_SpringBoot + Hikari集成多数据源（impala，oracle，mysql）

1.用IDEA新建一个 springBoot 项目，此处就不说怎么新建项目了

项目整体架构图：

image.png

2.pom文件引入

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.3.0.RELEASE

com.longi

bigdata

0.0.1-SNAPSHOT

datamonitor

data monitor

1.8

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-test

test

org.projectlombok

lombok

1.18.12

org.springframework.boot

spring-boot-starter-jdbc

mysql

mysql-connector-java

runtime

com.alibaba

druid

1.1.8

org.springframework.boot

spring-boot-starter-jdbc

org.springframework.boot

spring-boot-configuration-processor

true

com.cloudera.longi

ImpalaJDBC41

2.6.15

com.oracle

ojdbc6

11.2.0.3

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.2.0

mysql

mysql-connector-java

5.1.39

commons-dbcp

commons-dbcp

1.4

junit

junit

4.12

com.alibaba

fastjson

1.2.56

org.springframework.boot

spring-boot-maven-plugin

3.application.yml配置文件

server:

port: 8885

spring:

profiles:

active: dev

logging:

config: classpath:logback-spring.xml #指定配置文件

eureka:

instance:

hostname: 127.0.0.1

client:

registerWithEureka: false

fetchRegistry: false

serviceUrl:

defaultZone: http://${eureka.instance.hostname}:${server.port}/eureka/

4.application-dev.yml配置文件

spring:

profiles: dev

datasource:

impala:

driver-class-name: com.cloudera.impala.jdbc41.Driver

jdbc-url: jdbc:impala://10.1.5.80:21050

oracle:

first:

driver-class-name: oracle.jdbc.OracleDriver

jdbc-url: jdbc:oracle:thin:@10.1.5.80:1521:DB

username: username

password: password

mysql:

first:

driver-class-name: com.mysql.jdbc.Driver

jdbc-url: jdbc:mysql://10.0.10.98:3306/vaas?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false

username: root

password: root

5.Hikari配置初始化

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.zaxxer.hikari.HikariDataSource;

/**

* @version 1.0

* @CalssName HikariDataSourceConfig

* @Author sunke5

* @Date 2020-6-9 14:18

*/

@Configuration

public class HikariDataSourceConfig {

@Primary

@Bean(name = "impalaDataSource")

@Qualifier(value = "impalaDataSource")

@ConfigurationProperties(prefix = "spring.datasource.impala")

public DataSource primaryDataSource() {

return DataSourceBuilder.create().type(HikariDataSource.class).build();

}

@Bean(name = "jdbcTemplateImpala")

public JdbcTemplate jdbcTemplateImpala(@Qualifier("impalaDataSource") DataSource dataSource) {

return new JdbcTemplate(dataSource);

}

@Bean(name = "namedJdbcTemplateImpala")

public NamedParameterJdbcTemplate namedJdbcTemplateImpala(

@Qualifier("impalaDataSource") DataSource dataSource) {

return new NamedParameterJdbcTemplate(dataSource);

}

@Bean(name = "secondOracleDataSource")

@Qualifier(value = "secondOracleDataSource")

@ConfigurationProperties(prefix = "spring.datasource.oracle.first")

public DataSource firstOracleDataSource() {

return DataSourceBuilder.create().type(HikariDataSource.class).build();

}

@Bean(name = "jdbcTemplateOracle1")

public JdbcTemplate jdbcTemplateOracle1(

@Qualifier("secondOracleDataSource") DataSource dataSource) {

return new JdbcTemplate(dataSource);

}

@Bean(name = "thirdMysqlDataSource")

@Qualifier(value = "thirdMysqlDataSource")

@ConfigurationProperties(prefix = "spring.datasource.mysql.first")

public DataSource firstMysqlDataSource() {

return DataSourceBuilder.create().type(HikariDataSource.class).build();

}

@Bean(name = "jdbcTemplateMysql1")

public JdbcTemplate jdbcTemplateMysql1(

@Qualifier("thirdMysqlDataSource") DataSource dataSource) {

return new JdbcTemplate(dataSource);

}

}

服务配置完成，写个demo测试下

Controller

package com.longi.bigdata.controller;

import com.alibaba.fastjson.JSON;

import com.longi.bigdata.common.WebResultUtil;

import com.longi.bigdata.domain.DataRecordBean;

import com.longi.bigdata.domain.WebResult;

import com.longi.bigdata.service.DealDataService;

import lombok.extern.slf4j.Slf4j;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**

* @version 1.0

* @CalssName MonitorController

* @Author sunke5

* @Date 2020-6-10 11:12

*/

@Slf4j

@RestController

@RequestMapping("/monitor")

public class MonitorController {

@Autowired

private DealDataService dealDataService;

@RequestMapping("/queryImpalaCount")

public WebResult queryImpalaCount(@RequestParam("tableName") String tableName) {

List countList = dealDataService.queryHiveCount("","2020-06-08");

String jsonListStr = JSON.toJSONString(countList);

WebResult result = WebResultUtil.getResult(jsonListStr);

return result;

}

@RequestMapping("/queryOracleCount")

public WebResult queryOracleCount(@RequestParam("tableName") String tableName) {

Integer count = dealDataService.queryOracleCount("","");

WebResult result = WebResultUtil.getResult(count+"");

return result;

}

@RequestMapping("/queryMySQLCount")

public WebResult queryMySQLCount(@RequestParam("tableName") String tableName) {

Integer count = dealDataService.queryMysqlCount("","");

WebResult result = WebResultUtil.getResult(count+"");

return result;

}

}

Service

package com.longi.bigdata.service;

import com.longi.bigdata.common.WebResultUtil;

import com.longi.bigdata.domain.DataRecordBean;

import com.longi.bigdata.domain.WebResult;

import lombok.extern.slf4j.Slf4j;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Service;

import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

/**

* @version 1.0

* @CalssName DealDataService

* @Author sunke5

* @Date 2020-6-9 15:52

*/

@Slf4j

@Service

public class DealDataService {

@Autowired

private JdbcTemplate jdbcTemplateImpala;

@Autowired

private JdbcTemplate jdbcTemplateOracle1;

@Autowired

private JdbcTemplate jdbcTemplateMysql1;

public List queryHiveCount(String tableNames,String loadDate) {

log.info("impala jdbctemplate connection start");

//String sql = "select count(*) from ldw_ods.djzk_app_data t where t.base = 'yc' and t.loaddate = '2020-06-08' and t.area = 'F' and t.stovenum = 'F76'";

//List countList = jdbcTemplateImpala.queryForList(sql, DataRecordBean.class);

String sql = "select basearea,substr(nowtime,1,10) as dt ,count(*) as cant from ldw_ods.streaming_djzk_app_result where substr(nowtime,1,10) = '"+loadDate+"' group by 1,2 order by 1,2";

List countList = jdbcTemplateImpala.query(sql, new Object[]{}, new BeanPropertyRowMapper(DataRecordBean.class));

if(null != countList&& countList.size()>0){

DataRecordBean dataRecord = countList.get(0);

}

log.info("impalaJdbcTemplate query result :\t" + countList);

return countList;

}

public Integer queryOracleCount(String tableNames,String loadDate) {

log.info("oracle jdbctemplate connection start");

String sql = "select count(*) from yc_I65 t where t.nowtime between to_date('2020-06-08 00:0:01','yyyy-mm-dd hh24:mi:ss') and to_date('2020-06-08 23:59:59','yyyy-mm-dd hh24:mi:ss')";

Integer count = jdbcTemplateOracle1.queryForObject(sql, Integer.class);

log.info("jdbcTemplateOracle1 query result :\t" + count);

return count;

}

public Integer queryMysqlCount(String tableNames,String loadDate) {

log.info("mysql jdbctemplate connection start");

String sql = " select COUNT(*) from com_component_alarm_info t";

Integer count = jdbcTemplateMysql1.queryForObject(sql, Integer.class);

log.info("jdbcTemplateMysql1 query result :\t" + count);

return count;

}

}

WebResult

package com.longi.bigdata.domain;

/**

* @version 1.0

* @CalssName WebResult

* @Author sunke5

* @Date 2020-6-9 14:49

*/

public class WebResult {

private String errorCode;

private String errorMsg;

private String data;

public String getErrorCode() {

return errorCode;

}

public void setErrorCode(String errorCode) {

this.errorCode = errorCode;

}

public String getErrorMsg() {

return errorMsg;

}

public void setErrorMsg(String errorMsg) {

this.errorMsg = errorMsg;

}

public String getData() {

return data;

}

public void setData(String data) {

this.data = data;

}

@Override

public String toString() {

return "WebResult{" +

"errorCode='" + errorCode + '\'' +

", errorMsg='" + errorMsg + '\'' +

", data=" + data +

'}';

}

}

postman测试结果：

image.png

image.png

image.png