You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by ro...@vodafone.com on 2004/03/10 10:05:13 UTC
Getting SQL string from PreparedStatement
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: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
AW: Getting SQL string from PreparedStatement
Posted by Marco Rolappe <m_...@web.de>.
if you want the actual query sent (i.e. including substituted values); it's
not possible OOTB. but you might want to have a look at:
http://www-106.ibm.com/developerworks/library/j-loggable.html?ca=dnt-420
> -----Ursprüngliche Nachricht-----
> Von: users-return-64342-m_rolappe=web.de@cocoon.apache.org
> [mailto:users-return-64342-m_rolappe=web.de@cocoon.apache.org]Im Auftrag
> von Marco Rolappe
> Gesendet: Mittwoch, 10. März 2004 10:31
> An: users@cocoon.apache.org
> Betreff: AW: Getting SQL string from PreparedStatement
>
>
> hmmm, I might have missed something but... you're already getting that SQL
> string via getCompleteQuery(), ain't you?
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
AW: Getting SQL string from PreparedStatement
Posted by Marco Rolappe <m_...@web.de>.
hmmm, I might have missed something but... you're already getting that SQL
string via getCompleteQuery(), ain't you?
> -----Ursprüngliche Nachricht-----
> Von: users-return-64341-m_rolappe=web.de@cocoon.apache.org
> [mailto:users-return-64341-m_rolappe=web.de@cocoon.apache.org]Im Auftrag
> von robby.pelssers@vodafone.com
> Gesendet: Mittwoch, 10. März 2004 10:05
> An: users@cocoon.apache.org
> Betreff: Getting SQL string from PreparedStatement
>
>
> 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.getParame
> terType()
> != 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: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org