cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From robby.pelss...@vodafone.com
Subject Getting SQL string from PreparedStatement
Date Wed, 10 Mar 2004 09:05:13 GMT
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


Mime
View raw message