View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2004 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
17   */
18  
19  import java.io.BufferedOutputStream;
20  import java.io.ByteArrayOutputStream;
21  import java.io.IOException;
22  import java.io.ObjectOutputStream;
23  import java.io.Serializable;
24  import java.math.BigDecimal;
25  import java.sql.Connection;
26  import java.sql.PreparedStatement;
27  import java.sql.SQLException;
28  import java.sql.Statement;
29  import java.util.ArrayList;
30  import java.util.Collections;
31  import java.util.HashSet;
32  import java.util.Hashtable;
33  import java.util.Iterator;
34  import java.util.List;
35  
36  import org.apache.commons.lang.StringUtils;
37  
38  import org.apache.commons.logging.Log;
39  import org.apache.commons.logging.LogFactory;
40  
41  import org.apache.torque.Torque;
42  import org.apache.torque.TorqueException;
43  import org.apache.torque.adapter.DB;
44  import org.apache.torque.map.ColumnMap;
45  import org.apache.torque.map.DatabaseMap;
46  import org.apache.torque.map.MapBuilder;
47  import org.apache.torque.map.TableMap;
48  import org.apache.torque.oid.IdGenerator;
49  import org.apache.torque.om.NumberKey;
50  import org.apache.torque.om.ObjectKey;
51  import org.apache.torque.om.SimpleKey;
52  import org.apache.torque.om.StringKey;
53  
54  import com.workingdogs.village.Column;
55  import com.workingdogs.village.DataSet;
56  import com.workingdogs.village.KeyDef;
57  import com.workingdogs.village.QueryDataSet;
58  import com.workingdogs.village.Record;
59  import com.workingdogs.village.Schema;
60  import com.workingdogs.village.TableDataSet;
61  
62  /***
63   * This is the base class for all Peer classes in the system.  Peer
64   * classes are responsible for isolating all of the database access
65   * for a specific business object.  They execute all of the SQL
66   * against the database.  Over time this class has grown to include
67   * utility methods which ease execution of cross-database queries and
68   * the implementation of concrete Peers.
69   *
70   * @author <a href="mailto:frank.kim@clearink.com">Frank Y. Kim</a>
71   * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
72   * @author <a href="mailto:bmclaugh@algx.net">Brett McLaughlin</a>
73   * @author <a href="mailto:stephenh@chase3000.com">Stephen Haberman</a>
74   * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
75   * @author <a href="mailto:vido@ldh.org">Augustin Vidovic</a>
76   * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
77   * @version $Id: BasePeer.java,v 1.76.2.5 2004/09/11 14:28:59 seade Exp $
78   */
79  public abstract class BasePeer implements java.io.Serializable
80  {
81      /*** Constant criteria key to reference ORDER BY columns. */
82      public static final String ORDER_BY = "ORDER BY";
83  
84      /***
85       * Constant criteria key to remove Case Information from
86       * search/ordering criteria.
87       */
88      public static final String IGNORE_CASE = "IgNOrE cAsE";
89  
90      /*** Classes that implement this class should override this value. */
91      public static final String TABLE_NAME = "TABLE_NAME";
92  
93      /*** 
94       * The Torque default MapBuilder.
95       * 
96       * @deprecated there is no default map builder! 
97       */
98      public static final String DEFAULT_MAP_BUILDER =
99          "org.apache.torque.util.db.map.TurbineMapBuilder";
100 
101     /*** Hashtable that contains the cached mapBuilders. */
102     private static Hashtable mapBuilders = new Hashtable(5);
103 
104     /*** the log */
105     protected static Log log = LogFactory.getLog(BasePeer.class);
106 
107     /***
108      * Converts a hashtable to a byte array for storage/serialization.
109      *
110      * @param hash The Hashtable to convert.
111      * @return A byte[] with the converted Hashtable.
112      * @throws TorqueException Any exceptions caught during processing will be
113      *         rethrown wrapped into a TorqueException.
114      */
115     public static byte[] hashtableToByteArray(Hashtable hash)
116         throws TorqueException
117     {
118         Hashtable saveData = new Hashtable(hash.size());
119         String key = null;
120         Object value = null;
121         byte[] byteArray = null;
122 
123         Iterator keys = hash.keySet().iterator();
124         while (keys.hasNext())
125         {
126             key = (String) keys.next();
127             value = hash.get(key);
128             if (value instanceof Serializable)
129             {
130                 saveData.put(key, value);
131             }
132         }
133 
134         ByteArrayOutputStream baos = null;
135         BufferedOutputStream bos = null;
136         ObjectOutputStream out = null;
137         try
138         {
139             // These objects are closed in the finally.
140             baos = new ByteArrayOutputStream();
141             bos = new BufferedOutputStream(baos);
142             out = new ObjectOutputStream(bos);
143 
144             out.writeObject(saveData);
145             out.flush();
146             bos.flush();
147             byteArray = baos.toByteArray();
148         }
149         catch (Exception e)
150         {
151             throwTorqueException(e);
152         }
153         finally
154         {
155             if (out != null)
156             {
157                 try
158                 {
159                     out.close();
160                 }
161                 catch (IOException ignored)
162                 {
163                 }
164             }
165 
166             if (bos != null)
167             {
168                 try
169                 {
170                     bos.close();
171                 }
172                 catch (IOException ignored)
173                 {
174                 }
175             }
176 
177             if (baos != null)
178             {
179                 try
180                 {
181                     baos.close();
182                 }
183                 catch (IOException ignored)
184                 {
185                 }
186             }
187         }
188         return byteArray;
189     }
190 
191     private static void throwTorqueException(Exception e)
192         throws TorqueException
193     {
194         if (e instanceof TorqueException) 
195         {
196             throw (TorqueException)e;
197         }
198         else 
199         {
200             throw new TorqueException(e);
201         }
202     }
203 
204     /***
205      * Sets up a Schema for a table.  This schema is then normally
206      * used as the argument for initTableColumns().
207      *
208      * @param tableName The name of the table.
209      * @return A Schema.
210      */
211     public static Schema initTableSchema(String tableName)
212     {
213         return initTableSchema(tableName, Torque.getDefaultDB());
214     }
215 
216     /***
217      * Sets up a Schema for a table.  This schema is then normally
218      * used as the argument for initTableColumns
219      *
220      * @param tableName The propery name for the database in the
221      * configuration file.
222      * @param dbName The name of the database.
223      * @return A Schema.
224      */
225     public static Schema initTableSchema(String tableName, String dbName)
226     {
227         Schema schema = null;
228         Connection con = null;
229 
230         try
231         {
232             con = Torque.getConnection(dbName);
233             schema = new Schema().schema(con, tableName);
234         }
235         catch (Exception e)
236         {
237             log.error(e);
238             throw new Error("Error in BasePeer.initTableSchema("
239                     + tableName
240                     + "): "
241                     + e.getMessage());
242         }
243         finally
244         {
245             Torque.closeConnection(con);
246         }
247         return schema;
248     }
249 
250     /***
251      * Creates a Column array for a table based on its Schema.
252      *
253      * @param schema A Schema object.
254      * @return A Column[].
255      */
256     public static Column[] initTableColumns(Schema schema)
257     {
258         Column[] columns = null;
259         try
260         {
261             int numberOfColumns = schema.numberOfColumns();
262             columns = new Column[numberOfColumns];
263             for (int i = 0; i < numberOfColumns; i++)
264             {
265                 columns[i] = schema.column(i + 1);
266             }
267         }
268         catch (Exception e)
269         {
270             log.error(e);
271             throw new Error(
272                 "Error in BasePeer.initTableColumns(): " + e.getMessage());
273         }
274         return columns;
275     }
276 
277     /***
278      * Convenience method to create a String array of column names.
279      *
280      * @param columns A Column[].
281      * @return A String[].
282      */
283     public static String[] initColumnNames(Column[] columns)
284     {
285         String[] columnNames = null;
286         columnNames = new String[columns.length];
287         for (int i = 0; i < columns.length; i++)
288         {
289             columnNames[i] = columns[i].name().toUpperCase();
290         }
291         return columnNames;
292     }
293 
294     /***
295      * Convenience method to create a String array of criteria keys.
296      *
297      * @param tableName Name of table.
298      * @param columnNames A String[].
299      * @return A String[].
300      */
301     public static String[] initCriteriaKeys(
302         String tableName,
303         String[] columnNames)
304     {
305         String[] keys = new String[columnNames.length];
306         for (int i = 0; i < columnNames.length; i++)
307         {
308             keys[i] = tableName + "." + columnNames[i].toUpperCase();
309         }
310         return keys;
311     }
312 
313     /***
314      * Convenience method that uses straight JDBC to delete multiple
315      * rows.  Village throws an Exception when multiple rows are
316      * deleted.
317      *
318      * @param con A Connection.
319      * @param table The table to delete records from.
320      * @param column The column in the where clause.
321      * @param value The value of the column.
322      * @throws TorqueException Any exceptions caught during processing will be
323      *         rethrown wrapped into a TorqueException.
324      */
325     public static void deleteAll(
326         Connection con,
327         String table,
328         String column,
329         int value)
330         throws TorqueException
331     {
332         Statement statement = null;
333         try
334         {
335             statement = con.createStatement();
336 
337             StringBuffer query = new StringBuffer();
338             query.append("DELETE FROM ")
339                 .append(table)
340                 .append(" WHERE ")
341                 .append(column)
342                 .append(" = ")
343                 .append(value);
344 
345             statement.executeUpdate(query.toString());
346         }
347         catch (SQLException e)
348         {
349             throw new TorqueException(e);
350         }
351         finally
352         {
353             if (statement != null)
354             {
355                 try
356                 {
357                     statement.close();
358                 }
359                 catch (SQLException ignored)
360                 {
361                 }
362             }
363         }
364     }
365 
366     /***
367      * Convenience method that uses straight JDBC to delete multiple
368      * rows.  Village throws an Exception when multiple rows are
369      * deleted.  This method attempts to get the default database from
370      * the pool.
371      *
372      * @param table The table to delete records from.
373      * @param column The column in the where clause.
374      * @param value The value of the column.
375      * @throws TorqueException Any exceptions caught during processing will be
376      *         rethrown wrapped into a TorqueException.
377      */
378     public static void deleteAll(String table, String column, int value)
379         throws TorqueException
380     {
381         Connection con = null;
382         try
383         {
384             // Get a connection to the db.
385             con = Torque.getConnection("default");
386             deleteAll(con, table, column, value);
387         }
388         finally
389         {
390             Torque.closeConnection(con);
391         }
392     }
393 
394     /***
395      * Method to perform deletes based on values and keys in a
396      * Criteria.
397      *
398      * @param criteria The criteria to use.
399      * @throws TorqueException Any exceptions caught during processing will be
400      *         rethrown wrapped into a TorqueException.
401      */
402     public static void doDelete(Criteria criteria) throws TorqueException
403     {
404         Connection con = null;
405         try
406         {
407             con = Transaction.beginOptional(
408                     criteria.getDbName(),
409                     criteria.isUseTransaction());
410             doDelete(criteria, con);
411             Transaction.commit(con);
412         }
413         catch (TorqueException e)
414         {
415             Transaction.safeRollback(con);
416             throw e;
417         }
418     }
419 
420     /***
421      * Method to perform deletes based on values and keys in a Criteria.
422      *
423      * @param criteria The criteria to use.
424      * @param con A Connection.
425      * @throws TorqueException Any exceptions caught during processing will be
426      *         rethrown wrapped into a TorqueException.
427      */
428     public static void doDelete(Criteria criteria, Connection con)
429         throws TorqueException
430     {
431         DB db = Torque.getDB(criteria.getDbName());
432         DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
433 
434         // Set up a list of required tables and add extra entries to
435         // criteria if directed to delete all related records.
436         // StringStack.add() only adds element if it is unique.
437         HashSet tables = new HashSet();
438         Iterator it = criteria.keySet().iterator();
439         while (it.hasNext())
440         {
441             String key = (String) it.next();
442             Criteria.Criterion c = criteria.getCriterion(key);
443             List tableNames = c.getAllTables();
444             for (int i = 0; i < tableNames.size(); i++)
445             {
446                 String name = (String) tableNames.get(i);
447                 String tableName2 = criteria.getTableForAlias(name);
448                 if (tableName2 != null)
449                 {
450                     tables.add(new StringBuffer(
451                             name.length() + tableName2.length() + 1)
452                             .append(tableName2)
453                             .append(' ')
454                             .append(name)
455                             .toString());
456                 }
457                 else
458                 {
459                     tables.add(name);
460                 }
461             }
462 
463             if (criteria.isCascade())
464             {
465                 // This steps thru all the columns in the database.
466                 TableMap[] tableMaps = dbMap.getTables();
467                 for (int i = 0; i < tableMaps.length; i++)
468                 {
469                     ColumnMap[] columnMaps = tableMaps[i].getColumns();
470                     for (int j = 0; j < columnMaps.length; j++)
471                     {
472                         // Only delete rows where the foreign key is
473                         // also a primary key.  Other rows need
474                         // updating, but that is not implemented.
475                         if (columnMaps[j].isForeignKey()
476                             && columnMaps[j].isPrimaryKey()
477                             && key.equals(columnMaps[j].getRelatedName()))
478                         {
479                             tables.add(tableMaps[i].getName());
480                             criteria.add(columnMaps[j].getFullyQualifiedName(),
481                                 criteria.getValue(key));
482                         }
483                     }
484                 }
485             }
486         }
487         Iterator tabIt = tables.iterator();
488         while (tabIt.hasNext())
489         {
490             String tab = (String) tabIt.next();
491             KeyDef kd = new KeyDef();
492             HashSet whereClause = new HashSet();
493 
494             ColumnMap[] columnMaps = dbMap.getTable(tab).getColumns();
495             for (int j = 0; j < columnMaps.length; j++)
496             {
497                 ColumnMap colMap = columnMaps[j];
498                 if (colMap.isPrimaryKey())
499                 {
500                     kd.addAttrib(colMap.getColumnName());
501                 }
502                 String key = new StringBuffer(colMap.getTableName())
503                         .append('.')
504                         .append(colMap.getColumnName())
505                         .toString();
506                 if (criteria.containsKey(key))
507                 {
508                     if (criteria.getComparison(key).equals(Criteria.CUSTOM))
509                     {
510                         whereClause.add(criteria.getString(key));
511                     }
512                     else
513                     {
514                         whereClause.add(SqlExpression.build(
515                                 colMap.getColumnName(),
516                                 criteria.getValue(key),
517                                 criteria.getComparison(key),
518                                 criteria.isIgnoreCase(),
519                                 db));
520                     }
521                 }
522             }
523 
524             // Execute the statement.
525             TableDataSet tds = null;
526             try
527             {
528                 tds = new TableDataSet(con, tab, kd);
529                 String sqlSnippet = StringUtils.join(whereClause.iterator(), " AND ");
530 
531                 if (log.isDebugEnabled())
532                 {
533                     log.debug("BasePeer.doDelete: whereClause=" + sqlSnippet);
534                 }
535 
536                 tds.where(sqlSnippet);
537                 tds.fetchRecords();
538                 if (tds.size() > 1 && criteria.isSingleRecord())
539                 {
540                     handleMultipleRecords(tds);
541                 }
542                 for (int j = 0; j < tds.size(); j++)
543                 {
544                     Record rec = tds.getRecord(j);
545                     rec.markToBeDeleted();
546                     rec.save();
547                 }
548             }
549             catch (Exception e)
550             {
551                 throwTorqueException(e);
552             }
553             finally
554             {
555                 if (tds != null)
556                 {
557                     try
558                     {
559                         tds.close();
560                     }
561                     catch (Exception ignored)
562                     {
563                     }
564                 }
565             }
566         }
567     }
568 
569     /***
570      * Method to perform inserts based on values and keys in a
571      * Criteria.
572      * <p>
573      * If the primary key is auto incremented the data in Criteria
574      * will be inserted and the auto increment value will be returned.
575      * <p>
576      * If the primary key is included in Criteria then that value will
577      * be used to insert the row.
578      * <p>
579      * If no primary key is included in Criteria then we will try to
580      * figure out the primary key from the database map and insert the
581      * row with the next available id using util.db.IDBroker.
582      * <p>
583      * If no primary key is defined for the table the values will be
584      * inserted as specified in Criteria and -1 will be returned.
585      *
586      * @param criteria Object containing values to insert.
587      * @return An Object which is the id of the row that was inserted
588      * (if the table has a primary key) or null (if the table does not
589      * have a primary key).
590      * @throws TorqueException Any exceptions caught during processing will be
591      *         rethrown wrapped into a TorqueException.
592      */
593     public static ObjectKey doInsert(Criteria criteria) throws TorqueException
594     {
595         Connection con = null;
596         ObjectKey id = null;
597 
598         try
599         {
600             con = Transaction.beginOptional(
601                     criteria.getDbName(),
602                     criteria.isUseTransaction());
603             id = doInsert(criteria, con);
604             Transaction.commit(con);
605         }
606         catch (TorqueException e)
607         {
608             Transaction.safeRollback(con);
609             throw e;
610         }
611 
612         return id;
613     }
614 
615     /***
616      * Method to perform inserts based on values and keys in a
617      * Criteria.
618      * <p>
619      * If the primary key is auto incremented the data in Criteria
620      * will be inserted and the auto increment value will be returned.
621      * <p>
622      * If the primary key is included in Criteria then that value will
623      * be used to insert the row.
624      * <p>
625      * If no primary key is included in Criteria then we will try to
626      * figure out the primary key from the database map and insert the
627      * row with the next available id using util.db.IDBroker.
628      * <p>
629      * If no primary key is defined for the table the values will be
630      * inserted as specified in Criteria and null will be returned.
631      *
632      * @param criteria Object containing values to insert.
633      * @param con A Connection.
634      * @return An Object which is the id of the row that was inserted
635      * (if the table has a primary key) or null (if the table does not
636      * have a primary key).
637      * @throws TorqueException Any exceptions caught during processing will be
638      *         rethrown wrapped into a TorqueException.
639      */
640     public static ObjectKey doInsert(Criteria criteria, Connection con)
641         throws TorqueException
642     {
643         SimpleKey id = null;
644 
645         // Get the table name and method for determining the primary
646         // key value.
647         String tableName = null;
648         Iterator keys = criteria.keySet().iterator();
649         if (keys.hasNext())
650         {
651             tableName = criteria.getTableName((String) keys.next());
652         }
653         else
654         {
655             throw new TorqueException("Database insert attempted without "
656                     + "anything specified to insert");
657         }
658 
659         DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
660         TableMap tableMap = dbMap.getTable(tableName);
661         Object keyInfo = tableMap.getPrimaryKeyMethodInfo();
662         IdGenerator keyGen = tableMap.getIdGenerator();
663 
664         ColumnMap pk = getPrimaryKey(criteria);
665 
666         // pk will be null if there is no primary key defined for the table
667         // we're inserting into.
668         if (pk != null && !criteria.containsKey(pk.getFullyQualifiedName()))
669         {
670             if (keyGen == null)
671             {
672                 throw new TorqueException(
673                     "IdGenerator for table '" + tableName + "' is null");
674             }
675             // If the keyMethod is SEQUENCE or IDBROKERTABLE, get the id
676             // before the insert.
677 
678             if (keyGen.isPriorToInsert())
679             {
680                 try
681                 {
682                     if (pk.getType() instanceof Number)
683                     {
684                         id = new NumberKey(
685                                 keyGen.getIdAsBigDecimal(con, keyInfo));
686                     }
687                     else
688                     {
689                         id = new StringKey(keyGen.getIdAsString(con, keyInfo));
690                     }
691                 }
692                 catch (Exception e)
693                 {
694                     throwTorqueException(e);
695                 }
696                 criteria.add(pk.getFullyQualifiedName(), id);
697             }
698         }
699 
700         // Use Village to perform the insert.
701         TableDataSet tds = null;
702         try
703         {
704             tds = new TableDataSet(con, tableName);
705             Record rec = tds.addRecord();
706             BasePeer.insertOrUpdateRecord(rec, tableName, criteria);
707         }
708         catch (Exception e)
709         {
710             throwTorqueException(e);
711         }
712         finally
713         {
714             if (tds != null)
715             {
716                 try
717                 {
718                     tds.close();
719                 }
720                 catch (Exception e)
721                 {
722                     throwTorqueException(e);
723                 }
724             }
725         }
726 
727         // If the primary key column is auto-incremented, get the id
728         // now.
729         if (pk != null && keyGen != null && keyGen.isPostInsert())
730         {
731             try
732             {
733                 if (pk.getType() instanceof Number)
734                 {
735                     id = new NumberKey(keyGen.getIdAsBigDecimal(con, keyInfo));
736                 }
737                 else
738                 {
739                     id = new StringKey(keyGen.getIdAsString(con, keyInfo));
740                 }
741             }
742             catch (Exception e)
743             {
744                 throwTorqueException(e);
745             }
746         }
747 
748         return id;
749     }
750 
751     /***
752      * Grouping of code used in both doInsert() and doUpdate()
753      * methods.  Sets up a Record for saving.
754      *
755      * @param rec A Record.
756      * @param tableName Name of table.
757      * @param criteria A Criteria.
758      * @throws TorqueException Any exceptions caught during processing will be
759      *         rethrown wrapped into a TorqueException.
760      */
761     private static void insertOrUpdateRecord(
762         Record rec,
763         String tableName,
764         Criteria criteria)
765         throws TorqueException
766     {
767         DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
768 
769         ColumnMap[] columnMaps = dbMap.getTable(tableName).getColumns();
770         boolean shouldSave = false;
771         for (int j = 0; j < columnMaps.length; j++)
772         {
773             ColumnMap colMap = columnMaps[j];
774             String key = new StringBuffer(colMap.getTableName())
775                     .append('.')
776                     .append(colMap.getColumnName())
777                     .toString();
778             if (criteria.containsKey(key))
779             {
780                 // A village Record.setValue( String, Object ) would
781                 // be nice here.
782                 Object obj = criteria.getValue(key);
783                 if (obj instanceof SimpleKey)
784                 {
785                     obj = ((SimpleKey) obj).getValue();
786                 }
787                 try
788                 {
789                     if (obj == null)
790                     {
791                         rec.setValueNull(colMap.getColumnName());
792                     }
793                     else if (obj instanceof String)
794                     {
795                         rec.setValue(colMap.getColumnName(), (String) obj);
796                     }
797                     else if (obj instanceof Integer)
798                     {
799                         rec.setValue(colMap.getColumnName(),
800                                 criteria.getInt(key));
801                     }
802                     else if (obj instanceof BigDecimal)
803                     {
804                         rec.setValue(colMap.getColumnName(), (BigDecimal) obj);
805                     }
806                     else if (obj instanceof Boolean)
807                     {
808                         rec.setValue(colMap.getColumnName(),
809                             criteria.getBoolean(key) ? 1 : 0);
810                     }
811                     else if (obj instanceof java.util.Date)
812                     {
813                         rec.setValue(colMap.getColumnName(),
814                             (java.util.Date) obj);
815                     }
816                     else if (obj instanceof Float)
817                     {
818                         rec.setValue(colMap.getColumnName(),
819                             criteria.getFloat(key));
820                     }
821                     else if (obj instanceof Double)
822                     {
823                         rec.setValue(colMap.getColumnName(),
824                             criteria.getDouble(key));
825                     }
826                     else if (obj instanceof Byte)
827                     {
828                         rec.setValue(colMap.getColumnName(),
829                             ((Byte) obj).byteValue());
830                     }
831                     else if (obj instanceof Long)
832                     {
833                         rec.setValue(colMap.getColumnName(),
834                             criteria.getLong(key));
835                     }
836                     else if (obj instanceof Short)
837                     {
838                         rec.setValue(colMap.getColumnName(),
839                             ((Short) obj).shortValue());
840                     }
841                     else if (obj instanceof Hashtable)
842                     {
843                         rec.setValue(colMap.getColumnName(),
844                             hashtableToByteArray((Hashtable) obj));
845                     }
846                     else if (obj instanceof byte[])
847                     {
848                         rec.setValue(colMap.getColumnName(), (byte[]) obj);
849                     }
850                 }
851                 catch (Exception e)
852                 {
853                     throwTorqueException(e);
854                 }
855                 shouldSave = true;
856             }
857         }
858 
859         if (shouldSave)
860         {
861             try
862             {
863                 rec.save();
864             }
865             catch (Exception e)
866             {
867                 throwTorqueException(e);
868             }
869         }
870         else
871         {
872             throw new TorqueException("No changes to save");
873         }
874     }
875 
876     /***
877      * Method to create an SQL query for display only based on values in a
878      * Criteria.
879      *
880      * @param criteria A Criteria.
881      * @return the SQL query for display
882      * @exception TorqueException Trouble creating the query string.
883      */
884     static String createQueryDisplayString(Criteria criteria)
885         throws TorqueException
886     {
887         return createQuery(criteria).toString();
888     }
889 
890     /***
891      * Method to create an SQL query for actual execution based on values in a
892      * Criteria.
893      *
894      * @param criteria A Criteria.
895      * @return the SQL query for actual execution
896      * @exception TorqueException Trouble creating the query string.
897      */
898     public static String createQueryString(Criteria criteria)
899         throws TorqueException
900     {
901         Query query = createQuery(criteria);
902         return query.toString();
903     }
904 
905     /***
906      * Method to create an SQL query based on values in a Criteria.  Note that
907      * final manipulation of the limit and offset are performed when the query
908      * is actually executed.
909      *
910      * @param criteria A Criteria.
911      * @return the sql query
912      * @exception TorqueException Trouble creating the query string.
913      */
914     static Query createQuery(Criteria criteria)
915         throws TorqueException
916     {
917         Query query = new Query();
918         DB db = Torque.getDB(criteria.getDbName());
919         DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
920 
921         UniqueList selectModifiers = query.getSelectModifiers();
922         UniqueList selectClause = query.getSelectClause();
923         UniqueList fromClause = query.getFromClause();
924         UniqueList whereClause = query.getWhereClause();
925         UniqueList orderByClause = query.getOrderByClause();
926         UniqueList groupByClause = query.getGroupByClause();
927 
928         UniqueList orderBy = criteria.getOrderByColumns();
929         UniqueList groupBy = criteria.getGroupByColumns();
930         UniqueList select = criteria.getSelectColumns();
931         Hashtable aliases = criteria.getAsColumns();
932         UniqueList modifiers = criteria.getSelectModifiers();
933 
934         for (int i = 0; i < modifiers.size(); i++)
935         {
936             selectModifiers.add(modifiers.get(i));
937         }
938 
939         for (int i = 0; i < select.size(); i++)
940         {
941             String columnName = (String) select.get(i);
942             if (columnName.indexOf('.') == -1  && columnName.indexOf('*') == -1)
943             {
944                 throwMalformedColumnNameException("select", columnName);
945             }
946             String tableName = null;
947             selectClause.add(columnName);
948             int parenPos = columnName.indexOf('(');
949             if (parenPos == -1)
950             {
951                 tableName = columnName.substring(0, columnName.indexOf('.'));
952             }
953             else if (columnName.indexOf('.') > -1)
954             {
955                 tableName =
956                     columnName.substring(parenPos + 1, columnName.indexOf('.'));
957                 // functions may contain qualifiers so only take the last
958                 // word as the table name.
959                 int lastSpace = tableName.lastIndexOf(' ');
960                 if (lastSpace != -1)
961                 {
962                     tableName = tableName.substring(lastSpace + 1);
963                 }
964             }
965             String tableName2 = criteria.getTableForAlias(tableName);
966             if (tableName2 != null)
967             {
968                 fromClause.add(new StringBuffer(
969                         tableName.length() + tableName2.length() + 1)
970                         .append(tableName2)
971                         .append(' ')
972                         .append(tableName)
973                         .toString());
974             }
975             else
976             {
977                 fromClause.add(tableName);
978             }
979         }
980 
981         Iterator it = aliases.keySet().iterator();
982         while (it.hasNext())
983         {
984             String key = (String) it.next();
985             selectClause.add((String) aliases.get(key) + " AS " + key);
986         }
987 
988         Iterator critKeys = criteria.keySet().iterator();
989         while (critKeys.hasNext())
990         {
991             String key = (String) critKeys.next();
992             Criteria.Criterion criterion = criteria.getCriterion(key);
993             Criteria.Criterion[] someCriteria =
994                 criterion.getAttachedCriterion();
995             String table = null;
996             for (int i = 0; i < someCriteria.length; i++)
997             {
998                 String tableName = someCriteria[i].getTable();
999                 table = criteria.getTableForAlias(tableName);
1000                 if (table != null)
1001                 {
1002                     fromClause.add(new StringBuffer(
1003                             tableName.length() + table.length() + 1)
1004                             .append(table)
1005                             .append(' ')
1006                             .append(tableName)
1007                             .toString());
1008                 }
1009                 else
1010                 {
1011                     fromClause.add(tableName);
1012                     table = tableName;
1013                 }
1014 
1015                 boolean ignorCase = ((criteria.isIgnoreCase()
1016                         || someCriteria[i].isIgnoreCase())
1017                         && (dbMap
1018                             .getTable(table)
1019                             .getColumn(someCriteria[i].getColumn())
1020                             .getType()
1021                             instanceof String));
1022 
1023                 someCriteria[i].setIgnoreCase(ignorCase);
1024             }
1025 
1026             criterion.setDB(db);
1027             whereClause.add(criterion.toString());
1028         }
1029 
1030         List join = criteria.getJoinL();
1031         if (join != null)
1032         {
1033             for (int i = 0; i < join.size(); i++)
1034             {
1035                 String join1 = (String) join.get(i);
1036                 String join2 = (String) criteria.getJoinR().get(i);
1037                 if (join1.indexOf('.') == -1)
1038                 {
1039                     throwMalformedColumnNameException("join", join1);
1040                 }
1041                 if (join2.indexOf('.') == -1)
1042                 {
1043                     throwMalformedColumnNameException("join", join2);
1044                 }
1045 
1046                 String tableName = join1.substring(0, join1.indexOf('.'));
1047                 String table = criteria.getTableForAlias(tableName);
1048                 if (table != null)
1049                 {
1050                     fromClause.add(new StringBuffer(
1051                             tableName.length() + table.length() + 1)
1052                             .append(table)
1053                             .append(' ')
1054                             .append(tableName)
1055                             .toString());
1056                 }
1057                 else
1058                 {
1059                     fromClause.add(tableName);
1060                 }
1061 
1062                 int dot = join2.indexOf('.');
1063                 tableName = join2.substring(0, dot);
1064                 table = criteria.getTableForAlias(tableName);
1065                 if (table != null)
1066                 {
1067                     fromClause.add(new StringBuffer(
1068                             tableName.length() + table.length() + 1)
1069                             .append(table)
1070                             .append(' ')
1071                             .append(tableName)
1072                             .toString());
1073                 }
1074                 else
1075                 {
1076                     fromClause.add(tableName);
1077                     table = tableName;
1078                 }
1079 
1080                 boolean ignorCase = (criteria.isIgnoreCase()
1081                         && (dbMap
1082                             .getTable(table)
1083                             .getColumn(join2.substring(dot + 1, join2.length()))
1084                             .getType()
1085                             instanceof String));
1086 
1087                 whereClause.add(
1088                     SqlExpression.buildInnerJoin(join1, join2, ignorCase, db));
1089             }
1090         }
1091 
1092         // need to allow for multiple group bys
1093         if (groupBy != null && groupBy.size() > 0)
1094         {
1095             for (int i = 0; i < groupBy.size(); i++)
1096             {
1097                 String groupByColumn = (String) groupBy.get(i);
1098                 if (groupByColumn.indexOf('.') == -1)
1099                 {
1100                     throwMalformedColumnNameException("group by",
1101                             groupByColumn);
1102                 }
1103                 groupByClause.add(groupByColumn);
1104             }
1105         }
1106 
1107         Criteria.Criterion having = criteria.getHaving();
1108         if (having != null)
1109         {
1110             //String groupByString = null;
1111             query.setHaving(having.toString());
1112         }
1113 
1114         if (orderBy != null && orderBy.size() > 0)
1115         {
1116             // Check for each String/Character column and apply
1117             // toUpperCase().
1118             for (int i = 0; i < orderBy.size(); i++)
1119             {
1120                 String orderByColumn = (String) orderBy.get(i);
1121                 if (orderByColumn.indexOf('.') == -1)
1122                 {
1123                     throwMalformedColumnNameException("order by",
1124                             orderByColumn);
1125                 }
1126                 String tableName =
1127                     orderByColumn.substring(0, orderByColumn.indexOf('.'));
1128                 String table = criteria.getTableForAlias(tableName);
1129                 if (table == null)
1130                 {
1131                     table = tableName;
1132                 }
1133 
1134                 // See if there's a space (between the column list and sort
1135                 // order in ORDER BY table.column DESC).
1136                 int spacePos = orderByColumn.indexOf(' ');
1137                 String columnName;
1138                 if (spacePos == -1)
1139                 {
1140                     columnName =
1141                         orderByColumn.substring(orderByColumn.indexOf('.') + 1);
1142                 }
1143                 else
1144                 {
1145                     columnName = orderByColumn.substring(
1146                             orderByColumn.indexOf('.') + 1, spacePos);
1147                 }
1148                 ColumnMap column = dbMap.getTable(table).getColumn(columnName);
1149                 if (column.getType() instanceof String)
1150                 {
1151                     if (spacePos == -1)
1152                     {
1153                         orderByClause.add(
1154                                 db.ignoreCaseInOrderBy(orderByColumn));
1155                     }
1156                     else
1157                     {
1158                         orderByClause.add(db.ignoreCaseInOrderBy(
1159                                 orderByColumn.substring(0, spacePos))
1160                                 + orderByColumn.substring(spacePos));
1161                     }
1162                     selectClause.add(
1163                         db.ignoreCaseInOrderBy(table + '.' + columnName));
1164                 }
1165                 else
1166                 {
1167                     orderByClause.add(orderByColumn);
1168                 }
1169             }
1170         }
1171 
1172         LimitHelper.buildLimit(criteria, query);
1173         if (log.isDebugEnabled())
1174         {
1175             log.debug(query.toString());
1176         }
1177         return query;
1178     }
1179 
1180     /***
1181      * Returns all results.
1182      *
1183      * @param criteria A Criteria.
1184      * @return List of Record objects.
1185      * @throws TorqueException Any exceptions caught during processing will be
1186      *         rethrown wrapped into a TorqueException.
1187      */
1188     public static List doSelect(Criteria criteria) throws TorqueException
1189     {
1190         Connection con = null;
1191         List results = null;
1192 
1193         try
1194         {
1195             con = Transaction.beginOptional(
1196                     criteria.getDbName(),
1197                     criteria.isUseTransaction());
1198             results = doSelect(criteria, con);
1199             Transaction.commit(con);
1200         }
1201         catch (Exception e)
1202         {
1203             Transaction.safeRollback(con);
1204             throwTorqueException(e);
1205         }
1206         return results;
1207     }
1208 
1209     /***
1210      * Returns all results.
1211      *
1212      * @param criteria A Criteria.
1213      * @param con A Connection.
1214      * @return List of Record objects.
1215      * @throws TorqueException Any exceptions caught during processing will be
1216      *         rethrown wrapped into a TorqueException.
1217      */
1218     public static List doSelect(Criteria criteria, Connection con)
1219         throws TorqueException
1220     {
1221         Query query = createQuery(criteria);
1222 
1223         if (query.hasLimit())
1224         {
1225             // We don't need Village to limit the Query
1226             return executeQuery(query.toString(),
1227                     0,
1228                     -1,
1229                     criteria.isSingleRecord(),
1230                     con);
1231         }
1232         else
1233         {
1234             // There is not limit string registered
1235             // with the query. Let Village decide.
1236             return executeQuery(query.toString(),
1237                     criteria.getOffset(),
1238                     criteria.getLimit(),
1239                     criteria.isSingleRecord(),
1240                     con);
1241         }
1242     }
1243 
1244     /***
1245      * Utility method which executes a given sql statement.  This
1246      * method should be used for select statements only.  Use
1247      * executeStatement for update, insert, and delete operations.
1248      *
1249      * @param queryString A String with the sql statement to execute.
1250      * @return List of Record objects.
1251      * @throws TorqueException Any exceptions caught during processing will be
1252      *         rethrown wrapped into a TorqueException.
1253      */
1254     public static List executeQuery(String queryString) throws TorqueException
1255     {
1256         return executeQuery(queryString, Torque.getDefaultDB(), false);
1257     }
1258 
1259     /***
1260      * Utility method which executes a given sql statement.  This
1261      * method should be used for select statements only.  Use
1262      * executeStatement for update, insert, and delete operations.
1263      *
1264      * @param queryString A String with the sql statement to execute.
1265      * @param dbName The database to connect to.
1266      * @return List of Record objects.
1267      * @throws TorqueException Any exceptions caught during processing will be
1268      *         rethrown wrapped into a TorqueException.
1269      */
1270     public static List executeQuery(String queryString, String dbName)
1271         throws TorqueException
1272     {
1273         return executeQuery(queryString, dbName, false);
1274     }
1275 
1276     /***
1277      * Method for performing a SELECT.  Returns all results.
1278      *
1279      * @param queryString A String with the sql statement to execute.
1280      * @param dbName The database to connect to.
1281      * @param singleRecord Whether or not we want to select only a
1282      * single record.
1283      * @return List of Record objects.
1284      * @throws TorqueException Any exceptions caught during processing will be
1285      *         rethrown wrapped into a TorqueException.
1286      */
1287     public static List executeQuery(
1288         String queryString,
1289         String dbName,
1290         boolean singleRecord)
1291         throws TorqueException
1292     {
1293         return executeQuery(queryString, 0, -1, dbName, singleRecord);
1294     }
1295 
1296     /***
1297      * Method for performing a SELECT.  Returns all results.
1298      *
1299      * @param queryString A String with the sql statement to execute.
1300      * @param singleRecord Whether or not we want to select only a
1301      * single record.
1302      * @param con A Connection.
1303      * @return List of Record objects.
1304      * @throws TorqueException Any exceptions caught during processing will be
1305      *         rethrown wrapped into a TorqueException.
1306      */
1307     public static List executeQuery(
1308         String queryString,
1309         boolean singleRecord,
1310         Connection con)
1311         throws TorqueException
1312     {
1313         return executeQuery(queryString, 0, -1, singleRecord, con);
1314     }
1315 
1316     /***
1317      * Method for performing a SELECT.
1318      *
1319      * @param queryString A String with the sql statement to execute.
1320      * @param start The first row to return.
1321      * @param numberOfResults The number of rows to return.
1322      * @param dbName The database to connect to.
1323      * @param singleRecord Whether or not we want to select only a
1324      * single record.
1325      * @return List of Record objects.
1326      * @throws TorqueException Any exceptions caught during processing will be
1327      *         rethrown wrapped into a TorqueException.
1328      */
1329     public static List executeQuery(
1330         String queryString,
1331         int start,
1332         int numberOfResults,
1333         String dbName,
1334         boolean singleRecord)
1335         throws TorqueException
1336     {
1337         Connection db = null;
1338         List results = null;
1339         try
1340         {
1341             db = Torque.getConnection(dbName);
1342             // execute the query
1343             results = executeQuery(
1344                     queryString,
1345                     start,
1346                     numberOfResults,
1347                     singleRecord,
1348                     db);
1349         }
1350         finally
1351         {
1352             Torque.closeConnection(db);
1353         }
1354         return results;
1355     }
1356 
1357     /***
1358      * Method for performing a SELECT.  Returns all results.
1359      *
1360      * @param queryString A String with the sql statement to execute.
1361      * @param start The first row to return.
1362      * @param numberOfResults The number of rows to return.
1363      * @param singleRecord Whether or not we want to select only a
1364      * single record.
1365      * @param con A Connection.
1366      * @return List of Record objects.
1367      * @throws TorqueException Any exceptions caught during processing will be
1368      *         rethrown wrapped into a TorqueException.
1369      */
1370     public static List executeQuery(
1371         String queryString,
1372         int start,
1373         int numberOfResults,
1374         boolean singleRecord,
1375         Connection con)
1376         throws TorqueException
1377     {
1378         QueryDataSet qds = null;
1379         List results = Collections.EMPTY_LIST;
1380         try
1381         {
1382             // execute the query
1383             long startTime = System.currentTimeMillis();
1384             qds = new QueryDataSet(con, queryString);
1385             if (log.isDebugEnabled())
1386             {
1387                 log.debug("Elapsed time=" 
1388                         + (System.currentTimeMillis() - startTime) + " ms");
1389             }
1390             results = getSelectResults(
1391                     qds, start, numberOfResults, singleRecord);
1392         }
1393         catch (Exception e)
1394         {
1395             throwTorqueException(e);
1396         }
1397         finally
1398         {
1399             if (qds != null)
1400             {
1401                 try
1402                 {
1403                     qds.close();
1404                 }
1405                 catch (Exception ignored)
1406                 {
1407                 }
1408             }
1409         }
1410         return results;
1411     }
1412 
1413     /***
1414      * Returns all records in a QueryDataSet as a List of Record
1415      * objects.  Used for functionality like util.LargeSelect.
1416      *
1417      * @see #getSelectResults(QueryDataSet, int, int, boolean)
1418      * @param qds the QueryDataSet
1419      * @return a List of Record objects
1420      * @throws TorqueException Any exceptions caught during processing will be
1421      *         rethrown wrapped into a TorqueException.
1422      */
1423     public static List getSelectResults(QueryDataSet qds)
1424         throws TorqueException
1425     {
1426         return getSelectResults(qds, 0, -1, false);
1427     }
1428 
1429     /***
1430      * Returns all records in a QueryDataSet as a List of Record
1431      * objects.  Used for functionality like util.LargeSelect.
1432      *
1433      * @see #getSelectResults(QueryDataSet, int, int, boolean)
1434      * @param qds the QueryDataSet
1435      * @param singleRecord
1436      * @return a List of Record objects
1437      * @throws TorqueException Any exceptions caught during processing will be
1438      *         rethrown wrapped into a TorqueException.
1439      */
1440     public static List getSelectResults(QueryDataSet qds, boolean singleRecord)
1441         throws TorqueException
1442     {
1443         return getSelectResults(qds, 0, -1, singleRecord);
1444     }
1445 
1446     /***
1447      * Returns numberOfResults records in a QueryDataSet as a List
1448      * of Record objects.  Starting at record 0.  Used for
1449      * functionality like util.LargeSelect.
1450      *
1451      * @see #getSelectResults(QueryDataSet, int, int, boolean)
1452      * @param qds the QueryDataSet
1453      * @param numberOfResults
1454      * @param singleRecord
1455      * @return a List of Record objects
1456      * @throws TorqueException Any exceptions caught during processing will be
1457      *         rethrown wrapped into a TorqueException.
1458      */
1459     public static List getSelectResults(
1460         QueryDataSet qds,
1461         int numberOfResults,
1462         boolean singleRecord)
1463         throws TorqueException
1464     {
1465         List results = null;
1466         if (numberOfResults != 0)
1467         {
1468             results = getSelectResults(qds, 0, numberOfResults, singleRecord);
1469         }
1470         return results;
1471     }
1472 
1473     /***
1474      * Returns numberOfResults records in a QueryDataSet as a List
1475      * of Record objects.  Starting at record start.  Used for
1476      * functionality like util.LargeSelect.
1477      *
1478      * @param qds The <code>QueryDataSet</code> to extract results
1479      * from.
1480      * @param start The index from which to start retrieving
1481      * <code>Record</code> objects from the data set.
1482      * @param numberOfResults The number of results to return (or
1483      * <code> -1</code> for all results).
1484      * @param singleRecord Whether or not we want to select only a
1485      * single record.
1486      * @return A <code>List</code> of <code>Record</code> objects.
1487      * @exception TorqueException If any <code>Exception</code> occurs.
1488      */
1489     public static List getSelectResults(
1490         QueryDataSet qds,
1491         int start,
1492         int numberOfResults,
1493         boolean singleRecord)
1494         throws TorqueException
1495     {
1496         List results = null;
1497         try
1498         {
1499             if (numberOfResults <= 0)
1500             {
1501                 results = new ArrayList();
1502                 qds.fetchRecords();
1503             }
1504             else
1505             {
1506                 results = new ArrayList(numberOfResults);
1507                 qds.fetchRecords(start, numberOfResults);
1508             }
1509             if (qds.size() > 1 && singleRecord)
1510             {
1511                 handleMultipleRecords(qds);
1512             }
1513 
1514             int startRecord = 0;
1515 
1516             //Offset the correct number of people
1517             if (start > 0 && numberOfResults <= 0)
1518             {
1519                 startRecord = start;
1520             }
1521 
1522             // Return a List of Record objects.
1523             for (int i = startRecord; i < qds.size(); i++)
1524             {
1525                 Record rec = qds.getRecord(i);
1526                 results.add(rec);
1527             }
1528         }
1529         catch (Exception e)
1530         {
1531             throwTorqueException(e);
1532         }
1533         return results;
1534     }
1535 
1536     /***
1537      * Helper method which returns the primary key contained
1538      * in the given Criteria object.
1539      *
1540      * @param criteria A Criteria.
1541      * @return ColumnMap if the Criteria object contains a primary
1542      *          key, or null if it doesn't.
1543      * @throws TorqueException Any exceptions caught during processing will be
1544      *         rethrown wrapped into a TorqueException.
1545      */
1546     private static ColumnMap getPrimaryKey(Criteria criteria)
1547         throws TorqueException
1548     {
1549         // Assume all the keys are for the same table.
1550         String key = (String) criteria.keys().nextElement();
1551 
1552         String table = criteria.getTableName(key);
1553         ColumnMap pk = null;
1554 
1555         if (!table.equals(""))
1556         {
1557             DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
1558             if (dbMap == null)
1559             {
1560                 throw new TorqueException("dbMap is null");
1561             }
1562             if (dbMap.getTable(table) == null)
1563             {
1564                 throw new TorqueException("dbMap.getTable() is null");
1565             }
1566 
1567             ColumnMap[] columns = dbMap.getTable(table).getColumns();
1568 
1569             for (int i = 0; i < columns.length; i++)
1570             {
1571                 if (columns[i].isPrimaryKey())
1572                 {
1573                     pk = columns[i];
1574                     break;
1575                 }
1576             }
1577         }
1578         return pk;
1579     }
1580 
1581     /***
1582      * Convenience method used to update rows in the DB.  Checks if a
1583      * <i>single</i> int primary key is specified in the Criteria
1584      * object and uses it to perform the udpate.  If no primary key is
1585      * specified an Exception will be thrown.
1586      * <p>
1587      * Use this method for performing an update of the kind:
1588      * <p>
1589      * "WHERE primary_key_id = an int"
1590      * <p>
1591      * To perform an update with non-primary key fields in the WHERE
1592      * clause use doUpdate(criteria, criteria).
1593      *
1594      * @param updateValues A Criteria object containing values used in
1595      *        set clause.
1596      * @throws TorqueException Any exceptions caught during processing will be
1597      *         rethrown wrapped into a TorqueException.
1598      */
1599     public static void doUpdate(Criteria updateValues) throws TorqueException
1600     {
1601         Connection con = null;
1602         try
1603         {
1604             con = Transaction.beginOptional(
1605                     updateValues.getDbName(),
1606                     updateValues.isUseTransaction());
1607             doUpdate(updateValues, con);
1608             Transaction.commit(con);
1609         }
1610         catch (TorqueException e)
1611         {
1612             Transaction.safeRollback(con);
1613             throw e;
1614         }
1615     }
1616 
1617     /***
1618      * Convenience method used to update rows in the DB.  Checks if a
1619      * <i>single</i> int primary key is specified in the Criteria
1620      * object and uses it to perform the udpate.  If no primary key is
1621      * specified an Exception will be thrown.
1622      * <p>
1623      * Use this method for performing an update of the kind:
1624      * <p>
1625      * "WHERE primary_key_id = an int"
1626      * <p>
1627      * To perform an update with non-primary key fields in the WHERE
1628      * clause use doUpdate(criteria, criteria).
1629      *
1630      * @param updateValues A Criteria object containing values used in
1631      * set clause.
1632      * @param con A Connection.
1633      * @throws TorqueException Any exceptions caught during processing will be
1634      *         rethrown wrapped into a TorqueException.
1635      */
1636     public static void doUpdate(Criteria updateValues, Connection con)
1637         throws TorqueException
1638     {
1639         ColumnMap pk = getPrimaryKey(updateValues);
1640         Criteria selectCriteria = null;
1641 
1642         if (pk != null && updateValues.containsKey(pk.getFullyQualifiedName()))
1643         {
1644             selectCriteria = new Criteria(2);
1645             selectCriteria.put(pk.getFullyQualifiedName(),
1646                 updateValues.remove(pk.getFullyQualifiedName()));
1647         }
1648         else
1649         {
1650             throw new TorqueException("No PK specified for database update");
1651         }
1652 
1653         doUpdate(selectCriteria, updateValues, con);
1654     }
1655 
1656     /***
1657      * Method used to update rows in the DB.  Rows are selected based
1658      * on selectCriteria and updated using values in updateValues.
1659      * <p>
1660      * Use this method for performing an update of the kind:
1661      * <p>
1662      * WHERE some_column = some value AND could_have_another_column =
1663      * another value AND so on...
1664      *
1665      * @param selectCriteria A Criteria object containing values used in where
1666      *        clause.
1667      * @param updateValues A Criteria object containing values used in set
1668      *        clause.
1669      * @throws TorqueException Any exceptions caught during processing will be
1670      *         rethrown wrapped into a TorqueException.
1671      */
1672     public static void doUpdate(Criteria selectCriteria, Criteria updateValues)
1673         throws TorqueException
1674     {
1675         Connection con = null;
1676         try
1677         {
1678             con = Transaction.beginOptional(selectCriteria.getDbName(),
1679                                             updateValues.isUseTransaction());
1680             doUpdate(selectCriteria, updateValues, con);
1681             Transaction.commit(con);
1682         }
1683         catch (TorqueException e)
1684         {
1685             Transaction.safeRollback(con);
1686             throw e;
1687         }
1688     }
1689 
1690     /***
1691      * Method used to update rows in the DB.  Rows are selected based
1692      * on selectCriteria and updated using values in updateValues.
1693      * <p>
1694      * Use this method for performing an update of the kind:
1695      * <p>
1696      * WHERE some_column = some value AND could_have_another_column =
1697      * another value AND so on.
1698      *
1699      * @param selectCriteria A Criteria object containing values used in where
1700      *        clause.
1701      * @param updateValues A Criteria object containing values used in set
1702      *        clause.
1703      * @param con A Connection.
1704      * @throws TorqueException Any exceptions caught during processing will be
1705      *         rethrown wrapped into a TorqueException.
1706      */
1707     public static void doUpdate(
1708         Criteria selectCriteria,
1709         Criteria updateValues,
1710         Connection con)
1711         throws TorqueException
1712     {
1713         DB db = Torque.getDB(selectCriteria.getDbName());
1714         DatabaseMap dbMap = Torque.getDatabaseMap(selectCriteria.getDbName());
1715 
1716         // Set up a list of required tables. StringStack.add()
1717         // only adds element if it is unique.
1718         HashSet tables = new HashSet();
1719         Iterator it = selectCriteria.keySet().iterator();
1720         while (it.hasNext())
1721         {
1722             tables.add(selectCriteria.getTableName((String) it.next()));
1723         }
1724 
1725         Iterator tabIt = tables.iterator();
1726         while (tabIt.hasNext())
1727         {
1728             String tab = (String) tabIt.next();
1729             KeyDef kd = new KeyDef();
1730             HashSet whereClause = new HashSet();
1731             DatabaseMap tempDbMap = dbMap;
1732 
1733             ColumnMap[] columnMaps = tempDbMap.getTable(tab).getColumns();
1734             for (int j = 0; j < columnMaps.length; j++)
1735             {
1736                 ColumnMap colMap = columnMaps[j];
1737                 if (colMap.isPrimaryKey())
1738                 {
1739                     kd.addAttrib(colMap.getColumnName());
1740                 }
1741                 String key = new StringBuffer(colMap.getTableName())
1742                         .append('.')
1743                         .append(colMap.getColumnName())
1744                         .toString();
1745                 if (selectCriteria.containsKey(key))
1746                 {
1747                     if (selectCriteria
1748                         .getComparison(key)
1749                         .equals(Criteria.CUSTOM))
1750                     {
1751                         whereClause.add(selectCriteria.getString(key));
1752                     }
1753                     else
1754                     {
1755                         whereClause.add(
1756                             SqlExpression.build(
1757                                 colMap.getColumnName(),
1758                                 selectCriteria.getValue(key),
1759                                 selectCriteria.getComparison(key),
1760                                 selectCriteria.isIgnoreCase(),
1761                                 db));
1762                     }
1763                 }
1764             }
1765             TableDataSet tds = null;
1766             try
1767             {
1768                 // Get affected records.
1769                 tds = new TableDataSet(con, tab, kd);
1770                 String sqlSnippet = StringUtils.join(whereClause.iterator(), " AND ");
1771                 if (log.isDebugEnabled())
1772                 {
1773                     log.debug("BasePeer.doUpdate: whereClause=" + sqlSnippet);
1774                 }
1775                 tds.where(sqlSnippet);
1776                 tds.fetchRecords();
1777 
1778                 if (tds.size() > 1 && selectCriteria.isSingleRecord())
1779                 {
1780                     handleMultipleRecords(tds);
1781                 }
1782                 for (int j = 0; j < tds.size(); j++)
1783                 {
1784                     Record rec = tds.getRecord(j);
1785                     BasePeer.insertOrUpdateRecord(rec, tab, updateValues);
1786                 }
1787             }
1788             catch (Exception e)
1789             {
1790                 throwTorqueException(e);
1791             }
1792             finally
1793             {
1794                 if (tds != null)
1795                 {
1796                     try
1797                     {
1798                         tds.close();
1799                     }
1800                     catch (Exception e)
1801                     {
1802                         throwTorqueException(e);
1803                     }
1804                 }
1805             }
1806         }
1807     }
1808 
1809     /***
1810      * Utility method which executes a given sql statement.  This
1811      * method should be used for update, insert, and delete
1812      * statements.  Use executeQuery() for selects.
1813      *
1814      * @param stmt A String with the sql statement to execute.
1815      * @return The number of rows affected.
1816      * @throws TorqueException Any exceptions caught during processing will be
1817      *         rethrown wrapped into a TorqueException.
1818      */
1819     public static int executeStatement(String stmt) throws TorqueException
1820     {
1821         return executeStatement(stmt, Torque.getDefaultDB());
1822     }
1823 
1824     /***
1825      * Utility method which executes a given sql statement.  This
1826      * method should be used for update, insert, and delete
1827      * statements.  Use executeQuery() for selects.
1828      *
1829      * @param stmt A String with the sql statement to execute.
1830      * @param dbName Name of database to connect to.
1831      * @return The number of rows affected.
1832      * @throws TorqueException Any exceptions caught during processing will be
1833      *         rethrown wrapped into a TorqueException.
1834      */
1835     public static int executeStatement(String stmt, String dbName)
1836         throws TorqueException
1837     {
1838         Connection db = null;
1839         int rowCount = -1;
1840         try
1841         {
1842             db = Torque.getConnection(dbName);
1843             rowCount = executeStatement(stmt, db);
1844         }
1845         finally
1846         {
1847             Torque.closeConnection(db);
1848         }
1849         return rowCount;
1850     }
1851 
1852     /***
1853      * Utility method which executes a given sql statement.  This
1854      * method should be used for update, insert, and delete
1855      * statements.  Use executeQuery() for selects.
1856      *
1857      * @param stmt A String with the sql statement to execute.
1858      * @param con A Connection.
1859      * @return The number of rows affected.
1860      * @throws TorqueException Any exceptions caught during processing will be
1861      *         rethrown wrapped into a TorqueException.
1862      */
1863     public static int executeStatement(String stmt, Connection con)
1864         throws TorqueException
1865     {
1866         int rowCount = -1;
1867         Statement statement = null;
1868         try
1869         {
1870             statement = con.createStatement();
1871             rowCount = statement.executeUpdate(stmt);
1872         }
1873         catch (SQLException e)
1874         {
1875             throw new TorqueException(e);
1876         }
1877         finally
1878         {
1879             if (statement != null)
1880             {
1881                 try
1882                 {
1883                     statement.close();
1884                 }
1885                 catch (SQLException e)
1886                 {
1887                     throw new TorqueException(e);
1888                 }
1889             }
1890         }
1891         return rowCount;
1892     }
1893 
1894     /***
1895      * If the user specified that (s)he only wants to retrieve a
1896      * single record and multiple records are retrieved, this method
1897      * is called to handle the situation.  The default behavior is to
1898      * throw an exception, but subclasses can override this method as
1899      * needed.
1900      *
1901      * @param ds The DataSet which contains multiple records.
1902      * @exception TorqueException Couldn't handle multiple records.
1903      */
1904     protected static void handleMultipleRecords(DataSet ds)
1905         throws TorqueException
1906     {
1907         throw new TorqueException("Criteria expected single Record and "
1908                 + "Multiple Records were selected");
1909     }
1910 
1911     /***
1912      * This method returns the MapBuilder specified in the
1913      * configuration file. By default, this is
1914      * org.apache.torque.util.db.map.TurbineMapBuilder.
1915      * FIXME! With the decoupled Torque there seem to be no
1916      * default map builder anymore.
1917      *
1918      * @return A MapBuilder.
1919      * @throws TorqueException Any exceptions caught during processing will be
1920      *         rethrown wrapped into a TorqueException.
1921      * @deprecated you have to specify the name of the map builder!
1922      */
1923     public static MapBuilder getMapBuilder() throws TorqueException
1924     {
1925         return getMapBuilder(DEFAULT_MAP_BUILDER.trim());
1926     }
1927 
1928     /***
1929      * This method returns the MapBuilder specified in the name
1930      * parameter.  You should pass in the full path to the class, ie:
1931      * org.apache.torque.util.db.map.TurbineMapBuilder.  The
1932      * MapBuilder instances are cached in this class for speed.
1933      *
1934      * @param name name of the MapBuilder
1935      * @return A MapBuilder, or null (and logs the error) if the
1936      * MapBuilder was not found.
1937      */
1938     public static MapBuilder getMapBuilder(String name)
1939     {
1940         try
1941         {
1942             MapBuilder mb = (MapBuilder) mapBuilders.get(name);
1943             // Use the 'double-check pattern' for syncing
1944             //  caching of the MapBuilder.
1945             if (mb == null)
1946             {
1947                 synchronized (mapBuilders)
1948                 {
1949                     mb = (MapBuilder) mapBuilders.get(name);
1950                     if (mb == null)
1951                     {
1952                         mb = (MapBuilder) Class.forName(name).newInstance();
1953                         // Cache the MapBuilder before it is built.
1954                         mapBuilders.put(name, mb);
1955                     }
1956                 }
1957             }
1958 
1959             // Build the MapBuilder in its own synchronized block to
1960             //  avoid locking up the whole Hashtable while doing so.
1961             // Note that *all* threads need to do a sync check on isBuilt()
1962             //  to avoid grabing an uninitialized MapBuilder. This, however,
1963             //  is a relatively fast operation.
1964             synchronized (mb)
1965             {
1966                 if (!mb.isBuilt())
1967                 {
1968                     try
1969                     {
1970                         mb.doBuild();
1971                     }
1972                     catch (Exception e)
1973                     {
1974                         // need to think about whether we'd want to remove
1975                         //  the MapBuilder from the cache if it can't be
1976                         //  built correctly...?  pgo
1977                         throw e;
1978                     }
1979                 }
1980             }
1981             return mb;
1982         }
1983         catch (Exception e)
1984         {
1985             // Have to catch possible exceptions because method is
1986             // used in initialization of Peers.  Log the exception and
1987             // return null.
1988             String message =
1989                 "BasePeer.MapBuilder failed trying to instantiate: " + name;
1990             log.error(message, e);
1991         }
1992         return null;
1993     }
1994 
1995     /***
1996      * Performs a SQL <code>select</code> using a PreparedStatement.
1997      * Note: this method does not handle null criteria values.
1998      *
1999      * @param criteria
2000      * @param con
2001      * @return
2002      * @throws TorqueException Error performing database query.
2003      */
2004     public static List doPSSelect(Criteria criteria, Connection con)
2005         throws TorqueException
2006     {
2007         List v = null;
2008 
2009         StringBuffer qry = new StringBuffer();
2010         List params = new ArrayList(criteria.size());
2011 
2012         createPreparedStatement(criteria, qry, params);
2013 
2014         PreparedStatement stmt = null;
2015         try
2016         {
2017             stmt = con.prepareStatement(qry.toString());
2018 
2019             for (int i = 0; i < params.size(); i++)
2020             {
2021                 Object param = params.get(i);
2022                 if (param instanceof java.sql.Date)
2023                 {
2024                     stmt.setDate(i + 1, (java.sql.Date) param);
2025                 }
2026                 else if (param instanceof NumberKey)
2027                 {
2028                     stmt.setBigDecimal(i + 1,
2029                         ((NumberKey) param).getBigDecimal());
2030                 }
2031                 else
2032                 {
2033                     stmt.setString(i + 1, param.toString());
2034                 }
2035             }
2036 
2037             QueryDataSet qds = null;
2038             try
2039             {
2040                 qds = new QueryDataSet(stmt.executeQuery());
2041                 v = getSelectResults(qds);
2042             }
2043             finally
2044             {
2045                 if (qds != null)
2046                 {
2047                     qds.close();
2048                 }
2049             }
2050         }
2051         catch (Exception e)
2052         {
2053             throwTorqueException(e);
2054         }
2055         finally
2056         {
2057             if (stmt != null)
2058             {
2059                 try
2060                 {
2061                     stmt.close();
2062                 }
2063                 catch (SQLException e)
2064                 {
2065                     throw new TorqueException(e);
2066                 }
2067             }
2068         }
2069         return v;
2070     }
2071 
2072     /***
2073      * Do a Prepared Statement select according to the given criteria
2074      *
2075      * @param criteria
2076      * @return
2077      * @throws TorqueException Any exceptions caught during processing will be
2078      *         rethrown wrapped into a TorqueException.
2079      */
2080     public static List doPSSelect(Criteria criteria) throws TorqueException
2081     {
2082         Connection con = Torque.getConnection(criteria.getDbName());
2083         List v = null;
2084 
2085         try
2086         {
2087             v = doPSSelect(criteria, con);
2088         }
2089         finally
2090         {
2091             Torque.closeConnection(con);
2092         }
2093         return v;
2094     }
2095 
2096     /***
2097      * Create a new PreparedStatement.  It builds a string representation
2098      * of a query and a list of PreparedStatement parameters.
2099      *
2100      * @param criteria
2101      * @param queryString
2102      * @param params
2103      * @throws TorqueException Any exceptions caught during processing will be
2104      *         rethrown wrapped into a TorqueException.
2105      */
2106     public static void createPreparedStatement(
2107         Criteria criteria,
2108         StringBuffer queryString,
2109         List params)
2110         throws TorqueException
2111     {
2112         DB db = Torque.getDB(criteria.getDbName());
2113         DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
2114 
2115         Query query = new Query();
2116 
2117         UniqueList selectModifiers = query.getSelectModifiers();
2118         UniqueList selectClause = query.getSelectClause();
2119         UniqueList fromClause = query.getFromClause();
2120         UniqueList whereClause = query.getWhereClause();
2121         UniqueList orderByClause = query.getOrderByClause();
2122 
2123         UniqueList orderBy = criteria.getOrderByColumns();
2124         UniqueList select = criteria.getSelectColumns();
2125         Hashtable aliases = criteria.getAsColumns();
2126         UniqueList modifiers = criteria.getSelectModifiers();
2127 
2128         for (int i = 0; i < modifiers.size(); i++)
2129         {
2130             selectModifiers.add(modifiers.get(i));
2131         }
2132 
2133         for (int i = 0; i < select.size(); i++)
2134         {
2135             String columnName = (String) select.get(i);
2136             if (columnName.indexOf('.') == -1)
2137             {
2138                 throwMalformedColumnNameException("select", columnName);
2139             }
2140             String tableName = null;
2141             selectClause.add(columnName);
2142             int parenPos = columnName.indexOf('(');
2143             if (parenPos == -1)
2144             {
2145                 tableName = columnName.substring(0, columnName.indexOf('.'));
2146             }
2147             else
2148             {
2149                 tableName =
2150                     columnName.substring(parenPos + 1, columnName.indexOf('.'));
2151                 // functions may contain qualifiers so only take the last
2152                 // word as the table name.
2153                 int lastSpace = tableName.lastIndexOf(' ');
2154                 if (lastSpace != -1)
2155                 {
2156                     tableName = tableName.substring(lastSpace + 1);
2157                 }
2158             }
2159             String tableName2 = criteria.getTableForAlias(tableName);
2160             if (tableName2 != null)
2161             {
2162                 fromClause.add(new StringBuffer(tableName.length()
2163                         + tableName2.length() + 1)
2164                         .append(tableName2)
2165                         .append(' ')
2166                         .append(tableName)
2167                         .toString());
2168             }
2169             else
2170             {
2171                 fromClause.add(tableName);
2172             }
2173         }
2174 
2175         Iterator it = aliases.keySet().iterator();
2176         while (it.hasNext())
2177         {
2178             String key = (String) it.next();
2179             selectClause.add((String) aliases.get(key) + " AS " + key);
2180         }
2181 
2182         Iterator critKeys = criteria.keySet().iterator();
2183         while (critKeys.hasNext())
2184         {
2185             String key = (String) critKeys.next();
2186             Criteria.Criterion criterion = criteria.getCriterion(key);
2187             Criteria.Criterion[] someCriteria =
2188                 criterion.getAttachedCriterion();
2189 
2190             String table = null;
2191             for (int i = 0; i < someCriteria.length; i++)
2192             {
2193                 String tableName = someCriteria[i].getTable();
2194                 table = criteria.getTableForAlias(tableName);
2195                 if (table != null)
2196                 {
2197                     fromClause.add(new StringBuffer(tableName.length()
2198                             + table.length() + 1)
2199                             .append(table)
2200                             .append(' ')
2201                             .append(tableName)
2202                             .toString());
2203                 }
2204                 else
2205                 {
2206                     fromClause.add(tableName);
2207                     table = tableName;
2208                 }
2209 
2210                 boolean ignorCase = ((criteria.isIgnoreCase()
2211                         || someCriteria[i].isIgnoreCase())
2212                         && (dbMap
2213                             .getTable(table)
2214                             .getColumn(someCriteria[i].getColumn())
2215                             .getType()
2216                             instanceof String));
2217 
2218                 someCriteria[i].setIgnoreCase(ignorCase);
2219             }
2220 
2221             criterion.setDB(db);
2222             StringBuffer sb = new StringBuffer();
2223             criterion.appendPsTo(sb, params);
2224             whereClause.add(sb.toString());
2225         }
2226 
2227         List join = criteria.getJoinL();
2228         if (join != null)
2229         {
2230             for (int i = 0; i < join.size(); i++)
2231             {
2232                 String join1 = (String) join.get(i);
2233                 String join2 = (String) criteria.getJoinR().get(i);
2234                 if (join1.indexOf('.') == -1)
2235                 {
2236                     throwMalformedColumnNameException("join", join1);
2237                 }
2238                 if (join2.indexOf('.') == -1)
2239                 {
2240                     throwMalformedColumnNameException("join", join2);
2241                 }
2242 
2243                 String tableName = join1.substring(0, join1.indexOf('.'));
2244                 String table = criteria.getTableForAlias(tableName);
2245                 if (table != null)
2246                 {
2247                     fromClause.add(new StringBuffer(tableName.length()
2248                             + table.length() + 1)
2249                             .append(table)
2250                             .append(' ')
2251                             .append(tableName)
2252                             .toString());
2253                 }
2254                 else
2255                 {
2256                     fromClause.add(tableName);
2257                 }
2258 
2259                 int dot = join2.indexOf('.');
2260                 tableName = join2.substring(0, dot);
2261                 table = criteria.getTableForAlias(tableName);
2262                 if (table != null)
2263                 {
2264                     fromClause.add(new StringBuffer(tableName.length()
2265                             + table.length() + 1)
2266                             .append(table)
2267                             .append(' ')
2268                             .append(tableName)
2269                             .toString());
2270                 }
2271                 else
2272                 {
2273                     fromClause.add(tableName);
2274                     table = tableName;
2275                 }
2276 
2277                 boolean ignorCase = (criteria.isIgnoreCase()
2278                         && (dbMap
2279                             .getTable(table)
2280                             .getColumn(join2.substring(dot + 1, join2.length()))
2281                             .getType()
2282                             instanceof String));
2283 
2284                 whereClause.add(
2285                     SqlExpression.buildInnerJoin(join1, join2, ignorCase, db));
2286             }
2287         }
2288 
2289         if (orderBy != null && orderBy.size() > 0)
2290         {
2291             // Check for each String/Character column and apply
2292             // toUpperCase().
2293             for (int i = 0; i < orderBy.size(); i++)
2294             {
2295                 String orderByColumn = (String) orderBy.get(i);
2296                 if (orderByColumn.indexOf('.') == -1)
2297                 {
2298                     throwMalformedColumnNameException("order by",
2299                         orderByColumn);
2300                 }
2301                 String table =
2302                     orderByColumn.substring(0, orderByColumn.indexOf('.'));
2303                 // See if there's a space (between the column list and sort
2304                 // order in ORDER BY table.column DESC).
2305                 int spacePos = orderByColumn.indexOf(' ');
2306                 String columnName;
2307                 if (spacePos == -1)
2308                 {
2309                     columnName =
2310                         orderByColumn.substring(orderByColumn.indexOf('.') + 1);
2311                 }
2312                 else
2313                 {
2314                     columnName = orderByColumn.substring(
2315                             orderByColumn.indexOf('.') + 1,
2316                             spacePos);
2317                 }
2318                 ColumnMap column = dbMap.getTable(table).getColumn(columnName);
2319                 if (column.getType() instanceof String)
2320                 {
2321                     if (spacePos == -1)
2322                     {
2323                         orderByClause.add(
2324                             db.ignoreCaseInOrderBy(orderByColumn));
2325                     }
2326                     else
2327                     {
2328                         orderByClause.add(db.ignoreCaseInOrderBy(
2329                                 orderByColumn.substring(0, spacePos))
2330                                 + orderByColumn.substring(spacePos));
2331                     }
2332                     selectClause.add(
2333                         db.ignoreCaseInOrderBy(table + '.' + columnName));
2334                 }
2335                 else
2336                 {
2337                     orderByClause.add(orderByColumn);
2338                 }
2339             }
2340         }
2341 
2342         LimitHelper.buildLimit(criteria, query);
2343         String sql = query.toString();
2344         log.debug(sql);
2345         queryString.append(sql);
2346     }
2347 
2348     /***
2349      * Throws a TorqueException with the malformed column name error
2350      * message.  The error message looks like this:<p>
2351      *
2352      * <code>
2353      *     Malformed column name in Criteria [criteriaPhrase]:
2354      *     '[columnName]' is not of the form 'table.column'
2355      * </code>
2356      *
2357      * @param criteriaPhrase a String, one of "select", "join", or "order by"
2358      * @param columnName a String containing the offending column name
2359      * @throws TorqueException Any exceptions caught during processing will be
2360      *         rethrown wrapped into a TorqueException.
2361      */
2362     private static void throwMalformedColumnNameException(
2363         String criteriaPhrase,
2364         String columnName)
2365         throws TorqueException
2366     {
2367         throw new TorqueException("Malformed column name in Criteria "
2368                 + criteriaPhrase
2369                 + ": '"
2370                 + columnName
2371                 + "' is not of the form 'table.column'");
2372     }
2373 }