Using OraclePreparedStatement

To run static SQL queries on the database, you use the Statement object. However, to run multiple similar queries or perform multiple updates that affect many columns in the database, it is not feasible to hard-code each query in your application.

You can use OraclePreparedStatement when you run the same SQL statement multiple times. Consider a query like the following:

SELECT * FROM Employees WHERE ID=xyz;

Every time the value of xyz in this query changes, the SQL statement needs to be compiled again.

If you use OraclePreparedStatement functionality, the SQL statement you want to run is precompiled and stored in a PreparedStatement object, and you can run it as many times as required without compiling it every time it is run. If the data in the statement changes, you can use bind variables as placeholders for the data and then provide literal values at run time.

Consider the following example of using OraclePreparedStatement:

The advantages of using the OraclePreparedStatement interface include:

  • You can batch updates by using the same PreparedStatement object

  • You can improve performance because the SQL statement that is run many times is compiled only the first time it is run.

  • You can use bind variables to make the code simpler and reusable.

Example 6-1 Creating a PreparedStatement

OraclePreparedStatement pstmt = conn.prepareStatement("UPDATE Employees 
                                SET salary = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)