1 package org.opensync.tools;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.SQLWarning;
7 import java.sql.Statement;
8 import java.util.Hashtable;
9 import java.util.Vector;
10
11 public class QuerySelectIndex extends Object {
12 private String codeTheme, themeIdParentFile, conditionWhere, conditionOrder;
13 private Vector resultsIndex;
14
15
16 private Element elements[];
17 private StringBuffer enteteCondition = new StringBuffer();
18 private StringBuffer select = new StringBuffer();
19 private StringBuffer newCriteria = new StringBuffer();
20 private int positionStartLine=0;
21 private int positionEndLine=0;
22 private String operator_concat = null;
23 private String function_concat = null;
24
25 public QuerySelectIndex(ConnectionPool pool,Vector v0, String s2, String s3, String s4, Vector r) throws SQLWarning,SQLException {
26
27
28 elements = new Element[v0.size()];
29 v0.toArray(elements);
30
31 this.themeIdParentFile = s2;
32 this.conditionWhere = s3;
33 this.conditionOrder = s4;
34 this.resultsIndex = r;
35
36 init(pool);
37
38 }
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58 public void setElementIndex (Hashtable h, Hashtable vK) {
59 String valueText,keyW,valueKey;
60 int i,iMax,posit=-1;
61
62 iMax = this.elements.length;
63
64 StringBuffer record = new StringBuffer();
65 StringBuffer recordKey = new StringBuffer();
66 for (i=0;i<iMax;i++) {
67 keyW = elements[i].getKeyWord();
68 valueText = (String)h.get(keyW);
69 valueKey = (String)vK.get(keyW);
70
71 if ( valueText!=null && valueText.indexOf("'") != -1 ) valueText = Utils.checkSpecialChar(valueText, '\'', "''" );
72 if ( valueText!=null ) record.append(valueText.trim());
73
74 if ( valueKey!=null && valueKey.indexOf("'") != -1 ) valueKey = Utils.checkSpecialChar(valueKey, '\'', "''" );
75 if ( valueKey!=null ) recordKey.append(valueKey.trim());
76 }
77 valueKey = recordKey.toString().trim();
78 valueText = record.toString().trim();
79 if ( !valueText.equals(valueKey) ) {
80
81 String resultIndexValues[]=null;
82 resultIndexValues = new String[resultsIndex.size()];
83 resultsIndex.toArray(resultIndexValues);
84 iMax = resultIndexValues.length;
85 for (i=0;i<iMax;i++) {
86 if( resultIndexValues[i].equals(valueKey) ) {
87 posit=i;
88 break;
89 }
90 }
91 if (posit>=0) resultsIndex.setElementAt(record.toString(),posit);
92 }
93 }
94 public void delElementIndex ( Hashtable vK ) {
95 String keyW,valueKey;
96 int i,iMax,posit=-1;
97
98 iMax = this.elements.length;
99
100 StringBuffer recordKey = new StringBuffer();
101 for (i=0;i<iMax;i++) {
102 keyW = elements[i].getKeyWord();
103 valueKey = (String)vK.get(keyW);
104
105 if ( valueKey!=null && valueKey.indexOf("'") != -1 ) valueKey = Utils.checkSpecialChar(valueKey, '\'', "''" );
106 if ( valueKey!=null ){
107 if(i>0) recordKey.append("|*|");
108 recordKey.append(valueKey.trim());
109 }
110 }
111 valueKey = recordKey.toString().trim();
112
113 String resultIndexValues[]=null;
114 resultIndexValues = new String[resultsIndex.size()];
115
116 if (Utils.debug) System.out.println("resultsIndex in delElementIndex : " + resultsIndex);
117 resultsIndex.toArray(resultIndexValues);
118 iMax = resultIndexValues.length;
119 for (i=0;i<iMax;i++) {
120 if( resultIndexValues[i].equals(valueKey) ) {
121 posit=i;
122 break;
123 }
124 }
125 if (posit>=0) resultsIndex.removeElementAt(posit);
126
127 }
128
129 public void setPositionStartLine(int num) {
130 this.positionStartLine = num;
131 }
132 public int getPositionStartLine() {
133 return positionStartLine;
134 }
135 public int getPositionEndLine() {
136 return positionEndLine;
137 }
138 public String getEnteteCondition() {
139 return enteteCondition.toString();
140 }
141 public Vector getResultsIndex() {
142 return resultsIndex;
143 }
144 public String getConditionWhere(int start, int num, boolean okInsert) {
145 int i, k, compteur;
146 boolean okFound;
147 newCriteria.delete(0,newCriteria.capacity());
148 newCriteria.append(" AND ");
149 newCriteria.append(enteteCondition.toString());
150 newCriteria.append(" IN (");
151
152 compteur = 0;
153 okFound = false;
154
155 if(Utils.debug) System.out.println("start : " + start + " num :" +num + " resultsIndex.size() : " + resultsIndex.size());
156
157 k = resultsIndex.size();
158
159
160 if(num <1000)
161 {
162 for (i=start;i<(start+num) && i<resultsIndex.size();i++) {
163 okFound = true;
164 newCriteria.append("'") ;
165 newCriteria.append((String)resultsIndex.elementAt(i));
166 newCriteria.append("'," );
167 compteur++;
168 }
169 }
170
171 else if(num >= 1000)
172 {
173 for(i=start; i<start+num ;i+=1000)
174 {
175 if(i > start)
176 {
177 if (okFound) newCriteria.delete(newCriteria.length()-1,newCriteria.length());
178 else newCriteria.append("NULL");
179 newCriteria.append(")");
180 newCriteria.append(" OR " + enteteCondition.toString() + " IN (");
181 }
182
183 for(int j=i ; j<(1000 + i) && j < num+start ; j++)
184 {
185 okFound = true;
186 newCriteria.append("'") ;
187 newCriteria.append((String)resultsIndex.elementAt(j));
188 newCriteria.append("'," );
189 compteur++;
190 }
191 }
192 }
193
194
195 if (okInsert==false) positionEndLine = compteur;
196
197 if (okFound) newCriteria.delete(newCriteria.length()-1,newCriteria.length());
198 else newCriteria.append("NULL");
199 newCriteria.append(")");
200
201 if(Utils.debug) System.out.println("Where condition in QuerySelectIndex: " + newCriteria.toString());
202
203 return newCriteria.toString();
204 }
205
206 public void init(ConnectionPool connectionPool) throws SQLWarning,SQLException{
207 String key,valueText,type;
208 int i,iMax;
209
210 operator_concat = connectionPool.getOperatorConcat();
211 function_concat = connectionPool.getConversionFunction();
212
213 iMax = elements.length;
214
215 select.delete(0,select.capacity());
216 enteteCondition.delete(0,enteteCondition.capacity());
217
218 select.append("SELECT ");
219 for (i=0;i<iMax;i++) {
220
221 if(i>0) {
222 enteteCondition.append(operator_concat);
223 enteteCondition.append("'|*|'");
224 enteteCondition.append(operator_concat);
225 }
226 select.append(elements[i].getField());
227 select.append(" ,");
228 if ( elements[i].getType().equals("N") ) {
229
230 enteteCondition.append("LTRIM(");
231 enteteCondition.append(function_concat + "(");
232 enteteCondition.append(elements[i].getField());
233 enteteCondition.append("))");
234 }
235 else {
236 enteteCondition.append("RTRIM(");
237 enteteCondition.append(elements[i].getField());
238 enteteCondition.append(") ");
239
240 }
241
242 }
243 if (Utils.debug) System.out.println("enteteCondition="+enteteCondition);
244 if (Utils.debug) System.out.println("operator_concat="+operator_concat);
245
246
247
248 select.delete(select.length()-1,select.length());
249 select.append(" FROM ");
250 select.append(themeIdParentFile);
251 select.append(" WHERE 1=1 ");
252 select.append(conditionWhere);
253 if (conditionOrder.length() > 0 ) select.append(conditionOrder);
254 if (Utils.debug) System.out.println("Requete principale : "+select);
255 Connection conn = null;
256 Statement stmt = null;
257 ResultSet rqPrincipal = null;
258 try {
259 conn = connectionPool.getConnection();
260 stmt = conn.createStatement();
261 rqPrincipal = stmt.executeQuery(select.toString());
262 while (rqPrincipal.next()) {
263 StringBuffer record = new StringBuffer();
264 for (i=0;i<iMax;i++) {
265 if(i>0) record.append("|*|");
266 valueText = rqPrincipal.getString(i+1);
267 if ( valueText!=null && valueText.indexOf("'") != -1 ) valueText = Utils.checkSpecialChar(valueText, '\'', "''" );
268 if ( valueText!=null ) {
269 if ( elements[i].getType().equals("N") ) {
270
271 record.append(String.valueOf((long)Float.parseFloat(valueText)));
272 } else {
273 record.append(valueText.trim());
274
275 }
276 }
277 }
278 resultsIndex.addElement(record.toString());
279 }
280
281 }finally{
282
283 if (rqPrincipal != null){
284 rqPrincipal.close();
285 }
286 if (stmt != null){
287 stmt.close();
288 }
289 if (conn != null){
290 connectionPool.freeConnection(conn);
291 }
292 }
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308 }
309
310 }