Running SQL Statements

Running a Common SQL Statement

To enable an application to operate data in the database by running SQL statements (statements that do not need to transfer parameters), perform the following operations:

Operations such as SELECT, UPDATE, INSERT, and DELETE can be performed on XML data.

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

   1 2 3 4 5 6 7

   // There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation). String userName = System.getenv("EXAMPLE_USERNAME_ENV"); String password = System.getenv("EXAMPLE_PASSWORD_ENV"); String sourceURL = "jdbc:mysql://$ip:$port/database"; Connection conn = DriverManager.getConnection(sourceURL,userName,password); Statement stmt = conn.createStatement();

2. Run the SQL statement by calling the executeUpdate method in Statement.

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

3. Close the statement object.

   1	stmt.close();

Running a Prepared SQL Statement

Prepared statements are complied and optimized once but can be used in different scenarios by assigning multiple values. Using prepared statements improves execution efficiency. If you want to run a statement for several times, use a prepared statement. Perform the following operations:

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 calling the setShort method in PreparedStatement.

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

3. Run the prepared statement by calling the executeUpdate method in PreparedStatement.

   1	int rowcount = pstmt.executeUpdate();

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

   1	pstmt.close();

Batch Processing

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

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

   1 2 3 4 5 6 7

   // There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation). String userName = System.getenv("EXAMPLE_USERNAME_ENV"); String password = System.getenv("EXAMPLE_PASSWORD_ENV"); String sourceURL = "jdbc:mysql://$ip:$port/database"; Connection conn = DriverManager.getConnection(sourceURL,userName,password); PreparedStatement pstmt = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?)");

2. Call the corresponding API to set parameters for each piece of data and call addBatch to add SQL statements to the batch processing.

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

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

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

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

   1	pstmt.close();

   

   Do not terminate a batch processing action when it is ongoing; otherwise, database performance will deteriorate. Therefore, disable automatic commit during batch processing. Manually commit several rows at a time. The statement for disabling automatic commit is conn.setAutoCommit(false);.

Currently, the server PBE is not supported. When the application sets the connection parameter useServerPrepStmts to true, the parameter does not take effect.