Statement and PreparedStatement

Summary

In this chapter, you learned how to query and modify data using the JDBC classes Statement, PreparedStatement, and OraclePreparedStatement. You also learned the reasons you should not use the Statement class in production code, as it does not support using bind variables. By using bind variables in your program, you not only make your application more performant and scaleable, but you also make it more secure by preventing SQL injection attacks.

You discovered how to boost application performance tremendously by using update batching, which in Oracle is available only when you use prepared statements. You saw a comparison of standard and Oracle update batching in terms of elapsed times and latch consumption, and you observed that Oracle update batching outperforms standard update batching in terms of elapsed time (in terms of latches, the difference between the two is negligible). You also saw how batching can dramatically reduce the latch consumption, thus improving scalability. You looked at how mixing interdependent batch statements in your application can sometimes lead to unexpected results.

A major take-away message from this chapter is that, if you are embedding SQL statements in your JDBC code, you should use a PreparedStatement object and use bind variables wherever appropriate. However, a strong case can also be made for wrapping your DML statements in PL/SQL packaged procedures and invoking them from JDBC using CallableStatement objects. This is what we will examine in the next chapter.