操作指导

示例

• JDBC示例
  1. 调用Connection接口的prepareStatement方法创建预编译语句对象pstmt。

     PreparedStatement pstmt = conn.prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");

  2. 调用PreparedStatement接口的setString方法设置参数。

     pstmt.setString(1, "new Data");

  3. 调用PreparedStatement接口的executeUpdate方法执行预编译SQL语句。

     int rowcount = pstmt.executeUpdate();

  4. 调用PreparedStatement接口的close方法关闭预编译语句对象pstmt。

     pstmt.close();

  完整示例：

  1. 建表。

     gaussdb=# CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));
     CREATE TABLE

  2. JDBC脚本。

     import java.sql.Connection;
     import java.sql.PreparedStatement;
     import java.sql.SQLException;
     import java.util.Arrays;
     import java.sql.DriverManager;
     public class TestBatch {
         public static Connection getConnection() throws ClassNotFoundException, SQLException{
      String driver = "com.huawei.gaussdb.jdbc.Driver";
             // 指定数据库sourceURL（$ip、$port、database请根据业务场景进行修改）。
             String sourceURL = "jdbc:gaussdb://$ip:$port/database";
             // 用户名和密码从环境变量中获取。
             String userName = System.getenv("EXAMPLE_USERNAME_ENV");
             String password = System.getenv("EXAMPLE_PASSWORD_ENV");
             Class.forName(driver);
             return DriverManager.getConnection(sourceURL, userName, password);
         }
         public static void main(String[] args) {
             String sql = "UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1";
             try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
                 conn.setAutoCommit(false);
     		   pstmt.setString(1, "new Data");
     		   int rowcount = pstmt.executeUpdate();
     		   conn.commit();
                 pstmt.close();
     		   conn.close();
             } catch (ClassNotFoundException | SQLException e) {
                 throw new RuntimeException(e);
             }
         }
     }

• JDBC示例
  1. 准备批量操作SQL语句。

     使用preparedStatement对语句进行预编译。用户可根据实际业务场景替换成业务具体需要执行的语句，以下为向测试表中插入数据的语句：

     String sql = "INSERT INTO  TEST_BATCH(v1,v2) VALUES(?,?)";
     PreparedStatement preparedStatement = conn.prepareStatement(sql);

  2. 批量绑定参数。

     通过preparedStatement绑定参数，然后执行addBatch将SQL语句添加到批量执行列表。

     for(int i=0;i<5;i++){
         preparedStatement.setString(1,"value1_"+i);
         preparedStatement.setString(2,"value2_"+i);
         preparedStatement.addBatch();
     }

  3. 执行批量操作。

     preparedStatement执行executeBatch执行批量操作，返回一个int类型的数组results，通过打印results数组可以查看批量操作影响的数据条目数。

     Int[] results = preparedStatement.executeBatch();
     System.out.println(Arrays.toString(results));

  4. 释放资源和关闭数据库连接。

     preparedStatement.close();
     conn.close();

  完整示例：

  1. 建表。

     gaussdb=# CREATE TABLE TEST_BATCH(
     V1 TEXT,
     V2 TEXT);
     CREATE TABLE

  2. java脚本。

     import java.sql.Connection;
     import java.sql.PreparedStatement;
     import java.sql.SQLException;
     import java.util.Arrays;
     import java.sql.DriverManager;
     public class TestBatch {
         public static Connection getConnection() throws ClassNotFoundException, SQLException{
      String driver = "com.huawei.gaussdb.jdbc.Driver";
             // 指定数据库sourceURL（$ip、$port、database请根据业务场景进行修改）。
             String sourceURL = "jdbc:gaussdb://$ip:$port/database";
             // 用户名和密码从环境变量中获取。
             String userName = System.getenv("EXAMPLE_USERNAME_ENV");
             String password = System.getenv("EXAMPLE_PASSWORD_ENV");
             Class.forName(driver);
             return DriverManager.getConnection(sourceURL, userName, password);
         }
         public static void main(String[] args) {
             String sql = "insert into TEST_BATCH(v1,v2) values(?,?)";
             try (Connection conn = getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
                 conn.setAutoCommit(false);
                 for (int i = 0; i < 5; i++) {
     		preparedStatement.setString(1,"value1_"+i);
     		preparedStatement.setString(2,"value2_"+i);
     		preparedStatement.addBatch();
                 }
                 int[] results = preparedStatement.executeBatch();
                 conn.commit();
                 System.out.println(Arrays.toString(results));
                 preparedStatement.close();
                 conn.close();
             } catch (ClassNotFoundException | SQLException e) {
                 throw new RuntimeException(e);
             }
         }
     }

• gsql下PREPARE EXECUTE示例

  --创建表reason。
  gaussdb=# CREATE TABLE reason (
      CD_DEMO_SK          int NOT NULL,
      CD_GENDER           varchar(10),
      CD_MARITAL_STATUS   varchar(10)
  );
   
  --为一个INSERT语句创建一个预备语句并执行预编译语句。
  gaussdb=# PREPARE insert_reason(int,varchar(10),varchar(10)) AS INSERT INTO reason VALUES($1,$2,$3);
  gaussdb=# EXECUTE insert_reason(52, 'AAAAAAAADD', 'reason 52');
   
  --查询数据。
  gaussdb=# SELECT * FROM reason;
   cd_demo_sk | cd_gender  | cd_marital_status
  ------------+------------+-------------------
           52 | AAAAAAAADD | reason 52
  (1 row)
   
  gaussdb=# SELECT name,statement,parameter_types,from_sql,is_deleted,size, valid, is_reused FROM gs_all_prepared_statements WHERE statement LIKE '%reason%';
       name      |                                         statement                                          |                  parameter_typ
  es                  | from_sql | is_deleted | size  | valid | is_reused
  ---------------+--------------------------------------------------------------------------------------------+-------------------------------
  --------------------+----------+------------+-------+-------+-----------
   insert_reason | PREPARE insert_reason(int,varchar(10),varchar(10)) AS INSERT INTO reason VALUES($1,$2,$3); | {integer,"character varying","
  character varying"} | t        | f          | 23552 | t     | f
  (1 row)
   
   
  --删除表reason。
  gaussdb=# DROP TABLE reason;

• 存储过程示例

  gaussdb=# CREATE TABLE t1(a int);
  CREATE TABLE
  gaussdb=# CREATE OR REPLACE PROCEDURE test_proc AS
  BEGIN
      INSERT INTO t1 VALUES (1);
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL test_proc();
   test_proc
  -----------
  
  (1 row)
  
  --查视图。
  gaussdb=# SELECT func_signature,query FROM pg_catalog.gs_plsql_statements_details(pg_current_sessid());                                  func_signature                                 |           query --------------------------------------------------------------------------------+---------------------------  name: test_proc(), fn_collation: 0, search_path: addUser: false, schemas: 2200 | INSERT INTO t1 VALUES (1) (1 row)
  
  gaussdb=# DROP TABLE t1;
  DROP TABLE
  gaussdb=# DROP PROCEDURE test_proc();
  DROP PROCEDURE