springboot整合clickhouse
前⾔
了解了clickhouse的基础概念和相关的理论之后，本篇将通过实例代码演⽰如何在Java代码中操作clickhouse，主要涉及的内容包括：使⽤JDBC的⽅式操作clickhouse
clickhouse与springboot的整合使⽤
前置准备
1、clickhouse服务确保已开启
2、为保证实验效果，提前创建⼀张表，并为该表插⼊⼀些实验数据
create table t_order01(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
insert into t_order01 values
(101,'sku_001',1000.00,'2021-12-01 12:00:00'),
(102,'sku_002',2000.00,'2021-12-01 11:00:00'),
(102,'sku_004',2500.00,'2021-12-01 12:00:00'),
(102,'sku_002',2000.00,'2021-12-01 13:00:00'),
(102,'sku_002',12000.00,'2021-12-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-12 12:00:00');
执⾏完毕sql之后，查询下default数据库下的表是否成功创建
使⽤jdbc⽅式操作clickhouse 1、引⼊clickhouse的jdbc依赖
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.1.52</version>
</dependency
2、实例代码
实⼤部分的操作和我们使⽤jdbc操作mysql的步骤类似，下⾯直接贴出代码，可以结合注释进⾏参考使⽤
import ClickHouseConnection;
import ClickHouseDataSource;
import ClickHouseProperties;
import*;
import ArrayList;
import HashMap;
import List;
import Map;
public class CreateTableTest {
private static String username ="default";
private static String password ="你的连接密码";
private static String address ="jdbc:clickhouse://clickhouse的连接IP地址:8123";
private static String db ="连接数据库名称（默认数据库：default）";
private static int socketTimeout =600000;
public static void main(String[] args)throws Exception {
//getConn();
//queryTable();
//createTable("");
//insertOne();
//dropTable();
deleteById();
//updateById();
}
/**
* 查询数据
*/
public static void queryTable(){
List<Map<String, Object>> list =new ArrayList<>();
String sql ="select * from user_info";
Connection connection =getConn();
try{
Statement statement = ateStatement();
ResultSet rs  = uteQuery(sql);
springboot aop
ResultSetMetaData rsmd = rs.getMetaData();
()){
Map<String, Object> row =new HashMap<>();
for(int i =1; i <= ColumnCount(); i++){
row.ColumnName(i), rs.ColumnName(i)));
}
list.add(row);
}
}catch(SQLException e){
e.printStackTrace();
}
//在此可以根据实际需求将解析的数据封装到对象中
list.stream().forEach(item ->{
Map<String, Object> rowData = item;
System.out.println(rowData);
});
//System.out.println(list);
//System.out.println(list);
}
/**
* 创建表
* @throws Exception
*/
public static void createTable(String tableSql)throws Exception{
/*tableSql = "create table t_order02(\n" +
" id UInt32,\n" +
" sku_id String,\n" +
" total_amount Decimal(16,2),\n" +
" create_time Datetime\n" +
") engine =MergeTree\n" +
" partition by toYYYYMMDD(create_time)\n" +
" primary key (id)\n" +
" order by (id,sku_id);";*/
Connection connection =getConn();
Statement statement = ateStatement();
boolean execute = ute(tableSql);
if(execute){
System.out.println(execute);
System.out.println("创建表成功");
}
}
/**
* 删除表
* @throws Exception
*/
public static void dropTable()throws Exception{
Connection connection =getConn();
Statement statement = ateStatement();
System.out.println("删除表成功");
}
/**
* 插⼊数据
* 实际使⽤时候，插⼊的语句⾥⾯的参数从外部传⼊进去
* @throws Exception
*/
public static void insertOne()throws Exception{
Connection connection =getConn();
PreparedStatement pstmt = connection.prepareStatement("insert into t_order01 values('103', 'sku_004', '2500.00','2021-06-01 12:00:00')");
System.out.println("insert success");
}
/**
* 删除数据
* 实际使⽤时候，删除的语句⾥⾯的参数从外部传⼊进去
*/
public static void deleteById()throws Exception{
Connection connection =getConn();
//sku_id ='sku_001'
PreparedStatement pstmt = connection.prepareStatement("alter table t_order01 delete where sku_id = 'sku_002';");
System.out.println("delete success");
}
/**
* 修改数据
* 实际使⽤时候，修改的语句⾥⾯的参数从外部传⼊进去
*/
public static void updateById()throws Exception{
Connection connection =getConn();
PreparedStatement pstmt = connection.prepareStatement("alter table t_order01 update total_amount=toDecimal32(2000.00,2) where id = '102'");        ute();
System.out.println("update success");
}
public static Connection getConn(){
ClickHouseProperties properties =new ClickHouseProperties();
properties.setUser(username);
properties.setPassword(password);
properties.setDatabase(db);
properties.setSocketTimeout(socketTimeout);
ClickHouseDataSource clickHouseDataSource =new ClickHouseDataSource(address, properties);
ClickHouseConnection conn =null;
try{
conn = Connection();
System.out.println(conn);
System.out.println("连接成功");
return conn;
}catch(SQLException e){
e.printStackTrace();
}
return null;
}
}
3、测试，选择查询和删除⼀条数据为例做测试
查询功能测试结果，见下⾯的控制台数据打印
删除功能测试结果，删除 "sku_id = sku_002 "的数据， 执⾏⽅法之后，见下⾯的控制台数据打印

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。