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

线程池配置

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;
        }
        
        @Override
        public Connection create() throws Exception {
            return dataSource.getConnection();
        }
        
        @Override
        public 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>