Dynamic SQL from Java use any number of parameters

Following is the Code that uses Java and Oracle . Its an utility to do a dynamic search without defining the sql . SQL is generated on the fly ( dynamically ) at runtime . You may have to tweak the sql generated for your purpose , but you can resue most of the Code

Params can be changed to include a variety of search options
1.Quotes for Exact Search
2. % for wider search
- abc% means starts with "abc"
- %xyz means ends with "xyz"
3.Use () to specify OR condition e.g.,(abc , efg)
4.Search is Non-Case Sensitive

One limitation is that all the parameters are created by AND condition .However you can extend the utility class to include
that feature too .

Sql Utlity class

import java.util.StringTokenizer;

public class Sql {
private String sOn ; // column name to search
private String sParam; // exact search parameter

public Sql(String sOn , String sParam)
{
this.sOn = sOn;
this.sParam = sParam;
}
// Generate the sql
public String getSQL()
{
StringBuffer sSql = new StringBuffer();
String sToken = new String();
String sTemp = new String();

if(sParam.startsWith("\"")&& sParam.endsWith("\""))
{
sSql.append("AND UPPER(").append(sOn).append(") =").append("'").append((sParam.substring(sParam.indexOf("\"")+1,sParam.lastIndexOf("\""))).toUpperCase()).append("'");
}else if(sParam.startsWith("(")&& sParam.endsWith(")"))
{
sTemp = sParam.substring(sParam.indexOf("(")+1,sParam.indexOf(")")) ;
sSql.append("AND UPPER(").append(sOn).append(") IN (");
StringTokenizer st1 = new StringTokenizer(sTemp,",");
while ( st1.hasMoreTokens() ) {
sToken = st1.nextToken();
if(sToken !=null)
{
sToken = sToken.trim().toUpperCase();
sSql.append("'").append(sToken).append("',");
}
}
sSql = new StringBuffer(sSql.substring(0,sSql.length()-1)) ;
sSql.append(")");
}
else if(sParam.startsWith("%") || sParam.endsWith("%"))
{
sSql.append("AND UPPER(").append(sOn).append(") LIKE '").append(sParam.toUpperCase()).append("'");

}else if(sParam!=null&& !sParam.equals(""))
{
sSql.append("AND UPPER(").append(sOn).append(") LIKE '%").append(sParam.toUpperCase()).append("%'");

}
return sSql.toString();
}
}

Utlity method


public String getSearchSQL(Vector v)
{
StringBuffer sql = new StringBuffer();
for(Enumeration e=v.elements();e.hasMoreElements();)
{
sql.append(((Sql)e.nextElement()).getSQL() );

}
return sql.toString();
}


Application Logic


Vector v = new Vector();
v.add(new Sql("<name of table column name>", <value to search on use empty string if null> ));

.....repeat for all the parameters to be searched on

<sql string> = getSearchSQL(v);

Bingo !