SpringBoot + Hikari集成多数据源（impala
2020-06-10 本文已影响0人
spark孙
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
image.png
image.png
