当前位置：首页 > news >正文

封装clickHouse线程池与ibeetl解析SQL并对结果转进行转化

news 来源：原创 2024/9/20 2:55:27

线程池配置

import com.cloudwise.dcim.common.utils.ClickHouseConnectionPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** @author cheng* @className ClickHousePoolConfig* @date 2024/08/15 11:11**/
@Configuration
public class ClickHousePoolConfig {@Value("${chServers}")private String servers;@Value("${chHttpPort}")private Integer port;@Value("${chUsername}")private String user;@Value("${chPassword}")private String password;@Bean("clickHouseConnectionPool")public ClickHouseConnectionPool chConnectionPool(){String clickHouseUrl = "jdbc:clickhouse://"+servers+":"+port;//clickHouseUrl = "jdbc:clickhouse://"+servers+":"+port+"/cw_db";GenericObjectPoolConfig poolConfig = new GenericObjectPoolConfig();/*** maxTotal: 池中最大活动对象数，如果设置为负值，则表示没有限制。** maxIdle: 池中最大空闲对象数，如果设置为负值，则表示没有限制。** minIdle: 池中最小空闲对象数。** maxWaitMillis: 当池中没有可用对象时，最大等待时间（以毫秒为单位），如果超时，将抛出 NoSuchElementException 异常。设置为-1表示无限等待。** testOnCreate: 对象被创建时是否进行检测，如果检测失败则对象被销毁。** testOnBorrow: 对象被借用时是否进行检测，如果检测失败则重新尝试借用或返回Null。** testOnReturn: 对象被归还时是否进行检测，如果检测失败则对象被销毁。** testWhileIdle: 是否启用空闲对象检测，如果启用，则一个空闲对象检测线程会周期性地运行。** timeBetweenEvictionRunsMillis: 空闲对象检测的运行间隔时间（以毫秒为单位），如果设置为负值，则不运行空闲对象检测线程。** numTestsPerEvictionRun: 每次空闲对象检测运行中最大检测对象数。*/// 配置连接池参数，例如最大连接数、最大空闲时间等poolConfig.setMaxTotal(10);poolConfig.setMaxIdle(5);poolConfig.setMinIdle(2);poolConfig.setMaxWaitMillis(-1);poolConfig.setTestOnBorrow(true);poolConfig.setTestOnReturn(false);poolConfig.setTestWhileIdle(false);ClickHouseConnectionPool connectionPool = new ClickHouseConnectionPool(clickHouseUrl, poolConfig,user,password);//Connection connection = connectionPool.getConnection();return connectionPool;}
}

连接数据库配置

import org.apache.commons.pool2.BasePooledObjectFactory;
import org.apache.commons.pool2.PooledObject;
import org.apache.commons.pool2.impl.DefaultPooledObject;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/*** @author cheng* @className ClickHouseConnectionPool* @date 2024/08/15 10:37**/
public class ClickHouseConnectionPool {private final GenericObjectPool<Connection> connectionPool;public ClickHouseConnectionPool(String clickHouseUrl, GenericObjectPoolConfig poolConfig,String user,String paassword) {// 创建ClickHouse连接属性ClickHouseProperties properties = new ClickHouseProperties();properties.setUser(user); // 设置用户名properties.setPassword(paassword); // 设置密码// 设置自动提交为true//properties.setAutoCommit(true);ClickHouseDataSource dataSource = new ClickHouseDataSource(clickHouseUrl,properties);connectionPool = new GenericObjectPool<>(new ClickHouseConnectionFactory(dataSource), poolConfig);}public Connection getConnection() throws SQLException {try {return connectionPool.borrowObject();} catch (Exception e) {throw new SQLException("Could not get a connection", e);}}public void returnConnection(Connection conn) {if (conn != null) {connectionPool.returnObject(conn);}}private static class ClickHouseConnectionFactory extends BasePooledObjectFactory<Connection> {private final DataSource dataSource;public ClickHouseConnectionFactory(DataSource dataSource) {this.dataSource = dataSource;}@Overridepublic Connection create() throws Exception {return dataSource.getConnection();}@Overridepublic PooledObject<Connection> wrap(Connection connection) {return new DefaultPooledObject<>(connection);}}
}

clickhouse查找数据工具

import cn.hutool.json.JSONUtil;
import com.cloudwise.dcim.common.exception.BaseException;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** ck 查找数据* @author cheng* @className ClickHouseQueryUtils* @date 2024/08/15 11:20**/
public class ClickHouseQueryUtils {/*** 无需转化指定实体类* @param sql* @return*/public static List<Map<String,Object>> querySelect(String sql){ClickHouseConnectionPool connectionPool =  SpringHoldUtil.getBean(ClickHouseConnectionPool.class);Connection connection = null;//输入动态参数//PreparedStatement preparedStatement = null;//Statement statement = null;// ResultSet resultSet = null;List<Map<String, Object>> dataMap = null;// 使用connection执行操作try {connection = connectionPool.getConnection();//preparedStatement = connection.prepareStatement() ;//statement = connection.createStatement();sql = BeetlTemplateUtils.render(sql,null);//resultSet= statement.executeQuery(sql);try(Statement statement = connection.createStatement()){try (ResultSet resultSet = statement.executeQuery(sql)) {// 处理结果集dataMap = convert(resultSet);}}}catch (SQLException e){throw  new BaseException(e);}finally {
//            try {
//                if (resultSet != null) {
//                    resultSet.close();
//                }
//                if (statement != null) {
//                    statement.close();
//                }
//            } catch (SQLException e) {
//                throw  new BaseException(e);
//            }if(connection!=null) {connectionPool.returnConnection(connection);}}return  dataMap;}/*** 需转化指定实体类* @param sql* @param cls* @param <T>* @return*/public static  <T> List<T> querySelect(String sql,Class<T> cls) {List<Map<String, Object>> dataMap =  querySelect(sql);if(dataMap!=null&&!dataMap.isEmpty()) {return (List<T>) JSONUtil.toList(JSONUtil.toJsonStr(dataMap), cls);}return  null;}/**** 模版参数需转化指定实体类* @param sqlTemplate* @param params* @param cls* @param <T>* @return*/public static <T> List<T> querySelect(String sqlTemplate, Map<String,Object> params,Class<T> cls){String sql = BeetlTemplateUtils.render(sqlTemplate,params);return querySelect(sql,cls);}private static List<Map<String, Object>> convert(ResultSet rs) throws SQLException {ResultSetMetaData md = rs.getMetaData();int columns = md.getColumnCount();List<Map<String, Object>> list = new ArrayList<>();while (rs.next()) {Map<String, Object> row = new HashMap<>(columns);for (int i = 1; i <= columns; ++i) {row.put(md.getColumnName(i), rs.getObject(i));}list.add(row);}return list;}
}

模版解析工具

import org.beetl.core.Configuration;
import org.beetl.core.GroupTemplate;
import org.beetl.core.Template;
import org.beetl.core.resource.StringTemplateResourceLoader;import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @author derek* @className BeetlTemplateUtils* @date 2024/08/15 14:19**/
public class BeetlTemplateUtils {private static GroupTemplate gt;static {//初始化代码StringTemplateResourceLoader resourceLoader = new StringTemplateResourceLoader();Configuration cfg = null;try {cfg = Configuration.defaultConfiguration();} catch (IOException e) {e.printStackTrace();}gt = new GroupTemplate(resourceLoader, cfg);}public static String render(String template, Map<String,? extends Object> params){//获取模板Template t = gt.getTemplate(template);if(params!=null&&!params.isEmpty()){params.forEach((key,value)->{t.binding(key, value);});}return  t.render();}public static void main(String[] args) throws IOException {String template = "hello,${name}";Map<String,String> params = new HashMap<>();params.put("name","Lucy");// 输出渲染后的字符串System.out.println(BeetlTemplateUtils.render(template,params));}
}

依赖jar

<dependency><groupId>com.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.3.2-patch11</version><scope>compile</scope></dependency><dependency><groupId>com.ibeetl</groupId><artifactId>beetl-core</artifactId></dependency>