|
1
19 package org.openbravo.erpCommon.utility;
20
21 import org.openbravo.database.ConnectionProvider;
22 import org.openbravo.data.FieldProvider;
23 import java.util.Vector  ;
24 import java.sql.*;
25 import org.openbravo.data.UtilSql;
26 import org.apache.log4j.Logger;
27 import javax.servlet.ServletException ;
28
29
30 public class ExecuteQuery {
31 static Logger log4j = Logger.getLogger(ExecuteQuery.class);
32 private ConnectionProvider pool;
33 private Vector <String > parameters = new Vector <String >();
34 private String sql;
35
36 public ExecuteQuery() {
37 }
38
39 public ExecuteQuery(ConnectionProvider _conn, String _sql, Vector <String > _parameters) throws Exception {
40 setPool(_conn);
41 setSQL(_sql);
42 setParameters(_parameters);
43 }
44
45 public void setPool(ConnectionProvider _conn) throws Exception {
46 if (_conn==null) throw new Exception ("The pool is null");
47 this.pool = _conn;
48 }
49
50 public ConnectionProvider getPool() {
51 return this.pool;
52 }
53
54 public void setSQL(String _sql) throws Exception {
55 this.sql = ((_sql==null)?"":_sql);
56 }
57
58 public String getSQL() {
59 return this.sql;
60 }
61
62 public void setParameters(Vector <String > _parameters) throws Exception {
63 this.parameters = _parameters;
64 }
65
66 public Vector <String > getParameters() {
67 return this.parameters;
68 }
69
70 public void addParameter(String _value) {
71 if (this.parameters==null) this.parameters = new Vector <String >();
72 if (_value==null || _value.equals("")) this.parameters.addElement("");
73 else this.parameters.addElement(_value);
74 }
75
76 public String getParameter(int position) {
77 if (this.parameters == null || this.parameters.size() < position) return "";
78 else return this.parameters.elementAt(position);
79 }
80
81 public FieldProvider[] select(int startPosition, int rangeLength) throws ServletException {
82 PreparedStatement st = null ;
83 ResultSet result;
84 Vector <SQLReturnObject> vector = new Vector <SQLReturnObject>(0);
85
86 try {
87 result = selectResultset(st);
88 boolean first = true;
89 int numColumns=0;
90 int rowNum = 0;
91 Vector <String > names = new Vector <String >(0);
92 while(result.next()) {
93 if (first) {
94 ResultSetMetaData rmeta=result.getMetaData();
95 numColumns=rmeta.getColumnCount();
96 for (int i=1;i<=numColumns;i++) {
97 names.addElement(rmeta.getColumnName(i));
98 }
99 first=false;
100 }
101 if (rowNum>=startPosition && (rangeLength<=0 || rowNum<(startPosition + rangeLength))) {
102 SQLReturnObject sqlReturnObject = new SQLReturnObject();
103 for (int i=0;i<numColumns;i++) {
104 sqlReturnObject.setData(names.elementAt(i), UtilSql.getValue(result, names.elementAt(i)));
105 }
106 vector.addElement(sqlReturnObject);
107 } else if (rangeLength>0 && rowNum>=(startPosition + rangeLength)) break;
108 rowNum++;
109 }
110 result.close();
111 } catch(SQLException e){
112 log4j.error("SQLException:"+ e);
113 throw new ServletException ("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
114 } catch(Exception ex){
115 log4j.error("Exception:"+ ex);
116 throw new ServletException ("@CODE=@" + ex.getMessage());
117 } finally {
118 try {
119 getPool().releasePreparedStatement(st);
120 } catch (Exception ignore) {}
121 }
122 FieldProvider objectListData[] = new FieldProvider[vector.size()];
123 vector.copyInto(objectListData);
124 return(objectListData);
125 }
126
127 public ResultSet selectResultset(PreparedStatement st) throws ServletException {
128 if (log4j.isDebugEnabled()) log4j.debug("SQL: " + getSQL());
129 ResultSet result;
130
131 try {
132 st = getPool().getPreparedStatement(getSQL());
133 Vector <String > params = getParameters();
134 if (params!=null) {
135 for (int iParameter=0;iParameter<params.size();iParameter++) {
136 if (log4j.isDebugEnabled()) log4j.debug("PARAMETER " + iParameter + ":" + getParameter(iParameter));
137 UtilSql.setValue(st, iParameter+1, 12, null, getParameter(iParameter));
138 }
139 }
140 result = st.executeQuery();
141 } catch(SQLException e){
142 log4j.error("SQL error in query: " + getSQL() + "Exception:"+ e);
143 throw new ServletException ("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
144 } catch(Exception ex){
145 log4j.error("Exception in query: " + getSQL() + "Exception:"+ ex);
146 throw new ServletException ("@CODE=@" + ex.getMessage());
147 }
148 return(result);
149 }
150
151 public int executeStatement() throws ServletException {
152 if (log4j.isDebugEnabled()) log4j.debug("SQL: " + getSQL());
153 PreparedStatement st = null;
154 int total = 0;
155
156 try {
157 st = getPool().getPreparedStatement(getSQL());
158 Vector <String > params = getParameters();
159 if (params!=null) {
160 for (int iParameter=0;iParameter<params.size();iParameter++) {
161 UtilSql.setValue(st, iParameter+1, 12, null, getParameter(iParameter));
162 }
163 }
164 total = st.executeUpdate();
165 } catch(SQLException e){
166 log4j.error("SQLException:"+ e);
167 throw new ServletException ("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
168 } catch(Exception ex){
169 log4j.error("Exception:"+ ex);
170 throw new ServletException ("@CODE=@" + ex.getMessage());
171 } finally {
172 try {
173 getPool().releasePreparedStatement(st);
174 } catch (Exception ignore) {}
175 }
176 return(total);
177 }
178 }
179 |
Popular Tags |
Java API By Example, From Geeks To Geeks.