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>
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.
Joined: Jul 09, 2000
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.