springBoot + clickHouse

本人开发环境：

springBoot +	jdk 1.8 +	eclipse

1.引入jar

   <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.2.12.RELEASE</version>
      </dependency>
<dependency>
		    <groupId>ru.yandex.clickhouse</groupId>
		    <artifactId>clickhouse-jdbc</artifactId>
		    <version>0.1.53</version>
		</dependency>

2.配置文件

spring:
  datasource: 
    clickhouse:
      address: jdbc:clickhouse://ip:端口
      username: default
      password:
      db: default
      socketTimeout: 600000

3.主要代码

package com.swap.scan.clickhouse;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.sql.*;
import java.util.*;

@Slf4j
@Component
public class ClickHouseService {
	private static String clickhouseAddress;

	private static String clickhouseUsername;

	private static String clickhousePassword;

	private static String clickhouseDB;

	private static Integer clickhouseSocketTimeout;

	@Value("${spring.datasource.clickhouse.address}")
	public  void setClickhouseAddress(String address) {
		ClickHouseService.clickhouseAddress = address;
	}
	@Value("${spring.datasource.clickhouse.username}")
	public  void setClickhouseUsername(String username) {
		ClickHouseService.clickhouseUsername = username;
	}
	@Value("${spring.datasource.clickhouse.password}")
	public  void setClickhousePassword(String password) {
		ClickHouseService.clickhousePassword = password;
	}
	@Value("${spring.datasource.clickhouse.db}")
	public  void setClickhouseDB(String db) {
		ClickHouseService.clickhouseDB = db;
	}
	@Value("${spring.datasource.clickhouse.socketTimeout}")
	public  void setClickhouseSocketTimeout(Integer socketTimeout) {
		ClickHouseService.clickhouseSocketTimeout = socketTimeout;
	}

	public static Connection getConn() {

		ClickHouseConnection conn = null;
		ClickHouseProperties properties = new ClickHouseProperties();
		properties.setUser(clickhouseUsername);
		properties.setPassword(clickhousePassword);
		properties.setDatabase(clickhouseDB);
		properties.setSocketTimeout(clickhouseSocketTimeout);
		ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress,properties);
		try {
			conn = clickHouseDataSource.getConnection();
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void exeSql(String sql){
//		log.info("cliockhouse 执行sql：" + sql);
		Connection connection = getConn();
		try {
			Statement statement = connection.createStatement();
			ResultSet results = statement.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	public static List<Map<String,String>> selectSql(String sql){
//		log.info("cliockhouse 执行sql：" + sql);
		Connection connection = getConn();
		try {
			Statement statement = connection.createStatement();
			ResultSet results = statement.executeQuery(sql);
			ResultSetMetaData rsmd = results.getMetaData();
			List<Map<String,String>> list = new ArrayList<>();
			while(results.next()){
				Map<String,String> row = new HashMap<>();
				for(int i = 1;i<=rsmd.getColumnCount();i++){
					row.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
				}
				list.add(row);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}

package com.swap.scan.clickhouse;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
 
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = true)
public class ClickHouseDataSourceProperties extends ClickHouseProperties{
 
    private String url;
 
}

需要手动修改sql，main方法直接调用接口使用

package com.swap.scan.clickhouse;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.swap.scan.entity.ScanBaseTransfer;

import cn.hutool.db.Page;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ClickHoseUtil {

	public static ClickHoseUtil getClickHoseUtil() {
		return new ClickHoseUtil();
	}
	public void select() {
		//String sql = "select * from aaa where 1= 1";
		String sql = sql;
		List<Map<String,String>> listMap = ClickHouseService.selectSql(sql);
		//List clickHouseList = JSONArray.parseArray(JSONObject.toJSONString(listMap),  vo.getClass()); 
		...
	}
	
	
	
	@SuppressWarnings("unchecked")
	public void selectByPage() {
		
//		String sql = "select * from aaa order by id limit  begin , PageSize";
		String sql = sql;
		List<Map<String,String>> list = ClickHouseService.selectSql(sql);
		@SuppressWarnings("rawtypes")
		//List clickHouseList = JSONArray.parseArray(JSONObject.toJSONString(list),  vo.getClass()); 
		...
	}
	
	public  void insert()  {
		try {
			String sql = "插入sql";
			ClickHouseService.exeSql(sql);
//			log.info("成功插入");
		} catch (Exception e) {
			log.error("存入失败",e);
		}
	}
 
}