Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statements vs. statement.execute

 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Bosun Bello
Ranch Hand
Posts: 1511
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Braj Prasad
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might find this link very relevant.
Prepared Statements vs Statement
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic