[开发工具]IDEA JDBC连接Clickhouse

1.下载依赖包：

2.IDEA中创建项目

?3.在SRC目录下创建log4j.properties

log4j.rootLogger=WARN, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n

?4.编写主代码:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Main {
  public static void ExecuteSql(String sql){
        String address = "jdbc:clickhouse://192.168.152.128:8123/test";
        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);
            ResultSetMetaData rsmd = results.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (results.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.println(rsmd.getColumnName(i) + ":" + results.getString(i));
                }
            }
        } 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();
            }
        }
    }


    public static void main(String[] args) {
	// write your code here
        String sqlDB = "show databases";//查询数据库
        String sqlTab = "show tables";//查看表
        String sqlCount = "select count(*) count from cell_towers";//查询记录数
        ExecuteSql(sqlDB);
        ExecuteSql(sqlTab);
        ExecuteSql(sqlCount);
  }
}

?

或者连接方式改为官网方式

import ru.yandex.clickhouse.ClickHouseDataSource;
import java.sql.*;
import java.util.Properties;

public class CKLink {
    public static void ExecuteSql(String sql){
        String url = "jdbc:clickhouse://192.168.152.128:8123/test";
        Properties properties = new Properties();
        //properties.setProperty("client_name", "Agent #1");

        ClickHouseDataSource dataSource = new ClickHouseDataSource(url);
        //ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
        try {
            try (Connection conn = dataSource.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {
                ResultSetMetaData rsmd = rs.getMetaData();

                int columnCount = rsmd.getColumnCount();
                System.out.println("columnCount:"+columnCount);
                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        System.out.println(rsmd.getColumnName(i) + ":" + rs.getString(i));
                    }
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void main(String[] args) {
        String sqlDB = "show databases";//查询数据库
        String sqlTab = "show tables";//查看表
        String sqlCount = "select count(*) count from cell_towers";//查询ontime数据量
        String sqlQuery="select * from   cell_towers  limit 20";
        ExecuteSql(sqlDB);
        ExecuteSql(sqlTab);
        ExecuteSql(sqlCount);
        ExecuteSql(sqlQuery);
    }
}