wood burning stoves 2.0*
The moose likes JDBC and the fly likes Enhanced PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Enhanced PreparedStatement" Watch "Enhanced PreparedStatement" New topic
Author

Enhanced PreparedStatement

Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Hi all,
I remember a couple of occurrences where I could've paid anything to see the SQL string that some PreparedStatement was actually sending to a database... I think I even wrote a "debugging decorator" for PreparedStatement a couple of years ago just to see "roughly" how the SQL looks like.
Back then I wondered why the PreparedStatement API is such a black box and hoped that the next versions of JDBC would bring delight. People are often looking after solutions for mapping database queries dynamically to different tables etc. using some string concatenation hack.
Why isn't there a standard class providing PreparedStatement-like programming interface with setter methods also for table names? Maybe the PreparedStatement wouldn't be so prepared anymore but it would definitely be a cleaner solution than to do

It shouldn't be too difficult to add a method like setIdentifier(int index, String identifierName) to a statement class.
Am I missing something? Is this really a completely irrelevant/outdated question?


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
You can also turn on tracing at the database (i.e. session trace file) that way you get a dump of all the SQL, if you develop on a PC & have cygwin then you can just tail the file.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
I'm probably missing a point here, because I don't see how PreparedStatement is a "black box". It certainly doesn't, if that is what you're implying, assemble your SQL statement and the values of the bind variables into an SQL statement before sending the whole to the database. At least not for the databases I'm familiar with. They typically support this entire prepared statement/bind variable thing natively, making PreparedStatement simply a fairly "raw" interface to the underlying database.
Table names and so forth aren't valid as bind variables because databases don't support this. As you note, it would defeat the purpose of the PreparedStatement: you cannot prepare an execution plan if the very table you're using is paremeterised!
- Peter
[ June 13, 2003: Message edited by: Peter den Haan ]
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
That's exactly what I meant with "not so prepared anymore", Peter.
Regardless of the fact that this hypothetical "EnhancedStatement" wouldn't provide the performance benefits of precompilation, it would still deliver a more controlled way to construct SQL statements based on variables.
SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
Lasse,
There is a simple reason why you cannot "toString" a prepared statement. A prepared statement can bind simple type values, such as int, long, String, char, etc, but it also supports binding complex types, such as Clob, Reader, InputStream, etc, and since these complex types cannot be displayed as readable characters, you cannot toString() a PreparedStatement to see the SQL statement.
Last year I wrote a special class that supports a good number of the setXXX() methods in PreparedStatement with the exception of the setXXX() methods for the complex types. I also added my own toString() method. It even supports an append() method for dynamically adding SQL. Here it is, and hence the name DynamicStatement, and it works well:

[ June 13, 2003: Message edited by: SAFROLE YUTANI ]
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Thanks for posting the code. It actually looks very much like the class I wrote for logging SQL statements back in the day. The only significant difference was that I stored the parameters within the statement object (able to call setXxx(index, value) for a second time).
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

You might find this thread where we discuss DebuggableStatements useful
Dave
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Thanks David. I had missed that thread--and the JavaWorld article as well. The JavaWorld thingie sounds pretty much like what I had (except that they must've created a much better implementation).
 
 
subject: Enhanced PreparedStatement
 
Similar Threads
editing records
Dynamic column selection in prepared statement?
Solved : How delete oldest records
PreparedStatment for Search Queries
PreparedStatement and 'null' value in WHERE clause