日常开发中,对于操作ClickHouse中的数据,查询是最常用的功能。本文通过代码示例介绍使用JDBC方式连接ClickHouse查询数据的两种接口:Statement 和 PreparedStatement接口。
1. 引入ClickHouse驱动依赖包
笔者使用idea开发工程,首先创建maven项目,POM文件引入ClickHouse驱动依赖包。
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>
2. 编写应用程序代码查询ClickHouse数据
JDBC连接ClickHouse的两种接口主要区别是:Statement 接口不接受参数,PreparedStatement 接口运行时接受输入的参数。
2.1 Statement接口
Statement可以正常访问数据库,适用于运行静态 SQL 语句。 Statement 接口不接受参数。
import java.sql.*;
public class ClickHouseClient {
private static final String URL = "jdbc:clickhouse://<host>:<port>[/<database>]";
private static final String USER = "your username";
private static final String PASSWORD = "your password";
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
// 注册JDBC驱动
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
// 打开连接
connection = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println("connected database successfully");
// 执行查询
statement = connection.createStatement();
String sql = "select * from database.table_name limit 10";
ResultSet rs = statement.executeQuery(sql);
// 从结果集中提取数据
while (rs.next()){
String name = rs.getString("name");
float size = rs.getFloat("size");
System.out.println(name + " " + size);
}
rs.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
// 释放资源
try {
if(statement!=null){
statement.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(connection!=null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2.2 PreparedStatement接口
PreparedStatement计划多次使用 SQL 语句, PreparedStatement 接口运行时接受输入的参数。
import java.sql.*;
public class ClickHouseClient2 {
private static final String URL = "jdbc:clickhouse://<host>:<port>[/<database>]";
private static final String USER = "your username";
private static final String PASSWORD = "your password";
public static void main(String[] args) {
Connection connection = null;
// 注意这里使用的CK自己实现的PreparedStatement
ClickHousePreparedStatement statement = null;
try {
// 注册JDBC驱动
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
// 打开连接
connection = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println("connected database successfully");
// 执行查询
String sql = "select * from database.table_name where name = ?";
ClickHousePreparedStatement statement = (ClickHousePreparedStatement)connection.prepareStatement(sql);
statement.setString(1, "bjehp");
ResultSet rs = statement.executeQuery();
// 打印填充后的SQL语句(ck实现的PreparedStatement类包含了打印sql语句的方法)
System.out.println("execute: " + statement.asSql());
// 从结果集中提取数据
while (rs.next()){
String name = rs.getString("name");
float size = rs.getFloat("size");
System.out.println(name + " " + size);
}
rs.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
// 释放资源
try {
if(statement!=null){
statement.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if(connection!=null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
这里要注意的是,在调用executeQuery方法时,Statement需要传入sql参数,而PreparedStatement无需传入sql参数,因为在创建PrepareStatement对象时,已经传入sql参数。PrepareStatement可以使用占位符,会对传入的sql进行预编译,批处理比Statement效率高。
3. 参考资料
[1] JDBC教程 https://www.yiibai.com/jdbc/jdbc_quick_guide.html
[2] ClickHouse/clickhouse-jdbc https://github.com/ClickHouse/clickhouse-jdbc