假设在一些BI的场景里面,数据直接存储在clickhouse里面的,此时一些报表的数据就会直接使用javaweb通过java api进行查询。所以本文我们演示下使用java api的方式和clickhouse进行交互,整个的过程比较简单,这里直接上演示代码:
package com.clickhouse.api;
import ru.yandex.clickhouse.BalancedClickhouseDataSource;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseStatement;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* JavaAPI
*/
public class JavaAPI {
//这里是clickhouse的连接url,如果是集群的话,需要配置多个ip,示例如下:jdbc:clickhouse://node1:8123,node2:8123,node3:8123/default
private static String clickhouseurl = "jdbc:clickhouse://192.168.31.254:8123/test";
//这里是clickhouse的连接用户名
private static final String clickhouse_name = "default";
//这里是clickhouse的连接密码
private static final String clickhouse_passwd = "";
private static BalancedClickhouseDataSource dataSource;
static {
ClickHouseProperties props = new ClickHouseProperties();
props.setUser(clickhouse_name);
props.setPassword(clickhouse_passwd);
dataSource = new BalancedClickhouseDataSource(clickhouseurl, props);
}
public static void main(String[] args) throws Exception {
//插入数据
// insert();
//更新数据
update();
//删除数据
// delete();
//查询数据
findAll();
}
/**
* 更新数据
* @throws SQLException
*/
private static void update() throws SQLException {
ClickHouseConnection connection = dataSource.getConnection();
String sql = "alter table test.users update age = 10 where id = 2";
ClickHouseStatement statement = connection.createStatement();
statement.execute(sql);
connection.close();
}
/**
* 演示删除数据
* @throws SQLException
*/
private static void delete() throws SQLException {
ClickHouseConnection connection = dataSource.getConnection();
String sql = "alter table test.users delete where id = 3";
ClickHouseStatement statement = connection.createStatement();
statement.execute(sql);
connection.close();
}
/**
* 演示插入数据
* @throws SQLException
*/
private static void insert() throws SQLException {
ClickHouseConnection connection = dataSource.getConnection();
String sql = "insert into test.users(id,name,age) values(3,'王五',3)";
ClickHouseStatement statement = connection.createStatement();
statement.execute(sql);
connection.close();;
}
/**
* 演示查询数据
* @throws SQLException
*/
private static void findAll() throws SQLException {
ClickHouseConnection connection = dataSource.getConnection();
ClickHouseStatement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select * from users");
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println("id = "+id+",name = "+name +",age = "+age);
}
rs.close();
connection.close();
}
}这个案例里面我们使用java api对clickhouse进行了简单的增删改查。大家看起来是不是和jdbc的操作方式差不多的。如果使用mybatis的话,可以直接进行替换。
需要注意下,这里引入的clickhouse驱动包依赖是:
<dependency> <groupId>ru.yandex.clickhouse</groupId> <artifactId>clickhouse-jdbc</artifactId> <version>0.3.2</version> </dependency>
备注:
1、大家使用传统操作mysql的方式操作clickhouse即可。 2、这里如果是集群的话,需要配置多个地址。 3、注意clickhouse里面对数据进行更新和删除的时候,sql和传统的不一样。









还没有评论,来说两句吧...