SQL - PreparedStatement - Efficiency - JDBC
java, jdbc, oracle, sql
Solution
The second way is much better using `PreparedStatement` because it can make use of prepared statement pooling which increases performance.
Prepared Statement Reuse by caching
In the first part using `Statement` your statement is tied to a single data, which will have to create a new statement everytime for different data.
In case of Prepared Statement the same statement can be executed multiple times using different data.
EDIT:
Can you please elaborate more about "Reuse by caching"???
Caching of `PreparedStatement`s is a transparent mechanism in which `Connection` maintains a pool of prepared statements, when you ask for a prepared statement with same SQL query then a cached one is returned. If there was no caching then a new one would have to be created everytime. The feature is driver dependent.
Less Validation Overhead
When you use Prepared statement the query is validated only once, but when you use Statement it is validated every time
Prevention of SQL Injection
Not necessarily a performance perk, but use of `PreparedStatement` will also save you from SQL injection attacks.
Oracle Prepared Statement Caching
When you create an `OraclePreparedStatement` or `OracleCallableStatement`, the JDBC driver automatically searches the cache for a matching statement. The match criteria are the following:
The SQL string in the statement must be identical (case-sensitive) to one in the cache.
The statement type must be the same (prepared or callable).
The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable). You can determine the scrollability when you create the statement. (See "Specifying Result Set Scrollability and Updatability" for complete details.)
If a match is found during the cache search, the cached statement is returned. If a match is not found, then a new statement is created and returned. The new statement, along with its cursor and state, are cached when you call the `close()` method of the statement object.
Problem
Which is a better way to execute queries in JDBC Case 1 ``` sql = "SELECT * FROM TABLE_1 WHERE ID = 1"; conn.prepareStatement(sql); ps.executeQuery(); ``` Case 2 ``` sql = "SELECT * FROM TABLE_1 WHERE ID = ?"; conn.prepareStatement(sql); ps.setInt(1,1); ps.executeQuery(); ``` Note ``` ps is PreparedStatement sql is String ``` I have to query for 1300 ID's (0 to 1299) each time. Please do specify why that case is better... I have read that PreparedStatement precompiles the query