测试类型

工具功能

测试版本

7.0.0LTS

问题描述

opengauss向opengaus录制回放 解析报错

操作系统和硬件信息

2203

测试环境

企业版单机

被测功能

opengauss向opengauss B库录制回放

预置条件

建立java文件，opengauss建立B库db_0826
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.math.BigDecimal;

/**

• 编译执行:
• javac -cp .:opengauss-jdbc-7.0.0-RC3.jar OgJdbcDemo.java
• java -cp .:opengauss-jdbc-7.0.0-RC3.jar OgJdbcDemo
  */
  public class OgJdbcDemo {

// ===== 连接参数 =====
private static final String URL  = "jdbc:opengauss://20.20.20.219:10820/db_0826";
private static final String USER = "opengauss_test";
private static final String PWD  = "xxxxxxx";

// ===== 表名 =====
private static final String TABLE = "og_jdbc_demo";

public static void main(String[] args) {
    // 显式加载驱动(JDBC 4.0+ 可省略,保留以兼容旧版本)
    try {
        Class.forName("org.opengauss.Driver");
    } catch (ClassNotFoundException e) {
        System.err.println("[FAIL] 驱动加载失败: " + e.getMessage());
        return;
    }

    // try-with-resources 自动关闭外层连接
    try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
        conn.setAutoCommit(false);   // 开启事务
        System.out.println("[0/7] 连接成功,当前库=" + conn.getCatalog());

        try {
            step1CreateTable(conn);
            step2InsertPbe(conn);
            step3InsertBatch(conn);
            step4Select(conn);
            step5Update(conn);
            step6Delete(conn);
            step7FinalSelect(conn);

            conn.commit();
            System.out.println("[OK] 所有操作完成,事务已提交");
        } catch (Exception e) {
            conn.rollback();
            System.err.println("[FAIL] 异常,事务已回滚: " + e.getMessage());
            e.printStackTrace();
        }
    } catch (Exception e) {
        System.err.println("[FAIL] 获取连接失败: " + e.getMessage());
        e.printStackTrace();
    }
}

// ===== 1. 建表 =====
private static void step1CreateTable(Connection conn) throws Exception {
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("DROP TABLE IF EXISTS " + TABLE);
        // openGauss 原生类型: int/bigint/decimal/varchar/timestamp/bytea
        st.executeUpdate(
            "CREATE TABLE " + TABLE + " (" +
            "  id       bigint       PRIMARY KEY," +
            "  name     varchar(50)  NOT NULL," +
            "  price    decimal(10,2)," +
            "  cnt      int          DEFAULT 0," +
            "  create_time timestamp DEFAULT current_timestamp," +
            "  remark   varchar(200)," +
            "  blob_col bytea" +
            ")"
        );
        System.out.println("[1/7] 建表 " + TABLE + " OK");
    }
}

// ===== 2. PBE 单条插入(演示 setXxx 参数绑定) =====
private static void step2InsertPbe(Connection conn) throws Exception {
    String sql = "INSERT INTO " + TABLE +
                 "(id, name, price, cnt, remark, blob_col) VALUES(?,?,?,?,?,?)";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setLong(1, 1001L);                                       // bigint
        ps.setString(2, "openGauss-JDBC-演示-1");                   // varchar
        ps.setBigDecimal(3, BigDecimal.valueOf(99.50));            // decimal
        ps.setInt(4, 10);                                           // int
        ps.setString(5, "PBE 单条插入");                            // varchar
        ps.setBytes(6, "hello openGauss".getBytes("UTF-8"));        // bytea

        int rows = ps.executeUpdate();
        System.out.println("[2/7] PBE 单条插入,影响行数=" + rows);
    }
}

// ===== 3. 批量插入(addBatch / executeBatch) =====
private static void step3InsertBatch(Connection conn) throws Exception {
    String sql = "INSERT INTO " + TABLE +
                 "(id, name, price, cnt, remark) VALUES(?,?,?,?,?)";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (int i = 1; i <= 3; i++) {
            ps.setLong(1, 2000L + i);
            ps.setString(2, "批量记录-" + i);
            ps.setBigDecimal(3, BigDecimal.valueOf(10.00 + i));
            ps.setInt(4, i);
            ps.setString(5, "addBatch 第" + i + "条");
            ps.addBatch();
        }
        int[] result = ps.executeBatch();
        int total = 0;
        for (int r : result) total += r;
        System.out.println("[3/7] 批量插入完成,总行数=" + total);
    }
}

// ===== 4. 查询(普通 + 参数化) =====
private static void step4Select(Connection conn) throws Exception {
    // 4.1 普通查询
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT id, name, price, cnt FROM " + TABLE + " ORDER BY id")) {
        System.out.println("[4/7-a] 全表查询结果:");
        while (rs.next()) {
            System.out.println("  id=" + rs.getLong(1)
                    + ", name=" + rs.getString(2)
                    + ", price=" + rs.getBigDecimal(3)
                    + ", cnt=" + rs.getInt(4));
        }
    }

    // 4.2 参数化查询
    try (PreparedStatement ps = conn.prepareStatement(
            "SELECT id, name, price FROM " + TABLE + " WHERE id >= ? AND id <= ? ORDER BY id")) {
        ps.setLong(1, 1000L);
        ps.setLong(2, 2002L);
        try (ResultSet rs = ps.executeQuery()) {
            System.out.println("[4/7-b] 参数化查询(id 1000~2002):");
            while (rs.next()) {
                System.out.println("  id=" + rs.getLong(1)
                        + ", name=" + rs.getString(2)
                        + ", price=" + rs.getBigDecimal(3));
            }
        }
    }
}

// ===== 5. 更新(PreparedStatement) =====
private static void step5Update(Connection conn) throws Exception {
    try (PreparedStatement ps = conn.prepareStatement(
            "UPDATE " + TABLE + " SET price = ?, cnt = ?, remark = ? WHERE id = ?")) {
        ps.setBigDecimal(1, BigDecimal.valueOf(199.99));
        ps.setInt(2, 100);
        ps.setString(3, "PBE 更新后");
        ps.setLong(4, 1001L);

        int rows = ps.executeUpdate();
        System.out.println("[5/7] 更新 id=1001,影响行数=" + rows);
    }
}

// ===== 6. 删除(PreparedStatement) =====
private static void step6Delete(Connection conn) throws Exception {
    try (PreparedStatement ps = conn.prepareStatement(
            "DELETE FROM " + TABLE + " WHERE id = ?")) {
        ps.setLong(1, 2001L);
        int rows = ps.executeUpdate();
        System.out.println("[6/7] 删除 id=2001,影响行数=" + rows);
    }
}

// ===== 7. 最终查询确认 =====
private static void step7FinalSelect(Connection conn) throws Exception {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(
             "SELECT id, name, price, cnt, remark, " +
             "  encode(blob_col, 'escape') AS blob_txt " +
             "FROM " + TABLE + " ORDER BY id")) {
        System.out.println("[7/7] 最终表数据:");
        while (rs.next()) {
            System.out.println("  id=" + rs.getLong(1)
                    + ", name=" + rs.getString(2)
                    + ", price=" + rs.getBigDecimal(3)
                    + ", cnt=" + rs.getInt(4)
                    + ", remark=" + rs.getString(5)
                    + ", blob_txt=" + rs.getString(6));
        }
    }
}

}

操作步骤

step1 建立录制，opengauss向opengauss。窗口1执行录制，窗口2运行上述java文件
step2 分析

预期输出

分析结果正常

实际输出

报错

日志信息

提单组织

测试团队

测试代码