美文网首页
SpringBoot + Hikari集成多数据源(impala

SpringBoot + Hikari集成多数据源(impala

作者: spark孙 | 来源:发表于2020-06-10 14:45 被阅读0次

    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

    相关文章

      网友评论

          本文标题:SpringBoot + Hikari集成多数据源(impala

          本文链接:https://www.haomeiwen.com/subject/jnfptktx.html