• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Avoiding hardcoded SQL statements

 
Nick Heudecker
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
ravi janap
Ranch Hand
Posts: 389
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Or use a templating system to build your SQL from stored templates. Or use PreparedStatement from fixed strings and fill in values later.
 
John Bateman
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Nick Heudecker
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic