Java > Open Source Codes > com > jcorporate > expresso > core > dataobjects > jdbc > JDBCUtil


1 /* ====================================================================
2  * The Jcorporate Apache Style Software License, Version 1.2 05-07-2002
3  *
4  * Copyright (c) 1995-2002 Jcorporate Ltd. All rights reserved.
5  *
6  * Redistribution and use in source and binary forms, with or without
7  * modification, are permitted provided that the following conditions
8  * are met:
9  *
10  * 1. Redistributions of source code must retain the above copyright
11  * notice, this list of conditions and the following disclaimer.
12  *
13  * 2. Redistributions in binary form must reproduce the above copyright
14  * notice, this list of conditions and the following disclaimer in
15  * the documentation and/or other materials provided with the
16  * distribution.
17  *
18  * 3. The end-user documentation included with the redistribution,
19  * if any, must include the following acknowledgment:
20  * "This product includes software developed by Jcorporate Ltd.
21  * (http://www.jcorporate.com/)."
22  * Alternately, this acknowledgment may appear in the software itself,
23  * if and wherever such third-party acknowledgments normally appear.
24  *
25  * 4. "Jcorporate" and product names such as "Expresso" must
26  * not be used to endorse or promote products derived from this
27  * software without prior written permission. For written permission,
28  * please contact info@jcorporate.com.
29  *
30  * 5. Products derived from this software may not be called "Expresso",
31  * or other Jcorporate product names; nor may "Expresso" or other
32  * Jcorporate product names appear in their name, without prior
33  * written permission of Jcorporate Ltd.
34  *
35  * 6. No product derived from this software may compete in the same
36  * market space, i.e. framework, without prior written permission
37  * of Jcorporate Ltd. For written permission, please contact
38  * partners@jcorporate.com.
39  *
40  * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
41  * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
42  * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
43  * DISCLAIMED. IN NO EVENT SHALL JCORPORATE LTD OR ITS CONTRIBUTORS
44  * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
45  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
46  * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
47  * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
48  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
49  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
50  * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
51  * SUCH DAMAGE.
52  * ====================================================================
53  *
54  * This software consists of voluntary contributions made by many
55  * individuals on behalf of the Jcorporate Ltd. Contributions back
56  * to the project(s) are encouraged when you make modifications.
57  * Please send them to support@jcorporate.com. For more information
58  * on Jcorporate Ltd. and its products, please see
59  * <http://www.jcorporate.com/>.
60  *
61  * Portions of this software are based upon other open source
62  * products and are subject to their respective licenses.
63  */
64
65 package com.jcorporate.expresso.core.dataobjects.jdbc;
66
67 import com.jcorporate.expresso.core.dataobjects.DataException;
68 import com.jcorporate.expresso.core.dataobjects.DataFieldMetaData;
69 import com.jcorporate.expresso.core.dataobjects.DataObject;
70 import com.jcorporate.expresso.core.db.DBConnection;
71 import com.jcorporate.expresso.core.db.DBException;
72 import com.jcorporate.expresso.core.db.TypeMapper;
73 import com.jcorporate.expresso.core.dbobj.DBField;
74 import com.jcorporate.expresso.core.misc.ConfigJdbc;
75 import com.jcorporate.expresso.core.misc.ConfigManager;
76 import com.jcorporate.expresso.core.misc.ConfigurationException;
77 import com.jcorporate.expresso.core.misc.StringUtil;
78 import com.jcorporate.expresso.core.security.filters.Filter;
79 import com.jcorporate.expresso.kernel.util.FastStringBuffer;
80 import org.apache.commons.collections.LRUMap;
81 import org.apache.log4j.Logger;
82
83 import java.sql.CallableStatement ;
84 import java.sql.SQLException ;
85 import java.text.ParseException ;
86 import java.text.SimpleDateFormat ;
87 import java.util.Collections ;
88 import java.util.Iterator ;
89 import java.util.Map ;
90
91 /**
92  * This class provides utility functions that are common across the <code>jdbc.*</code>
93  * package classes. This class is normally not accessed except through other
94  * classes in the jdbc package.
95  *
96  * @author Michael Rimov
97  * @since Expresso 5.0
98  */
99
100 public class JDBCUtil extends DataException {
101     static private JDBCUtil theInstance = new JDBCUtil();
102     static private Logger log = Logger.getLogger("expresso.core.dataobjects.jdbc.JDBCUtil");
103
104     private FieldRangeParser rangeParser = new FieldRangeParser();
105     public static final String DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
106     public static final String DATE_FORMAT = "yyyy-MM-dd";
107     public static final String TIME_FORMAT = "HH:mm:ss";
108
109
110     private class DateReturnFormat {
111         String returnValue;
112         boolean foundConvertFunction;
113     }
114
115     /**
116      * It takes quite a bit of horsepower to create a SimpleDateFormat object.
117      * By caching the most recent 30 patterns, we can save a lot of CPU time.
118      */
119     static private Map dateConvertFormatMap = Collections.synchronizedMap(new LRUMap(30));
120
121     protected JDBCUtil() {
122     }
123
124     static public JDBCUtil getInstance() {
125         return theInstance;
126     }
127
128     /**
129      * Given the value of a date/time or date/time field, return the value formatted
130      * as appropriate for the current DBMS. Can be configured using property
131      * file values.
132      *
133      * @param fieldName java.lang.String The value for the date/time field.
134      * @param theObject the <code>DataObject</code> that the date field is coming
135      * from.
136      * @return java.lang.String The formatted date time, ready for use in the DBMS
137      */
138     public String formatDateTime(DataObject theObject, String fieldName)
139             throws DataException {
140         return formatDateTime(theObject, fieldName, true);
141     }
142
143
144     /**
145      * Given the value of a date/time or datetime field, return the value formatted
146      * as appropriate for the current DBMS. Can be configured using expresso-config
147      * file values.
148      *
149      * @param fieldName java.lang.String The value for the date/time field.
150      * @param theObject the <code>DataObject</code> that the date field is coming
151      * from.
152      * @param surroundWithQuotes set to <b>true</b> if you want the returned value
153      * to be surrounded with single quotes, such as for a standard JDBC SELECT statement.
154      * Use false if you want to use it, for example, in a PreparedStatement
155      * @return java.lang.String The formatted date time, ready for use in the DBMS
156      */
157     public String formatDateTime(DataObject theObject, String fieldName, boolean surroundWithQuotes)
158             throws DataException {
159         DateReturnFormat df = formatDateTimeInternal(theObject, fieldName);
160         String oneValue = df.returnValue;
161
162         if (oneValue == null) {
163             return null;
164         }
165
166         if (surroundWithQuotes && !df.foundConvertFunction) {
167             FastStringBuffer fsb = FastStringBuffer.getInstance();
168             String returnValue = null;
169             try {
170                 fsb.append("'");
171                 fsb.append(oneValue);
172                 fsb.append("'");
173                 returnValue = fsb.toString();
174             } finally {
175                 fsb.release();
176                 fsb = null;
177             }
178             return returnValue;
179         } else {
180             return oneValue;
181         }
182     }
183
184     /**
185      * Get a SimpleDateFormat object that is cached. Make sure you have
186      * dateConvertFormatMap already locked before calling this function or you'll
187      * have a race condition.
188      *
189      * @param pattern the format pattern to look up
190      * @return an instantiated SimpleDateFormat object. SimpleDateFormat is NOT
191      * threadsafe, so make sure you do your parsing while still in the synchronized
192      * block. Perhaps in the future a keyed Object pool will be better.
193      */
194     protected SimpleDateFormat getSimpleDateFormat(String pattern) {
195         SimpleDateFormat aFormat = null;
196         aFormat = (SimpleDateFormat ) dateConvertFormatMap.get(pattern);
197
198         if (aFormat == null) {
199             aFormat = new SimpleDateFormat (pattern);
200             dateConvertFormatMap.put(pattern, aFormat);
201         }
202
203         return aFormat;
204     }
205
206     /**
207      * Helper function that helps convert any string values from the database's
208      * format
209      *
210      * @param theObject the source data object
211      * @param fieldName the name of the field
212      * @return java.util.Date
213      * @throws DataException upon error (possibly conversion error)
214      */
215     public java.util.Date getDateField(DataObject theObject, String fieldName) throws DataException {
216         DataFieldMetaData oneField = theObject.getFieldMetaData(fieldName);
217
218         Object o = theObject.get(oneField.getName());
219
220         java.util.Date returnDate = null;
221         String convertFormat = null;
222         ConfigJdbc myConfig = null;
223         String strVal = null;
224
225         if (o == null) {
226             return null;
227         } else if (o instanceof String ) {
228             strVal = (String ) o;
229         } else if (o instanceof java.util.Date ) {
230             returnDate = (java.util.Date ) o;
231         } else {
232             strVal = o.toString();
233         }
234
235         if (strVal != null && strVal.length() == 0) {
236             return null;
237         }
238
239         if (returnDate == null) {
240
241             try {
242                 myConfig = ConfigManager.getJdbcRequired(theObject.getMappedDataContext());
243             } catch (ConfigurationException ce) {
244                 throw new DataException(ce);
245             }
246             if (oneField.isDateOnlyType()) {
247                 if (!StringUtil.notNull(myConfig.getDateSelectFormat()).equals("")) {
248                     convertFormat = myConfig.getDateSelectFormat();
249                 } else {
250                     convertFormat = DATE_FORMAT;
251                 }
252             } else if (oneField.isDateTimeType()) {
253                 if (!StringUtil.notNull(myConfig.getDateTimeSelectFormat()).equals("")) {
254                     convertFormat = myConfig.getDateTimeSelectFormat();
255                 } else {
256                     convertFormat = DATETIME_FORMAT;
257                 }
258             } else if (oneField.isTimeType()) {
259                 if (!StringUtil.notNull(myConfig.getTimeSelectFormat()).equals("")) {
260                     convertFormat = myConfig.getTimeSelectFormat();
261                 } else {
262                     convertFormat = TIME_FORMAT;
263                 }
264             } else {
265                 throw new DataException("Field '" + fieldName +
266                         "' is not a date, datetime or time - it is a " +
267                         oneField.getTypeString() +
268                         ", which cannot be retrieved as a Date type");
269             }
270             try {
271                 //We have to lock the formatter because we don't want somebody
272 //else to call parse while we're working on the value.
273 SimpleDateFormat formatter = getSimpleDateFormat(convertFormat);
274                 synchronized (formatter) {
275                     returnDate = formatter.parse(strVal);
276                 }
277             } catch (ParseException pe) {
278                 String message = "(" + getClass().getName() +
279                         ") Unable to parse a date value from field '" +
280                         fieldName + "' which contained '" + strVal +
281                         "' using pattern '" + convertFormat + "'";
282                 log.error(message, pe);
283                 throw new DataException(message, pe);
284             }
285             if (returnDate == null) {
286                 throw new DataException("(" + getClass().getName() +
287                         ") Unable to get date value from field " +
288                         fieldName + ", value was " + strVal);
289             }
290
291         }
292
293         return returnDate;
294     }
295
296     /**
297      * This class combines the old getFieldDate() with quoteIfNeeded() to format
298      * the Date-Time fields appropriately for storage. This probably could use
299      * some serious optimization since it looks like at first glance it does
300      * the same operation twice. Any volunteers? :)
301      *
302      * @param theObject The object that contains the data to froatm
303      * @param fieldName the name of the field to format
304      * @return a DateReturnFormat object.
305      * @throws DataException upon error
306      */
307     protected DateReturnFormat formatDateTimeInternal(DataObject theObject,
308                                                       String fieldName) throws DataException {
309
310         DateReturnFormat returnFormat = new DateReturnFormat();
311         DataFieldMetaData oneField = theObject.getFieldMetaData(fieldName);
312         java.util.Date returnDate = this.getDateField(theObject, fieldName);
313         if (returnDate == null) {
314             returnFormat.returnValue = "";
315             return returnFormat;
316         }
317         java.util.Date originalDate = (java.util.Date ) returnDate.clone();
318         String convertFormat = null;
319         ConfigJdbc myConfig = null;
320         String strVal = null;
321         convertFormat = "";
322         String convertFunction = "";
323         try {
324             myConfig = ConfigManager.getJdbcRequired(theObject.getMappedDataContext());
325         } catch (ConfigurationException ce) {
326             throw new DataException(ce);
327         }
328
329         try {
330             if (myConfig == null) {
331                 myConfig = ConfigManager.getJdbcRequired(theObject.getMappedDataContext());
332             }
333
334             if (oneField.getTypeString().equalsIgnoreCase("date")) {
335                 convertFormat = myConfig.getDateUpdateFormat();
336                 convertFunction = myConfig.getDateUpdateFunction();
337
338                 if (StringUtil.notNull(convertFormat).length() == 0 &&
339                         StringUtil.notNull(convertFunction).length() == 0) {
340                     convertFormat = "yyyy-MM-dd";
341                 }
342             } else if (oneField.getTypeString().equalsIgnoreCase("datetime")
343                     || oneField.getTypeString().equalsIgnoreCase(DBField.TIMESTAMP_TYPE)) {
344                 convertFormat = myConfig.getDateTimeUpdateFormat();
345                 convertFunction = myConfig.getDateTimeUpdateFunction();
346                 if (StringUtil.notNull(convertFormat).length() == 0 &&
347                         StringUtil.notNull(convertFunction).length() == 0) {
348                     convertFormat = "yyyy-MM-dd HH:mm:ss";
349                 }
350             } else if (oneField.getTypeString().equalsIgnoreCase("time")) {
351                 convertFormat = myConfig.getTimeUpdateFormat();
352                 convertFunction = myConfig.getTimeUpdateFunction();
353                 if (StringUtil.notNull(convertFormat).length() == 0 &&
354                         StringUtil.notNull(convertFunction).length() == 0) {
355                     convertFormat = "HH:mm:ss";
356                 }
357             } else {
358                 throw new DataException("Field '" + fieldName +
359                         "' is not a date, datetime or time - it is a " +
360                         oneField.getTypeString() +
361                         ", which cannot be formatted " +
362                         "as a Date/Time type");
363             }
364         } catch (ConfigurationException ce) {
365             throw new DataException(ce);
366         }
367
368         convertFormat = StringUtil.notNull(convertFormat);
369         convertFunction = StringUtil.notNull(convertFunction);
370
371         String returnValue = null;
372
373         /* If no format was specified, don't change the existing field */
374         if (convertFormat == null || convertFormat.length() == 0) {
375             if (strVal == null) {
376                 strVal = returnDate.toString();
377             }
378             if (convertFunction.length() > 0) {
379                 returnFormat.foundConvertFunction = true;
380                 returnFormat.returnValue = StringUtil.replace(convertFunction, "%s", strVal);
381                 return returnFormat;
382             } else {
383                 returnFormat.returnValue = strVal;
384                 return returnFormat;
385             }
386         }
387
388         SimpleDateFormat formatter = getSimpleDateFormat(convertFormat);
389         synchronized (formatter) {
390             returnValue = formatter.format(originalDate);
391         }
392
393         if (convertFunction == null || convertFunction.length() == 0) {
394             // no conversion
395 } else {
396             returnFormat.foundConvertFunction = true;
397             if (returnValue != null) {
398                 returnValue = StringUtil.replace(convertFunction, "%s", returnValue);
399             }
400         }
401
402         if (returnValue == null) {
403             throw new DataException("(" + getClass().getName() +
404                     ") Unable to format date value from field " +
405                     fieldName + ", value was " +
406                     strVal);
407         }
408
409         returnFormat.returnValue = returnValue;
410         return returnFormat;
411     }
412
413
414     /**
415      * Build and return a string consisting of an SQL 'where' clause
416      * using the current field values as criteria for the search. See
417      * setCustomWhereClause for information on specifying a more complex where clause.
418      *
419      * @param criteria the JDBCDataObject to build from
420      * @param useAllFields True if all fields are to be used,
421      * false for only key fields
422      * @return The where clause to use in a query.
423      */
424     public String buildWhereClause(JDBCDataObject criteria, boolean useAllFields)
425             throws DataException {
426         FastStringBuffer fsb = FastStringBuffer.getInstance();
427         try {
428             return buildWhereClauseBuffer(criteria, useAllFields, fsb).toString();
429         } finally {
430             fsb.release();
431         }
432     } /* buildWhereClause(boolean) */
433
434
435     /**
436      * Build and return a FastStringBuffer ring consisting of an SQL 'where' clause
437      * using the current field values as criteria for the search. See
438      * setCustomWhereClause for information on specifying a more complex where clause.
439      *
440      * @param criteria the JDBCDataObject to build from
441      * @param useAllFields True if all fields are to be used,
442      * false for only key fields
443      * @param allocatedBuffer - An already allocated FastStringBuffer to fill out.
444      * This allows for compatability with, for example, object pools.
445      * @return A FastStringBuffer containing the "where" clause for the SQL statement
446      * @throws DataException upon error
447      */
448     public FastStringBuffer buildWhereClauseBuffer(JDBCDataObject criteria, boolean useAllFields,
449                                                    FastStringBuffer allocatedBuffer)
450             throws DataException {
451         Iterator fieldsToUse = null;
452         FastStringBuffer myStatement = allocatedBuffer;
453
454         if (useAllFields) {
455             fieldsToUse = criteria.getMetaData().getFieldListArray().iterator();
456         } else {
457             fieldsToUse = criteria.getMetaData().getKeyFieldListArray().iterator();
458         }
459
460         /* Now go thru each field - if it is non-empty, add it's criteria */
461
462         /* to the where clause. If it is empty, just skip to the next one */
463         boolean addWhere = true;
464         boolean addAnd = false;
465         DataFieldMetaData oneField = null;
466         String oneFieldName = null;
467         String oneFieldValue = null;
468         boolean skipText = false;
469         boolean postgresql = false;
470
471         try {
472             ConfigJdbc myConfig = ConfigManager.getJdbcRequired(criteria.getMappedDataContext());
473             skipText = myConfig.skipText();
474             //We have to do this because postgres won't be smart enough to
475 //cast floating point literals to truly a floating point value. :(
476 if ("org.postgresql.Driver".equals(myConfig.getDriver())) {
477                 postgresql = true;
478             }
479         } catch (ConfigurationException ce) {
480             throw new DataException(ce);
481         }
482
483         boolean skipField = false;
484
485         while (fieldsToUse.hasNext()) {
486             oneFieldName = (String ) fieldsToUse.next();
487             oneField = criteria.getFieldMetaData(oneFieldName);
488             skipField = false;
489
490             if (oneField.isVirtual()) {
491                 skipField = true;
492             }
493
494             try {
495                 oneFieldValue = StringUtil.notNull(criteria.getDataField(oneField
496                         .getName()).asString());
497             } catch (DBException ex) {
498                 if (ex instanceof DataException) {
499                     throw ((DataException) ex);
500                 } else {
501                     throw new DataException("Error getting field value", ex);
502                 }
503             }
504
505             String rangeString = rangeParser.denotesRange(oneFieldValue);
506
507             if (!oneFieldValue.equals("")) {
508                 if (oneFieldValue.trim().equalsIgnoreCase("is null") ||
509                         oneFieldValue.trim().equalsIgnoreCase("is not null")) {
510                     ;
511                 } else {
512                     oneFieldValue = quoteIfNeeded(criteria, oneFieldName, rangeString);
513                 }
514             }
515             if (oneFieldValue == null) {
516                 skipField = true;
517             }
518             if (oneFieldValue.trim().equals("\'\'")) {
519                 skipField = true;
520             }
521
522             //
523 //There was a TODO item asking about why \r and \n's aren't allowed
524 //in text fiels here. and the reason is that the JDBC parsers expect
525 //all quoted fields to be without crlf in them. in all honesty,
526 //you shouldn't be searching for a text field anyway since it's arbitrary
527 //length and you SERIOUSLY dog performance by doing it that way.
528 //If you must search for a text field, you can deal with cr/lf's by
529 //using a prepared statement such as the LOBSupport class.
530 //
531 if (oneField.getTypeString().equalsIgnoreCase("text")) {
532                 if (skipText) {
533                     skipField = true;
534
535                     if (log.isDebugEnabled()) {
536                         log.debug("Skipping criteria in text field '" +
537                                 oneFieldName + "'");
538                     }
539                 } else {
540                     if (oneFieldValue.indexOf("\n") > 0) {
541                         oneFieldValue = StringUtil.replace(oneFieldValue, "\n",
542                                 "");
543                     }
544                     if (oneFieldValue.indexOf("\r") > 0) {
545                         oneFieldValue = StringUtil.replace(oneFieldValue, "\r",
546                                 "");
547                     }
548                     if (oneFieldValue.equals("\'\'")) {
549                         skipField = true;
550                     }
551                 }
552             } /* if text field */
553
554             if (oneFieldValue.trim().equals("")) {
555                 skipField = true;
556             }
557             if (!skipField) {
558                 // check to see if the field value is valid (protects agains sql injection)
559 try {
560                     String unalteredFieldValue = criteria.getDataField(oneField.getName()).asString();
561                     if (rangeString != null) {
562                         boolean valid = rangeParser.isValidRange(criteria.getFieldMetaData(oneField.getName()),
563                                 unalteredFieldValue);
564                         if (!valid) {
565                             throw new DataException("Invalid field range value: " + unalteredFieldValue);
566                         }
567                     } else if (containsWildCards(criteria, oneFieldValue)) {
568                         Object origValue = criteria.getDataField(oneFieldName).getValue();
569
570                         String [] wildcards = null;
571                         wildcards = (String []) criteria.getConnectionPool().getWildCardsList().toArray(new String [0]);
572                         Filter filter = new Filter(wildcards, wildcards);
573                         String valueWithoutWildCards = filter.stripFilter(unalteredFieldValue);
574                         // if the value without wildcards is empty, then we know the field is valid
575 if (!valueWithoutWildCards.equals("")) {
576                             criteria.getDataField(oneFieldName).setValue(valueWithoutWildCards);
577                             criteria.getDataField(oneFieldName).checkValue();
578                             criteria.getDataField(oneFieldName).setValue(origValue);
579                         }
580                     } else {
581                         criteria.getDataField(oneFieldName).checkValue();
582                     }
583                 } catch (DBException ex) {
584                     if (ex instanceof DataException) {
585                         throw ((DataException) ex);
586                     } else {
587                         throw new DataException("Error getting field value", ex);
588                     }
589                 }
590
591                 if (addWhere) {
592                     myStatement.append(" WHERE ");
593                     addWhere = false;
594                 }
595                 if (addAnd) {
596                     myStatement.append(" AND ");
597                 }
598                 if (containsWildCards(criteria, oneFieldValue)) {
599                     if (criteria.caseSensitiveQuery) {
600                         myStatement.append(oneFieldName);
601                         myStatement.append(" LIKE ");
602                         myStatement.append(oneFieldValue);
603                     } else {
604                         myStatement.append("UPPER(");
605                         myStatement.append(oneFieldName);
606                         myStatement.append(") LIKE ");
607                         myStatement.append(oneFieldValue.toUpperCase());
608                     }
609                 } else if (rangeString != null) {
610                     myStatement.append(oneFieldName);
611                     String theValue = rangeString + " " + oneFieldValue;
612                     boolean valid = rangeParser.isValidRange(criteria
613                             .getFieldMetaData(oneField.getName()), theValue);
614                     if (!valid) {
615                         throw new DataException("Invalid field range value: " + theValue);
616                     }
617
618                     myStatement.append(" ");
619                     myStatement.append(theValue);
620                 } else if ((oneFieldValue.trim().equalsIgnoreCase("is null")) ||
621                         (oneFieldValue.trim().equalsIgnoreCase("is not null"))) {
622                     myStatement.append(oneFieldName);
623                     myStatement.append(" ");
624                     myStatement.append(oneFieldValue.trim());
625                 } else if (oneField.isDateType()) {
626                     myStatement.append(oneFieldName);
627                     myStatement.append(" = ");
628                     Object tmpData = null;
629                     try {
630                         tmpData = criteria.getDataField(oneFieldName).getValue();
631                     } catch (DBException ex) {
632                         if (ex instanceof DataException) {
633                             throw ((DataException) ex);
634                         } else {
635                             throw new DataException("Error getting field value", ex);
636                         }
637                     }
638                     String data;
639                     //
640 //FIXME allow for appropriate support of other data types.
641 //
642 if (tmpData == null) {
643                         data = null;
644                     } else if (tmpData instanceof String ) {
645                         data = (String ) tmpData;
646                     } else {
647                         data = tmpData.toString();
648                     }
649
650                     if (data == null || (data.length() == 0)) {
651                         myStatement.append("null");
652                     } else {
653                         myStatement.append(JDBCUtil.getInstance()
654                                 .formatDateTime(criteria, oneField.getName()));
655                     }
656                 } else if (oneField.isFloatingPointType()) {
657                     //Floating point types have to be searched within a certain
658 //precision. Thus we compare the ABS to the less than
659 //the field precision.
660 myStatement.append("ABS(");
661                     myStatement.append(oneFieldName);
662                     myStatement.append(" - ");
663                     if (postgresql) {
664                         myStatement.append(" CAST (");
665                     }
666                     myStatement.append(oneFieldValue);
667                     if (postgresql) {
668                         myStatement.append(" as FLOAT)");
669                     }
670                     myStatement.append(") <");
671                     if (postgresql) {
672                         myStatement.append(" CAST (");
673                     }
674                     myStatement.append(".");
675                     int precision = oneField.getPrecision();
676                     if (precision == 0) {
677                         precision = 1;
678                     }
679                     for (int i = 0; i < oneField.getPrecision() - 1; i++) {
680                         myStatement.append("0");
681                     }
682                     myStatement.append("1");
683                     if (postgresql) {
684                         myStatement.append(" as FLOAT)");
685                     }
686
687                 } else {
688                     if (oneField.isQuotedTextType() && !criteria.caseSensitiveQuery) {
689                         myStatement.append("UPPER(");
690                         myStatement.append(oneFieldName);
691                         myStatement.append(") = ");
692                         myStatement.append(oneFieldValue.toUpperCase());
693                     } else {
694                         myStatement.append(oneFieldName);
695                         myStatement.append(" = ");
696                         myStatement.append(oneFieldValue);
697                     }
698                 }
699
700                 addAnd = true;
701             }
702
703             /* if field is not skipped for some reason */
704         }
705         /* for each field */
706         if (log.isDebugEnabled()) {
707             log.debug("Built where clause '" + myStatement.toString() + "'");
708         }
709
710         return myStatement;
711     }
712
713
714     /**
715      * Creates the limitation syntax optimisation stub
716      * to embed inside the SQL command that performs
717      * search and retrieve.
718      * <p/>
719      * <p>This method takes the limitation syntax string
720      * and performs a string replacement on the following
721      * tokens
722      * <p/>
723      * <ul>
724      * <p/>
725      * <li><b>%offset%</b><li><br>
726      * the number of rows in the <code>ResultSet</code> to skip
727      * before reading the data.
728      * <p/>
729      * <li><b>%maxrecord%</b><li><br>
730      * the maximum number of rows to read from the <code>ResultSet</code>.
731      * Also known as the <i>rowlength</i>.
732      * <p/>
733      * <li><b>%endrecord%</b><li><br>
734      * the last record of in the <code>ResultSet</code> that the
735      * search and retrieved should retrieve. The end record number
736      * is equal to <code>( %offset% + %maxrecord% - 1 )</code>
737      * <p/>
738      * </ul>
739      * <p/>
740      * </p>
741      * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
742      *
743      * @param theConnection the db connection to make this stub from
744      * @param theObj the DataObject to query for the limitation stub
745      * @return the limitation syntax stub string
746      */
747     public String makeLimitationStub(DBConnection theConnection, DataObject theObj) {
748         String limit = theConnection.getLimitationSyntax();
749         int offset = theObj.getOffsetRecord();
750         int maxrec = theObj.getMaxRecords();
751         int endrec = offset + maxrec - 1;
752         limit = StringUtil.replace(limit, "%offset%", Integer.toString(offset));
753         limit = StringUtil.replace(limit, "%maxrecords%",
754                 Integer.toString(maxrec));
755
756         // limit = StringUtil.replace( limit, "%length%", Integer.toString( maxrec ) );
757 limit = StringUtil.replace(limit, "%endrecord%",
758                 Integer.toString(endrec));
759
760         return limit;
761     } /* makeLimitationStub(DBConnection) */
762
763     /**
764      * Return the value of this field, placing double quotes around it if the
765      * field's datatype requires it.
766      *
767      * @param fieldName The name of the field to be used
768      * @param rangeString the appropriately formatted string
769      * @param targetObject the JDBCDataObject to query for metadata
770      * @return A string, quoted if necessary, to be used in building an SQL statement
771      * @throws DataException If there is no such field or it's value cannot be
772      * determined
773      */
774     public String quoteIfNeeded(JDBCDataObject targetObject, String fieldName, String rangeString)
775             throws DataException {
776         DataFieldMetaData oneField = targetObject.getFieldMetaData(fieldName);
777         if (oneField == null) {
778             throw new DataException("(" + targetObject.getClass().getName() +
779                     ") No such field as " + fieldName);
780         }
781         boolean noTrim = false;
782         if (!oneField.isMasked() && !targetObject.isGlobalMasked()) {
783             try {
784                 noTrim = ConfigManager.getJdbcRequired(targetObject.getMappedDataContext()).isStringNotTrim();
785             } catch (ConfigurationException ce) {
786                 throw new DataException(ce);
787             }
788         }
789
790         String fieldValue = targetObject.getSerialForm(oneField);
791
792         if (rangeString != null) {
793             fieldValue = fieldValue.substring(rangeString.length());
794         }
795
796         /* if the field is null, we don't need to worry about quotes */
797         if (fieldValue == null) {
798             return null;
799         }
800
801         if (oneField.isNumericType()) {
802             if (fieldValue.length() == 0) {
803                 return "0";
804             }
805
806             return fieldValue.trim();
807         } /* if a numeric type */
808
809
810         if (oneField.isQuotedTextType()) {
811             if (rangeString != null) {
812                 return fieldValue;
813             }
814             FastStringBuffer returnValue = FastStringBuffer.getInstance();
815             String returnString = null;
816             try {
817                 String value = "";
818                 if (noTrim) {
819                     value = fieldValue;
820                 } else {
821                     value = fieldValue.trim();
822                 }
823                 returnValue.append("\'");
824 // returnValue.append(targetObject.getConnectionPool().getEscapeHandler().escapeString(fieldValue.trim()));
825 returnValue.append(targetObject.getConnectionPool().getEscapeHandler().escapeString(value));
826                 returnValue.append("\'");
827                 returnString = returnValue.toString();
828             } catch (DBException e) {
829                 throw new DataException(e);
830             } finally {
831                 returnValue.release();
832                 returnValue = null;
833             }
834             return returnString;
835         } /* if a quoted type */
836
837         if (oneField.isDateType()) {
838             if (rangeString != null) {
839                 return fieldValue;
840             }
841             FastStringBuffer returnValue = FastStringBuffer.getInstance();
842             String returnString = null;
843             try {
844                 returnValue.append("\'");
845                 returnValue.append(fieldValue);
846                 returnValue.append("\'");
847                 returnString = returnValue.toString();
848             } finally {
849                 returnValue.release();
850                 returnValue = null;
851             }
852             return returnString;
853         }
854
855         //
856 //We don't care about rangestrings in boolean types.... they don't
857 //exactly make sense.
858 //
859 if (oneField.isBooleanType()) {
860             try {
861                 boolean nativeBoolean = ConfigManager.getContext(targetObject.getMappedDataContext()).getJdbc().isNativeBool();
862
863                 if (!nativeBoolean) {
864                     FastStringBuffer returnValue = FastStringBuffer.getInstance();
865                     String returnString = null;
866                     try {
867                         returnValue.append("\'");
868                         returnValue.append(fieldValue.trim());
869                         returnValue.append("\'");
870                         returnString = returnValue.toString();
871                     } finally {
872                         returnValue.release();
873                         returnValue = null;
874                     }
875                     return returnString;
876                 }
877             } catch (ConfigurationException ce) {
878                 throw new DataException(ce);
879             }
880         }
881
882         // if the field contains wildcards, it needs to be quoted regardless of it's type
883 if (containsWildCards(targetObject, fieldValue)) {
884             if (rangeString != null) {
885                 return fieldValue;
886             }
887             FastStringBuffer returnValue = FastStringBuffer.getInstance();
888             String returnString = null;
889             try {
890                 returnValue.append("\'");
891                 returnValue.append(fieldValue);
892                 returnValue.append("\'");
893                 returnString = returnValue.toString();
894             } finally {
895                 returnValue.release();
896                 returnValue = null;
897             }
898             return returnString;
899         }
900
901         if (oneField.isNumericType()) {
902             if (fieldValue.length() == 0) {
903                 return "0";
904             }
905         }
906
907         if (noTrim) {
908             return fieldValue;
909         } else {
910
911             return fieldValue.trim();
912         }
913     } /* quoteIfNeeded(String) */
914
915
916     /**
917      * See if this field value contains wild cards (e.g. pattern matching
918      * criteria for the database). The wild cards can be configured via the
919      * properties file.
920      *
921      * @param fieldValue The field value to check for wild cards
922      * @param ownerObject the obejct to query for metadata
923      * @return True if the string does contain wild cards, False if it does not
924      */
925     public boolean containsWildCards(JDBCDataObject ownerObject, String fieldValue)
926             throws DataException {
927         if (fieldValue == null) {
928             fieldValue = ("");
929         }
930
931
932         for (Iterator it = ownerObject.getConnectionPool().getWildCardsList().iterator(); it.hasNext();) {
933             if (fieldValue.indexOf((String ) it.next()) >= 0) {
934                 return true;
935             }
936         }
937
938         return false;
939     } /* containsWildCards(String) */
940
941     /**
942      * Build and return a FastStringBuffer ring consisting of an SQL 'where' clause
943      * using the current field values as criteria for the search. See
944      * setCustomWhereClause for information on specifying a more complex where clause.
945      *
946      * @param criteria the JDBCDataObject to build from
947      * @param useAllFields True if all fields are to be used,
948      * false for only key fields
949      * @param allocatedBuffer - An already allocated FastStringBuffer to fill out.
950      * This allows for compatability with, for example, object pools.
951      * @return A FastStringBuffer containing the "where" clause for the SQL statement
952      * @throws DataException upon error
953      */
954     public void buildStoreProcedureCallableStatement(JDBCDataObject criteria, CallableStatement myCallableStatement)
955             throws DataException {
956         Iterator fieldsToUse = null;
957         FastStringBuffer myStatement = FastStringBuffer.getInstance();
958
959         fieldsToUse = criteria.getMetaData().getFieldListArray().iterator();
960
961         /* Now go thru each field - if it is non-empty, add it's criteria */
962
963         boolean inField = false;
964         boolean outField = false;
965         DataFieldMetaData oneField = null;
966         String oneFieldName = null;
967         String oneFieldValue = null;
968         boolean skipText = false;
969         boolean postgresql = false;
970         TypeMapper typeMapper = null;
971
972         try {
973             ConfigJdbc myConfig = ConfigManager.getJdbcRequired(criteria.getMappedDataContext());
974             skipText = myConfig.skipText();
975             //We have to do this because postgres won't be smart enough to
976 //cast floating point literals to truly a floating point value. :(
977 if ("org.postgresql.Driver".equals(myConfig.getDriver())) {
978                 postgresql = true;
979             }
980             typeMapper = TypeMapper.getInstance(criteria.getDataContext());
981         } catch (ConfigurationException ce) {
982             throw new DataException(ce);
983         } catch (DBException de) {
984             throw new DataException(de);
985         }
986
987         boolean skipField = false;
988         try {
989
990             while (fieldsToUse.hasNext()) {
991                 oneFieldName = (String ) fieldsToUse.next();
992                 oneField = criteria.getFieldMetaData(oneFieldName);
993                 skipField = false;
994
995                 if (oneField.isVirtual()) {
996                     skipField = true;
997                 }
998
999                 if (criteria.getDef().isInField(oneField.getName())) {
1000                    inField = true;
1001                }
1002
1003                if (criteria.getDef().isOutField(oneField.getName())) {
1004                    outField = true;
1005                }
1006
1007                try {
1008                    oneFieldValue = StringUtil.notNull(criteria.getDataField(oneField.getName()).asString());
1009                } catch (DBException ex) {
1010                    if (ex instanceof DataException) {
1011                        throw ((DataException) ex);
1012                    } else {
1013                        throw new DataException("Error getting field value", ex);
1014                    }
1015                }
1016
1017                String rangeString = rangeParser.denotesRange(oneFieldValue);
1018                if (!oneFieldValue.equals("")) {
1019                    if (oneFieldValue.trim().equalsIgnoreCase("is null") ||
1020                            oneFieldValue.trim().equalsIgnoreCase("is not null")) {
1021                        ;
1022                    } else {
1023                        oneFieldValue = quoteIfNeeded(criteria, oneFieldName, rangeString);
1024                    }
1025                }
1026                if (oneFieldValue == null) {
1027                    skipField = true;
1028                }
1029                if (oneFieldValue.trim().equals("\'\'")) {
1030                    skipField = true;
1031                }
1032
1033                //
1034 //There was a TODO item asking about why \r and \n's aren't allowed
1035 //in text fiels here. and the reason is that the JDBC parsers expect
1036 //all quoted fields to be without crlf in them. in all honesty,
1037 //you shouldn't be searching for a text field anyway since it's arbitrary
1038 //length and you SERIOUSLY dog performance by doing it that way.
1039 //If you must search for a text field, you can deal with cr/lf's by
1040 //using a prepared statement such as the LOBSupport class.
1041 //
1042 if (oneField.getTypeString().equalsIgnoreCase("text")) {
1043                    if (skipText) {
1044                        skipField = true;
1045
1046                        if (log.isDebugEnabled()) {
1047                            log.debug("Skipping criteria in text field '" +
1048                                    oneFieldName + "'");
1049                        }
1050                    } else {
1051                        if (oneFieldValue.indexOf("\n") > 0) {
1052                            oneFieldValue = StringUtil.replace(oneFieldValue, "\n",
1053                                    "");
1054                        }
1055                        if (oneFieldValue.indexOf("\r") > 0) {
1056                            oneFieldValue = StringUtil.replace(oneFieldValue, "\r",
1057                                    "");
1058                        }
1059                        if (oneFieldValue.equals("\'\'")) {
1060                            skipField = true;
1061                        }
1062                    }
1063                } /* if text field */
1064
1065                if (oneFieldValue.trim().equals("")) {
1066                    skipField = true;
1067                }
1068                if (!skipField) {
1069                    if (rangeString != null) {
1070                        String theValue = rangeString + " " + oneFieldValue;
1071                        boolean valid = rangeParser.isValidRange(criteria.getFieldMetaData(oneField.getName()),
1072                                theValue);
1073                        if (!valid) {
1074                            throw new DataException("Invalid field range value: " + theValue);
1075                        }
1076
1077                        if (inField) {
1078                            myCallableStatement.setString(Integer.parseInt(oneFieldName), theValue);
1079                        }
1080                        if (outField) {
1081                            myCallableStatement.registerOutParameter(Integer.parseInt(oneFieldName),
1082                                    typeMapper.getJavaSQLType(oneField.getTypeString()));
1083                        }
1084                    } else if ((oneFieldValue.trim().equalsIgnoreCase("is null")) || (oneFieldValue.trim().equalsIgnoreCase(
1085                            "is not null"))) {
1086                        if (inField) {
1087                            myCallableStatement.setString(Integer.parseInt(oneFieldName), oneFieldValue.trim());
1088                        }
1089                        if (outField) {
1090                            myCallableStatement.registerOutParameter(Integer.parseInt(oneFieldName),
1091                                    typeMapper.getJavaSQLType(oneField.getTypeString()));
1092                        }
1093                    } else if (oneField.isDateType()) {
1094                        Object tmpData = null;
1095                        try {
1096                            tmpData = criteria.getDataField(oneFieldName).getValue();
1097                        } catch (DBException ex) {
1098                            if (ex instanceof DataException) {
1099                                throw ((DataException) ex);
1100                            } else {
1101                                throw new DataException("Error getting field value", ex);
1102                            }
1103                        }
1104                        String data;
1105                        //
1106 //FIXME allow for appropriate support of other data types.
1107 //
1108 if (tmpData == null) {
1109                            data = null;
1110                        } else if (tmpData instanceof String ) {
1111                            data = (String ) tmpData;
1112                        } else {
1113                            data = tmpData.toString();
1114                        }
1115
1116                        if (data == null || (data.length() == 0)) {
1117                            if (inField) {
1118                                myCallableStatement.setString(Integer.parseInt(oneFieldName), "null");
1119                            }
1120                        } else {
1121                            myCallableStatement.setString(Integer.parseInt(oneFieldName),
1122                                    JDBCUtil.getInstance().formatDateTime(criteria, oneField.getName()));
1123                        }
1124                        if (outField) {
1125                            myCallableStatement.registerOutParameter(Integer.parseInt(oneFieldName),
1126                                    typeMapper.getJavaSQLType(oneField.getTypeString()));
1127                        }
1128                    } else if (oneField.isFloatingPointType()) {
1129                        //Floating point types have to be searched within a certain
1130 //precision. Thus we compare the ABS to the less than
1131 //the field precision.
1132 myStatement.append("ABS(");
1133                        myStatement.append(oneFieldName);
1134                        myStatement.append(" - ");
1135                        if (postgresql) {
1136                            myStatement.append(" CAST (");
1137                        }
1138
1139                        myStatement.append(oneFieldValue);
1140                        if (postgresql) {
1141                            myStatement.append(" as FLOAT)");
1142                        }
1143                        myStatement.append(") <");
1144                        if (postgresql) {
1145                            myStatement.append(" CAST (");
1146                        }
1147                        myStatement.append(".");
1148                        int precision = oneField.getPrecision();
1149                        if (precision == 0) {
1150                            precision = 1;
1151                        }
1152                        for (int i = 0; i < oneField.getPrecision() - 1; i++) {
1153                            myStatement.append("0");
1154                        }
1155                        myStatement.append("1");
1156                        if (postgresql) {
1157                            myStatement.append(" as FLOAT)");
1158                        }
1159                        if (inField) {
1160                            myCallableStatement.setString(Integer.parseInt(oneFieldName), myStatement.toString());
1161                        }
1162                        myStatement.clear();
1163                        if (outField) {
1164                            myCallableStatement.registerOutParameter(Integer.parseInt(oneFieldName),
1165                                    typeMapper.getJavaSQLType(oneField.getTypeString()), oneField.getPrecision());
1166                        }
1167                    } else {
1168                        if (inField) {
1169                            myCallableStatement.setString(Integer.parseInt(oneFieldName), oneFieldValue);
1170                        }
1171                        if (outField) {
1172                            myCallableStatement.registerOutParameter(Integer.parseInt(oneFieldName),
1173                                    typeMapper.getJavaSQLType(oneField.getTypeString()));
1174                        }
1175                    }/* if (oneField.isFloatingPointType()) */
1176
1177                    /* if field is not skipped for some reason */
1178                    myStatement.release();
1179                    myStatement = null;
1180                } /* if (!skipField) */
1181
1182            } /* While (for each field) */
1183        } catch (SQLException ce) {
1184            throw new DataException(ce);
1185        } catch (DBException de) {
1186            throw new DataException(de);
1187        }
1188        if (log.isDebugEnabled()) {
1189            log.debug("Built callable statement for store procedure ");
1190        }
1191    } /* buildStoreProcedureCallableStatement(JDBCDataObject, boolean, CallableStatement) */
1192
1193}
1194
1195

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags