Showing posts with label dynamic sql from java. Show all posts
Showing posts with label dynamic sql from java. Show all posts

Wednesday, October 10, 2007

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 !