clickhouse通过jdbc连接

逃离我推掉我的手 2022-11-05 00:56 464阅读 0赞

clickhouse通过jdbc连接步骤：

pom.xml文件

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>org.example</groupId>
        <artifactId>jdbc_clickhouse</artifactId>
        <version>1.0-SNAPSHOT</version>
    
    
        <dependencies>
            <dependency>
                <groupId>ru.yandex.clickhouse</groupId>
                <artifactId>clickhouse-jdbc</artifactId>
                <version>0.1.40</version>
            </dependency>
        </dependencies>
    
    </project>

主类

import java.sql.*;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * Created by IntelliJ IDEA.
     * User: admin
     * Date: 2021/2/2
     */
    
    public class jdbc_clickhouse {
        public static void main(String[] args) {
            String sqlDB = "show databases";//查询数据库
            String sqlTab = "show tables";//查看表
            String sqlCount = "select count(*) count from BOC_CN_CUSTOMER";//查询BOC_CN_CUSTOMER数据量
            exeSql(sqlDB);
            exeSql(sqlTab);
            exeSql(sqlCount);
        }
    
        public static void exeSql(String sql){
            String address = "jdbc:clickhouse://10.30.202.21:8123/default";
            Connection connection = null;
            Statement statement = null;
            ResultSet results = null;
            try {
                Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
                connection = DriverManager.getConnection(address);
                statement = connection.createStatement();
                long begin = System.currentTimeMillis();
                results = statement.executeQuery(sql);
                long end = System.currentTimeMillis();
                System.out.println("执行（"+sql+"）耗时："+(end-begin)+"ms");
                ResultSetMetaData rsmd = results.getMetaData();
                List<Map> list = new ArrayList();
                while(results.next()){
                    Map map = new HashMap();
                    for(int i = 1;i<=rsmd.getColumnCount();i++){
                        map.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                    }
                    list.add(map);
                }
                for(Map map : list){
                    System.err.println(map);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {//关闭连接
                try {
                    if(results!=null){
                        results.close();
                    }
                    if(statement!=null){
                        statement.close();
                    }
                    if(connection!=null){
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

执行连接输出结果：

D:\develop\jdk8\bin\java.exe "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.2.3\lib\idea_rt.jar=60925:C:\Program Files\JetBrains\IntelliJ IDEA 2019.2.3\bin" -Dfile.encoding=UTF-8 -classpath D:\develop\jdk8\jre\lib\charsets.jar;D:\develop\jdk8\jre\lib\deploy.jar;D:\develop\jdk8\jre\lib\ext\access-bridge-64.jar;D:\develop\jdk8\jre\lib\ext\cldrdata.jar;D:\develop\jdk8\jre\lib\ext\dnsns.jar;D:\develop\jdk8\jre\lib\ext\jaccess.jar;D:\develop\jdk8\jre\lib\ext\jfxrt.jar;D:\develop\jdk8\jre\lib\ext\localedata.jar;D:\develop\jdk8\jre\lib\ext\nashorn.jar;D:\develop\jdk8\jre\lib\ext\sunec.jar;D:\develop\jdk8\jre\lib\ext\sunjce_provider.jar;D:\develop\jdk8\jre\lib\ext\sunmscapi.jar;D:\develop\jdk8\jre\lib\ext\sunpkcs11.jar;D:\develop\jdk8\jre\lib\ext\zipfs.jar;D:\develop\jdk8\jre\lib\javaws.jar;D:\develop\jdk8\jre\lib\jce.jar;D:\develop\jdk8\jre\lib\jfr.jar;D:\develop\jdk8\jre\lib\jfxswt.jar;D:\develop\jdk8\jre\lib\jsse.jar;D:\develop\jdk8\jre\lib\management-agent.jar;D:\develop\jdk8\jre\lib\plugin.jar;D:\develop\jdk8\jre\lib\resources.jar;D:\develop\jdk8\jre\lib\rt.jar;D:\soft\idea_workspace\jdbc_clickhouse\target\classes;D:\soft\repository_maven_new\ru\yandex\clickhouse\clickhouse-jdbc\0.1.40\clickhouse-jdbc-0.1.40.jar;D:\soft\repository_maven_new\org\apache\httpcomponents\httpclient\4.5.2\httpclient-4.5.2.jar;D:\soft\repository_maven_new\org\apache\httpcomponents\httpcore\4.4.4\httpcore-4.4.4.jar;D:\soft\repository_maven_new\commons-logging\commons-logging\1.2\commons-logging-1.2.jar;D:\soft\repository_maven_new\commons-codec\commons-codec\1.9\commons-codec-1.9.jar;D:\soft\repository_maven_new\org\apache\httpcomponents\httpmime\4.5.2\httpmime-4.5.2.jar;D:\soft\repository_maven_new\net\jpountz\lz4\lz4\1.3.0\lz4-1.3.0.jar;D:\soft\repository_maven_new\com\fasterxml\jackson\core\jackson-databind\2.7.3\jackson-databind-2.7.3.jar;D:\soft\repository_maven_new\com\fasterxml\jackson\core\jackson-annotations\2.7.0\jackson-annotations-2.7.0.jar;D:\soft\repository_maven_new\com\fasterxml\jackson\core\jackson-core\2.7.3\jackson-core-2.7.3.jar;D:\soft\repository_maven_new\com\google\guava\guava\19.0\guava-19.0.jar;D:\soft\repository_maven_new\org\slf4j\slf4j-api\1.7.21\slf4j-api-1.7.21.jar;D:\soft\repository_maven_new\joda-time\joda-time\2.9.9\joda-time-2.9.9.jar jdbc_clickhouse
    SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
    SLF4J: Defaulting to no-operation (NOP) logger implementation
    SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
    执行（show databases）耗时：81ms
    {name=_temporary_and_external_tables}
    {name=default}
    {name=system}
    执行（show tables）耗时：421ms
    {name=BOC_CN_CUSTOMER}
    {name=BOC_CN_CUSTOMER_ALL}
    {name=BOC_CN_CUSTOMER_TEST555}
    
    {count=6644}
    执行（select count(*) count from BOC_CN_CUSTOMER）耗时：692ms
    
    Process finished with exit code 0