依赖包引入
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.4</version>
</dependency>
数据源加载
import com.alibaba.druid.pool.DruidDataSource;
import com.paratera.console.datasync.constans.DataSyncConstants;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import ru.yandex.clickhouse.ClickHouseConnection;
import javax.sql.DataSource;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
@Data
@Configuration
@ConfigurationProperties(prefix = "clickhouse")
@Slf4j
public class ClickHouseDataSource {
private String driverClassName;
private String url;
private String username;
private String password;
private Integer initialSize;
private Integer maxActive;
private Integer minIdle;
private Integer maxWait;
@Bean("clickhouseConnection")
public Connection getConn() throws SQLException {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setDriverClassName(driverClassName);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setValidationQuery("SELECT 1");
datasource.setTestWhileIdle(true);
return datasource.getConnection();
}
}
操作工具类
import com.paratera.console.datasync.constans.DataSyncConstants;
import lombok.extern.slf4j.Slf4j;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
@Slf4j
@Component
public class ClickHouseUtils {
@Autowired
private RabbitTemplate rabbitTemplate;
@Value("${clickhouse.url}")
private String url;
@Resource(name = "clickhouseConnection")
private Connection connection;
public void execSQL(String sql) {
log.debug("cliockhouse 执行sql:" + sql);
try (Statement statement = connection.createStatement()) {
statement.executeQuery(sql);
} catch (SQLException e) {
sendMessage2WxRobot(e);
}
}
public List<Map> exeSql(String sql) {
log.info("cliockhouse 执行sql:" + sql);
try (Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(sql)) {
ResultSetMetaData rsmd = results.getMetaData();
List<Map> list = new ArrayList();
while (results.next()) {
Map 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) {
sendMessage2WxRobot(e);
}
return Collections.emptyList();
}
}