It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Avoiding hardcoded SQL statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Avoiding hardcoded SQL statements" Watch "Avoiding hardcoded SQL statements" New topic

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="" target="_blank" rel="nofollow"></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().

Frank Carver

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 ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
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.
- companyID int; (PK)
- companyName varchar(80)
- etc etc.
- 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:
subject: Avoiding hardcoded SQL statements
It's not a secret anymore!