SpringBoot + Hikari集成多数据源（impala，oracle，mysql）

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

项目整体架构图：

image.png

2.pom文件引入

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.longi</groupId>
    <artifactId>bigdata</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>datamonitor</name>
    <description>data monitor</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--引入druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

        <!--数据库连接池-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- Impala start -->
        <dependency>
            <groupId>com.cloudera.longi</groupId>
            <artifactId>ImpalaJDBC41</artifactId>
            <version>2.6.15</version>
        </dependency>
        <!-- Impala end -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.56</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

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<DataRecordBean> 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<DataRecordBean> 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<DataRecordBean> 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<DataRecordBean> countList = jdbcTemplateImpala.query(sql, new Object[]{}, new BeanPropertyRowMapper<DataRecordBean>(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