Looping to create dynamic SQL string...

Question

I am working on a problem that will use certain fields to return a unique row. The amount of fields could be 2 or 20, so I need to dynamically generate a SQL string based on the list of key fields and their values. The SQL will look something like this:

Select count(data_cd) from dev_util.t_generic_repository AS ...

This is where I need to generate the string dynamically, so if I had three key fields I would need...

Select count(data_cd from dev_util.t_generic_repository AS X, dev_util.t_generic_repository AS Y, dev_util.t_generic_repository AS Z

What would be the best (and most efficient) way of solving this? The language is Java, but any language could work.

Answer 1

since your are using grails (which uses hibernate by default under the covers), you can use 'criteria queries'. these allow you to programmatically construct your query. It is better than dynamically creating a query string yourself.

http://www.grails.org/Hibernate+Criteria+Builder