Source code: com/jcorporate/expresso/core/dataobjects/jdbc/JDBCDataObject.java
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 package com.jcorporate.expresso.core.dataobjects.jdbc;
65
66 import EDU.oswego.cs.dl.util.concurrent.ConcurrentReaderHashMap;
67 import com.jcorporate.expresso.core.dataobjects.BaseDataObject;
68 import com.jcorporate.expresso.core.dataobjects.DataException;
69 import com.jcorporate.expresso.core.dataobjects.DataExecutorInterface;
70 import com.jcorporate.expresso.core.dataobjects.DataFieldMetaData;
71 import com.jcorporate.expresso.core.dataobjects.DataObjectMetaData;
72 import com.jcorporate.expresso.core.dataobjects.DataQueryInterface;
73 import com.jcorporate.expresso.core.db.DBConnection;
74 import com.jcorporate.expresso.core.db.DBConnectionPool;
75 import com.jcorporate.expresso.core.db.DBException;
76 import com.jcorporate.expresso.core.db.config.JDBCConfig;
77 import com.jcorporate.expresso.core.dbobj.DBField;
78 import com.jcorporate.expresso.core.dbobj.DBObjectDef;
79 import com.jcorporate.expresso.core.misc.Base64;
80 import com.jcorporate.expresso.core.misc.ConfigJdbc;
81 import com.jcorporate.expresso.core.misc.ConfigManager;
82 import com.jcorporate.expresso.core.misc.ConfigurationException;
83 import com.jcorporate.expresso.core.misc.DateTime;
84 import com.jcorporate.expresso.core.misc.StringUtil;
85 import com.jcorporate.expresso.core.registry.RequestRegistry;
86 import com.jcorporate.expresso.core.security.CryptoManager;
87 import com.jcorporate.expresso.kernel.exception.ChainedException;
88 import com.jcorporate.expresso.kernel.util.FastStringBuffer;
89 import org.apache.log4j.Logger;
90
91 import java.io.InputStream;
92 import java.sql.CallableStatement;
93 import java.util.ArrayList;
94 import java.util.HashMap;
95 import java.util.Iterator;
96
97 /**
98 * Base class for JDBC-based data objects.
99 *
100 * @author Michael Rimov
101 * @since Expresso 5.1
102 * <p/>
103 * Modify by Yves Henri AMAIZO <amy_amaizo@compuserve.com>
104 */
105
106 abstract public class JDBCDataObject extends BaseDataObject {
107
108 private static transient final Logger log = Logger.getLogger(JDBCDataObject.class);
109
110 public static int LONGBINARY_READ_DEFAULT_SIZE = 262144;
111
112 /**
113 * a local connection pool. Often used to save on going back and forth
114 * to the connection pool, or if you want JDBC transactions.
115 */
116 transient private DBConnectionPool myPool = null;
117
118
119 /**
120 * Normally originalDBKey is the same as DB key, except where the DB object is mapped
121 * to another database specifically, in which case originalDBKey can be used
122 * to determine the database to be used to find Expresso's own tables
123 */
124 private String mappedDataContext = null;
125
126 /**
127 * If we are using a custom where clause for this query, it's stored here.
128 * If null, then build the where clause
129 */
130 protected String customWhereClause = null;
131
132 /**
133 * Flag to indicate whether we append any custom WHERE clause to
134 * the one generated, or just use the supplied custom WHERE clause
135 */
136 protected boolean appendCustomWhere = false;
137
138 /**
139 * dbKey is used to indicate that this object is actually stored in
140 * other than the default database. Null indicates it's in the default
141 * database
142 */
143 protected String dbKey = null;
144
145
146 /**
147 * The ArrayList of DB objects retrieved by the last searchAndRetrieve method
148 */
149 protected ArrayList recordSet = null;
150
151
152 /**
153 * We use getClass().getName() a lot in this class, however, it takes
154 * actually significant time to use this over the time it takes to do, say,
155 * a concurrentReaderHashMap lookup. So we precalculate this value at
156 * instantiation and go from there.
157 */
158 transient protected String myClassName = getClass().getName();
159
160 /**
161 * Map of any distinct fields for retrieval.
162 */
163 protected HashMap distinctFields = null;
164
165
166 /**
167 * The actual fields retrieved
168 */
169 public HashMap retrieveFields = null;
170
171 /**
172 * DBObjects themselves do not contain the "meta data" or the definition of
173 * what fields they contain and other information. Instead, a DBObjectRef object
174 * (one per TYPE of DBObject) contains this info and is looked up as needed.
175 * This HashMap contains the DBObjectRef objects for all initialized DBObjects
176 *
177 * @todo move this static variable outside DBObject so we can wrap it for
178 * EJBs
179 */
180 volatile transient protected static ConcurrentReaderHashMap sMetadataMap
181 = new ConcurrentReaderHashMap();
182
183 /**
184 * If setFieldsToRetrieve has been used to specify fields which will be
185 * retrieve by the next query.
186 */
187 protected boolean anyFieldsToRetrieve = false;
188
189
190 /**
191 * Local connection that we use if it's initialized, but
192 * if it's null we generate our own connection
193 */
194 transient protected DBConnection localConnection = null;
195
196
197 /**
198 * The number of records we must skip over before we start reading
199 * the <code>ResultSet</code> proper in a searchAndRetrieve.
200 * 0 means no limit
201 * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
202 */
203 transient protected int offsetRecord = 0;
204
205 /* The max number of records we retrieve in a searchAndRetrieve.
206 * 0 means no limit
207 */
208 transient protected int maxRecords = 0;
209
210 /**
211 * The list of fields by which this object should be sorted when
212 * records are retrieved
213 */
214 transient protected ArrayList sortKeys = null;
215
216 /**
217 * Holds the FieldUpdate objects representing any changes made to this
218 * object since it was retrieved
219 */
220 transient protected ArrayList myUpdates = null;
221
222
223 /**
224 * If setFieldDistinct has been used to specify distinct/unique fields for
225 * the next query.
226 */
227 public boolean anyFieldsDistinct = false;
228
229 /**
230 * This flag tells the buildWhereClause method(s) to either be case
231 * sensitive (normal queries) or to be case insensitive. The case
232 * insensitive query is useful when you want to do a search and retreive
233 * and want case insensitive matching.
234 */
235 protected boolean caseSensitiveQuery = true;
236
237
238 /**
239 * Utility class that currently provides help for getFieldDate() function.
240 */
241 transient static private JDBCUtil sJdbcUtil = JDBCUtil.getInstance();
242
243
244 /**
245 * Helper component that provides the specific interactions between the
246 * DBObject and the underlying datasource, JDBC or later on, otherwise
247 *
248 * @since Expresso 5.0
249 */
250 transient private static DataExecutorInterface sDataExecutor = new JDBCExecutor();
251
252 /**
253 * Helper component that provides the specific querying capabilities
254 * against any particular datasrouce, JDBC, or later on, others.
255 *
256 * @since Expresso 5.0
257 */
258 transient private static DataQueryInterface sDataQueryObject = new JDBCQuery();
259
260
261 /**
262 * Default constructor
263 */
264 public JDBCDataObject() {
265 }
266
267
268 /**
269 * Returns the name of the physical database that we're talking with. This
270 * is opposed to getDataContext() which returns the security context as well.
271 * getMappedDataContext() is strictly used to get at the low level database
272 * connection.
273 *
274 * @return java.lang.String... the underlying data context that is mapped
275 * to the physical database
276 */
277 public String getMappedDataContext() {
278
279 if (mappedDataContext == null) {
280 this.setDataContext(RequestRegistry.getDataContext());
281 if (log.isDebugEnabled()) {
282 log.debug("Setting Database Context from Request Registry ");
283 }
284 }
285 return mappedDataContext;
286 }
287
288
289 /**
290 * Retrieve the connection pool associated with this DBObject.
291 *
292 * @return DBConnectionPool instance for the current mapped context.
293 * @throws DataException upon error getting the connection pool instance
294 */
295 public DBConnectionPool getConnectionPool() throws DataException {
296 try {
297 if (myPool == null) {
298 if (this.getMappedDataContext() == null) {
299 this.setDataContext("default");
300 }
301 String myDataContext = this.getMappedDataContext();
302 myPool = DBConnectionPool.getInstance(myDataContext);
303 }
304 } catch (DBException ex) {
305 throw new DataException("Error getting Connection Pool", ex);
306 }
307
308 return myPool;
309 }
310
311 /**
312 * Sets the connection pool associated with this dbobject. Is only settable
313 * from derived classes as we normally create our own as needed.
314 *
315 * @param newPool the new DBConnectionPool to set
316 */
317 protected void setDBConnectionPool(DBConnectionPool newPool) {
318 myPool = newPool;
319 }
320
321 /**
322 * This function is called whenever the DBField is about to be written to
323 * the database. It may do additional processing such as encryption depending
324 * on the field attributes.
325 *
326 * @param theField A DBField object
327 * @return the value to write to the data source.
328 * @throws DataException upon error
329 * @todo This is not completely implemented yet, and this responsibility for
330 * formating a field should probably be
331 * put into DBField for better object orientation.
332 */
333 public String getSerialForm(DataFieldMetaData theField)
334 throws DataException {
335 try {
336 if (theField.isEncrypted()) {
337 return Base64.encodeNoPadding(CryptoManager.getInstance()
338 .getStringEncryption().encryptString(this
339 .getDataField(theField.getName()).asString()));
340 } else {
341 String result = getDataField(theField.getName()).asString();
342 if (theField.isBooleanType()) {
343 // do we have to convert from true/false to 'Y'/'N' ??
344 try {
345 boolean nativeBoolean = ConfigManager.getContext(
346 getMappedDataContext()).getJdbc().isNativeBool();
347 if (!nativeBoolean) {
348 // fix up a true/false into what we expect to serialize
349 if ("true".equalsIgnoreCase(result)) {
350 result = "Y";
351 }
352 if ("false".equalsIgnoreCase(result)) {
353 result = "N";
354 }
355 }
356 } catch (ConfigurationException ce) {
357 throw new DataException(ce);
358 }
359 }
360 return result;
361 }
362 } catch (ChainedException e) {
363 throw new DataException("Error getting Serialized Form for field: "
364 + theField.getName(), e);
365 }
366 } /* getSerializedForm() */
367
368
369 /**
370 * Set the database name/context for this db object. If setDBName is not called,
371 * the "default" db name and context is used.
372 * See com.jcorporate.expresso.core.misc.ConfigManager for information about
373 * multiple contexts. Note that setting a db/context name only affects the
374 * object when it allocates it's own db connections - if a specific connection
375 * is used (via the setConnection(DBConnection) method) then that connection must
376 * be already associated with the correct db/context.
377 * If there is an entry in the DBOtherMap table for this object, it is forced to
378 * that database, and a warning is logged if any other database is specified.
379 *
380 * @param newOther The name of the context or database to use
381 */
382 public synchronized void setDBName(String newOther)
383 throws DBException {
384
385 /* Blank or null gets interpreted as "default" */
386 if (StringUtil.notNull(newOther).equals("")) {
387 newOther = "default";
388 }
389
390
391 if (log.isDebugEnabled()) {
392 log.debug("Object '" + myClassName + "' requesting db '" +
393 newOther + "'");
394 }
395 String mappedContext = newOther;
396 dbKey = newOther;
397 /* We don't allow an alternate database name to be specified for DBOtherMap itself! */
398 if (!"com.jcorporate.expresso.services.dbobj.DBOtherMap".equals(myClassName)) {
399 String otherdbname = StringUtil.notNull(ConfigManager.getOtherDbLocation(newOther, myClassName));
400
401 if (!otherdbname.equals("")) {
402 mappedContext = otherdbname;
403
404 if (log.isDebugEnabled()) {
405 log.debug("Object '" + myClassName +
406 "' mapped to database '" + dbKey + "'");
407 }
408 }
409 } /* if we are not DBOtherMap */
410
411 //Clear the connectionPool if it doesn't exist.
412 myPool = null;
413
414 this.setMappedDataContext(mappedContext);
415 } /* setDBName(String) */
416
417
418 /**
419 * Retrieve the database object's metadata
420 *
421 * @return a built DataObjectMetaData
422 */
423 public final DataObjectMetaData getMetaData() {
424 return getDef();
425 }
426
427 /**
428 * Retrieve the JDBCObjectMetaData
429 *
430 * @return the JDBCObjectMetadata. [Actually an instance of DataObjectMetaData,
431 * but this way it's type correct]
432 */
433 public final JDBCObjectMetaData getJDBCMetaData() {
434 return (JDBCObjectMetaData) getMetaData();
435 }
436
437 /**
438 * Return the DBObjectRef object that contains the definition of the current
439 * DBObject. If there isn't one in the dbobjMetadata hashmap, initialize a new one
440 * as required
441 *
442 * @return static the DBObject Definition
443 */
444 protected final DBObjectDef getDef() {
445 DBObjectDef myDef = (DBObjectDef) sMetadataMap.get(this.myClassName);
446
447 return myDef;
448 } /* getRef() */
449
450
451 /**
452 * Construction method to allow for specialized metadata with specialized
453 * fields other than DBObjectDef. Override in classes that need custom
454 * derived from DBObjectDef classes.
455 *
456 * @return DBOBjectDef derived instance
457 * @throws DBException upon construction error
458 */
459 protected DBObjectDef constructNewMetaData() throws DBException {
460 return new DBObjectDef();
461 }
462
463
464 /**
465 * Returns the checkzero update as defined by the object's metadata.
466 *
467 * @return true if checkZeroUpdate is unabled
468 */
469 public boolean checkZeroUpdate() {
470 return getDef().checkZeroUpdate();
471 }
472
473
474 /**
475 * Set the name of the db context that was the "original" context
476 * for this object - e.g. before any database mapping took place.
477 *
478 * @param newOriginalName new value to set
479 */
480 protected void setOriginalDBName(String newOriginalName) {
481 mappedDataContext = newOriginalName;
482 }
483
484 protected void setMappedDataContext(String newMappedName) {
485 mappedDataContext = newMappedName;
486 }
487
488 /**
489 * This tells the buildWhereClause to either respect case (true) or
490 * ignore case (false). You can call this method before doing a search and
491 * retrieve if you want to match without worrying about case. For example
492 * if you were to call this method with isCaseSensitiveQuery = FALSE
493 * then this comparison would match in the search:
494 * <p/>
495 * vendor_name actual value = "My Name"
496 * <p/>
497 * query value = "my name"
498 * <p/>
499 * This would match in a search and retrieve.
500 * <p/>
501 * author Adam Rossi, PlatinumSolutions
502 *
503 * @param caseSensitiveQuery boolean
504 */
505 public void setCaseSensitiveQuery(boolean caseSensitiveQuery) {
506
507 this.caseSensitiveQuery = caseSensitiveQuery;
508 }
509
510 /**
511 * Build an appropriate String for use in the select part of an SQL statement
512 * by doing the
513 *
514 * @param fieldName The name of the field to be handled
515 * @return The portion of the select clause with the appropriate function
516 * wrapped around it
517 */
518 public String selectFieldString(String fieldName)
519 throws DBException {
520 DataFieldMetaData oneField = getFieldMetaData(fieldName);
521
522 try {
523 JDBCConfig myConfig = null;
524 // String fieldType = oneField.getTypeString();
525
526 if (oneField.isDateOnlyType()) {
527 myConfig = ConfigManager.getJdbcRequired(getDataContext());
528
529 if (!StringUtil.notNull(myConfig.getDateSelectFunction()).equals("")) {
530 return StringUtil.replace(myConfig.getDateSelectFunction(), "%s",
531 fieldName);
532 }
533 }
534 if (oneField.isTimeType()) {
535 myConfig = ConfigManager.getJdbcRequired(getDataContext());
536
537 if (!StringUtil.notNull(myConfig.getTimeSelectFunction()).equals("")) {
538 return StringUtil.replace(myConfig.getTimeSelectFunction(), "%s",
539 fieldName);
540 }
541 }
542 if (oneField.isDateTimeType()) {
543 myConfig = ConfigManager.getJdbcRequired(getDataContext());
544
545 if (!StringUtil.notNull(myConfig.getDateTimeSelectFunction()).equals("")) {
546 return StringUtil.replace(myConfig.getDateTimeSelectFunction(), "%s",
547 fieldName);
548 }
549 }
550 } catch (ConfigurationException ce) {
551 throw new DBException(ce);
552 }
553
554 return fieldName;
555 } /* selectFieldString(String) */
556
557
558 /**
559 * Return the value of this field, placing double quotes around it if the
560 * field's datatype requires it.
561 *
562 * @param fieldName The name of the field to be used
563 * @param rangeString the appropriately formatted string
564 * @return A string, quoted if necessary, to be used in building an SQL statement
565 * @throws DBException If there is no such field or it's value cannot be
566 * determined
567 */
568 public String quoteIfNeeded(String fieldName, String rangeString)
569 throws DBException {
570 DataFieldMetaData oneField = getFieldMetaData(fieldName);
571 if (oneField == null) {
572 throw new DBException("(" + this.myClassName +
573 ") No such field as " + fieldName);
574 }
575
576 boolean noTrim = false;
577 if (!oneField.isMasked() && !isGlobalMasked()) {
578 try {
579 noTrim = ConfigManager.getJdbcRequired(this.getMappedDataContext()).isStringNotTrim();
580 } catch (ConfigurationException ce) {
581 throw new DataException(ce);
582 }
583 }
584
585 String fieldValue = getSerialForm(oneField);
586
587 /* if the field is null, we don't need to worry about quotes */
588 if (fieldValue == null) {
589 return null;
590 }
591
592 if (rangeString != null) {
593 fieldValue = fieldValue.substring(rangeString.length());
594 }
595
596 /* if the field is null, we don't need to worry about quotes */
597 if (fieldValue == null) {
598 return null;
599 }
600
601 if ((oneField.isBooleanType() || "bit".equalsIgnoreCase(oneField.getTypeString()))
602 && fieldValue.length() == 0) {
603 return null;
604 }
605
606 if (oneField.isNumericType()) {
607 if (fieldValue.length() == 0) {
608 return null;
609 }
610
611 return fieldValue.trim();
612 } /* if a numeric type */
613
614
615 if (oneField.isQuotedTextType()) {
616 if (rangeString != null) {
617 return fieldValue;
618 }
619
620 //
621 //Fix so we don't get escaped double-single quotes accidentally
622 //
623 if (fieldValue.length() == 0) {
624 return "''";
625 }
626
627 FastStringBuffer returnValue = FastStringBuffer.getInstance();
628 String returnString = null;
629 try {
630 String value = "";
631 if (noTrim) {
632 value = fieldValue;
633 } else {
634 value = fieldValue.trim();
635 }
636 returnValue.append("\'");
637 // returnValue.append(this.getConnectionPool().getEscapeHandler().escapeString(fieldValue.trim()));
638 returnValue.append(this.getConnectionPool().getEscapeHandler().escapeString(value));
639 returnValue.append("\'");
640 returnString = returnValue.toString();
641 } finally {
642 returnValue.release();
643 returnValue = null;
644 }
645 return returnString;
646 } /* if a quoted type */
647
648 if (oneField.isDateType()) {
649 if (rangeString != null) {
650 return fieldValue;
651 }
652 FastStringBuffer returnValue = FastStringBuffer.getInstance();
653 String returnString = null;
654 try {
655 returnValue.append("\'");
656 returnValue.append(fieldValue);
657 returnValue.append("\'");
658 returnString = returnValue.toString();
659 } finally {
660 returnValue.release();
661 returnValue = null;
662 }
663 return returnString;
664 }
665
666 //
667 //We don't care about rangestrings in boolean types.... they don't
668 //exactly make sense.
669 //
670 if (oneField.isBooleanType()) {
671 try {
672 boolean nativeBoolean = ConfigManager.getContext(this.getDataContext()).getJdbc().isNativeBool();
673
674 if (!nativeBoolean) {
675 FastStringBuffer returnValue = FastStringBuffer.getInstance();
676 String returnString = null;
677 try {
678 returnValue.append("\'");
679 returnValue.append(fieldValue.trim());
680 returnValue.append("\'");
681 returnString = returnValue.toString();
682 } finally {
683 returnValue.release();
684 returnValue = null;
685 }
686 return returnString;
687 }
688 } catch (ConfigurationException ce) {
689 throw new DBException(ce);
690 }
691 }
692
693 if (noTrim) {
694 return fieldValue;
695 } else {
696 return fieldValue.trim();
697 }
698 } /* quoteIfNeeded(String) */
699
700 /**
701 * Set a specific DB connection for use with this db object. If you do not set
702 * a connection, the db object will request it's own connection from the
703 * appropriate connection pool & release it again after every operation (e.g.
704 * add, update, etc). It is important to use your own explicit connection when
705 * dealing with a database transactional environment (e.g. commit(), rollback()).
706 *
707 * @param newConnection The new DBConnection object to be used by this DB Object
708 */
709 public synchronized void setConnection(DBConnection newConnection)
710 throws DBException {
711 setConnection(newConnection, newConnection.getDataContext());
712 } /* setConnection(DBConnection) */
713
714 /**
715 * <p/>
716 * Set a specific DB connection for use with this db object. If you do not set
717 * a connection, the db object will request it's own connection from the
718 * appropriate connection pool & release it again after every operation (e.g.
719 * add, update, etc). It is important to use your own explicit connection when
720 * dealing with a database transactional environment (e.g. commit(), rollback()).
721 * </p>
722 * <p>The difference between this and setConnection(DBConnection) is that this
723 * is used for using otherDB capabilities within a transaction. So you use
724 * a dbconnection from your other pool, but the setup tables are in a different
725 * context</p>
726 *
727 * @param newConnection The new DBConnection object to be used by this DB Object
728 * @param setupTablesContext the data context that is used for the expresso setup tables.
729 * @see #setConnection(DBConnection)
730 */
731 public synchronized void setConnection(DBConnection newConnection,
732 String setupTablesContext) throws DBException {
733 localConnection = newConnection;
734 this.setDataContext(setupTablesContext);
735 }
736
737
738 /**
739 * Refactoring to split the execution of a query statement into the query
740 * part and the load part. The DBConnection returned will have the query
741 * already executed.
742 * <p/>
743 * SIDE-EFFECT: custom 'where' clause is set to null.
744 *
745 * @param retrievedFieldList instantiate an ArrayList and the function will
746 * fill out the field order that the SQL statement will have it's fields in.
747 * @return connection that has already executed the search statement.
748 * <p/>
749 * Modify by Yves Henri AMAIZO <amy_amaizo@compuserve.com>
750 * @throws DBException upon database communication error
751 * @since $DatabaseSchema $Date: 2004/11/18 02:03:27 $
752 */
753 public DBConnection createAndExecuteSearch(java.util.ArrayList retrievedFieldList)
754 throws DBException {
755 boolean needComma = false;
756
757 if (recordSet == null) {
758 recordSet = new ArrayList();
759 } else {
760 recordSet.clear();
761 }
762
763 myUpdates = null;
764
765 DBConnection myConnection = null;
766 JDBCObjectMetaData myMetadata = this.getJDBCMetaData();
767 FastStringBuffer myStatement = FastStringBuffer.getInstance();
768 try {
769 if (localConnection != null) {
770 myConnection = localConnection;
771 } else {
772 myConnection = this.getConnectionPool().getConnection(this.myClassName);
773 }
774
775 myStatement.append("SELECT ");
776
777 if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_SELECT &&
778 (offsetRecord > 0 || maxRecords > 0)) {
779
780 // Insert limitation stub after table nomination
781 String limitStub = makeLimitationStub(myConnection);
782
783 myStatement.append(" ");
784 myStatement.append(limitStub);
785 myStatement.append(" ");
786 }
787
788 if (anyFieldsDistinct) {
789 String oneFieldName = null;
790 myStatement.append(" ");
791 myStatement.append(getConnectionPool().getDistinctRowsetKeyword());
792 myStatement.append(" ");
793
794 for (Iterator i = getDistinctFieldArrayList().iterator();
795 i.hasNext();) {
796 oneFieldName = (String) i.next();
797 retrievedFieldList.add(oneFieldName);
798
799 if (needComma) {
800 myStatement.append(", ");
801 }
802
803 myStatement.append(selectFieldString(oneFieldName));
804 needComma = true;
805 }
806 } else if (anyFieldsToRetrieve) { /* for each distinct field */
807 String oneFieldName = null;
808
809 for (Iterator i = getFieldsToRetrieveIterator(); i.hasNext();) {
810 oneFieldName = (String) i.next();
811
812 if (needComma) {
813 myStatement.append(", ");
814 }
815
816 retrievedFieldList.add(oneFieldName);
817 myStatement.append(selectFieldString(oneFieldName));
818 needComma = true;
819 }
820 } else { /* for each field */
821 for (Iterator i = myMetadata.getAllFieldsMap().values().iterator();
822 i.hasNext();) {
823 DBField oneField = (DBField) i.next();
824
825 if (!oneField.isVirtual() && !oneField.isBinaryObjectType()) {
826 if (needComma) {
827 myStatement.append(", ");
828 }
829
830 retrievedFieldList.add(oneField.getName());
831 myStatement.append(selectFieldString(oneField.getName()));
832 needComma = true;
833 } /* if field is not virtual */
834
835 } /* for each field */
836
837 } /* else this is a regular (non-distinct) search */
838
839
840 myStatement.append(" FROM ");
841 myStatement.append(myMetadata.getTargetSQLTable(this.getDataContext()));
842 if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_TABLE &&
843 (offsetRecord > 0 || maxRecords > 0)) {
844
845 // Insert limitation stub after table nomination
846 String limitStub = makeLimitationStub(myConnection);
847 myStatement.append(" ");
848 myStatement.append(limitStub);
849 myStatement.append(" ");
850 }
851
852 String whereClause;
853
854 if (customWhereClause != null) {
855 if (appendCustomWhere) {
856 whereClause = buildWhereClause(true) + customWhereClause;
857 appendCustomWhere = false;
858 } else {
859 whereClause = customWhereClause;
860 }
861 myStatement.append(whereClause);
862 customWhereClause = null;
863 } else {
864 whereClause = buildWhereClause(true);
865 myStatement.append(whereClause);
866 }
867 if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_WHERE &&
868 (offsetRecord > 0 || maxRecords > 0)) {
869
870 // Insert limitation stub after table nomination
871 String limitStub = makeLimitationStub(myConnection);
872
873 if (whereClause.length() > 0) {
874 myStatement.append(" AND");
875 }
876
877 myStatement.append(" ");
878 myStatement.append(limitStub);
879 myStatement.append(" ");
880 }
881 /* Add the ORDER BY clause if any sortKeys are specified */
882 if (sortKeys != null && sortKeys.size() > 0) {
883 myStatement.append(" ORDER BY ");
884
885 boolean needComma2 = false;
886
887 for (Iterator i = sortKeys.iterator(); i.hasNext();) {
888 if (needComma2) {
889 myStatement.append(", ");
890 }
891
892 myStatement.append((String) i.next());
893 needComma2 = true;
894 }
895 if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_ORDER_BY &&
896 (offsetRecord > 0 || maxRecords > 0)) {
897 myStatement.append(" ");
898 myStatement.append(makeLimitationStub(myConnection));
899 }
900 }
901
902 myConnection.execute(myStatement.toString());
903 return myConnection;
904 } catch (DBException ex) {
905 if (myConnection != null && localConnection == null) {
906 myConnection.release();
907 }
908 log.error("Error building and executing search statement", ex);
909 throw ex;
910 } finally {
911 myStatement.release();
912 }
913 }
914
915 /**
916 * Fills the given constructed data object with data from the connection
917 * given the field order specified in retrievedFieldList. Similar to
918 * loadFromConnection but much faster because the connection fields are
919 * retrieved via number instead of name.
920 *
921 * @param myObj the Object to have the values filled out. [in/out parameter]
922 * @param myConnection [in] the connection to retrieve the fields from
923 * @param retrievedFieldList [in] An array of Strings representing the field
924 * names to retrieve in the order they exist in the connection.
925 * @throws DBException upon error
926 */
927 public void loadFromConnection(JDBCDataObject myObj,
928 DBConnection myConnection, ArrayList retrievedFieldList) throws DBException {
929 int i = 1;
930 String oneFieldName = null;
931 Object tmpData = null;
932
933 for (Iterator it = retrievedFieldList.iterator();
934 it.hasNext();) {
935 oneFieldName = (String) it.next();
936 DataFieldMetaData oneDBField = getFieldMetaData(oneFieldName);
937
938 try {
939 // * @author Yves Henri AMAIZO
940 // Handle correctly date from resultSet data retrieve from Database
941 if (oneDBField.isDateType()) {
942 tmpData = getCustomStringFieldValue(myConnection, oneDBField.getName());
943 } else {
944 if (!oneDBField.isLongBinaryType() && !oneDBField.isLongCharacterType()) {
945 if (myConnection.isStringNotTrim()) {
946 tmpData = myConnection.getStringNoTrim(i);
947 } else {
948 tmpData = myConnection.getString(i);
949 }
950 } else {
951 if (oneDBField.isLongBinaryType()) {
952 tmpData = null;
953 InputStream is = myConnection.getBinaryStream(i);
954 if (is != null) {
955 byte[] bstr = new byte[LONGBINARY_READ_DEFAULT_SIZE];
956 int j = is.read(bstr);
957 if (j > 0) {
958 byte[] content = new byte[j];
959 System.arraycopy(bstr, 0, content, 0, j);
960 tmpData = content;
961 }
962 }
963 } else {
964 tmpData = myConnection.getStringNoTrim(i);
965 }
966 }
967
968 }
969 } catch (DBException de) {
970 throw new DBException("Error retrieving field '" +
971 oneFieldName, de);
972 } catch (Exception de) {
973 throw new DBException("Not DBException Error retrieving field '" +
974 oneFieldName, de);
975 }
976
977 i++;
978 myObj.set(oneFieldName, tmpData);
979 } /* for each retrieved field name */
980
981
982 myObj.setDataContext(this.getDataContext());
983 myObj.setStatus(BaseDataObject.STATUS_CURRENT);
984 }
985
986 /**
987 * Creates the limitation syntax optimisation stub
988 * to embed inside the SQL command that performs
989 * search and retrieve.
990 * <p/>
991 * <p>This method takes the limitation syntax string
992 * and performs a string replacement on the following
993 * tokens
994 * <p/>
995 * <ul>
996 * <p/>
997 * <li><b>%offset%</b><li><br>
998 * the number of rows in the <code>ResultSet</code> to skip
999 * before reading the data.
1000 * <p/>
1001 * <li><b>%maxrecord%</b><li><br>
1002 * the maximum number of rows to read from the <code>ResultSet</code>.
1003 * Also known as the <i>rowlength</i>.
1004 * <p/>
1005 * <li><b>%endrecord%</b><li><br>
1006 * the last record of in the <code>ResultSet</code> that the
1007 * search and retrieved should retrieve. The end record number
1008 * is equal to <code>( %offset% + %maxrecord% - 1 )</code>
1009 * <p/>
1010 * </ul>
1011 * <p/>
1012 * </p>
1013 * <p/>
1014 * <p/>
1015 * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
1016 *
1017 * @param theConnection the db connection to make this stub from
1018 * @return the limitation syntax stub string
1019 * @see #searchAndRetrieveList()
1020 * @see #setOffsetRecord( int )
1021 * @see #setMaxRecords( int )
1022 */
1023 protected String makeLimitationStub(DBConnection theConnection) {
1024 String limit = theConnection.getLimitationSyntax();
1025 int offset = this.getOffsetRecord();
1026 int maxrec = this.getMaxRecords();
1027 int endrec = offset + maxrec - 1;
1028 limit = StringUtil.replace(limit, "%offset%", Integer.toString(offset));
1029 limit = StringUtil.replace(limit, "%maxrecords%",
1030 Integer.toString(maxrec));
1031
1032 // limit = StringUtil.replace( limit, "%length%", Integer.toString( maxrec ) );
1033 limit = StringUtil.replace(limit, "%endrecord%",
1034 Integer.toString(endrec));
1035
1036 return limit;
1037 } /* makeLimitationStub(DBConnection) */
1038
1039 /**
1040 * Get a special <code>ArrayList</code> object list of all
1041 * of the fields in this object that are set to <b>distinct</b>
1042 *
1043 * @return An Iterator of all the
1044 * <b>distinct</b> fieldNames in this object
1045 * @throws DBException If the list cannot be retrieved
1046 * <p/>
1047 * author Peter Pilgrim <peter.pilgrim@db.com>
1048 */
1049 public ArrayList getDistinctFieldArrayList()
1050 throws DBException {
1051 ArrayList arl = new ArrayList();
1052
1053 if (distinctFields == null) {
1054 return arl;
1055 }
1056 for (Iterator i = this.getMetaData().getFieldListArray().iterator(); i.hasNext();) {
1057 String fieldName = (String) i.next();
1058
1059 if (distinctFields.containsKey(fieldName)) {
1060 arl.add(fieldName);
1061 }
1062 }
1063
1064 return arl;
1065 }
1066
1067 /**
1068 * Get a special <code>Iterator</code> object list of all
1069 * of the fields in this object that are set to <b>retrieve</b>
1070 * <p>Author Yves henri Amaizo <amy_amaizo@compuserve.com></p>
1071 *
1072 * @return An Iterator of all the
1073 * <b>retrieve</b> fieldNames in this object
1074 * @throws DBException If the list cannot be retrieved
1075 */
1076 public Iterator getFieldsToRetrieveIterator()
1077 throws DBException {
1078
1079 //Do a dummy so we don't throw null pointer exceptions
1080 if (retrieveFields == null) {
1081 return new HashMap().keySet().iterator();
1082 }
1083
1084 return retrieveFields.keySet().iterator();
1085 }
1086
1087 /**
1088 * Build and return a string consisting of an SQL 'where' clause
1089 * using the current field values as criteria for the search. See
1090 * setCustomWhereClause for information on specifying a more complex where clause.
1091 *
1092 * @param useAllFields True if all fields are to be used,
1093 * false for only key fields
1094 * @return The where clause to use in a query.
1095 */
1096 public String buildWhereClause(boolean useAllFields)
1097 throws DBException {
1098
1099 return sJdbcUtil.buildWhereClause(this, useAllFields);
1100 } /* buildWhereClause(boolean) */
1101
1102
1103 /**
1104 * Build and return a FastStringBuffer ring consisting of an SQL 'where' clause
1105 * using the current field values as criteria for the search. See
1106 * setCustomWhereClause for information on specifying a more complex where clause.
1107 *
1108 * @param useAllFields True if all fields are to be used,
1109 * false for only key fields
1110 * @param allocatedBuffer - An already allocated FastStringBuffer to fill out.
1111 * This allows for compatability with, for example, object pools.
1112 * @return A FastStringBuffer containing the "where" clause for the SQL statement
1113 */
1114 protected FastStringBuffer buildWhereClauseBuffer(boolean useAllFields,
1115 FastStringBuffer allocatedBuffer)
1116 throws DBException {
1117
1118 try {
1119 return sJdbcUtil.buildWhereClauseBuffer(this, useAllFields, allocatedBuffer);
1120 } catch (DataException ex) {
1121 throw new DBException(ex.getMessage());
1122 }
1123 }
1124
1125 /**
1126 * Get the JDBC Util functions
1127 *
1128 * @return JDBCUtil class.
1129 */
1130 protected JDBCUtil getJDBCUtil() {
1131 return sJdbcUtil;
1132 }
1133
1134 /**
1135 * Use this function to acquire the Executor interface that is associated
1136 * with this data object
1137 *
1138 * @return DataExecutorInterface or null if no Executor has been associated
1139 * with this object
1140 */
1141 public DataExecutorInterface getExecutor() {
1142 return sDataExecutor;
1143 }
1144
1145 /**
1146 * Use this function to acquire the DataQueryInterface that is associated
1147 * with this data object
1148 *
1149 * @return DataQueryInterface or null if no QueryInterface has been
1150 * associated with this object
1151 */
1152 public DataQueryInterface getQueryInterface() {
1153 return sDataQueryObject;
1154 }
1155
1156 /**
1157 * <p>This convenience method retrieve through the resultSet.MetaData
1158 * the date value of field define as date or time <code>DBObject</code>
1159 *
1160 * @param connection The DBConnection
1161 * @param oneFieldName the field name to get the custom field value from
1162 * @return Custom String value retrieve from resultSet according to metaData.
1163 * @throws DBException author Yves Henri AMAIZO <amy_amaizo@compuserve.com>
1164 */
1165 public String getCustomStringFieldValue(DBConnection connection, String oneFieldName)
1166 throws DBException {
1167 ConfigJdbc myConfig = null;
1168 String oneFieldValue = null;
1169 DataFieldMetaData fieldMetadata = this.getFieldMetaData(oneFieldName);
1170 try {
1171 myConfig = ConfigManager.getJdbcRequired(getDataContext());
1172 } catch (ConfigurationException ce) {
1173 throw new DBException(ce);
1174 }
1175 if (fieldMetadata.isDateTimeType()) {
1176 if (StringUtil.notNull(myConfig.getDateTimeSelectFormat()).length() > 0) {
1177 oneFieldValue = DateTime.getDateTimeForDB(connection.getTimestamp(oneFieldName), dbKey);
1178 } else {
1179 oneFieldValue = connection.getString(oneFieldName);
1180 }
1181 }
1182 if (fieldMetadata.isTimeType()) {
1183 if (!StringUtil.notNull(myConfig.getTimeSelectFormat()).equals("")) {
1184 oneFieldValue = DateTime.getTimeForDB(connection.getTime(oneFieldName), dbKey);
1185 } else {
1186 oneFieldValue = connection.getString(oneFieldName);
1187 }
1188 }
1189 if (fieldMetadata.isDateOnlyType()) {
1190 if (!StringUtil.notNull(myConfig.getDateSelectFormat()).equals("")) {
1191 oneFieldValue = DateTime.getDateForDB(connection.getDate(oneFieldName), dbKey);
1192 } else {
1193 oneFieldValue = connection.getString(oneFieldName);
1194 }
1195 }
1196 return oneFieldValue;
1197 } /* getCustomStringFieldValue() */
1198
1199
1200 /**
1201 * <p>Return local DBConnection value
1202 * <p/>
1203 * author Yves Henri AMAIZO <amy_amaizo@compuserve.com>
1204 *
1205 * @return DBConnection
1206 */
1207 public DBConnection getLocalConnection() {
1208 return localConnection;
1209 }
1210
1211
1212 /**
1213 * Refactoring to split the execution of a query statement into the query
1214 * part and the load part. The DBConnection returned will have the query
1215 * already executed, it is ready to
1216 *
1217 * @param retrievedFieldList instantiate an ArrayList and the function will
1218 * fill out the field order that the SQL statement will have it's fields in.
1219 * @return connection that has already executed the search statement.
1220 * @throws DBException upon database communication error
1221 */
1222 public DBConnection createAndRunStoreProcedure(java.util.ArrayList retrievedFieldList)
1223 throws DBException {
1224 if (recordSet == null) {
1225 recordSet = new ArrayList();
1226 } else {
1227 recordSet.clear();
1228 }
1229
1230 myUpdates = null;
1231
1232 DBConnection myConnection = null;
1233 FastStringBuffer myStatement = FastStringBuffer.getInstance();
1234 try {
1235 if (localConnection != null) {
1236 myConnection = localConnection;
1237 } else {
1238 myConnection = this.getConnectionPool().getConnection(this.myClassName);
1239 }
1240
1241 int nbParams = this.getDef().getFieldListArray().size();
1242 if (this.getDef().isReturningValue()) {
1243 myStatement.append("{? = call ");
1244 nbParams--;
1245 } else {
1246 myStatement.append("{call ");
1247 }
1248 myStatement.append(this.getJDBCMetaData().getTargetTable());
1249
1250 if (nbParams > 0) {
1251 myStatement.append("(?");
1252 for (int i = 1; i < nbParams; i++) {
1253 myStatement.append(", ?");
1254 }
1255 myStatement.append(")");
1256 }
1257 myStatement.append("}");
1258
1259 CallableStatement theStroreProcedureStatement = myConnection.createCallableStatement(
1260 myStatement.toString());
1261 sJdbcUtil.buildStoreProcedureCallableStatement(this, theStroreProcedureStatement);
1262 myConnection.executeProcedure();
1263
1264 return myConnection;
1265 } catch (DBException ex) {
1266 if (myConnection != null && localConnection == null) {
1267 myConnection.release();
1268 }
1269 log.error("Error building and running store procedure statement", ex);
1270 throw ex;
1271 } finally {
1272 myStatement.release();
1273 }
1274 } /* createAndRunStoreProcedure(java.util.ArrayList) */
1275
1276 /**
1277 * Add a new field to the list of fields that are part of this
1278 * object's list of input parameters. Called after all of the "addField" calls in the setupFields()
1279 * method to specify which fields make up the primary key of this object.
1280 *
1281 * @param inFieldName The name of the field to add as part of the key
1282 * @throws DBException if the field name is not valid or the field
1283 * allows nulls
1284 */
1285 protected synchronized void addInParam(String inFieldName)
1286 throws DBException {
1287 getDef().addInParam(inFieldName);
1288 } /* addInParam(String) */
1289
1290 /**
1291 * Add a new field to the list of fields that are part of this
1292 * object's list of output parameter. Called after all of the "addField" calls in the setupFields()
1293 * method to specify which fields make up the primary key of this object.
1294 *
1295 * @param outFieldName The name of the field to add as part of the key
1296 * @throws DBException if the field name is not valid or the field
1297 * allows nulls
1298 */
1299 protected synchronized void addOutParam(String outFieldName)
1300 throws DBException {
1301 getDef().addOutParam(outFieldName);
1302 } /* addOutParam(String) */
1303
1304 /**
1305 * Set the target store procedure for this DBObject.
1306 *
1307 * @param theStoreProcedure Table for this object
1308 * @throws DBException upon execution error.
1309 */
1310 public synchronized void setTargetStoreProcedure(String theStoreProcedure)
1311 throws DBException {
1312 getDef().setTargetStoreProcedure(theStoreProcedure);
1313 } /* setTargetStoreProcedure(String) */
1314
1315 /**
1316 * Run a particular store procedure in the database into this object's fields
1317 *
1318 * @throws DBException If the record could not be retrieved.
1319 */
1320 public void runStoredProcedure()
1321 throws DBException {
1322
1323 this.getExecutor().runStoreProcedure(this);
1324
1325 if (getDef().isLoggingEnabled()) {
1326 myUpdates = null;
1327 }
1328
1329 this.setStatus(BaseDataObject.STATUS_CURRENT);
1330 }
1331
1332
1333 /**
1334 * Run a particular store procedure in the database into this object's fields
1335 *
1336 * @return Vector A vector of new database objects containing the results
1337 * of the search
1338 * @throws DBException If the record could not be retrieved.
1339 * @todo one line below was adapted during move from dbobject.java, and is probably wrong.; after correction, make method public 10/04 Larry
1340 */
1341 protected synchronized ArrayList runStoredProcedureAndRetrieveList()
1342 throws