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?
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 ]
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.
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 ]
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).