Creating a dynamic SQL query builder class
I have a jobs table where each row contains information about jobs run on a machine. I have a web view of this database which shows different information and queries for this information are built using the following fields:
TYPE
TITLE
NODE
STATUS (an array of 4 different possible statuses)
TIMESTAMP
GUID (will be used when a view of a specific job (row in the table) is requested, no other search criteria needed when this view is required.)
All the criteria will be passed from the view layer into a search criteria object which is then passed to the DAO class which passes it to a BuildSQL class to use it to build the SQL.
There are many different "views" of the data which will result in many different combinations of criteria being passed down to the data access layer. (for example a status of failed and a timestamp resulting in all the jobs run in the last 2 hours)
As there are a number of different search criteria used in each query I'm having trouble designing the class to dynamically build the query. Are there any standard ways of doing this out there that could guide me?
At the moment I'm seeing this class as being a large combination of if statements and not really being very dynamic.
I was thinking maybe checking all the criteria to see if its null, if so ignore it straight away and then running through all the other criteria and appending one by one to the may SELECT which has already been created. Quite how i'd decided whether it would need an AND or OR before it I dont know.
Are there any standard proven ways to approach this because i've done a search here and on google and not really come back with much, so any advice would be great. I've got to say I'm pretty new to java and don't really have many bright ideas here!
[1843 byte] By [
al18exa] at [2007-11-27 5:24:09]
# 1
Well my dear friend... I did something special for a business logic support to an web autocomplete component.
The criteria building was based on a custom variable into the SQL statement:
SELECT * FROM TABLE WHERE __MC__ AND (FIXED CRITERIA)
Where:
*FIXED CRITERIA: Is a fixed criteria that doesn't variate
*__MC__: Is a "custom variable" defined for me to make the replacement.
There are two main clases:
*SQLBuilder: It has many logic to build SQL Statements
*Multicriteria: It has the specific logic to insert into a SQL statement multiple criterias
// The SQLBuilder class
public class SQLBuilder {
public static final String MC = "__MC__";
...
static String buildMultiCriteria(String sql, Object[] params) throws Exception {
for(int i=0; i<params.length; i++) {
if(params[i] instanceof MultiCriteria) {
sql = sql.replaceFirst("__MC__",
((MultiCriteria)params[i]).getCriteria());
params[i] = null;
}
}
return sql;
}
}
// The MultiCriteria class
public class MultiCriteria {
public static final int BEGINS = 0;
public static final int ENDS = 1;
public static final int CONTAINS = 2;
private String criteria = null;
public MultiCriteria(String[] fields, String value, int type) throws Exception {
String[] tokens = value.split(" ");
StringBuffer sb = new StringBuffer("(");
for(int i=0; i><fields.length; i++) {
for(int j=0; j><tokens.length; j++) {
if(tokens[j].length()>0) {
sb.append(fields[i].toUpperCase());
sb.append(" LIKE '");
sb.append(toType(tokens[j], type));
sb.append("'");
if(j<tokens.length-1) sb.append(" OR ");
}
}
if(i><fields.length-1) sb.append(" OR ");
}
sb.append(")");
criteria = sb.toString();
}
public String getCriteria() {
return criteria;
}
private final String toType(String value, int type) throws Exception {
switch(type) {
case BEGINS:
value = value + "%";
break;
case ENDS:
value = "%" + value;
break;
case CONTAINS:
value = "%" + value + "%";
break;
default:
throw new Exception("Undefined MutiCriteria type");
}
return value;
}
}
// I use the MultiCriteria this way
// criteria is a string introduced in a html input text
// The class splits the string by " " and ensemble multiple criterias
// for the given fields permuting them with the tokens resulting of the
// split
MultiCriteria mc = new MultiCriteria(
new String[] {"FIELD_01", "FIELD_02"},
criteria,
MultiCriteria.CONTAINS
);
I think this idea could help you.
Anything else, write me at remnahush@gmail.com
Best Regards,>
# 3
Remnahush,
I've taken your idea of the buffer and for loop to help get round my problem. I don't know if its really the best way to do it and its not really that dynamic whether or not this will matter I don't know!
Basically I created 6 loops to get the values of the set search criteria in the search criteria object class. The number of search criteria can be different everytime, whether its searching for just a job id or all jobs in a number of statuses.
The following is one of the loops ive used, all six are like this;
ArrayList statuses = sc.getStatuses();
if(!statuses.isEmpty()){
StringBuffer status = new StringBuffer();
for (int i = 0; i < statuses.size(); i++ )
{
if(i>0)
status.append(" OR ");
status.append("J.STATUS = '" + statuses.get(i) + "'");
}
Status = status.toString();
Status = ("(" + Status + ")");
criteria.add(Status);
}
I was thinking that I should maybe make this more dynamic but i thought I might run into problems when specifying the correct field to search in the SQL.
Each of these adds the created SQL to the criteria string buffer which then gets appended to the main part of the SQL query which has been specified at the beginning of the class.
If you have any ideas on whether I could improve what i've done please let me know I'm open to ideas.
Regards,
Alex
