Executing SQL Statements

Executing a Common SQL Statement

Applications manipulate database data by executing SQL statements (statements that do not require passing parameters) through the following steps:

Call the createStatement method of Connection to create a statement object.
1

Statement stmt = con.createStatement();

Call the executeUpdate method of Statement to execute the SQL statement.

stmt.executeUpdate("create table if not exists test_01(" +
    "p_partkey int4," +
    "p_name text," +
    "P_MFGR char(25)," +
    "P_BRAND char(25)," +
    "p_type text," +
    "p_size int4," +
    "P_CONTAINER char(10)," +
    "p_retailprice decimal(15,2)," +
    "p_comment text)" +
    "store as orc;");

Close the statement object.
1

stmt.close();

Executing Precompiled SQL Statements

A precompiled statement is compiled and optimized once and can be reused with different parameter values. Since it is precompiled, subsequent executions reduce execution time. Therefore, if a statement needs to be executed multiple times, use a precompiled statement. Follow these steps:

Call the prepareStatement method of Connection to create a precompiled statement object.

      
           PreparedStatement pstmt = con.prepareStatement("select * from test_01 where p_partkey = ?");

Call the setShort method of PreparedStatement to set parameters.
1

pstmt.setString(1, "1502");
Call the executeUpdate method of PreparedStatement to execute the precompiled SQL statement.
1

ResultSet resultSet = pstmt.executeQuery();
Call the close method of PreparedStatement to close the precompiled statement object.
1

pstmt.close();

Executing Batch Processing

Using a single preprocessed statement to handle multiple similar data records allows the database to create an execution plan only once, saving compilation and optimization time. Follow these steps:

Call the prepareStatement method of Connection to create a precompiled statement object.

      
           PreparedStatement pstmt = con.prepareStatement("INSERT INTO test_01 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");

For each data record, call the setShort method to set parameters and then call the addBatch method to confirm completion of the settings.

preparedStatement.setInt(1, 1);
preparedStatement.setString(2, "name");
preparedStatement.setString(3, "cata");
preparedStatement.setString(4, "test4");
preparedStatement.setString(5, "test5");
preparedStatement.setInt(6, 2);
preparedStatement.setString(7, "test7");
preparedStatement.setBigDecimal(8, new BigDecimal(502.1));
preparedStatement.setString(9, "test9");
preparedStatement.addBatch();

Call the executeBatch method of PreparedStatement to execute batch processing.
1

int[] rowcount = pstmt.executeBatch();
Call the close method of PreparedStatement to close the precompiled statement object.
1

pstmt.close();