It is also possible to pool the statements. Like as a prepared statement allows us to keep frequently used SQL statement in a pre-compile form, thus improving performance if that statement is executed multiple times. But there is a dark side of this. Creating a PreparedStatement object introduces a certain amount of overhead. There are some developers sometimes change their object models to increase the lifetime of a PreparedStatement object. Good thing is that JDBC 3.0 frees the developer from this, by making data source layer responsible for caching prepared statements.
The code snippet shows how to take advantage of JDBC's prepared statement pooling support.
Java Code:
String INSERT_BOOK_QUERY = "INSERT INTO BOOKLIST " +
"(AUTHOR, TITLE) " +
"VALUES (?, ?) ";
Connection conn = aPooledConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(INSERT_BOOK_QUERY);
ps.setString(1, "Orwell, George");
ps.setString(2, "1984");
ps.executeUpdate();
ps.close();
conn.close();
conn = aPooledConnection.getConnection();
PreparedStatement cachedStatement =
conn.prepareStatement(INSERT_BOOK_QUERY);