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 QuerySelectBean extends Thread {
12
13 private String themeIdParentFile, conditionWhere, conditionOrder;
14 private ConnectionPool pool;
15 private Hashtable querySelects, elementsIndex;
16 private Vector elementsListe,elementsUpdate;
17 private int nbLines,indCol;
18 private int indLine;
19 private String results[][];
20 boolean stopRun = false;
21 boolean failled = false;
22 Exception exception = null;
23
24 public QuerySelectBean(ConnectionPool pool,Hashtable qS, Hashtable kS, String s2, String s3, String s4, String res[][], Vector v1, Vector v2) {
25
26
27 this.pool = pool;
28 this.querySelects = qS;
29 this.elementsIndex = kS;
30 this.themeIdParentFile = s2;
31 this.conditionWhere = s3;
32 this.conditionOrder = s4;
33 this.nbLines = res.length;
34 this.results = res;
35 this.elementsListe = v1;
36 this.elementsUpdate = v2;
37 if (Utils.debug){
38
39 System.out.println("QuerySelectBean::QuerySelectBean - querySelects="+querySelects);
40 System.out.println("QuerySelectBean::QuerySelectBean - elementsIndex="+elementsIndex);
41 System.out.println("QuerySelectBean::QuerySelectBean - themeIdParentFile="+themeIdParentFile);
42 System.out.println("QuerySelectBean::QuerySelectBean - conditionWhere="+conditionWhere);
43 System.out.println("QuerySelectBean::QuerySelectBean - conditionOrder="+conditionOrder);
44 System.out.println("QuerySelectBean::QuerySelectBean - results="+results);
45 System.out.println("QuerySelectBean::QuerySelectBean - elementsListe="+elementsListe);
46 System.out.println("QuerySelectBean::QuerySelectBean - elementsUpdate="+elementsUpdate);
47
48
49
50 }
51 }
52
53 public synchronized void run() {
54 if (Utils.debug)System.out.println("QuerySelectBean::run - begin");
55
56 try {
57 launchQueryParent();
58 }
59 catch (SQLException e) {
60 Utils.SqlLogToFile("SQLException in when starting the query select bean thread: \n"+e.getMessage());
61 this.stopRun(e);
62 }
63 finally {
64 notifyAll();
65 }
66 }
67
68 public void stopRun() {
69 stopRun = true;
70 }
71 private void stopRun(Exception ex) {
72 stopRun = true;
73 failled = true;
74 exception = ex;
75 }
76 public boolean hasFailled() {
77 return failled;
78 }
79 public Exception getException() {
80 return exception;
81 }
82
83 public String getThemeIdParentFile() {
84 return themeIdParentFile;
85 }
86
87 public int getLineRecordProgress() {
88 return indLine;
89 }
90
91 void launchQueryParent () throws SQLException {
92 if (Utils.debug)System.out.println("QuerySelectBean::launchQueryParent - begin");
93 Exception trackException = null;
94 String select = "";
95 long lStartTime = System.currentTimeMillis();
96 try{
97 Tracker.begin();
98 String criteria,valueText;
99 String key,label,field,childFile,keyChildFile;
100 int index;
101 int i,j,indexP,iMax,jMax,indexPMax;
102 boolean found;
103
104 QuerySelect qs = (QuerySelect)querySelects.get(themeIdParentFile);
105 select = getSelectString(themeIdParentFile, qs);
106 select = select + " WHERE (1=1) " + conditionWhere + conditionOrder;
107 if (Utils.debug)System.out.println("QuerySelectBean::launchQueryParent - Query Pricipal + Sort : " + select);
108
109 Connection conn = null;
110 Statement stmt = null;
111 ResultSet rqPrincipal = null;
112 valueText = null;
113 key=null;
114 childFile=null;
115 field=null;
116 keyChildFile=null;
117 index=0;
118 try {
119 conn = pool.getConnection();
120 stmt = conn.createStatement();
121
122
123
124 rqPrincipal = stmt.executeQuery(select);
125
126 indLine = 0;
127 indCol = -1;
128 while ( (rqPrincipal.next() && indLine < nbLines) && !stopRun) {
129
130 iMax = qs.getQuerySelectFieldSize();
131 for (i=0;i<iMax && !stopRun;i++) {
132
133 key = qs.getKeyWord(i);
134 label = qs.getLabel(i);
135 field = qs.getField(i);
136 childFile = qs.getChildFile(i);
137 keyChildFile = qs.getKeychildFile(i);
138 index = qs.getIndex(i);
139
140
141 try {
142 valueText = rqPrincipal.getString(index);
143 } catch (SQLException e) {
144 valueText = "";
145 }
146 if (valueText==null || valueText.length()==0) valueText = "";
147 if (Utils.debug) System.out.println("valueText : " + valueText);
148
149 if ( elementsIndex.containsKey(key) ) indCol = ((Integer)elementsIndex.get(key)).intValue();
150 else indCol = -1;
151 if ( childFile==null || childFile.equals("") ) {
152 if (indCol != -1) results[indLine][indCol] = valueText.trim();
153 }
154 else {
155 criteria = getCriteriaInput(qs, rqPrincipal, field,childFile,keyChildFile,index,valueText );
156 if (Utils.debug)System.out.println("QuerySelectBean::launchQueryParent - criteria for child "+childFile+"="+criteria);
157 if ( criteria.length()>0 ) launchQueryChild(conn, results, indLine, childFile, key );
158 }
159 }
160 indLine++;
161 }
162 }catch(SQLWarning e1){
163 System.out.println("SQLWarning launchQueryParent : " + e1.getMessage());
164 System.out.println("SQLState: " + e1.getSQLState());
165 System.out.println("VendorError: " + e1.getErrorCode());
166 System.out.println("Exception in Requete Parent : " + select);
167 throw e1;
168 }
169 catch(SQLException e){
170 System.out.println("SQLException: " + e.getMessage());
171 System.out.println("SQLState: " + e.getSQLState());
172 System.out.println("VendorError: " + e.getErrorCode());
173 System.out.println("Exception in Requete launchQueryParent : " + select);
174 throw e;
175 }finally{
176 if (rqPrincipal != null){
177 rqPrincipal.close();
178 }
179 if (stmt != null){
180 stmt.close();
181 }
182 if (conn != null){
183 pool.freeConnection(conn);
184 }
185 }
186 } catch (SQLException e) {
187 trackException = e;
188 throw e;
189 }finally{
190
191 if (Utils.m_bSqlTimings){
192 String sThreadName = Thread.currentThread().getName();
193 long lTime = System.currentTimeMillis() - lStartTime;
194 if (lTime >100){
195 System.out.println("QuerySelectBean::launchQueryParent warning");
196 System.out.println("QuerySelectBean::launchQueryParent warning");
197 System.out.println("QuerySelectBean::launchQueryParent warning");
198 System.out.println("QuerySelectBean::launchQueryParent warning");
199 System.out.println("QuerySelectBean::launchQueryParent - sThreadName="+sThreadName+", sql="+select+", total time="+lTime+"ms");
200 System.out.println("QuerySelectBean::launchQueryParent warning");
201 System.out.println("QuerySelectBean::launchQueryParent warning");
202 System.out.println("QuerySelectBean::launchQueryParent warning");
203 System.out.println("QuerySelectBean::launchQueryParent warning");
204 }else{
205 System.out.println("QuerySelectBean::launchQueryChild - sThreadName="+sThreadName+", sql="+select+", total time="+lTime+"ms");
206 }
207 }
208 Tracker.end(trackException);
209 }
210 }
211
212 String getSelectString(String fileName, QuerySelect q ) {
213 String selectString;
214 selectString = q.getQuerySelect();
215 selectString = selectString.substring(0,selectString.lastIndexOf(","));
216 selectString = "SELECT "+selectString+" FROM "+q.getFileName();
217 return selectString;
218
219 }
220
221 String getCriteriaInput (QuerySelect q, ResultSet r, String fld, String child, String keyChild, int ind, String valueT) throws SQLException {
222 String criteriaText,valueText,criteriaBefore,typeField;
223 int x,y;
224 QuerySelect qss;
225
226 criteriaText = "";
227 valueText = "";
228 typeField = "";
229
230 ind = ind - 1;
231
232
233
234 valueText = valueT;
235 valueText = Utils.checkSpecialChar(valueText, '\'', "''" );
236 typeField = q.getTypeField(ind);
237 if ( valueText==null || valueText.equals("") ) {
238 if (!typeField.equals("N")){
239
240
241
242
243 criteriaText = " "+keyChild+" = '' ";
244 }else{
245 criteriaText = " "+keyChild+" IS NULL ";
246 }
247 }
248 else {
249 if (!typeField.equals("N")) {
250 criteriaText = " "+keyChild+" = '"+valueText.trim()+"' ";
251 if (typeField.equals("L")) criteriaText = criteriaText + " AND CODE_LANGUE='"+Utils.langue+"' ";
252 }
253 else {
254
255 criteriaText = " "+keyChild+"= '"+valueText.trim()+"' ";
256 }
257 }
258 y = q.getQuerySelectFieldSize();
259
260 for (x=0;x<y;x++) {
261
262 qss = (QuerySelect)querySelects.get(q.getChildFile(x));
263 if ( qss==null ) {
264 criteriaBefore = "";
265 }
266 else {
267 criteriaBefore = qss.getCriteria();
268 }
269
270 if ( q.getKeyWord(x).indexOf("|CHILD|",0)!= -1 ) {
271 typeField = q.getTypeField(ind);
272 if (Utils.debug) System.out.println("typeField in |CHILD|: " + typeField);
273 if ( child.equals(q.getChildFile(x)) ) {
274 if ( keyChild.equals(q.getKeychildFile(x))==false ) {
275
276
277 if ( criteriaBefore==null || criteriaBefore.indexOf(criteriaText,0) < 0 ) {
278 valueText = r.getString(q.getIndex(x));
279 if ( valueText==null || valueText.equals("") ) {
280 if (!typeField.equals("N"))
281
282
283
284
285 criteriaText = criteriaText + " AND ( " + q.getKeychildFile(x) + "= '' )";
286 else {
287 criteriaText = criteriaText + " AND " + q.getKeychildFile(x) + " IS NULL ";
288 }
289 }
290 else {
291 valueText = Utils.checkSpecialChar(valueText, '\'', "''" );
292 if (!typeField.equals("N")) {
293 criteriaText = criteriaText + " AND "+q.getKeychildFile(x)+" = '"+valueText.trim()+"' ";
294 if (typeField.equals("L")) criteriaText = criteriaText + " AND CODE_LANGUE='"+Utils.langue+"' ";
295 }
296 else {
297
298 criteriaText = criteriaText + " AND "+q.getKeychildFile(x)+"= '"+valueText.trim()+"' ";
299 }
300 }
301 }
302 else {
303 criteriaText = "";
304 return criteriaText;
305 }
306 }
307 }
308 }
309 }
310
311
312
313
314
315
316
317
318
319
320
321
322
323 QuerySelect _qC = (QuerySelect)querySelects.get(child);
324 _qC.setCriteria(criteriaText);
325
326 return criteriaText;
327 }
328
329 void launchQueryChild ( Connection conn, String[][] _results,int _indLine, String fileName, String _keyW ) throws SQLException {
330 if (Utils.debug)System.out.println("QuerySelectBean::launchQueryChild - begin");
331 Exception trackException = null;
332 String _select = "";
333 long lStartTime = System.currentTimeMillis();
334 try{
335 Tracker.begin();
336 String _criteria,_valueText;
337 String _key,_label,_field,_childFile,_keyChildFile;
338 int _index;
339 int _i,_j,_indexP,_iMax,_jMax,_indexPMax, _indCol;
340 boolean _found,_foundK, _childFound ;
341
342 QuerySelect qC = (QuerySelect)querySelects.get(fileName);
343 _select = getSelectString(fileName, qC);
344
345 _criteria = qC.getCriteria();
346
347
348 _childFound = qC.isChildsFound(elementsListe,elementsUpdate);
349
350 if ( _criteria!=null && _criteria.length() >0 && _childFound) {
351 _select = _select + " WHERE " + _criteria;
352 if (Utils.debug) System.out.println("QuerySelectBean::launchQueryChild - _select="+_select);
353
354
355 Connection _conn = conn;
356 Statement _stmt = null;
357 ResultSet rqC = null;
358 try {
359 _stmt = _conn.createStatement();
360
361 rqC = _stmt.executeQuery(_select);
362 _found = false;
363 _indCol = -1;
364 while (rqC.next()) {
365 _found = true;
366 _jMax = qC.getQuerySelectFieldSize();
367 for (_j=0;_j<_jMax;_j++){
368
369 _key = qC.getKeyWord(_j);
370 _label = qC.getLabel(_j);
371 _field = qC.getField(_j);
372 _childFile = qC.getChildFile(_j);
373 _keyChildFile = qC.getKeychildFile(_j);
374 _index = qC.getIndex(_j);
375
376 _valueText = "";
377 try{
378 _valueText = rqC.getString(_index);
379 }catch (SQLException e){
380 _valueText = "";
381 }
382 if (_valueText==null || _valueText.length()==0) _valueText = "";
383
384 if ( elementsIndex.containsKey(_key) ) _indCol = ((Integer)elementsIndex.get(_key)).intValue();
385 else _indCol = -1;
386 if ( _childFile==null || _childFile.equals("") ) {
387 _foundK = false;
388 if ( _keyChildFile==null || _keyChildFile.equals("") ) {
389 _foundK = true;
390 }else{
391 if ( _keyW.trim().indexOf(_keyChildFile.trim(),0) > 0 ) _foundK = true;
392 else _foundK = false;
393 }
394 if(_foundK ){
395 if(_indCol != -1) _results[_indLine][_indCol] = _valueText.trim();
396 }
397 }else{
398 _criteria = getCriteriaInput(qC, rqC, _field,_childFile,_keyChildFile,_index,_valueText);
399 if (Utils.debug)System.out.println("QuerySelectBean::launchQueryChild - _criteria=" + _criteria);
400 if ( _criteria.equals("")==false ) launchQueryChild(_conn, _results,_indLine, _childFile, _key);
401 }
402 }
403 }
404 }catch(SQLWarning e1){
405 System.out.println("SQLWarning: " + e1.getMessage());
406 System.out.println("SQLState: " + e1.getSQLState());
407 System.out.println("VendorError: " + e1.getErrorCode());
408 System.out.println("Exception in Requete LaunchQueryChild: " + _select);
409 throw e1;
410 }catch(SQLException e) {
411 System.out.println("SQLException: " + e.getMessage());
412 System.out.println("SQLState: " + e.getSQLState());
413 System.out.println("VendorError: " + e.getErrorCode());
414 System.out.println("Exception in Requete LaunchQueryChild : " + _select);
415 throw e;
416 }finally{
417 try{
418 if (rqC != null){
419 rqC.close();
420 }
421 if (_stmt != null){
422 _stmt.close();
423 }
424 }catch(SQLException e) {
425 System.out.println("SQLException: " + e.getMessage());
426 System.out.println("SQLState: " + e.getSQLState());
427 System.out.println("VendorError: " + e.getErrorCode());
428 System.out.println("Exception in Requete LaunchQueryChild : " + _select);
429 }
430 }
431 }
432 } catch (SQLException e) {
433 trackException = e;
434 throw e;
435 }finally{
436 if (Utils.m_bSqlTimings){
437 String sThreadName = Thread.currentThread().getName();
438 long lTime = System.currentTimeMillis() - lStartTime;
439 if (lTime >100){
440 System.out.println("QuerySelectBean::launchQueryChild warning");
441 System.out.println("QuerySelectBean::launchQueryChild warning");
442 System.out.println("QuerySelectBean::launchQueryChild warning");
443 System.out.println("QuerySelectBean::launchQueryChild warning");
444 System.out.println("QuerySelectBean::launchQueryChild - sThreadName="+sThreadName+", sql="+_select+", total time="+lTime+"ms");
445 System.out.println("QuerySelectBean::launchQueryChild warning");
446 System.out.println("QuerySelectBean::launchQueryChild warning");
447 System.out.println("QuerySelectBean::launchQueryChild warning");
448 System.out.println("QuerySelectBean::launchQueryChild warning");
449 }else{
450 System.out.println("QuerySelectBean::launchQueryChild - sThreadName="+sThreadName+", sql="+_select+", total time="+lTime+"ms");
451 }
452 }
453 Tracker.end(trackException);
454 }
455 }
456
457 }