Dynamically assembling SQL queries in Java

Question

What is the best way to assemble an SQL query with join conditions dynamically? I don't want to hard code the query for each different condition on a webpage or a set of webpages. Is it even feasible?

I got as far as being able to assemble simple queries but i got stumped when i needed to insert join conditions, i.e. how to figure out dependencies for joins etc.

Answer 1

You can use iBatis. It has a feature called Dynamic SQL. With this you can create dynamic queries at runtime based on several conditions.

Answer 2

public String AssembleSimpleSelectQuery(String TableName,Hashtable criteria) { Hashtable columnlist = ReturnColumnList(TableName); Iterator iter = columnlist.keySet().iterator(); int count = 0; query=new StringBuilder(); query.append("SELECT "); while(iter.hasNext()) {
count++;

        query.append(iter.next());
        if(count < (columnlist.size()))
        {
            query.append(",");
        }
    }
    query.append(" From " + TableName );


    Iterator<String> crit = criteria.keySet().iterator();
    if(criteria.size()>0)
    {
        query.append(" where ");
    }
    count = 0;
    while(crit.hasNext())
    {
        count++;
        String temp = crit.next();
        query.append(temp + "=");
        if(columnlist.get(temp).equals("String") || columnlist.get(temp).equals("Id"))
        {
            query.append("'" + criteria.get(temp) + "'");
        }
        else if(columnlist.get(temp).equals("Date"))
        {
            query.append("to_date('"+criteria.get(temp)+"','mm-dd-yyyy')");
        }
        if(count < criteria.size())
        {
            query.append(" and ");
        }
    }
    return query.toString();
}

/this is the sample function that creates a simple select query --i call returncolumnlist that reads off from an xml file that stores tablenames and their columns and returns a hashtable of column names and their datatypes.... --i havent yet figured out how to fit in a join condition (maybe more than 1) into this code...so looking for ideas for that...im not looking for code.../

Answer 3

My preferred approach to building complex dynamic queries is:

1. Enumerate all the possible queries (i.e. query patterns) that are required. This allows me to see commonalities and find patterns for which generic code can be written.
2. Generate each part of the SQL statement separately, then concatenate at the very end. e.g. (this is not meant to be working code, just a sketch of an idea):

<pseudocode>

select_clause = 'SELECT '
from_clause = 'FROM '
where_clause = 'WHERE '
orderby_clause = 'ORDER BY '

if [query on person] then
  select_clause += 'p.name, p.dob '
  from_clause += 'person p '
  orderby_clause += 'p.name '

  if [query on address] then
    select_clause += 'a.address_text '
    from_clause += ', address a '
    where_clause += 'p.address_id = a.id AND a.id=:p1 '
  else
    where_clause += 'p.id=:p1'
  end if

end if

sql_stmt = select_clause + from_clause + where_clause + orderby_clause + ';'

</pseudocode>

So, the above code might produce the following statements:

SELECT p.name, p.dob
FROM person p
WHERE p.id=:p1
ORDER BY p.name;

SELECT p.name, p.dob, a.address_text
FROM person p, address a
WHERE p.address_id = a.id AND a.id=:p1
ORDER BY p.name;

With more parameters, this approach means that I don't have to deal with an exponentially-increasing number of possible combinations of criteria.

Answer 4

Hibernate has API to construct queries without having to concatenate strings at all, see here:

http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/Criteria.html

Answer 5

Check this article HOW TO: SQL in JAVA for details on how to connect to SQL Server database from C#.NET database applications as well as Java database applications. It also describes how to pass embedded SQL queries (SELECT, INSERT, UPDATE, DELETE), calling stored procedures, pass parameters etc.