HikariCP 连接池示例 ​

package org.opengauss.entity;

public class User {
    private Integer id;
    private String name;
    private Integer age;

    public User() {
    }

    public User(String name, Integer age) {
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

package org.opengauss;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.opengauss.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HikariCpConnectDemo {

    private static final HikariDataSource DATA_SOURCE;

    static {
        HikariConfig config = new HikariConfig();
        // MySQL 协议连接串，端口为 dolphin_server_port 配置值（本文示例为 3306）
        config.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mysql_test_db"
                + "?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8&allowPublicKeyRetrieval=true");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        config.setUsername("mysql_test_db");
        config.setPassword("******");

        // 连接池参数
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(10);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(540000);
        config.setKeepaliveTime(300000);

        DATA_SOURCE = new HikariDataSource(config);
    }

    public static void main(String[] args) {
        // 从连接池获取连接，try-with-resources 自动归还连接到池
        try (Connection connection = DATA_SOURCE.getConnection()) {
            selectUserTable(connection);

            insertUserTable(connection, "zhaoliu", 18);
            selectUserTable(connection);

            int id = getUserIdByName(connection, "zhaoliu");
            updateUserTable(connection, id, "zhaoliuliuliu", 28);
            selectUserTable(connection);

            deleteUserTable(connection, id);
            selectUserTable(connection);

            // 事务示例：批量插入并在异常时回滚（需先关闭 autoCommit）
            transactionDemo(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 应用退出前关闭连接池，释放所有连接
            DATA_SOURCE.close();
        }
    }

    public static void insertUserTable(Connection connection, String name, int age) throws SQLException {
        String insertSql = "INSERT INTO user (name, age) VALUES (?, ?);";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            int rows = preparedStatement.executeUpdate();
            System.out.println("插入了" + rows + "行数据");
        }
    }

    public static void updateUserTable(Connection connection, int id, String name, int age) throws SQLException {
        String updateSql = "UPDATE user SET name = ?, age = ? WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(updateSql)) {
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setInt(3, id);
            int rows = preparedStatement.executeUpdate();
            System.out.println("更新了" + rows + "行数据");
        }
    }

    public static void deleteUserTable(Connection connection, int id) throws SQLException {
        String deleteSql = "DELETE FROM user WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(deleteSql)) {
            preparedStatement.setInt(1, id);
            int rows = preparedStatement.executeUpdate();
            System.out.println("删除了" + rows + "行数据");
        }
    }

    public static void selectUserTable(Connection connection) throws SQLException {
        String selectSql = "SELECT * FROM user;";
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSql)) {
            System.out.println("查询user表中所有数据:");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println("id: " + id + ", name: " + name + ", age: " + age);
            }
        }
    }

    public static int getUserIdByName(Connection connection, String name) throws SQLException {
        String selectSql = "SELECT id FROM user WHERE name LIKE ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(selectSql)) {
            preparedStatement.setString(1, "%" + name + "%");
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt("id");
                }
            }
        }
        return -1;
    }

    /**
     * 事务示例：批量插入并在异常时回滚。
     * 注意：HikariCP 返回的连接默认 autoCommit=true，
     * 需要事务时需先 setAutoCommit(false)，结束后再恢复。
     */
    public static void transactionDemo(Connection connection) throws SQLException {
        connection.setAutoCommit(false);
        try {
            insertUserTable(connection, "tom", 22);
            insertUserTable(connection, "jerry", 23);
            connection.commit();
            System.out.println("事务提交成功");
        } catch (SQLException e) {
            connection.rollback();
            System.out.println("事务回滚");
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }
}

// 建表（含 SERIAL 自增主键、VARCHAR、INT、DECIMAL、TIMESTAMP 字段）
String createSql = "CREATE TABLE sql_category_test ("
        + "id SERIAL PRIMARY KEY, "
        + "name VARCHAR(64) NOT NULL, "
        + "age INT DEFAULT 0, "
        + "score DECIMAL(5,2), "
        + "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
        + ")";
stmt.executeUpdate(createSql);

// 建索引
stmt.executeUpdate("CREATE INDEX idx_sql_category_test_name ON sql_category_test (name)");

// ALTER TABLE 增加列
stmt.executeUpdate("ALTER TABLE sql_category_test ADD COLUMN remark TEXT");

// ALTER TABLE 重命名列
stmt.executeUpdate("ALTER TABLE sql_category_test RENAME COLUMN remark TO description");

// TRUNCATE 清空表数据（保留结构）
stmt.executeUpdate("TRUNCATE TABLE sql_category_test");

// DROP TABLE 删除表
stmt.executeUpdate("DROP TABLE sql_category_test_2");

// CREATE TABLE AS SELECT 从查询结果创建新表
stmt.executeUpdate("CREATE TABLE sql_category_test_2 AS SELECT name, age FROM sql_category_test WHERE age > 20");

// 单行 INSERT（使用 PreparedStatement 防注入）
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO sql_category_test (name, age, score) VALUES (?, ?, ?)")) {
    ps.setString(1, "Alice");
    ps.setInt(2, 28);
    ps.setDouble(3, 95.50);
    int rows = ps.executeUpdate(); // 返回影响行数
}

// 批量 INSERT（addBatch + executeBatch）
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO sql_category_test (name, age, score) VALUES (?, ?, ?)")) {
    for (int i = 0; i < 3; i++) {
        ps.setString(1, "BatchUser" + i);
        ps.setInt(2, 20 + i);
        ps.setDouble(3, 80.0 + i * 5);
        ps.addBatch();
    }
    int[] results = ps.executeBatch(); // 每条的影响行数数组
}

