使用jdbc操作ClickHouse

今天药忘吃喽~ 2023-06-04 02:45 3阅读 0赞

# 使用jdbc操作ClickHouse #

2018年07月01日 01:33:00 [狮子头儿][Link 1] 阅读数 10501

本文链接： [https://blog.csdn.net/zwq\_zwq\_zwq/article/details/80871518][https_blog.csdn.net_zwq_zwq_zwq_article_details_80871518]

# #

*  创建maven项目引入依赖

引入ClickHouse驱动包

1.   
    
    <dependency>
2.   
    
    <groupId>ru.yandex.clickhouse</groupId>
3.   
    
    <artifactId>clickhouse-jdbc</artifactId>
4.   
    
    <version>0.1.40</version>
5.   
    
    </dependency>

# #

*  修改ClickHouse配置文件

vim /etc/clickhouse-server/config.xml

配置jdbc访问端口

<http_port>8123</http_port>

配置ip地址

1.   
    
    <listen\_host>::1</listen\_host>
2.   
    
    <listen\_host>172.16.1.23</listen\_host>

注：需要配置listen\_host,不然会出现 Connection refused的情况，修改完成之后需要重启

# #

*  编写测试类

1.   
    
    package com.zhangwq;
2.   
    
     
3.   
    
    import java.sql.\*;
4.   
    
    import java.util.ArrayList;
5.   
    
    import java.util.HashMap;
6.   
    
    import java.util.List;
7.   
    
    import java.util.Map;
8.   
    
     
9.   
    
    /\*\*
10.  
    
     \* @author zhangwq
11.  
    
     \* @date 2018/6/30 20:58
12.  
    
     \*/
13.  
    
    public class ClickHouseJDBC \{ 
14.  
    
    public static void main(String\[\] args) \{ 
15.  
    
    String sqlDB = "show databases";//查询数据库
16.  
    
    String sqlTab = "show tables";//查看表
17.  
    
    String sqlCount = "select count(\*) count from ontime";//查询ontime数据量
18.  
    
    exeSql(sqlDB);
19.  
    
    exeSql(sqlTab);
20.  
    
    exeSql(sqlCount);
21.  
    
    \}
22.  
    
     
23.  
    
    public static void exeSql(String sql)\{ 
24.  
    
    String address = "jdbc:clickhouse://172.16.1.23:8123/default";
25.  
    
    Connection connection = null;
26.  
    
    Statement statement = null;
27.  
    
    ResultSet results = null;
28.  
    
    try \{ 
29.  
    
    Class.forName( "ru.yandex.clickhouse.ClickHouseDriver");
30.  
    
    connection = DriverManager.getConnection(address);
31.  
    
    statement = connection.createStatement();
32.  
    
    long begin = System.currentTimeMillis();
33.  
    
    results = statement.executeQuery(sql);
34.  
    
    long end = System.currentTimeMillis();
35.  
    
    System.out.println( "执行（"+sql+"）耗时："+(end-begin)+"ms");
36.  
    
    ResultSetMetaData rsmd = results.getMetaData();
37.  
    
    List<Map> list = new ArrayList();
38.  
    
    while(results.next())\{ 
39.  
    
    Map map = new HashMap();
40.  
    
    for(int i = 1;i<=rsmd.getColumnCount();i++)\{ 
41.  
    
    map.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
42.  
    
    \}
43.  
    
    list.add(map);
44.  
    
    \}
45.  
    
    for(Map map : list)\{ 
46.  
    
    System.err.println(map);
47.  
    
    \}
48.  
    
    \} catch (Exception e) \{ 
49.  
    
    e.printStackTrace();
50.  
    
    \} finally \{ //关闭连接
51.  
    
    try \{ 
52.  
    
    if(results!=null)\{ 
53.  
    
    results.close();
54.  
    
    \}
55.  
    
    if(statement!=null)\{ 
56.  
    
    statement.close();
57.  
    
    \}
58.  
    
    if(connection!=null)\{ 
59.  
    
    connection.close();
60.  
    
    \}
61.  
    
    \} catch (SQLException e) \{ 
62.  
    
    e.printStackTrace();
63.  
    
    \}
64.  
    
    \}
65.  
    
    \}
66.  
    
    \}

启动项目测试，发现如下报错。

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".

SLF4J: Defaulting to no-operation (NOP) logger implementation

pom引入slf4j相关文件引入依赖，并resource下添加log4j2.properties解决该问题

1.   
    
    <dependency>
2.   
    
    <groupId>org.apache.logging.log4j</groupId>
3.   
    
    <artifactId>log4j-slf4j-impl</artifactId>
4.   
    
    <version>2.11.0</version>
5.   
    
    </dependency>

# #

*  查看输出结果

1.   
    
    \{name= default\}
2.   
    
    \{name=system\}
3.   
    
    执行（show databases）耗时：2ms
4.   
    
    \{name=ontime\}
5.   
    
    执行（show tables）耗时：2ms
6.   
    
    \{name=zwqtest\}
7.   
    
    \{name=zwqtestall\}
8.   
    
    执行（select count(\*) count from ontime）耗时：98ms
9.   
    
    \{ count=177920306\}
10.

转载于:https://www.cnblogs.com/think90/p/11443321.html

[Link 1]: https://me.csdn.net/zwq_zwq_zwq
[CC 4.0 BY-SA]: http://creativecommons.org/licenses/by-sa/4.0/
[https_blog.csdn.net_zwq_zwq_zwq_article_details_80871518]: https://blog.csdn.net/zwq_zwq_zwq/article/details/80871518