假设在一些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和传统的不一样。
还没有评论,来说两句吧...