|
1
19 package org.openbravo.erpCommon.ad_forms;
20
21 import java.sql.*;
22 import java.util.*;
23
24 import org.apache.log4j.Logger;
25
26 import javax.servlet.ServletException  ;
27
28 import org.openbravo.data.FieldProvider;
29 import org.openbravo.database.ConnectionProvider;
30 import org.openbravo.exception.*;
31
32
33 public class SQLExecutor_Query implements FieldProvider {
34 static Logger log4j = Logger.getLogger(SQLExecutor_Query.class);
35 Vector<String > data = new Vector<String >();
36 Vector<String > type = new Vector<String >();
37 Vector<String > name = new Vector<String >();
38
39 public String getField(String fieldIndex) {
40 int field = Integer.parseInt(fieldIndex);
41 if (data!=null && data.size()>field)
42 return data.elementAt(field);
43 else {
44 log4j.warn("The field does not exist: " + field);
45 return null;
46 }
47 }
48
49 public static SQLExecutor_Query[] select(ConnectionProvider connectionProvider, String strSQL) throws ServletException {
50 return select(connectionProvider, strSQL, 0, 0);
51 }
52
53 public static SQLExecutor_Query[] select(ConnectionProvider connectionProvider, String strSQL, int firstRegister, int numberRegisters) throws ServletException {
54
55 PreparedStatement st = null;
56 ResultSet result;
57 Vector<Object > vector = new Vector<Object >(0);
58
59 try {
60 if (log4j.isDebugEnabled()) log4j.debug("select - Preparing Statement\n");
61 st = connectionProvider.getPreparedStatement(strSQL);
62 if (log4j.isDebugEnabled()) log4j.debug("select - Statement Prepared\n");
63 if (log4j.isDebugEnabled()) log4j.debug("select - Executing Statement\n");
64 result = st.executeQuery();
65 if (log4j.isDebugEnabled()) log4j.debug("select - Statement Executed\n");
66 long countRecord = 0;
67 long countRecordSkip = 1;
68 boolean continueResult = true;
69 while(countRecordSkip < firstRegister && continueResult) {
70 continueResult = result.next();
71 countRecordSkip++;
72 }
73 ResultSetMetaData rmeta=result.getMetaData();
74 int numColumns=rmeta.getColumnCount();
75 Vector<String > types = new Vector<String >(0);
76 Vector<String > names = new Vector<String >(0);
77 if (log4j.isDebugEnabled()) log4j.debug("select - Making data\n");
78 while(continueResult && result.next()) {
79 countRecord++;
80 SQLExecutor_Query objectSQLExecutor_Query = new SQLExecutor_Query();
81 for (int i=1;i<=numColumns;i++) {
82 String aux = "";
83 try {
84 aux = result.getString(i);
85 } catch (Exception ignored) {}
86 if (aux==null) aux = "";
87 objectSQLExecutor_Query.data.addElement(aux);
88 if (countRecord>1) {
89 objectSQLExecutor_Query.type = types;
90 objectSQLExecutor_Query.name = names;
91 } else {
92 String auxType = transformSQLType(rmeta.getColumnType(i));
93 String auxName = rmeta.getColumnName(i);
94 if (auxType.equals("NUMBER") && auxName.toUpperCase().endsWith("_ID")) auxType="ID";
95 objectSQLExecutor_Query.type.addElement(auxType);
96 objectSQLExecutor_Query.name.addElement(auxName);
97 }
98 }
99 types = objectSQLExecutor_Query.type;
100 names = objectSQLExecutor_Query.name;
101
102 vector.addElement(objectSQLExecutor_Query);
103 if (countRecord >= numberRegisters && numberRegisters != 0) {
104 continueResult = false;
105 }
106 }
107 if (log4j.isDebugEnabled()) log4j.debug("select - Closing resultset\n");
108 result.close();
109 } catch (NoConnectionAvailableException ex) {
110 log4j.error("No connection available error in query: " + strSQL + "Exception:"+ ex);
111 throw new ServletException ("@CODE=NoConnectionAvailable");
112 } catch (SQLException ex2) {
113 log4j.error("SQL error in query: " + strSQL + "Exception:"+ ex2);
114 throw new ServletException ("@CODE=" + Integer.toString(ex2.getErrorCode()) + "@" + ex2.getMessage());
115 } catch (Exception ex3) {
116 log4j.error("Error in query: " + strSQL + "Exception:"+ ex3);
117 throw new ServletException ("@CODE=@" + ex3.getMessage());
118 } finally {
119 try {
120 connectionProvider.releasePreparedStatement(st);
121 } catch (Exception ignored) {}
122 }
123 SQLExecutor_Query objectSQLExecutor_Query[] = new SQLExecutor_Query[vector.size()];
124 vector.copyInto(objectSQLExecutor_Query);
125 if (log4j.isDebugEnabled()) log4j.debug("select - returning data\n");
126 return(objectSQLExecutor_Query);
127 }
128
129 public static String transformSQLType(int sql_type) {
130 String strType = "";
131 switch (sql_type) {
132 case Types.INTEGER:
133 case Types.SMALLINT:
134 case Types.TINYINT:
135 case Types.BIGINT: strType = "INTEGER";
136 break;
137 case Types.CLOB:
138 case Types.BLOB:
139 case Types.BINARY: strType = "FILE";
140 break;
141 case Types.DECIMAL:
142 case Types.DOUBLE:
143 case Types.FLOAT:
144 case Types.LONGVARBINARY:
145 case Types.LONGVARCHAR:
146 case Types.NUMERIC:
147 case Types.REAL:
148 case Types.BIT: strType = "NUMBER";
149 break;
150 case Types.BOOLEAN: strType = "BOOLEAN";
151 break;
152 case Types.DATE: strType = "DATE";
153 break;
154 case Types.TIME: strType = "TIME";
155 break;
156 case Types.TIMESTAMP: strType = "DATETIME";
157 break;
158 default: strType = "STRING";
159 }
160 return strType;
161 }
162 }
163 |
Popular Tags |
Java API By Example, From Geeks To Geeks.