This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Avoiding hardcoded SQL statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Avoiding hardcoded SQL statements" Watch "Avoiding hardcoded SQL statements" New topic
Author

Avoiding hardcoded SQL statements

Nick Heudecker
Ranch Hand

Joined: Jul 09, 2000
Posts: 52
Okay, I'm working on the first iteration of an application and one thing I want to avoid doing is hardcoding lots of SQL statements in my code. In later iterations I'm going to create a full-blown database persistence layer, but in the short term I'm wondering if anyone knows a clean way to dynamically generate SQL. Any thoughts/ideas/suggestions?


---<br />Co-author of "Hibernate Quickly"<br /><a href="http://www.manning.com/books/peak" target="_blank" rel="nofollow">http://www.manning.com/books/peak</a>
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
Use a StringBuffer 'sb' and depending on the program logic append the desired sql part of query to the StringBuffer 'sb' . Get the final query using sb.toString().


SCJP, SCJD, SCWCD, SCBCD, SCEA
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
Or use a templating system to build your SQL from stored templates. Or use PreparedStatement from fixed strings and fill in values later.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Hi
Use DatabaseMetaData and/or ResultSetMetaData/RowSetMetaData.
Let's say I have a Company Table related to an Invoice Table through a companyID
Pseudo-Structure looks like this.
CompanyTBL
- companyID int; (PK)
- companyName varchar(80)
- etc etc.
InvoiceTBL
- invoiceID int; (PK)
- companyID int; (FK)
- etc invoice details.

If you want to do some kind of dynamic reporter, you can create a form in an HTML Page that reads the MetaData information. This will get you the fields in your tables (can you hard code the CompanyTBL if you want at this point). Then do a 'hashing' of all your tables to match up the PK's with FK's. Once you have the PK-FK match you can create a 100% dynamic SQL select statement.
This can now display information in a table about a company and all it's related invoices.
I've used things like property files that specify 'ignore fields' for each table to disallow showing specific information.
I have also setup some complex relations using inner/outer joins with my property files. The easiest to create with the above mentioned method are standard equijoins. The beauty of this is in the event you no longer want to display (for example) tax information, you can just edit the property file and the changes are reflected in your app. No need to recompile.
Hope this helps.

SOURCE CODE should be SURROUNDED by "code" tags.
Nick Heudecker
Ranch Hand

Joined: Jul 09, 2000
Posts: 52
Thanks for that! I'll play with that solution for now. I'm completely dreading creating the persistence layer and anything that delays it further is more than welcome.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Avoiding hardcoded SQL statements
 
Similar Threads
Handling single quotes for an insert
PreparedStatement and CallableStatement?
SQL Statements in XML document
How to trace SQL statements ?
How to preserve new lines when parsing