java用jdbc連接impala

本文介紹一種使用使用mybatis + dbcp2操作impala的方法玩般。

第一步，創(chuàng)建一個maven工程淤井，目錄結(jié)構(gòu)如下

image.png

第二步史简，引入pom坐標(biāo)

 <dependency>
            <groupId>impala</groupId>
            <artifactId>impala-jdbc41</artifactId>
            <version>2.6.17.1020</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.28</version>
        </dependency>

如果impala-jdbc41的包maven不能自動下載到本地，就到官網(wǎng)下載顽频，然后上傳到maven私服藤肢。官網(wǎng)連接：https://www.cloudera.com/downloads/connectors/impala/jdbc/2-6-17.html

image.png

本文創(chuàng)建的是springboot項目，還需要引入坐標(biāo)

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
    </parent>

<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>
</dependencies>

第三步糯景，創(chuàng)建配置文件

在resources下面創(chuàng)建application.yml文件嘁圈，配置如下

server:
  port: 8190
spring:
  application:
    name: impala-server
  impala:
    url: jdbc:impala://110.110.110.110:21050/
    driver-class-name: com.cloudera.impala.jdbc41.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      max-active: 200
      initial-size: 1
      min-idle: 3
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      testWhileIdle: true
      validationQuery: select '1'
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 200

mybatis:
  mapper-locations:  classpath*:mapping/*.xml
  impala-mapper-locations: classpath*:impalaMapping/*.xml
  type-aliases-package: com.bigdata.bean
  configuration:
    call-setters-on-nulls: true

第四步，創(chuàng)建Java文件

在config目錄下創(chuàng)建BigDataSourceConfig

package com.bigdata.config;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.bind.RelaxedPropertyResolver;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = {"com.bigdata.dao"},sqlSessionFactoryRef = "impalaSqlSessionFactory")
public class BigDataSourceConfig implements EnvironmentAware {
    private RelaxedPropertyResolver propertyResolver;

    public void setEnvironment(Environment environment) {
        this.propertyResolver = new RelaxedPropertyResolver(environment);
    }

    @Bean
    public DataSource impalaDataSource() {
        BasicDataSource datasource = new BasicDataSource();
        datasource.setUrl(this.propertyResolver.getProperty("spring.impala.url"));
        datasource.setDriverClassName(this.propertyResolver.getProperty("spring.impala.driver-class-name"));
        datasource.setInitialSize(Integer.valueOf(this.propertyResolver.getProperty("spring.impala.druid.initial-size")));
        datasource.setMinIdle(Integer.valueOf(this.propertyResolver.getProperty("spring.impala.druid.min-idle")));
        datasource.setMaxWaitMillis(Long.valueOf(this.propertyResolver.getProperty("spring.impala.druid.max-wait")));
        datasource.setMaxTotal(Integer.valueOf(this.propertyResolver.getProperty("spring.impala.druid.max-active")));
        datasource.setMinEvictableIdleTimeMillis(Long.valueOf(this.propertyResolver.getProperty("spring.impala.druid.min-evictable-idle-time-millis")));
        datasource.setValidationQuery(String.valueOf(this.propertyResolver.getProperty("spring.impala.druid.validationQuery")));
        return datasource;
    }

    @Bean
    public SqlSessionFactory impalaSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(this.impalaDataSource());
        sqlSessionFactoryBean.setTypeAliasesPackage(this.propertyResolver.getProperty("mybatis.type-aliases-package"));
        sqlSessionFactoryBean.setMapperLocations((new PathMatchingResourcePatternResolver()).getResources(this.propertyResolver.getProperty("mybatis.impala-mapper-locations")));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setCallSettersOnNulls(true);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager impalaTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(this.impalaDataSource());
    }
}

在dao目錄下創(chuàng)建ImpalaDao類

import org.springframework.stereotype.Repository;

import java.util.LinkedHashMap;
import java.util.List;

@Repository
public interface ImpalaDao {
    List<LinkedHashMap> executeSql(String sql);
}

在resources目錄下創(chuàng)建impalaMapping文件夾莺奸，然后在下面創(chuàng)建ImpalaMapper.xml配置文件

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.bigdata.dao.ImpalaDao">

    <select id="executeSql" parameterType="String" resultType="java.util.LinkedHashMap">
        ${_parameter}
    </select>
</mapper>

4, 在service目錄下創(chuàng)建ImpalaJdbc.java文件

package com.bigdata.service;

import com.bigdata.dao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Component
public class ImpalaJdbc {
    @Resource
    private ImpalaDao impalaDao;
    public static final Logger logger = LoggerFactory.getLogger(ImpalaJdbc.class);
    public boolean executeSQL(String sql) {
        try {
            impalaDao.executeSql(sql);
            logger.info("executeSQL...." + sql);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List executeQueryForList(String execSql) {
        List list = impalaDao.executeSql(execSql);
        return list;
    }

    public long getCounte(String tabName) {
        String sql = "select count(*) c from " + tabName;
        try {
            List<LinkedHashMap> list = impalaDao.executeSql(sql);
            long num = Long.parseLong(list != null && list.size() > 0 ? ((LinkedHashMap)list.get(0)).get("c").toString() : "0");
            return num;
        } catch (Exception e) {
            e.printStackTrace();
            return 0L;
        }
    }
}

在controller目錄下創(chuàng)建BigDataController.java文件

package com.bigdata.controller;

import com.bigdata.service.ImpalaJdbc;

import javax.annotation.Resource;

@Slf4j
@RestController
@Api(value = "impala測試")
public class BigDataController {
    @Resource
    ImpalaJdbc impalaJdbc;

    @ApiOperation("sql測試")
    @RequestMapping(value = {"/executeSQL"}, method = {RequestMethod.POST})
    public boolean executeSQL(@RequestParam @ApiParam(value = "sql語句", required = true) String sql) {
        return impalaJdbc.executeSQL(sql);
    }

}

6.在com.bigdata目錄下創(chuàng)建BigDataApplication.java啟動類

package com.bigdata;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.bigdata.dao")
public class BigDataApplication {

    public static void main(String[] args) {
        SpringApplication.run(BigDataApplication.class, args);
    }
}

為了方便演示引入swagger丑孩，在config目錄下創(chuàng)建SwaggerConfig.java
先引入maven坐標(biāo)

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.8.0</version>
        </dependency>

package com.bigdata.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class SwaggerConfig {

    @Bean
    public Docket api() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                // 選擇那些路徑和api會生成document
                .select()
                // 對所有api進(jìn)行監(jiān)控
//                .apis(RequestHandlerSelectors.any())
                .apis(RequestHandlerSelectors.basePackage("com.bigdata.controller"))
                // 對所有路徑進(jìn)行監(jiān)控
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("impala測試")
                .description("測試API")
                .termsOfServiceUrl("")
                //程序猿ID
                .contact(new Contact("空塵AI","",""))
                .version("1.0")
                .build();
    }
}

8.運行啟動類BigDataApplication冀宴，啟動后通過瀏覽器訪問swagger頁面
http://localhost:8190/swagger-ui.html#!
在接口參數(shù)中輸入SQL語句即可測試