Source code: com/flexstor/ejb/util/FlexSqlPreparedStatement.java
1 /*
2 * FlexSqlPreparedStatement.java
3 *
4 * Copyright $Date: 2003/08/11 02:22:35 $ FLEXSTOR.net Inc.
5 *
6 * This work is licensed for use and distribution under license terms found at
7 * http://www.flexstor.org/license.html
8 *
9 */
10
11 package com.flexstor.ejb.util;
12
13 import java.sql.Connection;
14 import java.sql.PreparedStatement;
15 import java.sql.SQLException;
16 import java.sql.Types;
17 import java.text.ParseException;
18 import java.text.SimpleDateFormat;
19 import java.util.Date;
20 import java.util.Enumeration;
21 import java.util.Hashtable;
22 import java.util.Vector;
23
24 import com.flexstor.common.constants.FieldConstantsI;
25
26 /**
27 * <code>FlexSqlPreparedStatement</code> class dinamically creates a
28 * <code>java.sql.PreparedStatement</code> (SELECT, INSERT, UPDATE and DELETE).
29 *
30 * <code>FlexSqlPreparedStatement</code> constructs SQL statements of the following form:
31 * <pre>
32 * <strong>SELECT</strong> field1, .., fieldN
33 * [<strong>INTO</strong> new_table]
34 * <strong>FROM</strong> table1, .., tableN
35 * [<strong>WHERE</strong> (condition1 operand1 condition2) .. operandN-1 conditionN]
36 * [<strong>SORT BY</strong> field1, .., fieldN]
37 * [<strong>ORDER BY</strong> field1, .., fieldN]
38 *
39 * <strong>INSERT INTO</strong> table
40 * ( field1, .., fieldN )
41 * <strong>VALUES</strong> ( value1, .., valueN ) | select_statement
42 *
43 * <strong>UPDATE</strong> table
44 * <strong>SET</strong> field1 = value1, .., fieldN = valueN
45 * [<strong>WHERE</strong> (condition1 operand1 condition2) .. operandN-1 conditionN]
46 *
47 * <strong>DELETE FROM</strong> table
48 * [<strong>WHERE</strong> (condition1 operand1 condition2) .. operandN-1 conditionN]
49 *
50 * Where:
51 * condition = field operand value | select_statement | nested condition
52 * Notes:
53 * Clauses in brackets ( [] ) are optional
54 * Clauses separated by pipe ( | ) are alternatives
55 * </pre>
56 *
57 * @author David Cardozo
58 * @since FLEXSTORdb 3.0
59 */
60 public class FlexSqlPreparedStatement
61 {
62 public static final int SELECT = 0;
63 public static final int INSERT = 1;
64 public static final int UPDATE = 2;
65 public static final int DELETE = 3;
66 public static final int FIELD_TYPE_COLUMN = -1; // Special type for doing a comparison between columns.
67
68 private int nStatement = -1; // The type of SQL statement to be created
69 //private StringBuffer sbStatement = null; // The SQL statement to create
70 private Vector vTableList = null; // List of tables for a SELECT statement
71 private Vector vFieldList = null; // List of fields for a SELECT or INSERT statement
72 private Vector vValueList = null; // List of values for an INSERT statement
73 private Vector vValueStatementList = null; // Holds the values of the SELECT statement for an INSERT statement
74 private Hashtable htFieldValueList = null; // List of field values to be set in an INSERT or UDPATE statement
75 private String sNewTable = null; // For adding the INTO clause to a SELECT statement
76 private Vector vSortByFields = null; // For adding the SORT BY clause to a SELECT statement
77 private Vector vOrderByFields = null; // For adding the ORDER BY clause to a SELECT statement
78 private StringBuffer sbWhereClause = null; // For adding conditions to the WHERE clause
79 private Vector vWhereClause = null; // List of field values for the WHERE clause
80 private StringBuffer sbTempWhereClause = null; // For temporary creation of a WHERE clause
81 private Vector vTempWhereClause = null; // For temporary creation of a WHERE clause
82 private boolean bAddInto = false;
83 private boolean bAddSortBy = false;
84 private boolean bAddOrderBy = false;
85 private boolean bAddWhere = false;
86
87 public FlexSqlPreparedStatement( int nStatement )
88 {
89 this.nStatement = nStatement;
90 }
91
92 /**
93 * Adds a table for a SELECT, INSERT, UPDATE or DELETE statement.
94 * For a SELECT statement, this method could be called to add more than one
95 * table into the FROM clause.
96 * <pre>
97 * <strong>SELECT</strong> ... <strong>FROM</strong> table1, .., tableN
98 * <strong>INSERT INTO</strong> table
99 * <strong>UPDATE</strong> table
100 * <strong>DELETE FROM</strong> table
101 * </pre>
102 */
103 public void addTable( String sTable )
104 {
105 if ( vTableList == null )
106 vTableList = new Vector(1);
107
108 if ( sTable != null )
109 vTableList.addElement( sTable );
110 }
111
112 /**
113 * Adds multiple tables for a SELECT statement.
114 * <pre>
115 * <strong>SELECT</strong> ... <strong>FROM</strong> table1, .., tableN
116 * </pre>
117 */
118 public void addTables( Vector vTables )
119 {
120 if ( vTableList == null )
121 vTableList = new Vector();
122
123 if ( vTables != null )
124 for ( int i = 0; i < vTables.size(); i++ )
125 vTableList.addElement( (String)vTables.elementAt(i) );
126 }
127
128 /**
129 * Adds one field for a SELECT or INSERT statement.
130 * This method could be called several times for adding more fields.
131 * <pre>
132 * <strong>SELECT</strong> field1, .., fieldN
133 * <strong>INSERT INTO</strong> ... ( field1, fieldN )
134 * </pre>
135 */
136 public void addField( String sField )
137 {
138 if ( vFieldList == null )
139 vFieldList = new Vector(1);
140
141 if ( sField != null )
142 vFieldList.addElement( sField );
143 }
144
145 /**
146 * Adds multiple fields for a SELECT or INSERT statement.
147 * <pre>
148 * <strong>SELECT</strong> field1, .., fieldN
149 * <strong>INSERT INTO</strong> ... ( field1, fieldN )
150 * </pre>
151 */
152 public void addFields( Vector vFields )
153 {
154 if ( vFieldList == null )
155 vFieldList = new Vector();
156
157 if ( vFields != null )
158 for ( int i = 0; i < vFields.size(); i++ )
159 vFieldList.addElement( (String)vFields.elementAt(i) );
160 }
161
162 /**
163 * Adds the INTO new_table clause to the SELECT statement.
164 * <pre>
165 * <strong>INTO</strong> new_table
166 * </pre>
167 */
168 public void addIntoClause( String sNewTable )
169 {
170 this.bAddInto = true;
171 this.sNewTable = sNewTable;
172 }
173
174 /**
175 * Adds a field to the SORT BY clause to the SELECT statement.
176 * This method could be called several times for adding more fields.
177 * <pre>
178 * <strong>SORT BY</strong> field1, .., fieldN
179 * </pre>
180 */
181 public void addSortByClause( String sField )
182 {
183 this.bAddSortBy = true;
184 if ( vSortByFields == null )
185 vSortByFields = new Vector(1);
186
187 if ( sField != null )
188 vSortByFields.addElement( sField );
189 }
190
191 /**
192 * Adds the SORT BY clause to the SELECT statement.
193 * <pre>
194 * <strong>SORT BY</strong> field1, .., fieldN
195 * </pre>
196 */
197 public void addSortByClause( Vector vFields )
198 {
199 this.bAddSortBy = true;
200 if ( vSortByFields == null )
201 vSortByFields = new Vector();
202
203 if ( vFields != null )
204 for ( int i = 0; i < vFields.size(); i++ )
205 vSortByFields.addElement( (String)vFields.elementAt(i) );
206 }
207
208 /**
209 * Adds a field to the ORDER BY clause to the SELECT statement.
210 * This method could be called several times for adding more fields.
211 * <pre>
212 * <strong>ORDER BY</strong> field1, .., fieldN
213 * </pre>
214 */
215 public void addOrderByClause( String sField )
216 {
217 this.bAddOrderBy = true;
218 if ( vOrderByFields == null )
219 vOrderByFields = new Vector(1);
220
221 if ( sField != null )
222 vOrderByFields.addElement( sField );
223 }
224
225 /**
226 * Adds the ORDER BY clause to the SELECT statement.
227 * <pre>
228 * <strong>ORDER BY</strong> field1, .., fieldN
229 * </pre>
230 */
231 public void addOrderByClause( Vector vFields )
232 {
233 this.bAddOrderBy = true;
234 if ( vOrderByFields == null )
235 vOrderByFields = new Vector();
236
237 if ( vFields != null )
238 for ( int i = 0; i < vFields.size(); i++ )
239 vOrderByFields.addElement( (String)vFields.elementAt(i) );
240 }
241
242 /**
243 * Creates the first condition for a WHERE clause.
244 * The WHERE clause must be added to the statement by calling addWhereClause()
245 * <pre>
246 * <strong>WHERE</strong> field1 operand1 value1
247 * </pre>
248 */
249 public void createConditionForWhere( String sField, String sInternalOp, int nType, String sValue )
250 {
251 sbTempWhereClause = new StringBuffer( sField + " " + sInternalOp );
252 if ( nType == FIELD_TYPE_COLUMN )
253 sbTempWhereClause.append( " " + sValue + " " );
254 else
255 {
256 // If the value start with a SELECT keyword, it means that it is part of a lookup clause
257 // that looks like:
258 // WHERE make in ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE 'Ch%' )
259 // We need to remove the 'Ch%' portion and replace it with a question mark
260 // In sbTempWhereClause, instead of appending a " ? " we will be appending a
261 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE ? )
262 if ( sValue.startsWith("( SELECT") || sValue.startsWith("nvl((") )
263 {
264 String[] sNewValues = replaceValueInSelect( nType, sValue );
265 sbTempWhereClause.append( sNewValues[0] );
266 sValue = sNewValues[1];
267 }
268 else
269 sbTempWhereClause.append( " ? " );
270
271 vTempWhereClause = new Vector(1);
272 vTempWhereClause.addElement( new String[] { String.valueOf(nType), sValue } );
273 }
274 }
275
276 /**
277 * Appends a condition to a "in construction" WHERE clause.
278 * <pre>
279 * -<strong>WHERE</strong> field1 operand1 value1- OPERAND field2 operand2 value2
280 * </pre>
281 */
282 public void appendConditionToWhere( String sExternalOp, String sField, String sInternalOp, int nType, String sValue )
283 {
284 if ( sbTempWhereClause == null )
285 createConditionForWhere( sField, sInternalOp, nType, sValue );
286 else
287 {
288 sbTempWhereClause.append( sExternalOp + " " );
289 sbTempWhereClause.append( sField + " " + sInternalOp );
290 if ( nType == FIELD_TYPE_COLUMN )
291 sbTempWhereClause.append( " " + sValue + " " );
292 else
293 {
294 // If the value start with a SELECT keyword, it means that it is part of a lookup clause
295 // that looks like:
296 // WHERE make in ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE 'Ch%' )
297 // We need to remove the 'Ch%' portion and replace it with a question mark
298 // In sbTempWhereClause, instead of appending a " ? " we will be appending a
299 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE ? )
300 if ( sValue.startsWith("( SELECT") || sValue.startsWith("nvl((") )
301 {
302 String[] sNewValues = replaceValueInSelect( nType, sValue );
303 sbTempWhereClause.append( sNewValues[0] );
304 sValue = sNewValues[1];
305 }
306 else
307 sbTempWhereClause.append( " ? " );
308
309 vTempWhereClause.addElement( new String[] { String.valueOf(nType), sValue } );
310 }
311 }
312 }
313
314 /**
315 * Creates a select condition as the first condition of a WHERE clause.
316 * The WHERE clause must be added to the statement by calling addWhereClause()
317 * The argument must be a Vector returned by a call to FlexSqlPreparedStatement.getStatement()
318 * containing a previously created SQL SELECT statement.
319 * <pre>
320 * <strong>WHERE</strong> select field1, .., fieldN from table ...
321 * </pre>
322 */
323 public void createSelectConditionForWhere( Vector vSelectStatement )
324 {
325 if ( vSelectStatement != null && vSelectStatement.size() > 0 )
326 {
327 // Get the already created SELECT statement
328 sbTempWhereClause = new StringBuffer( (String)vSelectStatement.elementAt(0) );
329 vTempWhereClause = new Vector( vSelectStatement.size() - 1 );
330 for ( int i = 1; i < vSelectStatement.size(); i++ )
331 vTempWhereClause.addElement( (String[]) vSelectStatement.elementAt(i) );
332 }
333 }
334
335 /**
336 * Appends a condition to a "in construction" WHERE clause.
337 * The argument must be a Vector returned by a call to FlexSqlPreparedStatement.getStatement()
338 * containing a previously created SQL SELECT statement.
339 * <pre>
340 * -<strong>WHERE</strong> select field1, .., fieldN from table ...- OPERAND select field1, .., fieldN from table ...
341 * </pre>
342 */
343 public void appendSelectConditionToWhere( String sExternalOp, Vector vSelectStatement )
344 {
345 if ( sbTempWhereClause == null )
346 createSelectConditionForWhere( vSelectStatement );
347 else if ( vSelectStatement != null && vSelectStatement.size() > 0 )
348 {
349 sbTempWhereClause.append( sExternalOp + " " );
350 // Get the already created SELECT statement
351 sbTempWhereClause.append( vSelectStatement.elementAt(0) );
352 for ( int i = 1; i < vSelectStatement.size(); i++ )
353 vTempWhereClause.addElement( (String[]) vSelectStatement.elementAt(i) );
354 }
355 }
356
357 /**
358 * This method creates a IN condition for a "in construction" WHERE clause.
359 * <pre>
360 * <strong>WHERE</strong> field <strong>IN</strong> ( value1, .., valueN )
361 * </pre>
362 * @param sField field name
363 * @param vValues a Vector containing the field type and value for the fields to insert.
364 * The elements of the Vector should be of type String[] where the first
365 * element is the integer type and the second element is a String
366 * representing its value.
367 */
368 public void createInConditionForWhere( String sField, Vector vValues )
369 {
370 if ( sField != null && vValues != null && vValues.size() > 0 )
371 {
372 sbTempWhereClause = new StringBuffer( sField + " IN ( " );
373 vTempWhereClause = new Vector( vValues.size() );
374 String[] saValues = null;
375 for ( int i = 0; i < vValues.size(); i++ )
376 {
377 saValues = (String[]) vValues.elementAt(i);
378 if ( Integer.parseInt(saValues[0]) == FIELD_TYPE_COLUMN )
379 sbTempWhereClause.append( saValues[1] + ", " );
380 else
381 {
382 // If the value start with a SELECT keyword, it means that it is part of a lookup clause
383 // that looks like:
384 // WHERE make in ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE 'Ch%' )
385 // We need to remove the 'Ch%' portion and replace it with a question mark
386 // In sbTempWhereClause, instead of appending a " ? " we will be appending a
387 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE ? )
388 if ( saValues[1].startsWith("( SELECT") || saValues[1].startsWith("nvl((") )
389 {
390 String[] saNewValues = replaceValueInSelect( Integer.parseInt(saValues[0]), saValues[1] );
391 sbTempWhereClause.append( saNewValues[0] );
392 // Recreate the saValue array using the data type in the original saValue
393 // and the newly formatted value in saNewValues
394 saValues = new String[] { saValues[0], saNewValues[1] };
395 }
396 else
397 sbTempWhereClause.append( " ?, " );
398
399 vTempWhereClause.addElement( saValues );
400 }
401 }
402 sbTempWhereClause.setCharAt( sbTempWhereClause.length() - 2, ')' );
403 }
404 }
405
406 /**
407 * This method appends a IN condition to a "in construction" WHERE clause.
408 * <pre>
409 * -<strong>WHERE</strong> field1 operand1 value1- OPERAND field <strong>IN</strong> ( value1, .., valueN )
410 * </pre>
411 * @param sExternalOp The operand between the previous condition and this one
412 * @param sField field name
413 * @param vValues a Vector containing the field type and value for the fields to insert.
414 * The elements of the Vector should be of type String[] where the first
415 * element is the integer type and the second element is a String
416 * representing its value.
417 */
418 public void appendInConditionToWhere( String sExternalOp, String sField, Vector vValues )
419 {
420 if ( sField != null && vValues != null && vValues.size() > 0 )
421 {
422 if ( sbTempWhereClause == null )
423 createInConditionForWhere( sField, vValues );
424 else
425 {
426 sbTempWhereClause.append( sExternalOp + " " + sField + " IN ( " );
427 String[] saValues = null;
428 for ( int i = 0; i < vValues.size(); i++ )
429 {
430 saValues = (String[]) vValues.elementAt(i);
431 if ( Integer.parseInt(saValues[0]) == FIELD_TYPE_COLUMN )
432 sbTempWhereClause.append( saValues[1] + ", " );
433 else
434 {
435 // If the value start with a SELECT keyword, it means that it is part of a lookup clause
436 // that looks like:
437 // WHERE make in ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE 'Ch%' )
438 // We need to remove the 'Ch%' portion and replace it with a question mark
439 // In sbTempWhereClause, instead of appending a " ? " we will be appending a
440 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE ? )
441 if ( saValues[1].startsWith("( SELECT") || saValues[1].startsWith("nvl((") )
442 {
443 String[] saNewValues = replaceValueInSelect( Integer.parseInt(saValues[0]), saValues[1] );
444 sbTempWhereClause.append( saNewValues[0] );
445 // Recreate the saValue array using the data type in the original saValue
446 // and the newly formatted value in saNewValues
447 saValues = new String[] { saValues[0], saNewValues[1] };
448 }
449 else
450 sbTempWhereClause.append( " ?, " );
451
452 vTempWhereClause.addElement( saValues );
453 }
454 }
455 sbTempWhereClause.setCharAt( sbTempWhereClause.length() - 2, ')' );
456 }
457 }
458 }
459
460 /**
461 * Encloses between parenthesis a condition for a "in construction" WHERE clause.
462 * <pre>
463 * <strong>WHERE</strong> (field1 operand1 value1)
464 * </pre>
465 */
466 public void encloseWhereCondition()
467 {
468 if ( sbTempWhereClause != null )
469 {
470 sbTempWhereClause.insert( 0, "( " );
471 sbTempWhereClause.append( ") " );
472 }
473 }
474
475 /**
476 * Add the newly constructed WHERE clause to the SELECT, UPDATE or DELETE statement.
477 * <pre>
478 * -<strong>SELECT</strong> ...- <strong>WHERE</strong> condition1
479 * -<strong>UPDATE</strong> ...- <strong>WHERE</strong> condition1
480 * -<strong>DELETE FROM</strong> ...- <strong>WHERE</strong> condition1
481 * </pre>
482 */
483 public void addWhereClause()
484 {
485 if ( sbTempWhereClause != null )
486 {
487 this.bAddWhere = true;
488 if ( sbWhereClause == null )
489 {
490 sbWhereClause = new StringBuffer( sbTempWhereClause.toString() );
491 vWhereClause = vTempWhereClause;
492 }
493 else
494 {
495 sbWhereClause.append( sbTempWhereClause.toString() );
496 for ( int i = 0; i < vTempWhereClause.size(); i++ )
497 vWhereClause.addElement( (String[])vTempWhereClause.elementAt(i) );
498 }
499 sbTempWhereClause = null;
500 vTempWhereClause = null;
501 }
502 }
503
504 /**
505 * Append a condition to a WHERE clause already added to the SELECT, UPDATE or DELETE statement.
506 * <pre>
507 * -<strong>SELECT</strong> ... <strong>WHERE</strong> condition1- OPERAND condition2
508 * -<strong>UPDATE</strong> ... <strong>WHERE</strong> condition1- OPERAND condition2
509 * -<strong>DELETE FROM</strong> ... <strong>WHERE</strong> condition1- OPERAND condition2
510 * </pre>
511 */
512 public void appendToWhereClause( String sOperand )
513 {
514 if ( sbTempWhereClause != null )
515 {
516 if ( sbWhereClause == null )
517 {
518 addWhereClause();
519 }
520 else
521 {
522 sbWhereClause.append( sOperand + " " + sbTempWhereClause.toString() );
523 for ( int i = 0; i < vTempWhereClause.size(); i++ )
524 vWhereClause.addElement( (String[])vTempWhereClause.elementAt(i) );
525 }
526 sbTempWhereClause = null;
527 vTempWhereClause = null;
528 }
529 }
530
531 /**
532 * Adds a single field/value to be set in an INSERT or UPDATE statement.
533 * This method could be called several times for adding more fields/values.
534 * The sValue argument must be a valid String representation of the actual
535 * field type defined in nType. If the type is:
536 * FieldConstantsI.FIELD_TYPE_NUMBER sValue must be a String representing a java.lang.Integer
537 * or java.lang.Long
538 * FieldConstantsI.FIELD_TYPE_DATE sValue must be a String representating a java.util.Date
539 * FieldConstantsI.FIELD_TYPE_STRING sValue must be a java.lang.String
540 * FieldConstantsI.FIELD_TYPE_DATETIME sValue must be a String representating a java.util.Date
541 * FieldConstantsI.FIELD_TYPE_TIME sValue must be a String representating a java.util.Date
542 * <pre>
543 * -<strong>INSERT INTO</strong> table- ( field1, .., fieldN ) <strong>VALUES</strong> ( value1, .., valueN )
544 * -<strong>UPDATE</strong> table- <strong>SET</strong> field1 = value1, .., fieldN = valueN
545 * </pre>
546 */
547 public void addFieldValue( String sField, int nType, String sValue )
548 {
549 if ( htFieldValueList == null )
550 htFieldValueList = new Hashtable(1);
551
552 htFieldValueList.put( sField, new String[] { String.valueOf(nType), sValue } );
553 }
554
555 /**
556 * Adds multiple fields/values to be set in an INSERT or UPDATE statement.
557 * The value must be a valid String representation of the actual field type defined in nType.
558 * If the type is:
559 * FieldConstantsI.FIELD_TYPE_NUMBER sValue must be a String representing a java.lang.Integer
560 * or java.lang.Long
561 * FieldConstantsI.FIELD_TYPE_DATE sValue must be a String representating a java.util.Date
562 * FieldConstantsI.FIELD_TYPE_STRING sValue must be a java.lang.String
563 * FieldConstantsI.FIELD_TYPE_DATETIME sValue must be a String representating a java.util.Date
564 * FieldConstantsI.FIELD_TYPE_TIME sValue must be a String representating a java.util.Date
565 * <pre>
566 * -<strong>INSERT INTO</strong> table- ( field1, .., fieldN ) <strong>VALUES</strong> ( value1, .., valueN )
567 * -<strong>UPDATE</strong> table- <strong>SET</strong> field1 = value1, .., fieldN = valueN
568 * </pre>
569 * @param htFieldsValues a Hashtable containing the field name, type and value for the
570 * fields to insert or update. The key is the field name; the value
571 * is a String[] where the first element is the integer type and
572 * the second element is a String representing its value.
573 */
574 public void addFieldsValues( Hashtable htFieldsValues )
575 {
576 if ( htFieldValueList == null )
577 htFieldValueList = new Hashtable();
578
579 if ( htFieldsValues != null )
580 {
581 String sField = null;
582 String[] saValues = null;
583 for ( Enumeration e = htFieldsValues.keys(); e.hasMoreElements(); )
584 {
585 sField = (String) e.nextElement();
586 saValues = (String[]) htFieldsValues.get(sField);
587 if ( saValues != null )
588 htFieldValueList.put( sField, saValues );
589 }
590 }
591 }
592
593 /**
594 * Adds a single value to be set in an INSERT statement.
595 * This method could be called several times for adding more values.
596 * The sValue argument must be a valid String representation of the actual
597 * field type defined in nType. If the type is:
598 * FieldConstantsI.FIELD_TYPE_NUMBER sValue must be a String representing a java.lang.Integer
599 * or java.lang.Long
600 * FieldConstantsI.FIELD_TYPE_DATE sValue must be a String representating a java.util.Date
601 * FieldConstantsI.FIELD_TYPE_STRING sValue must be a java.lang.String
602 * FieldConstantsI.FIELD_TYPE_DATETIME sValue must be a String representating a java.util.Date
603 * FieldConstantsI.FIELD_TYPE_TIME sValue must be a String representating a java.util.Date
604 * <pre>
605 * -<strong>INSERT INTO</strong> table- <strong>VALUES</strong> ( value1, .., valueN )
606 * </pre>
607 */
608 public void addValue( int nType, String sValue )
609 {
610 if ( vValueList == null )
611 vValueList = new Vector(1);
612
613 vValueList.addElement( new String[] { String.valueOf(nType), sValue } );
614 }
615
616 /**
617 * Adds multiple values to be set in an INSERT statement.
618 * The value must be a valid String representation of the actual field type defined in nType.
619 * If the type is:
620 * FieldConstantsI.FIELD_TYPE_NUMBER sValue must be a String representing a java.lang.Integer
621 * or java.lang.Long
622 * FieldConstantsI.FIELD_TYPE_DATE sValue must be a String representating a java.util.Date
623 * FieldConstantsI.FIELD_TYPE_STRING sValue must be a java.lang.String
624 * FieldConstantsI.FIELD_TYPE_DATETIME sValue must be a String representating a java.util.Date
625 * FieldConstantsI.FIELD_TYPE_TIME sValue must be a String representating a java.util.Date
626 * <pre>
627 * -<strong>INSERT INTO</strong> table- <strong>VALUES</strong> ( value1, .., valueN )
628 * </pre>
629 * @param vValues a Vector containing the field type and value for the fields to insert.
630 * The elements of the Vector should be of type String[] where the first
631 * element is the integer type and the second element is a String
632 * representing its value.
633 */
634 public void addValues( Vector vValues )
635 {
636 if ( vValueList == null )
637 vValueList = new Vector();
638
639 if ( vValues != null )
640 for ( int i = 0; i < vValues.size(); i++ )
641 vValueList.addElement( (String[])vValues.elementAt(i) );
642 }
643
644 /**
645 * Adds a SELECT statement for a INSERT statement.
646 * The argument must be a Vector returned by a call to FlexSqlPreparedStatement.getStatement()
647 * containing a previously created SQL SELECT statement.
648 * <pre>
649 * -<strong>INSERT INTO</strong> table ( field1, .., fieldN )- select field1, .., fieldN from table ...
650 * </pre>
651 */
652 public void addValue( Vector vSelectStatement )
653 {
654 vValueStatementList = vSelectStatement;
655 }
656
657 /**
658 * Returns a statement created through the methods in this class.
659 * @return a Vector containing the prepared SQL statement (a String) and field type and value
660 * for the fields of this statement, if any. The first element of the Vector is the
661 * prepared SQL statement; the remaining elements are objects of type String[] where
662 * the first element is the integer type and the second element is a String representing
663 * its value.
664 * <pre>
665 * Vector.elementAt(0) = "<strong>UPDATE</strong> table <strong>SET</strong> field1 = ?, .., fieldN-1 = ? <strong>WHERE</strong> fieldN = ?"
666 * Vector.elementAt(1) = String[] where String[0] = FieldConstantsI.FIELD_TYPE_STRING
667 * String[1] = "A String..."
668 * ..
669 * Vector.elementAt(N) = String[] where String[0] = FieldConstantsI.FIELD_TYPE_NUMBER
670 * String[1] = 1234
671 * </pre>
672 */
673 public Vector getStatement()
674 {
675 Vector vStatement = null;
676 switch ( nStatement )
677 {
678 case SELECT:
679 vStatement = constructSelect();
680 break;
681 case INSERT:
682 vStatement = constructInsert();
683 break;
684 case UPDATE:
685 vStatement = constructUpdate();
686 break;
687 case DELETE:
688 vStatement = constructDelete();
689 break;
690 }
691 return vStatement;
692 }
693
694 private Vector constructSelect()
695 {
696 Vector vSelectStatement = new Vector();
697 StringBuffer sbStatement = new StringBuffer( "SELECT " );
698
699 // Add the fields to select
700 if ( vFieldList != null && vFieldList.size() > 0 )
701 {
702 for ( int i = 0; i < vFieldList.size() - 1; i++ )
703 sbStatement.append( (String) vFieldList.elementAt(i) + ", " );
704 // Do the last one
705 sbStatement.append( (String) vFieldList.lastElement() + " " );
706 }
707
708 // Add the INTO clause, if any
709 if ( bAddInto && sNewTable != null )
710 sbStatement.append( "INTO " + sNewTable + " " );
711
712 // Add the FROM clause
713 if ( vTableList != null && vTableList.size() > 0 )
714 {
715 sbStatement.append( "FROM " );
716 for ( int i = 0; i < vTableList.size() - 1; i++ )
717 sbStatement.append( (String) vTableList.elementAt(i) + ", " );
718 // Do the last one
719 sbStatement.append( (String) vTableList.lastElement() + " " );
720 }
721
722 // Add the WHERE clause, if any
723 if ( bAddWhere && sbWhereClause != null )
724 {
725 sbStatement.append( "WHERE " + sbWhereClause.toString() );
726 // Add the values for the WHERE clause
727 if ( vWhereClause != null )
728 for ( int i = 0; i < vWhereClause.size(); i++ )
729 vSelectStatement.addElement( (String[])vWhereClause.elementAt(i) );
730 }
731
732 // Add the SORT BY clause if any
733 if ( bAddSortBy && vSortByFields != null )
734 {
735 sbStatement.append( "SORT BY " );
736 for ( int i = 0; i < vSortByFields.size() - 1; i++ )
737 sbStatement.append( (String) vSortByFields.elementAt(i) + ", " );
738 // Do the last one
739 sbStatement.append( (String) vSortByFields.lastElement() + " " );
740 }
741
742 // Add the ORDER BY clause if any
743 if ( bAddOrderBy && vOrderByFields != null )
744 {
745 sbStatement.append( "ORDER BY " );
746 for ( int i = 0; i < vOrderByFields.size() - 1; i++ )
747 sbStatement.append( (String) vOrderByFields.elementAt(i) + ", " );
748 // Do the last one
749 sbStatement.append( (String) vOrderByFields.lastElement() + " " );
750 }
751
752 // Add the SELECT statement in the first position of the returning Vector
753 vSelectStatement.insertElementAt( sbStatement.toString(), 0 );
754 return vSelectStatement;
755 }
756
757 private Vector constructInsert()
758 {
759 Vector vInsertStatement = new Vector();
760 StringBuffer sbStatement = new StringBuffer( "INSERT INTO " );
761
762 // Add the table name
763 if ( vTableList != null && vTableList.size() > 0 )
764 sbStatement.append( (String)vTableList.elementAt(0) + " " );
765
766 // Insert field, values or the select statement
767 if ( htFieldValueList != null && htFieldValueList.size() > 0 )
768 {
769 StringBuffer sbValues = new StringBuffer( "VALUES (" );
770 sbStatement.append( "(" );
771 String sField = null;
772 String[] saValue = null;
773 for ( Enumeration e = htFieldValueList.keys(); e.hasMoreElements(); )
774 {
775 sField = (String) e.nextElement();
776 saValue = (String[]) htFieldValueList.get(sField);
777 if ( saValue != null )
778 {
779 sbStatement.append( sField + ", " );
780 if ( Integer.parseInt(saValue[0]) == FIELD_TYPE_COLUMN )
781 sbValues.append( saValue[1] + ", " );
782 else
783 {
784 // If the value start with a SELECT keyword or nvl call, it means that it is part of a
785 // lookup clause that looks like:
786 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = 'value' )
787 // or
788 // nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = 'value' ))
789 // We need to remove the 'value' portion and replace it with a question mark
790 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = ? )
791 // or
792 // nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = ? ))
793 if ( saValue[1].startsWith("( SELECT") || saValue[1].startsWith("nvl((") )
794 {
795 String[] saNewValues = replaceValueInSelect( Integer.parseInt(saValue[0]), saValue[1] );
796 sbValues.append( saNewValues[0] + ", " );
797 // Recreate the saValue array using the data type in the original saValue
798 // and the newly formatted value in saNewValues
799 saValue = new String[] { saValue[0], saNewValues[1] };
800 }
801 else
802 sbValues.append( "?, " );
803
804 vInsertStatement.addElement( saValue );
805 }
806 }
807 }
808 sbValues.setCharAt( sbValues.length() - 2, ')' ); // replace last comma with parenthesis
809 sbStatement.setCharAt( sbStatement.length() - 2, ')' ); // replace last comma with parenthesis
810 // Add VALUE clause to INSERT statement
811 sbStatement.append( sbValues.toString() );
812 }
813 else
814 {
815 // Insert the fields, if any
816 if ( vFieldList != null && vFieldList.size() > 0 )
817 {
818 sbStatement.append( "(" );
819 for ( int i = 0; i < vFieldList.size(); i++ )
820 sbStatement.append( (String) vFieldList.elementAt(i) + ", " );
821 sbStatement.setCharAt( sbStatement.length() - 2, ')' );
822 }
823 if ( vValueList != null && vValueList.size() > 0)
824 {
825
826 sbStatement.append( "VALUES (" );
827 String[] saValueList = null;
828 for ( int i = 0; i < vValueList.size(); i++ )
829 {
830 saValueList = (String[]) vValueList.elementAt(i);
831 if ( Integer.parseInt(saValueList[0]) == FIELD_TYPE_COLUMN )
832 sbStatement.append( saValueList[1] + ", " );
833 else
834 {
835 // If the value start with a SELECT keyword or nvl call, it means that it is part of a
836 // lookup clause that looks like:
837 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = 'value' )
838 // or
839 // nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = 'value' ))
840 // We need to remove the 'value' portion and replace it with a question mark
841 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = ? )
842 // or
843 // nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = ? ))
844 if ( saValueList[1].startsWith("( SELECT") || saValueList[1].startsWith("nvl((") )
845 {
846 String[] saNewValues = replaceValueInSelect( Integer.parseInt(saValueList[0]), saValueList[1] );
847 sbStatement.append( saNewValues[0] + ", " );
848 // Recreate the saValue array using the data type in the original saValue
849 // and the newly formatted value in saNewValues
850 saValueList = new String[] { saValueList[0], saNewValues[1] };
851 }
852 else
853 sbStatement.append( "?, " );
854
855 vInsertStatement.addElement( saValueList );
856 }
857 }
858 sbStatement.setCharAt( sbStatement.length() - 2, ')' ); // replace last comma with parenthesis
859 }
860 else if ( vValueStatementList != null && vValueStatementList.size() > 0 )
861 {
862 //add the SELECT statement
863 sbStatement.append( (String) vValueStatementList.elementAt(0) );
864 // Remove the string with the SELECT statement from the vector
865 vValueStatementList.removeElementAt(0);
866 // Add the values for the SELECT statement
867 for ( int i = 0; i < vValueStatementList.size(); i++ )
868 vInsertStatement.addElement( (String[])vValueStatementList.elementAt(i) );
869 }
870 }
871 // Add the INSERT statement in the first position of the returning Vector
872 vInsertStatement.insertElementAt( sbStatement.toString(), 0 );
873 return vInsertStatement;
874 }
875
876 private Vector constructUpdate()
877 {
878 Vector vUpdateStatement = new Vector();
879 StringBuffer sbStatement = new StringBuffer( "UPDATE " );
880
881 // Add the table name
882 if ( vTableList != null && vTableList.size() > 0 )
883 sbStatement.append( (String)vTableList.elementAt(0) + " " );
884
885 sbStatement.append( "SET " );
886 if ( htFieldValueList != null && htFieldValueList.size() > 0 )
887 {
888 String sField = null;
889 String[] saValue = null;
890 for ( Enumeration e = htFieldValueList.keys(); e.hasMoreElements(); )
891 {
892 sField = (String) e.nextElement();
893 saValue = (String[]) htFieldValueList.get(sField);
894 if ( saValue != null )
895 {
896 if ( Integer.parseInt(saValue[0]) == FIELD_TYPE_COLUMN )
897 sbStatement.append( sField + " = " + saValue[1] + ", " );
898 else
899 {
900 // If the value start with a SELECT keyword or nvl call, it means that it is part of a
901 // lookup clause that looks like:
902 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = 'value' )
903 // or
904 // nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = 'value' ))
905 // We need to remove the 'value' portion and replace it with a question mark
906 // ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = ? )
907 // or
908 // nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make = ? ))
909 if ( saValue[1].startsWith("( SELECT") || saValue[1].startsWith("nvl((") )
910 {
911 String[] saNewValues = replaceValueInSelect( Integer.parseInt(saValue[0]), saValue[1] );
912 sbStatement.append( sField + " = " + saNewValues[0] + ", " );
913 // Recreate the saValue array using the data type in the original saValue
914 // and the newly formatted value in saNewValues
915 saValue = new String[] { saValue[0], saNewValues[1] };
916 }
917 else
918 sbStatement.append( sField + " = ?, " );
919 vUpdateStatement.addElement( saValue );
920 }
921 }
922 }
923 sbStatement.setCharAt( sbStatement.length() - 2, ' ' );
924 }
925
926 // Add WHERE clause, if any
927 if ( bAddWhere && sbWhereClause != null )
928 {
929 sbStatement.append( "WHERE " + sbWhereClause.toString() );
930 // Add the values for the WHERE clause
931 if ( vWhereClause != null )
932 for ( int i = 0; i < vWhereClause.size(); i++ )
933 vUpdateStatement.addElement( (String[]) vWhereClause.elementAt(i) );
934 }
935
936 // Add the UPDATE statement in the first position of the returning Vector
937 vUpdateStatement.insertElementAt( sbStatement.toString(), 0 );
938 return vUpdateStatement;
939 }
940
941 private Vector constructDelete()
942 {
943 Vector vDeleteStatement = new Vector();
944 StringBuffer sbStatement = new StringBuffer( "DELETE FROM " );
945
946 // Add the table name
947 if ( vTableList != null && vTableList.size() > 0 )
948 sbStatement.append( (String)vTableList.elementAt(0) + " " );
949
950 // Add WHERE clause, if any
951 if ( bAddWhere && sbWhereClause != null )
952 {
953 sbStatement.append( "WHERE " + sbWhereClause.toString() );
954 // Add the values for the WHERE clause
955 if ( vWhereClause != null )
956 for ( int i = 0; i < vWhereClause.size(); i++ )
957 vDeleteStatement.addElement( (String[])vWhereClause.elementAt(i) );
958 }
959
960 // Add the DELETE statement in the first position of the returning Vector
961 vDeleteStatement.insertElementAt( sbStatement.toString(), 0 );
962 return vDeleteStatement;
963 }
964
965 /**
966 * Returns the java.sql.PreparedStatement object, ready for invoking the execute(), executeQuery()
967 * or executeUpdate() method.
968 */
969 public PreparedStatement getPreparedStatement( Connection connection )
970 throws SQLException
971 {
972 return getPreparedStatement( connection, getStatement() );
973 }
974
975 /**
976 * This method takes a Vector of String[] (field type and value ) and replace each argument
977 * of the PreparedStatement with its corresponding value in the Vector.
978 */
979 private PreparedStatement getPreparedStatement( Connection connection, Vector vStatement )
980 throws SQLException
981 {
982 PreparedStatement prepStatement = null;
983 if ( vStatement != null )
984 {
985 prepStatement = connection.prepareStatement( (String)vStatement.elementAt(0) );
986 String[] saValue = null;
987 int nType;
988 for ( int i = 1; i < vStatement.size(); i++ )
989 {
990 saValue = (String[]) vStatement.elementAt(i);
991 nType = Integer.parseInt(saValue[0]);
992 switch( nType )
993 {
994 case FieldConstantsI.FIELD_TYPE_DATE: // Value must be a java.util.Date
995 case FieldConstantsI.FIELD_TYPE_DATETIME: // Value must be a java.util.Date
996 case FieldConstantsI.FIELD_TYPE_TIME: // Value must be a java.util.Date
997 try
998 {
999 SimpleDateFormat formatter = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
1000 Date d = formatter.parse( saValue[1] );
1001 prepStatement.setDate( i, new java.sql.Date( d.getTime() ) );
1002 }
1003 catch ( ParseException pe )
1004 {
1005 prepStatement.setNull( i, Types.DATE );
1006 }
1007 break;
1008 default:
1009 prepStatement.setString( i, saValue[1] );
1010 break;
1011 }
1012 }
1013 }
1014 return prepStatement;
1015 }
1016
1017 public boolean whereClauseExists()
1018 {
1019 if ( sbWhereClause == null && sbTempWhereClause == null )
1020 return false;
1021 else
1022 return true;
1023 }
1024
1025 /**
1026 * This method takes a String of the form:
1027 * ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE 'Ch%' )
1028 * or
1029 * nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE 'Ch%' ), 0)
1030 * and returns two Strings of the form:
1031 * saReturned[0] = ( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE ? )
1032 * or
1033 * saReturned[0] = nvl(( SELECT cl_buck_0 FROM cl0_make_lu WHERE make LIKE ? ), 0)
1034 * and
1035 * saReturned[1] = Ch%
1036 */
1037 private String[] replaceValueInSelect( int nType, String sValue )
1038 {
1039 StringBuffer sbValue = new StringBuffer();
1040
1041 int nValueStartIndex, nValueEndIndex;
1042 switch ( nType )
1043 {
1044 case FieldConstantsI.FIELD_TYPE_DATE:
1045 // If data type is Date the statement will be of the form:
1046 // ( SELECT NNLU_BUCK_1 FROM NNLU_BUCK_1 WHERE DATEFIELD = to_date('07-15-02','MM/DD/YYYY') )
1047 // we will translate this to:
1048 // ( SELECT NNLU_BUCK_1 FROM NNLU_BUCK_1 WHERE DATEFIELD = to_date(?,'MM/DD/YYYY') )
1049 case FieldConstantsI.FIELD_TYPE_DATETIME:
1050 // If data type is DateTime the statement will be of the form:
1051 // ( SELECT NNLU_BUCK_1 FROM NNLU_BUCK_1 WHERE DATEFIELD = to_date('07-15-02 09:07:32','MM/DD/YYYY hh24:mi:ss') )
1052 // we will translate this to:
1053 // ( SELECT NNLU_BUCK_1 FROM NNLU_BUCK_1 WHERE DATEFIELD = to_date(?,'MM/DD/YYYY hh24:mi:ss') )
1054 case FieldConstantsI.FIELD_TYPE_TIME:
1055 // If data type is Time the statement will be of the form:
1056 // ( SELECT NNLU_BUCK_1 FROM NNLU_BUCK_1 WHERE DATEFIELD = to_date('09:07:32','hh24:mi:ss') )
1057 // we will translate this to:
1058 // ( SELECT NNLU_BUCK_1 FROM NNLU_BUCK_1 WHERE DATEFIELD = to_date(?,'hh24:mi:ss') )
1059 int nToDateIndex = sValue.indexOf("to_date(");
1060 nValueStartIndex = sValue.indexOf('\'', nToDateIndex);
1061 nValueEndIndex = sValue.indexOf('\'', nValueStartIndex + 1);
1062 break;
1063 case FieldConstantsI.FIELD_TYPE_STRING:
1064 // If data type is String the statement will be of the form:
1065 // ( SELECT NNLU_BUCK_0 FROM NNLU_BUCK_0 WHERE upper(STRINGFIELD) = 'TRANSFERS' )
1066 // we will translate this to:
1067 // ( SELECT NNLU_BUCK_0 FROM NNLU_BUCK_0 WHERE upper(STRINGFIELD) = ? )
1068 nValueStartIndex = sValue.indexOf('\'');
1069 nValueEndIndex = sValue.lastIndexOf('\'');
1070 break;
1071 case FieldConstantsI.FIELD_TYPE_NUMBER:
1072 case FieldConstantsI.FIELD_TYPE_UNDEFINED:
1073 default:
1074 // If data type is Number or undefined the statement will be of the form:
1075 // ( SELECT NNLU_BUCK_2 FROM NNLU_BUCK_2 WHERE NUMBERFIELD = 15 )
1076 // we will translate this to:
1077 // ( SELECT NNLU_BUCK_2 FROM NNLU_BUCK_2 WHERE NUMBERFIELD = ? )
1078 nValueEndIndex = sValue.lastIndexOf(' ');
1079 nValueStartIndex = sValue.lastIndexOf(' ', nValueEndIndex - 1);
1080 }
1081
1082 sbValue.append( sValue.substring(0, nValueStartIndex) );
1083 sbValue.append( '?' );
1084 sbValue.append( sValue.substring(nValueEndIndex + 1) );
1085
1086 sValue = sValue.substring( nValueStartIndex + 1, nValueEndIndex );
1087
1088 return new String[] { sbValue.toString(), sValue };
1089 }
1090
1091 /**
1092 * Get a String representation of this statement.
1093 */
1094 public String toString()
1095 {
1096 Vector vStatement = getStatement();
1097 String sStatement = (String) vStatement.elementAt(0);
1098 int nextIndex = sStatement.indexOf('?');
1099 int index = -1;
1100 if ( nextIndex == -1 )
1101 return sStatement;
1102
1103 StringBuffer sbStatement = new StringBuffer( sStatement.substring(0, nextIndex) );
1104 String[] saValue = null;
1105 int nType;
1106 for ( int i = 1; i < vStatement.size(); i++ )
1107 {
1108 saValue = (String[]) vStatement.elementAt(i);
1109 nType = Integer.parseInt(saValue[0]);
1110
1111 switch( nType )
1112 {
1113 case FieldConstantsI.FIELD_TYPE_DATE: // Value must be a java.util.Date
1114 case FieldConstantsI.FIELD_TYPE_DATETIME: // Value must be a java.util.Date
1115 case FieldConstantsI.FIELD_TYPE_TIME: // Value must be a java.util.Date
1116 try
1117 {
1118 SimpleDateFormat formatter = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
1119 Date d = formatter.parse( saValue[1] );
1120 sbStatement.append( d.toString() );
1121 }
1122 catch ( ParseException pe )
1123 {
1124 sbStatement.append( "NULL" );
1125 }
1126 break;
1127 default:
1128 sbStatement.append( saValue[1] );
1129 break;
1130 }
1131 index = nextIndex;
1132 nextIndex = sStatement.indexOf( '?', index + 1 );
1133 if ( nextIndex != - 1 )
1134 sbStatement.append( sStatement.substring( index + 1, nextIndex ) );
1135 }
1136 sbStatement.append( sStatement.substring( index + 1 ) );
1137 return sbStatement.toString();
1138 }
1139}