Hi,
This question is not really cocoon-related, but perhaps anyone has a
solution to my problem....
I dynamically create a preparedstatement as follows
SQLDataSource dataSource =
(SQLDataSource)Xenopsis.getDataSource("maximo");
expr = new SqlExpression();
expr.setDataSource(dataSource);
expr.setBaseQuery("SELECT DISTINCT T.ID AS TTID, T.STARTTIME FROM
MAXIMO.TTI_TROUBLES T");
expr.setOrderByExpr("ORDER BY T.STARTTIME DESC");
Sqlcondition CId = new Sqlcondition();
CId.setCondition("UPPER(T.ID) = ? ");
CId.setParameterType(4);
// bind textbox to condition
CId.bind(tbId);
expr.addSqlcondition(CId);
Sqlcondition CType = new Sqlcondition();
CType.setCondition("UPPER(T.TYPE) = ? ");
CType.setParameterType(4);
// bind dropdownbox to condition
CType.bind(ddbType);
expr.addSqlcondition(CType);
Depending of the dropdownboxes and textboxes have valid values, the
Sqlcondition are added to the SqlExpression. And finally i retreive
ResultSet rs = expr.getPreparedStatement().executeQuery();
I need to know the total SQL String that is dynamically constructed... Is it
possible to do this??
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------------------------------------
public class SqlExpression {
private PreparedStatement pstmt;
private String baseQuery;
private String orderbyexpr;
private LinkedList conditions;
private SQLDataSource ds;
public SqlExpression() {
this.conditions = new LinkedList();
this.baseQuery= "";
this.orderbyexpr = "";
}
public void setBaseQuery(String basequery) {
this.baseQuery = basequery;
}
public void setOrderByExpr(String orderbyexpr) {
this.orderbyexpr = orderbyexpr;
}
public void setDataSource(SQLDataSource datasource) throws Exception
{
this.ds = datasource;
}
public void addSqlcondition(Sqlcondition sqlcondition) {
this.conditions.add(sqlcondition);
}
public void addStatementParameter(String parameter, int type, int
index) throws Exception {
switch (type) {
case 1:
this.pstmt.setBoolean(index,
(Boolean.valueOf(parameter)).booleanValue());
break;
case 2:
this.pstmt.setInt(index,
(Integer.valueOf(parameter)).intValue());
break;
case 3:
this.pstmt.setLong(index,
(Long.valueOf(parameter)).longValue());
break;
case 4:
this.pstmt.setString(index, parameter);
break;
case 5:
this.pstmt.setString(index,"%" + parameter + "%");
break;
case 6:
this.pstmt.setDate(index, new
java.sql.Date(DateHelper.parse(parameter).getTime()));
break;
default:
this.pstmt.setString(index, parameter);
break;
}
}
public PreparedStatement getPreparedStatement() throws Exception {
this.pstmt =
this.ds.getPreparedStatement(this.getCompleteQuery());
int indexnumber = 1;
for (Iterator i = conditions.iterator(); i.hasNext();) {
Sqlcondition condition = (Sqlcondition)i.next();
if
(!condition.getCondition().equals("")&&condition.getParameterType()
!= 7) {
this.addStatementParameter(condition.getParameter(),
condition.getParameterType(), indexnumber);
indexnumber = indexnumber + 1;
}
}
return this.pstmt;
}
public String getCompleteQuery() throws Exception {
String completequery =
this.getFilterExpression().equals("")
? this.baseQuery + " " + this.orderbyexpr
: this.baseQuery + " WHERE " + this.getFilterExpression() +
" " + this.orderbyexpr;
return completequery;
}
public String getFilterExpression() throws Exception {
String result = "";
for (Iterator i = conditions.iterator(); i.hasNext();) {
Sqlcondition condition = (Sqlcondition)i.next();
if (!condition.getCondition().equals("")) {
result = result + (result.trim().equals("") ? "" : " AND
") + condition.getCondition();
}
}
return result;
}
}
public class Sqlcondition {
private String condition;
private int parametertype;
private FormField field;
private String parameter;
public Sqlcondition() {
this.condition = "";
this.parametertype = 4;
this.parameter = "";
}
public void setCondition(String condition) {
this.condition = condition;
}
/** ParameterType options are
1 : equals boolean
2 : equals int
3 : equals long
4 : equals string
5 : like string
6 : equals date
7 : condition without parameter
*/
public void setParameterType(int parametertype){
this.parametertype = parametertype;
}
public String getParameter() {
this.parameter = this.field.getValue().toUpperCase();
return this.parameter;
}
public void bind(FormField field) {
this.field = field;
}
public int getParameterType() {
return this.parametertype;
}
public String getCondition() {
return
this.field.hasValue()
? this.condition
: "";
}
}
public interface FormField {
public boolean hasValue();
public String getValue();
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
