cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From robby.pelss...@vodafone.com
Subject RE: Getting SQL string from PreparedStatement
Date Wed, 10 Mar 2004 09:41:52 GMT
Yes, but it contains questionmarks at this point...  So if i use
getCompleteQuery() i get something like

SELECT DISTINCT T.ID AS TTID, T.STARTTIME FROM MAXIMO.TTI_TROUBLES T WHERE
UPPER(T.TYPE) = ? AND UPPER(T.ESC_CODE) = ? ORDER BY T.STARTTIME DESC

So now my class also executes following code
this.pstmt.setString(1, value of selected Type);
this.pstmt.setString(2, value of selected Esc_Code);

Isn't is possible to get the Sql statement where the questionmarks are
replaced by the corresponding values ??

-----Original Message-----
From: m_rolappe@web.de [mailto:m_rolappe@web.de]
Sent: 10mm2004 10:31
To: users@cocoon.apache.org
Subject: 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?

> -----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

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org


Mime
View raw message