File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes prepared statements vs. statement.execute Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepared statements vs. statement.execute" Watch "prepared statements vs. statement.execute" New topic
Author

prepared statements vs. statement.execute

Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Hi all,
does anybody have a link to some document comparing where and when to use prepared statements?
Can anyone say what are the main points where prepared statements are better?
I've got an app where I haven't used any prepared statments at all and I think I need to implement a better framework for my database access.
Currently every database op has the SQL statement in two strings, one for Oracle and one for mySQL and the appropriate string is chosen depending on the config.
I don't really like this because I always did apps where I can use stored procedures and I don't need any SQL in my Java code.
If it turns into a big app, and we get a seperate database administrator, then he'll have to start diving into the java source files whenever he changes the database. Are there any other frameworks I can use?

Thanks!
Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
I know prepared statements are precompiled statements, hence it's already parsed. It also makes it easy to execute the same statement while only changing the parameters to the statement. It also tekes care of formatting, special characters, etc. You can check out the JDBC tutorial at Sun's site for more details.


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

I also think you need to use prepared statement when you are calling a stored procedure. But in Oracle I know I can run a query like
select stored_procedure(arguments) from dual
and run them in the statement.execute.
Just some 2 cents, for whatever it's worth
Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Braj Prasad
Greenhorn

Joined: Apr 08, 2002
Posts: 16
You might find this link very relevant.
Prepared Statements vs Statement
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Thanks for the tips, folks (especially the O'Reillys link).
So what about the other issue, about where to write the actual SQL? Does everybody here do it in the business objects / layer, or has anybody ever implemented a seperate data layer which does the call to the database and returns marshalled data?
Matt Dole
Greenhorn

Joined: Mar 06, 2001
Posts: 17
we use a data access layer. they contain all our db access code. These are commonly used to centralize your db code. other objects call methods in the data access object.
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
I just replied to your other message with the same link http://martinfowler.com/ and he talks about keeping SQL in an XML file and sucking it all in at startup.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepared statements vs. statement.execute