• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Enhanced PreparedStatement

 
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You might find this thread where we discuss DebuggableStatements useful
Dave
 
Lasse Koskela
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
This is my favorite tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic