Saturday, July 30, 2005
PreparedStatement
Coming from a Oracle-centric software company, I have long been exposed not just to Java but to proprietary Oracle technologies such as Forms, PL/SQL, etc. Also, my life has been so marred by heavy usage of SQL. Leaning towards the Java world, I can say that Java+SQL combination makes sense. Most of the operations that everybody will do are DML statements. To better execute DMLs, we can choose between JDBC's Statement and PreparedStatement. However, I would recommend the latter. This is because the latter is more efficient. The advantage will be more obvious to SQL statements that are executed repeatedly. In advanced, RDBMSes, the PreparedStatement causes the SQL statement to be precompiled. Hence, when the pstmt is executed at a later point, the RDBMS does not have to parse and compile the SQL statement, it proceeds immediately in executing it. Normally, prepared statements are used in conjunction with SQL statements that expects variable input and/or output, bind variables to be exact. However, pstmts can also be used with repeatedly executed SQL statements that do not accept parameters. Statements, however, does not mean that they are not pre-compiled. If you repeatedly call a Statement with the same SQL statement, the RDBMS will be smart enough that this has been executed before. We'll at least for Oracle DB. If you work on any Java+SQL environments ensure that both your Java code as well as your calls to the database or properly designed.
Subscribe to:
Post Comments (Atom)
2 comments:
Yep. I agree that for SQL statements that do not have to be created on the fly, the PreparedStatement is the way to go.
Regular Statements, however, have their uses. You are able to dynamically write your SQL during run-time and send it to a Statement object for execution. I have had to do this a few times.
Getting back to PreparedStatements, I thought you might be interested in something I wrote a couple of years ago. It's a class that allows you to put all of your SQL into a properties file, and then, upon initialization parses the SQL into PreparedStatements and puts them into a HashMap for future use.
Like I said, I wrote this class a couple of years ago, so there may be some design issues or it may not take full advantage of Java's latest version. Here's the link:
Stop Hard-coding PreparedStatements
I hope you find it useful.
thanks a lot rick. this is definitely helpful. i could probably incorporate this to any of my personal projects in the future. however, in the company the i work for, "legacy" or "outmoded" code rules.
Post a Comment