// UPDATE 条件更新
try (PreparedStatement ps = conn.prepareStatement(
        "UPDATE sql_category_test SET age = ?, score = ? WHERE name = ?")) {
    ps.setInt(1, 29);
    ps.setDouble(2, 96.00);
    ps.setString(3, "Alice");
    int rows = ps.executeUpdate();
}

// DELETE 条件删除
try (PreparedStatement ps = conn.prepareStatement(
        "DELETE FROM sql_category_test WHERE name LIKE 'BatchUser%'")) {
    int rows = ps.executeUpdate();
}

// INSERT 获取自增主键（RETURN_GENERATED_KEYS）
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO sql_category_test (name, age, score) VALUES ('Bob', 35, 87.25)",
        PreparedStatement.RETURN_GENERATED_KEYS)) {
    ps.executeUpdate();
    ResultSet keys = ps.getGeneratedKeys();
    if (keys.next()) long genId = keys.getLong(1); // 获取到 id
}

// 基础 SELECT *
ResultSet rs = stmt.executeQuery("SELECT id, name, age, score FROM sql_category_test ORDER BY id");
while (rs.next()) { /* 遍历结果集 */ }

// WHERE 条件过滤
rs = stmt.executeQuery("SELECT name, age FROM sql_category_test WHERE age >= 28 ORDER BY age");

// ORDER BY + LIMIT 排序与分页
rs = stmt.executeQuery("SELECT name, score FROM sql_category_test ORDER BY score DESC LIMIT 3");

// 聚合函数 COUNT/SUM/AVG/MIN/MAX
rs = stmt.executeQuery("SELECT COUNT(*) AS cnt, SUM(age) AS sum_age, AVG(score) AS avg_score, "
        + "MIN(age) AS min_age, MAX(age) AS max_age FROM sql_category_test");

// GROUP BY + HAVING 分组聚合与过滤
rs = stmt.executeQuery("SELECT CASE WHEN age < 25 THEN 'young' ELSE 'senior' END AS group_name, "
        + "COUNT(*) AS cnt, AVG(score) AS avg_s "
        + "FROM sql_category_test GROUP BY CASE WHEN age < 25 THEN 'young' ELSE 'senior' END "
        + "HAVING COUNT(*) >= 1");

// DISTINCT 去重
rs = stmt.executeQuery("SELECT DISTINCT name FROM sql_category_test ORDER BY name");

// 子查询
rs = stmt.executeQuery("SELECT name, age FROM sql_category_test "
        + "WHERE age > (SELECT AVG(age) FROM sql_category_test) ORDER BY age");

// 授予表级 SELECT 权限
stmt.executeUpdate("GRANT SELECT ON TABLE sql_category_test TO mysqluser");

// 授予多权限
stmt.executeUpdate("GRANT INSERT, UPDATE, DELETE ON TABLE sql_category_test TO mysqluser");

// 收回权限
stmt.executeUpdate("REVOKE INSERT, UPDATE, DELETE ON TABLE sql_category_test FROM mysqluser");

// 查询当前用户权限信息
ResultSet rs = stmt.executeQuery(
        "SELECT privilege_type, is_grantable FROM information_schema.table_privileges "
        + "WHERE table_name = 'sql_category_test' AND grantee = CURRENT_USER");
while (rs.next()) { String type = rs.getString("privilege_type"); }

// 显式 COMMIT
conn.setAutoCommit(false);
try (Statement s = conn.createStatement()) {
    s.executeUpdate("INSERT INTO sql_category_test (name, age, score) VALUES ('commit_test', 40, 77.00)");
}
conn.commit(); // 提交后数据可见

// ROLLBACK 回滚
conn.setAutoCommit(false);
try (Statement s = conn.createStatement()) {
    s.executeUpdate("INSERT INTO sql_category_test (name, age, score) VALUES ('rollback_test', 41, 66.00)");
}
conn.rollback(); // 回滚后数据不可见

// SAVEPOINT + ROLLBACK TO SAVEPOINT 部分回滚
conn.setAutoCommit(false);
try (Statement s = conn.createStatement()) {
    s.executeUpdate("INSERT INTO sql_category_test (name, age, score) VALUES ('sp_keep', 42, 55.00)");
    s.executeUpdate("SAVEPOINT sp1");
    s.executeUpdate("INSERT INTO sql_category_test (name, age, score) VALUES ('sp_discard', 43, 44.00)");
    s.executeUpdate("ROLLBACK TO SAVEPOINT sp1"); // sp_discard 被撤销，sp_keep 保留
}
conn.commit();

// 查询当前事务隔离级别
ResultSet rs = conn.createStatement().executeQuery("SELECT @@session.transaction_isolation AS iso");
if (rs.next()) String isolation = rs.getString("iso"); // 如 READ-COMMITTED
conn.setAutoCommit(true);

int threadCount = 10;
ExecutorService executor = Executors.newFixedThreadPool(threadCount);
CountDownLatch startGate = new CountDownLatch(1);
AtomicInteger passed = new AtomicInteger(0);

for (int i = 0; i < threadCount; i++) {
    final int idx = i;
    executor.submit(() -> {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            // 每个 worker: INSERT → SELECT 验证 → UPDATE → DELETE → commit
            // ...（完整代码见 SqlCategoryVerificationRunner.java）
            passed.incrementAndGet();
        }
    });
}
startGate.countDown(); // 所有线程同时开始
executor.awaitTermination(30, TimeUnit.SECONDS);
// passed.get() == threadCount 即全部通过