Java druid、hikari连接池链接方法配合jdbctemplate使用，trino链接池方法

测试trino及druid、hikari连接池链接方法，jdbctemplate使用。

找了很久的接入各种类型数据库（es、hive、mysql、clickhouse、……）的方案，最后被告知trino。还没让做的时候就在思考怎么连上trino进行配置啥的，然后就简单写了个测试类，如下：
想着代码随时删，网上使用连接池链接trino的例子也很少有人分享，为了让有需要者快速复制开发，就贴出来：
依赖：

Hikari 的依赖这里面 ：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
不然自己加：
 <dependency> 
 <groupId>com.zaxxer</groupId>
     <artifactId>HikariCP</artifactId> 
     <version>4.0.3</version> 
 </dependency>


druid的依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>】

trino
<dependency>
    <groupId>io.trino</groupId>
    <artifactId>trino-jdbc</artifactId>
<version>378</version>
</dependency>

代码自己测试过，可以用：

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.junit.Assert;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

import static java.util.concurrent.TimeUnit.MINUTES;
import static java.util.concurrent.TimeUnit.SECONDS;

/**
 * 测试trino及druid、hikari连接池链接方法，jdbctemplate使用。
 * Hikari 的依赖这里面 ：
 * <dependency>
 * <groupId>org.springframework.boot</groupId>
 * <artifactId>spring-boot-starter-jdbc</artifactId>
 * </dependency>
 *
 * druid的依赖：
 * <dependency>
 *     <groupId>com.alibaba</groupId>
 * <artifactId>druid</artifactId>
 * <version>1.2.8</version>
 * </dependency>
 *
 * <dependency>
 *     <groupId>io.trino</groupId>
 *     <artifactId>trino-jdbc</artifactId>
 * <version>378</version>
 * </dependency>
 *
 * @date 2022/4/24 11:10
 */
@Slf4j
@SpringBootTest
@ExtendWith(SpringExtension.class)
public class ConnectionPool_Druid_Hikari_Trino_Test {
    /** 构造Hikari连接池 */
    public static HikariDataSource getHikariConfig() throws SQLException {
        // 1. 初始化数据库
        initDataBase("default");
        // 2. 返回连接池
        HikariConfig config = new HikariConfig();
        // 最小空闲连接
        config.setMinimumIdle(10);
        // 最大连接数
        config.setMaximumPoolSize(50);
        // 自动提交从池中返回的连接
        config.setAutoCommit(true);
        // 空闲连接超时时间
        config.setIdleTimeout(MINUTES.toMillis(10));
        // 连接最大存活时间
        config.setMaxLifetime(MINUTES.toMillis(30));
        // 连接超时时间
        config.setConnectionTimeout(SECONDS.toMillis(30));
        config.setJdbcUrl("jdbc:trino://127.0.0.1:49228/es");
        config.setUsername("elasticsearch");
        return new HikariDataSource(config);
    }

    /** 初始化数据库，如果数据库不存在就创建数据 */
    private static void initDataBase(String dbName) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            Properties props = new Properties();
            // 获取数据库的备注信息
            // props.put("remarksReporting","true");
            props.put("user", "elasticsearch");
            // props.put("password",dbInfoConfig.getPassword());
            // 1.获取连接
            Class.forName("io.trino.jdbc.TrinoDriver");
            connection = DriverManager.getConnection("jdbc:trino://127.0.0.1:49228/es", props);
            // 2.查询是否存在数据库
            ResultSet catalogs = connection.getMetaData().getCatalogs();
            Set<String> databases = new HashSet<>();
            while (catalogs.next()) {
                databases.add(catalogs.getString(1));
            }
            if (!databases.contains(dbName)) {
                // 如果没有这个库， 新增这个库
                String sql = String.format("CREATE DATABASE %s", dbName);
                statement = connection.createStatement();
                statement.execute(sql);
            }
        } catch (Exception e) {
            log.error("初始化数据库失败", e);
        } finally {
            if (null != statement) {
                statement.close();
            }
            if (null != connection) {
                connection.close();
            }
        }
    }

    /**
     * springboot的依赖就包含了hikari 不然自己加 <dependency> <groupId>com.zaxxer</groupId>
     * <artifactId>HikariCP</artifactId> <version>4.0.3</version> </dependency>
     *
     * @since 2022/4/24 14:45
     */
    @Test
    void testHikariPool() throws SQLException {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getHikariConfig());
        List<Map<String, Object>> maps =
                jdbcTemplate.queryForList("select * from es.default.zyttest");
        Assert.assertEquals(
                "[{address=四川, age=12, name=张三}, {address=四川, age=12, name=李四}]", maps.toString());
    }

    /**
     * druid也是的 <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId>
     * <version>1.2.8</version> </dependency>
     *
     * @since 2022/4/24 14:45
     */
    @Test
    void testDruidPool() throws SQLException {
        // 直接使用statement来用，如果用jdbctemplate的话直接返回DruidDataSource即可
        try (final DruidPooledConnection connection = produceConnectPool()) {
            try (final PreparedStatement statement =
                    connection.prepareStatement("select * from table")) {
                try (final ResultSet resultSet = statement.executeQuery()) {
                    final ResultSetMetaData metaData = resultSet.getMetaData();
                    while (resultSet.next()) {
                        Object object = resultSet.getObject(1);
                    }
                }
            }
        }
    }

    @Test
    void testCommonConn() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getPrestoDataSource());
        String s = "select address from es.default.zyttest";
        RowMapper<String> rowMapper = new BeanPropertyRowMapper<>(String.class);
        List<Map<String, Object>> maps =
                jdbcTemplate.queryForList("select * from es.default.zyttest");
        Assert.assertEquals(
                "[{address=四川, age=12, name=张三}, {address=四川, age=12, name=李四}]", maps.toString());
    }

    public DataSource getPrestoDataSource() {
        return DataSourceBuilder.create()
                .driverClassName("io.trino.jdbc.TrinoDriver")
                .url("jdbc:trino://127.0.0.1:49228/es")
                .username("elasticsearch")
                .build();
    }

    public DruidPooledConnection produceConnectPool() {
        try (DruidDataSource dataSource = new DruidDataSource()) {
            // oracle : jdbc:oracle:thin:@127.0.0.1:1522/#service
            // mysql :
            // jdbc:mysql://127.0.0.1:3306/tableName?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&serverTimezone=Asia/Shanghai
            dataSource.setUrl("");
            dataSource.setUsername("");
            dataSource.setPassword("");
            // oracle : oracle.jdbc.OracleDriver
            // mysql : com.mysql.cj.jdbc.Driver
            // postgresql : org.postgresql.Driver
            dataSource.setDriverClassName("");
            dataSource.setValidationQuery("select 1 from dual");
            dataSource.setInitialSize(10);
            dataSource.setMinIdle(10);
            dataSource.setMaxActive(20);
            dataSource.setPoolPreparedStatements(false);
            dataSource.setRemoveAbandoned(true);
            dataSource.setRemoveAbandonedTimeout(300);
            dataSource.setLogAbandoned(false);
            return dataSource.getConnection();
        } catch (Exception e) {
            throw new RuntimeException("连接失败！原因 >> " + e.getMessage(), e);
        }
    }
}