Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Using JDBC or ODBC for GaussDB(DWS) Secondary Development/ JDBC-Based Development/ Executing SQL Statements
Updated on 2024-12-19 GMT+08:00
View PDF

Executing SQL Statements

Executing an Ordinary SQL Statement

The application performs data (parameter statements do not need to be transferred) in the database by running SQL statements, and you need to perform the following steps:

  1. Create a statement object by triggering the createStatement method in Connection. 

    1
    		 
    Statement stmt = con.createStatement();

  2. Execute the SQL statement by triggering the executeUpdate method in Statement. 

    1
    		 
    int rc = stmt.executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");

    If an execution request (not in a transaction block) received in the database contains multiple statements, the request is packed into a transaction. VACUUM is not supported in a transaction block. If one of the statements fails, the entire request will be rolled back.

  3. Close the statement object. 

    1
    		 
    stmt.close();

Executing a Prepared SQL Statement

Pre-compiled statements were once complied and optimized and can have additional parameters for different usage. For the statements have been pre-compiled, the execution efficiency is greatly improved. If you want to execute a statement for several times, use a precompiled statement. Perform the following procedure:

  1. Create a prepared statement object by calling the prepareStatement method in Connection. 

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

  2. Set parameters by triggering the setShort method in PreparedStatement. 

    1
    		 
    pstmt.setShort(1, (short)2);

  3. Execute the precompiled SQL statement by triggering the executeUpdate method in PreparedStatement. 

    1
    		 
    int rowcount = pstmt.executeUpdate();

  4. Close the precompiled statement object by calling the close method in PreparedStatement. 

    1
    		 
    pstmt.close();

Calling a Stored Procedure

Perform the following steps to call existing stored procedures through the JDBC interface in GaussDB(DWS):

  1. Create a call statement object by calling the prepareCall method in Connection. 

    1
    		 
    CallableStatement cstmt = myConn.prepareCall("{? = CALL TESTPROC(?,?,?)}");

  2. Set parameters by calling the setInt method in CallableStatement. 

    1
2
3
    		 
    cstmt.setInt(2, 50); 
cstmt.setInt(1, 20);
cstmt.setInt(3, 90);

  3. Register with an output parameter by calling the registerOutParameter method in CallableStatement. 

    1
    		 
    cstmt.registerOutParameter(4, Types.INTEGER);  //Register an OUT parameter as an integer.

  4. Call the stored procedure by calling the execute method in CallableStatement. 

    1
    		 
    cstmt.execute();

  5. Obtain the output parameter by calling the getInt method in CallableStatement. 

    1
    		 
    int out = cstmt.getInt(4);  //Obtain the OUT parameter.

    For example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    //The following stored procedure has been created with the OUT parameter:
create or replace procedure testproc 
(
    psv_in1 in integer,
    psv_in2 in integer,
    psv_inout in out integer
)
as
begin
    psv_inout := psv_in1 + psv_in2 + psv_inout;
end;
/

  6. Close the call statement by calling the close method in CallableStatement. 

    1
    		 
    cstmt.close();
    • Many database classes such as Connection, Statement, and ResultSet have a close() method. Close these classes after using their objects. Close these actions after using their objects. Closing Connection will close all the related Statements, and closing a Statement will close its ResultSet.
    • Some JDBC drivers support named parameters, which can be used to set parameters by name rather than sequence. If a parameter has a default value, you do not need to specify any parameter value but can use the default value directly. Even though the parameter sequence changes during a stored procedure, the application does not need to be modified. Currently, the GaussDB(DWS) JDBC driver does not support this method.
    • GaussDB(DWS) does not support functions containing OUT parameters, or default values of stored procedures and function parameters.

  • If JDBC is used to call a stored procedure whose returned value is a cursor, the returned cursor cannot be used.
  • A stored procedure and an SQL statement must be executed separately.

Batch Processing

When a prepared statement batch processes multiple pieces of similar data, the database creates only one execution plan. This improves the compilation and optimization efficiency. Perform the following procedure:

  1. Create a prepared statement object by calling the prepareStatement method in Connection. 

    1
    		 
    PreparedStatement pstmt = con.prepareStatement("INSERT INTO customer_t1 VALUES (?)");

  2. Call the setShort parameter for each piece of data, and call addBatch to confirm that the setting is complete. 

    1
2
    		 
    pstmt.setShort(1, (short)2);
pstmt.addBatch();

  3. Execute batch processing by calling the executeBatch method in PreparedStatement. 

    1
    		 
    int[] rowcount = pstmt.executeBatch();

  4. Close the precompiled statement object by calling the close method in PreparedStatement. 

    1
    		 
    pstmt.close();

    Do not terminate a batch processing action when it is ongoing; otherwise, the database performance will deteriorate. Therefore, disable the automatic submission function during batch processing, and manually submit every several lines. The statement for disabling automatic submission is conn.setAutoCommit(false).

Parent topic: JDBC-Based Development