View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2005 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.Serializable;
20  import java.sql.Connection;
21  import java.sql.PreparedStatement;
22  import java.sql.SQLException;
23  import java.sql.Statement;
24  import java.util.ArrayList;
25  import java.util.Collections;
26  import java.util.HashSet;
27  import java.util.Hashtable;
28  import java.util.Iterator;
29  import java.util.List;
30  import java.util.Set;
31  
32  import org.apache.commons.lang.StringUtils;
33  import org.apache.commons.logging.Log;
34  import org.apache.commons.logging.LogFactory;
35  import org.apache.torque.Torque;
36  import org.apache.torque.TorqueException;
37  import org.apache.torque.adapter.DB;
38  import org.apache.torque.map.ColumnMap;
39  import org.apache.torque.map.DatabaseMap;
40  import org.apache.torque.map.MapBuilder;
41  import org.apache.torque.map.TableMap;
42  import org.apache.torque.oid.IdGenerator;
43  import org.apache.torque.om.NumberKey;
44  import org.apache.torque.om.ObjectKey;
45  import org.apache.torque.om.SimpleKey;
46  import org.apache.torque.om.StringKey;
47  
48  import com.workingdogs.village.Column;
49  import com.workingdogs.village.DataSet;
50  import com.workingdogs.village.KeyDef;
51  import com.workingdogs.village.QueryDataSet;
52  import com.workingdogs.village.Record;
53  import com.workingdogs.village.Schema;
54  import com.workingdogs.village.TableDataSet;
55  
56  /***
57   * This is the base class for all Peer classes in the system.  Peer
58   * classes are responsible for isolating all of the database access
59   * for a specific business object.  They execute all of the SQL
60   * against the database.  Over time this class has grown to include
61   * utility methods which ease execution of cross-database queries and
62   * the implementation of concrete Peers.
63   *
64   * @author <a href="mailto:frank.kim@clearink.com">Frank Y. Kim</a>
65   * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
66   * @author <a href="mailto:bmclaugh@algx.net">Brett McLaughlin</a>
67   * @author <a href="mailto:stephenh@chase3000.com">Stephen Haberman</a>
68   * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
69   * @author <a href="mailto:vido@ldh.org">Augustin Vidovic</a>
70   * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
71   * @version $Id: BasePeer.java 239636 2005-08-24 12:38:09Z henning $
72   */
73  public abstract class BasePeer
74          implements Serializable
75  {
76      /*** Constant criteria key to reference ORDER BY columns. */
77      public static final String ORDER_BY = "ORDER BY";
78  
79      /***
80       * Constant criteria key to remove Case Information from
81       * search/ordering criteria.
82       */
83      public static final String IGNORE_CASE = "IgNOrE cAsE";
84  
85      /*** Classes that implement this class should override this value. */
86      public static final String TABLE_NAME = "TABLE_NAME";
87  
88      /*** Hashtable that contains the cached mapBuilders. */
89      private static Hashtable mapBuilders = new Hashtable(5);
90  
91      /*** the log */
92      protected static Log log = LogFactory.getLog(BasePeer.class);
93  
94      private static void throwTorqueException(Exception e)
95          throws TorqueException
96      {
97          if (e instanceof TorqueException)
98          {
99              throw (TorqueException) e;
100         }
101         else
102         {
103             throw new TorqueException(e);
104         }
105     }
106 
107     /***
108      * Sets up a Schema for a table.  This schema is then normally
109      * used as the argument for initTableColumns().
110      *
111      * @param tableName The name of the table.
112      * @return A Schema.
113      */
114     public static Schema initTableSchema(String tableName)
115     {
116         return initTableSchema(tableName, Torque.getDefaultDB());
117     }
118 
119     /***
120      * Sets up a Schema for a table.  This schema is then normally
121      * used as the argument for initTableColumns
122      *
123      * @param tableName The propery name for the database in the
124      * configuration file.
125      * @param dbName The name of the database.
126      * @return A Schema.
127      */
128     public static Schema initTableSchema(String tableName, String dbName)
129     {
130         Schema schema = null;
131         Connection con = null;
132 
133         try
134         {
135             con = Torque.getConnection(dbName);
136             schema = new Schema().schema(con, tableName);
137         }
138         catch (Exception e)
139         {
140             log.error(e);
141             throw new Error("Error in BasePeer.initTableSchema("
142                     + tableName
143                     + "): "
144                     + e.getMessage());
145         }
146         finally
147         {
148             Torque.closeConnection(con);
149         }
150         return schema;
151     }
152 
153     /***
154      * Creates a Column array for a table based on its Schema.
155      *
156      * @param schema A Schema object.
157      * @return A Column[].
158      */
159     public static Column[] initTableColumns(Schema schema)
160     {
161         Column[] columns = null;
162         try
163         {
164             int numberOfColumns = schema.numberOfColumns();
165             columns = new Column[numberOfColumns];
166             for (int i = 0; i < numberOfColumns; i++)
167             {
168                 columns[i] = schema.column(i + 1);
169             }
170         }
171         catch (Exception e)
172         {
173             log.error(e);
174             throw new Error(
175                 "Error in BasePeer.initTableColumns(): " + e.getMessage());
176         }
177         return columns;
178     }
179 
180     /***
181      * Convenience method to create a String array of column names.
182      *
183      * @param columns A Column[].
184      * @return A String[].
185      */
186     public static String[] initColumnNames(Column[] columns)
187     {
188         String[] columnNames = null;
189         columnNames = new String[columns.length];
190         for (int i = 0; i < columns.length; i++)
191         {
192             columnNames[i] = columns[i].name().toUpperCase();
193         }
194         return columnNames;
195     }
196 
197     /***
198      * Convenience method to create a String array of criteria keys.
199      *
200      * @param tableName Name of table.
201      * @param columnNames A String[].
202      * @return A String[].
203      */
204     public static String[] initCriteriaKeys(
205         String tableName,
206         String[] columnNames)
207     {
208         String[] keys = new String[columnNames.length];
209         for (int i = 0; i < columnNames.length; i++)
210         {
211             keys[i] = tableName + "." + columnNames[i].toUpperCase();
212         }
213         return keys;
214     }
215 
216     /***
217      * Convenience method that uses straight JDBC to delete multiple
218      * rows.  Village throws an Exception when multiple rows are
219      * deleted.
220      *
221      * @param con A Connection.
222      * @param table The table to delete records from.
223      * @param column The column in the where clause.
224      * @param value The value of the column.
225      * @throws TorqueException Any exceptions caught during processing will be
226      *         rethrown wrapped into a TorqueException.
227      */
228     public static void deleteAll(
229         Connection con,
230         String table,
231         String column,
232         int value)
233         throws TorqueException
234     {
235         Statement statement = null;
236         try
237         {
238             statement = con.createStatement();
239 
240             StringBuffer query = new StringBuffer();
241             query.append("DELETE FROM ")
242                 .append(table)
243                 .append(" WHERE ")
244                 .append(column)
245                 .append(" = ")
246                 .append(value);
247 
248             statement.executeUpdate(query.toString());
249         }
250         catch (SQLException e)
251         {
252             throw new TorqueException(e);
253         }
254         finally
255         {
256             if (statement != null)
257             {
258                 try
259                 {
260                     statement.close();
261                 }
262                 catch (SQLException ignored)
263                 {
264                 }
265             }
266         }
267     }
268 
269     /***
270      * Convenience method that uses straight JDBC to delete multiple
271      * rows.  Village throws an Exception when multiple rows are
272      * deleted.  This method attempts to get the default database from
273      * the pool.
274      *
275      * @param table The table to delete records from.
276      * @param column The column in the where clause.
277      * @param value The value of the column.
278      * @throws TorqueException Any exceptions caught during processing will be
279      *         rethrown wrapped into a TorqueException.
280      */
281     public static void deleteAll(String table, String column, int value)
282         throws TorqueException
283     {
284         Connection con = null;
285         try
286         {
287             // Get a connection to the db.
288             con = Torque.getConnection(Torque.getDefaultDB());
289             deleteAll(con, table, column, value);
290         }
291         finally
292         {
293             Torque.closeConnection(con);
294         }
295     }
296 
297     /***
298      * Method to perform deletes based on values and keys in a
299      * Criteria.
300      *
301      * @param criteria The criteria to use.
302      * @throws TorqueException Any exceptions caught during processing will be
303      *         rethrown wrapped into a TorqueException.
304      */
305     public static void doDelete(Criteria criteria) throws TorqueException
306     {
307         Connection con = null;
308         try
309         {
310             con = Transaction.beginOptional(
311                     criteria.getDbName(),
312                     criteria.isUseTransaction());
313             doDelete(criteria, con);
314             Transaction.commit(con);
315         }
316         catch (TorqueException e)
317         {
318             Transaction.safeRollback(con);
319             throw e;
320         }
321     }
322 
323     /***
324      * Method to perform deletes based on values and keys in a Criteria.
325      *
326      * @param criteria The criteria to use.
327      * @param con A Connection.
328      * @throws TorqueException Any exceptions caught during processing will be
329      *         rethrown wrapped into a TorqueException.
330      */
331     public static void doDelete(Criteria criteria, Connection con)
332         throws TorqueException
333     {
334         String dbName = criteria.getDbName();
335         final DB db = Torque.getDB(dbName);
336         final DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
337 
338         // This Callback adds all tables to the Table set which
339         // are referenced from a cascading criteria. As a result, all
340         // data that is referenced through foreign keys will also be
341         // deleted.
342         SQLBuilder.TableCallback tc = new SQLBuilder.TableCallback() {
343                 public void process (Set tables, String key, Criteria crit)
344                 {
345                     if (crit.isCascade())
346                     {
347                         // This steps thru all the columns in the database.
348                         TableMap[] tableMaps = dbMap.getTables();
349                         for (int i = 0; i < tableMaps.length; i++)
350                         {
351                             ColumnMap[] columnMaps = tableMaps[i].getColumns();
352 
353                             for (int j = 0; j < columnMaps.length; j++)
354                             {
355                                 // Only delete rows where the foreign key is
356                                 // also a primary key.  Other rows need
357                                 // updating, but that is not implemented.
358                                 if (columnMaps[j].isForeignKey()
359                                         && columnMaps[j].isPrimaryKey()
360                                         && key.equals(columnMaps[j].getRelatedName()))
361                                 {
362                                     tables.add(tableMaps[i].getName());
363                                     crit.add(columnMaps[j].getFullyQualifiedName(),
364                                             crit.getValue(key));
365                                 }
366                             }
367                         }
368                     }
369                 }
370             };
371 
372         Set tables = SQLBuilder.getTableSet(criteria, tc);
373 
374         try
375         {
376             processTables(criteria, tables, con, new ProcessCallback() {
377                     public void process(String table, String dbName, Record rec)
378                         throws Exception
379                     {
380                         rec.markToBeDeleted();
381                         rec.save();
382                     }
383                 });
384         }
385         catch (Exception e)
386         {
387             throwTorqueException(e);
388         }
389     }
390 
391     /***
392      * Method to perform inserts based on values and keys in a
393      * Criteria.
394      * <p>
395      * If the primary key is auto incremented the data in Criteria
396      * will be inserted and the auto increment value will be returned.
397      * <p>
398      * If the primary key is included in Criteria then that value will
399      * be used to insert the row.
400      * <p>
401      * If no primary key is included in Criteria then we will try to
402      * figure out the primary key from the database map and insert the
403      * row with the next available id using util.db.IDBroker.
404      * <p>
405      * If no primary key is defined for the table the values will be
406      * inserted as specified in Criteria and -1 will be returned.
407      *
408      * @param criteria Object containing values to insert.
409      * @return An Object which is the id of the row that was inserted
410      * (if the table has a primary key) or null (if the table does not
411      * have a primary key).
412      * @throws TorqueException Any exceptions caught during processing will be
413      *         rethrown wrapped into a TorqueException.
414      */
415     public static ObjectKey doInsert(Criteria criteria) throws TorqueException
416     {
417         Connection con = null;
418         ObjectKey id = null;
419 
420         try
421         {
422             con = Transaction.beginOptional(
423                     criteria.getDbName(),
424                     criteria.isUseTransaction());
425             id = doInsert(criteria, con);
426             Transaction.commit(con);
427         }
428         catch (TorqueException e)
429         {
430             Transaction.safeRollback(con);
431             throw e;
432         }
433 
434         return id;
435     }
436 
437     /***
438      * Method to perform inserts based on values and keys in a
439      * Criteria.
440      * <p>
441      * If the primary key is auto incremented the data in Criteria
442      * will be inserted and the auto increment value will be returned.
443      * <p>
444      * If the primary key is included in Criteria then that value will
445      * be used to insert the row.
446      * <p>
447      * If no primary key is included in Criteria then we will try to
448      * figure out the primary key from the database map and insert the
449      * row with the next available id using util.db.IDBroker.
450      * <p>
451      * If no primary key is defined for the table the values will be
452      * inserted as specified in Criteria and null will be returned.
453      *
454      * @param criteria Object containing values to insert.
455      * @param con A Connection.
456      * @return An Object which is the id of the row that was inserted
457      * (if the table has a primary key) or null (if the table does not
458      * have a primary key).
459      * @throws TorqueException Any exceptions caught during processing will be
460      *         rethrown wrapped into a TorqueException.
461      */
462     public static ObjectKey doInsert(Criteria criteria, Connection con)
463         throws TorqueException
464     {
465         SimpleKey id = null;
466 
467         // Get the table name and method for determining the primary
468         // key value.
469         String table = null;
470         Iterator keys = criteria.keySet().iterator();
471         if (keys.hasNext())
472         {
473             table = criteria.getTableName((String) keys.next());
474         }
475         else
476         {
477             throw new TorqueException("Database insert attempted without "
478                     + "anything specified to insert");
479         }
480 
481         String dbName = criteria.getDbName();
482         DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
483         TableMap tableMap = dbMap.getTable(table);
484         Object keyInfo = tableMap.getPrimaryKeyMethodInfo();
485         IdGenerator keyGen = tableMap.getIdGenerator();
486 
487         ColumnMap pk = getPrimaryKey(criteria);
488 
489         // If the keyMethod is SEQUENCE or IDBROKERTABLE, get the id
490         // before the insert.
491         if (keyGen != null && keyGen.isPriorToInsert())
492         {
493             // pk will be null if there is no primary key defined for the table
494             // we're inserting into.
495             if (pk != null && !criteria.containsKey(pk.getFullyQualifiedName()))
496             {
497                 if (keyGen == null)
498                 {
499                     throw new TorqueException(
500                             "IdGenerator for table '" + table + "' is null");
501                 }
502 
503                 id = getId(pk, keyGen, con, keyInfo);
504                 criteria.add(pk.getFullyQualifiedName(), id);
505             }
506         }
507 
508         // Use Village to perform the insert.
509         TableDataSet tds = null;
510         try
511         {
512             String tableName = SQLBuilder.getFullTableName(table, dbName);
513             tds = new TableDataSet(con, tableName);
514             Record rec = tds.addRecord();
515             // not the fully qualified name, insertOrUpdateRecord wants to use table as an index...
516             BasePeer.insertOrUpdateRecord(rec, table, dbName, criteria);
517         }
518         catch (Exception e)
519         {
520             throwTorqueException(e);
521         }
522         finally
523         {
524             VillageUtils.close(tds);
525         }
526 
527         // If the primary key column is auto-incremented, get the id
528         // now.
529         if (keyGen != null && keyGen.isPostInsert())
530         {
531             id = getId(pk, keyGen, con, keyInfo);
532         }
533 
534         return id;
535     }
536 
537     /***
538      * Create an Id for insertion in the Criteria
539      *
540      * @param pk ColumnMap for the Primary key
541      * @param keyGen The Id Generator object
542      * @param con The SQL Connection to run the id generation under
543      * @param keyInfo KeyInfo Parameter from the Table map
544      *
545      * @return A simple Key representing the new Id value
546      * @throws TorqueException Possible errors get wrapped in here.
547      */
548     private static SimpleKey getId(ColumnMap pk, IdGenerator keyGen, Connection con, Object keyInfo)
549             throws TorqueException
550     {
551         SimpleKey id = null;
552 
553         try
554         {
555             if (pk != null && keyGen != null)
556             {
557                 if (pk.getType() instanceof Number)
558                 {
559                     id = new NumberKey(
560                             keyGen.getIdAsBigDecimal(con, keyInfo));
561                 }
562                 else
563                 {
564                     id = new StringKey(keyGen.getIdAsString(con, keyInfo));
565                 }
566             }
567         }
568         catch (Exception e)
569         {
570             throwTorqueException(e);
571         }
572         return id;
573     }
574 
575     /***
576      * Grouping of code used in both doInsert() and doUpdate()
577      * methods.  Sets up a Record for saving.
578      *
579      * @param rec A Record.
580      * @param table Name of table.
581      * @param criteria A Criteria.
582      * @throws TorqueException Any exceptions caught during processing will be
583      *         rethrown wrapped into a TorqueException.
584      */
585     private static void insertOrUpdateRecord(
586         Record rec,
587         String table,
588         String dbName,
589         Criteria criteria)
590         throws TorqueException
591     {
592         DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
593 
594         ColumnMap[] columnMaps = dbMap.getTable(table).getColumns();
595         boolean shouldSave = false;
596         for (int j = 0; j < columnMaps.length; j++)
597         {
598             ColumnMap colMap = columnMaps[j];
599             String colName = colMap.getColumnName();
600             String key = new StringBuffer(colMap.getTableName())
601                     .append('.')
602                     .append(colName)
603                     .toString();
604             if (criteria.containsKey(key))
605             {
606                 try
607                 {
608                     VillageUtils.setVillageValue(criteria, key, rec, colName);
609                     shouldSave = true;
610                 }
611                 catch (Exception e)
612                 {
613                     throwTorqueException(e);
614                 }
615             }
616         }
617 
618         if (shouldSave)
619         {
620             try
621             {
622                 rec.save();
623             }
624             catch (Exception e)
625             {
626                 throwTorqueException(e);
627             }
628         }
629         else
630         {
631             throw new TorqueException("No changes to save");
632         }
633     }
634 
635     /***
636      * Method to create an SQL query for display only based on values in a
637      * Criteria.
638      *
639      * @param criteria A Criteria.
640      * @return the SQL query for display
641      * @exception TorqueException Trouble creating the query string.
642      */
643     static String createQueryDisplayString(Criteria criteria)
644         throws TorqueException
645     {
646         return createQuery(criteria).toString();
647     }
648 
649     /***
650      * Method to create an SQL query for actual execution based on values in a
651      * Criteria.
652      *
653      * @param criteria A Criteria.
654      * @return the SQL query for actual execution
655      * @exception TorqueException Trouble creating the query string.
656      */
657     public static String createQueryString(Criteria criteria)
658         throws TorqueException
659     {
660         Query query = createQuery(criteria);
661         return query.toString();
662     }
663 
664     /***
665      * Method to create an SQL query based on values in a Criteria.  Note that
666      * final manipulation of the limit and offset are performed when the query
667      * is actually executed.
668      *
669      * @param criteria A Criteria.
670      * @return the sql query
671      * @exception TorqueException Trouble creating the query string.
672      */
673     static Query createQuery(Criteria criteria)
674         throws TorqueException
675     {
676         return SQLBuilder.buildQueryClause(criteria, null, new SQLBuilder.QueryCallback() {
677                 public String process(Criteria.Criterion criterion, List params)
678                 {
679                     return criterion.toString();
680                 }
681             });
682     }
683 
684     /***
685      * Returns all results.
686      *
687      * @param criteria A Criteria.
688      * @return List of Record objects.
689      * @throws TorqueException Any exceptions caught during processing will be
690      *         rethrown wrapped into a TorqueException.
691      */
692     public static List doSelect(Criteria criteria) throws TorqueException
693     {
694         Connection con = null;
695         List results = null;
696 
697         try
698         {
699             con = Transaction.beginOptional(
700                     criteria.getDbName(),
701                     criteria.isUseTransaction());
702             results = doSelect(criteria, con);
703             Transaction.commit(con);
704         }
705         catch (TorqueException e)
706         {
707             Transaction.safeRollback(con);
708             throw e;
709         }
710         return results;
711     }
712 
713     /***
714      * Returns all results.
715      *
716      * @param criteria A Criteria.
717      * @param con A Connection.
718      * @return List of Record objects.
719      * @throws TorqueException Any exceptions caught during processing will be
720      *         rethrown wrapped into a TorqueException.
721      */
722     public static List doSelect(Criteria criteria, Connection con)
723         throws TorqueException
724     {
725         Query query = createQuery(criteria);
726 
727         if (query.hasLimit())
728         {
729             // We don't need Village to limit the Query
730             return executeQuery(query.toString(),
731                     0,
732                     -1,
733                     criteria.isSingleRecord(),
734                     con);
735         }
736         else
737         {
738             // There is no limit string registered
739             // with the query. Let Village decide.
740             return executeQuery(query.toString(),
741                     criteria.getOffset(),
742                     criteria.getLimit(),
743                     criteria.isSingleRecord(),
744                     con);
745         }
746     }
747 
748     /***
749      * Utility method which executes a given sql statement.  This
750      * method should be used for select statements only.  Use
751      * executeStatement for update, insert, and delete operations.
752      *
753      * @param queryString A String with the sql statement to execute.
754      * @return List of Record objects.
755      * @throws TorqueException Any exceptions caught during processing will be
756      *         rethrown wrapped into a TorqueException.
757      */
758     public static List executeQuery(String queryString) throws TorqueException
759     {
760         return executeQuery(queryString, Torque.getDefaultDB(), false);
761     }
762 
763     /***
764      * Utility method which executes a given sql statement.  This
765      * method should be used for select statements only.  Use
766      * executeStatement for update, insert, and delete operations.
767      *
768      * @param queryString A String with the sql statement to execute.
769      * @param dbName The database to connect to.
770      * @return List of Record objects.
771      * @throws TorqueException Any exceptions caught during processing will be
772      *         rethrown wrapped into a TorqueException.
773      */
774     public static List executeQuery(String queryString, String dbName)
775         throws TorqueException
776     {
777         return executeQuery(queryString, dbName, false);
778     }
779 
780     /***
781      * Method for performing a SELECT.  Returns all results.
782      *
783      * @param queryString A String with the sql statement to execute.
784      * @param dbName The database to connect to.
785      * @param singleRecord Whether or not we want to select only a
786      * single record.
787      * @return List of Record objects.
788      * @throws TorqueException Any exceptions caught during processing will be
789      *         rethrown wrapped into a TorqueException.
790      */
791     public static List executeQuery(
792         String queryString,
793         String dbName,
794         boolean singleRecord)
795         throws TorqueException
796     {
797         return executeQuery(queryString, 0, -1, dbName, singleRecord);
798     }
799 
800     /***
801      * Method for performing a SELECT.  Returns all results.
802      *
803      * @param queryString A String with the sql statement to execute.
804      * @param singleRecord Whether or not we want to select only a
805      * single record.
806      * @param con A Connection.
807      * @return List of Record objects.
808      * @throws TorqueException Any exceptions caught during processing will be
809      *         rethrown wrapped into a TorqueException.
810      */
811     public static List executeQuery(
812         String queryString,
813         boolean singleRecord,
814         Connection con)
815         throws TorqueException
816     {
817         return executeQuery(queryString, 0, -1, singleRecord, con);
818     }
819 
820     /***
821      * Method for performing a SELECT.
822      *
823      * @param queryString A String with the sql statement to execute.
824      * @param start The first row to return.
825      * @param numberOfResults The number of rows to return.
826      * @param dbName The database to connect to.
827      * @param singleRecord Whether or not we want to select only a
828      * single record.
829      * @return List of Record objects.
830      * @throws TorqueException Any exceptions caught during processing will be
831      *         rethrown wrapped into a TorqueException.
832      */
833     public static List executeQuery(
834         String queryString,
835         int start,
836         int numberOfResults,
837         String dbName,
838         boolean singleRecord)
839         throws TorqueException
840     {
841         Connection con = null;
842         List results = null;
843         try
844         {
845             con = Torque.getConnection(dbName);
846             // execute the query
847             results = executeQuery(
848                     queryString,
849                     start,
850                     numberOfResults,
851                     singleRecord,
852                     con);
853         }
854         finally
855         {
856             Torque.closeConnection(con);
857         }
858         return results;
859     }
860 
861     /***
862      * Method for performing a SELECT.  Returns all results.
863      *
864      * @param queryString A String with the sql statement to execute.
865      * @param start The first row to return.
866      * @param numberOfResults The number of rows to return.
867      * @param singleRecord Whether or not we want to select only a
868      * single record.
869      * @param con A Connection.
870      * @return List of Record objects.
871      * @throws TorqueException Any exceptions caught during processing will be
872      *         rethrown wrapped into a TorqueException.
873      */
874     public static List executeQuery(
875         String queryString,
876         int start,
877         int numberOfResults,
878         boolean singleRecord,
879         Connection con)
880         throws TorqueException
881     {
882         QueryDataSet qds = null;
883         List results = Collections.EMPTY_LIST;
884         try
885         {
886             // execute the query
887             long startTime = System.currentTimeMillis();
888             qds = new QueryDataSet(con, queryString);
889             if (log.isDebugEnabled())
890             {
891                 log.debug("Elapsed time="
892                         + (System.currentTimeMillis() - startTime) + " ms");
893             }
894             results = getSelectResults(
895                     qds, start, numberOfResults, singleRecord);
896         }
897         catch (Exception e)
898         {
899             throwTorqueException(e);
900         }
901         finally
902         {
903             VillageUtils.close(qds);
904         }
905         return results;
906     }
907 
908     /***
909      * Returns all records in a QueryDataSet as a List of Record
910      * objects.  Used for functionality like util.LargeSelect.
911      *
912      * @see #getSelectResults(QueryDataSet, int, int, boolean)
913      * @param qds the QueryDataSet
914      * @return a List of Record objects
915      * @throws TorqueException Any exceptions caught during processing will be
916      *         rethrown wrapped into a TorqueException.
917      */
918     public static List getSelectResults(QueryDataSet qds)
919         throws TorqueException
920     {
921         return getSelectResults(qds, 0, -1, false);
922     }
923 
924     /***
925      * Returns all records in a QueryDataSet as a List of Record
926      * objects.  Used for functionality like util.LargeSelect.
927      *
928      * @see #getSelectResults(QueryDataSet, int, int, boolean)
929      * @param qds the QueryDataSet
930      * @param singleRecord
931      * @return a List of Record objects
932      * @throws TorqueException Any exceptions caught during processing will be
933      *         rethrown wrapped into a TorqueException.
934      */
935     public static List getSelectResults(QueryDataSet qds, boolean singleRecord)
936         throws TorqueException
937     {
938         return getSelectResults(qds, 0, -1, singleRecord);
939     }
940 
941     /***
942      * Returns numberOfResults records in a QueryDataSet as a List
943      * of Record objects.  Starting at record 0.  Used for
944      * functionality like util.LargeSelect.
945      *
946      * @see #getSelectResults(QueryDataSet, int, int, boolean)
947      * @param qds the QueryDataSet
948      * @param numberOfResults
949      * @param singleRecord
950      * @return a List of Record objects
951      * @throws TorqueException Any exceptions caught during processing will be
952      *         rethrown wrapped into a TorqueException.
953      */
954     public static List getSelectResults(
955         QueryDataSet qds,
956         int numberOfResults,
957         boolean singleRecord)
958         throws TorqueException
959     {
960         List results = null;
961         if (numberOfResults != 0)
962         {
963             results = getSelectResults(qds, 0, numberOfResults, singleRecord);
964         }
965         return results;
966     }
967 
968     /***
969      * Returns numberOfResults records in a QueryDataSet as a List
970      * of Record objects.  Starting at record start.  Used for
971      * functionality like util.LargeSelect.
972      *
973      * @param qds The <code>QueryDataSet</code> to extract results
974      * from.
975      * @param start The index from which to start retrieving
976      * <code>Record</code> objects from the data set.
977      * @param numberOfResults The number of results to return (or
978      * <code> -1</code> for all results).
979      * @param singleRecord Whether or not we want to select only a
980      * single record.
981      * @return A <code>List</code> of <code>Record</code> objects.
982      * @exception TorqueException If any <code>Exception</code> occurs.
983      */
984     public static List getSelectResults(
985         QueryDataSet qds,
986         int start,
987         int numberOfResults,
988         boolean singleRecord)
989         throws TorqueException
990     {
991         List results = null;
992         try
993         {
994             if (numberOfResults <= 0)
995             {
996                 results = new ArrayList();
997                 qds.fetchRecords();
998             }
999             else
1000             {
1001                 results = new ArrayList(numberOfResults);
1002                 qds.fetchRecords(start, numberOfResults);
1003             }
1004             if (qds.size() > 1 && singleRecord)
1005             {
1006                 handleMultipleRecords(qds);
1007             }
1008 
1009             int startRecord = 0;
1010 
1011             //Offset the correct number of people
1012             if (start > 0 && numberOfResults <= 0)
1013             {
1014                 startRecord = start;
1015             }
1016 
1017             // Return a List of Record objects.
1018             for (int i = startRecord; i < qds.size(); i++)
1019             {
1020                 Record rec = qds.getRecord(i);
1021                 results.add(rec);
1022             }
1023         }
1024         catch (Exception e)
1025         {
1026             throwTorqueException(e);
1027         }
1028         return results;
1029     }
1030 
1031     /***
1032      * Helper method which returns the primary key contained
1033      * in the given Criteria object.
1034      *
1035      * @param criteria A Criteria.
1036      * @return ColumnMap if the Criteria object contains a primary
1037      *          key, or null if it doesn't.
1038      * @throws TorqueException Any exceptions caught during processing will be
1039      *         rethrown wrapped into a TorqueException.
1040      */
1041     private static ColumnMap getPrimaryKey(Criteria criteria)
1042         throws TorqueException
1043     {
1044         // Assume all the keys are for the same table.
1045         String key = (String) criteria.keys().nextElement();
1046 
1047         String table = criteria.getTableName(key);
1048         ColumnMap pk = null;
1049 
1050         if (!table.equals(""))
1051         {
1052             DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
1053             if (dbMap == null)
1054             {
1055                 throw new TorqueException("dbMap is null");
1056             }
1057             if (dbMap.getTable(table) == null)
1058             {
1059                 throw new TorqueException("dbMap.getTable() is null");
1060             }
1061 
1062             ColumnMap[] columns = dbMap.getTable(table).getColumns();
1063 
1064             for (int i = 0; i < columns.length; i++)
1065             {
1066                 if (columns[i].isPrimaryKey())
1067                 {
1068                     pk = columns[i];
1069                     break;
1070                 }
1071             }
1072         }
1073         return pk;
1074     }
1075 
1076     /***
1077      * Convenience method used to update rows in the DB.  Checks if a
1078      * <i>single</i> int primary key is specified in the Criteria
1079      * object and uses it to perform the udpate.  If no primary key is
1080      * specified an Exception will be thrown.
1081      * <p>
1082      * Use this method for performing an update of the kind:
1083      * <p>
1084      * "WHERE primary_key_id = an int"
1085      * <p>
1086      * To perform an update with non-primary key fields in the WHERE
1087      * clause use doUpdate(criteria, criteria).
1088      *
1089      * @param updateValues A Criteria object containing values used in
1090      *        set clause.
1091      * @throws TorqueException Any exceptions caught during processing will be
1092      *         rethrown wrapped into a TorqueException.
1093      */
1094     public static void doUpdate(Criteria updateValues) throws TorqueException
1095     {
1096         Connection con = null;
1097         try
1098         {
1099             con = Transaction.beginOptional(
1100                     updateValues.getDbName(),
1101                     updateValues.isUseTransaction());
1102             doUpdate(updateValues, con);
1103             Transaction.commit(con);
1104         }
1105         catch (TorqueException e)
1106         {
1107             Transaction.safeRollback(con);
1108             throw e;
1109         }
1110     }
1111 
1112     /***
1113      * Convenience method used to update rows in the DB.  Checks if a
1114      * <i>single</i> int primary key is specified in the Criteria
1115      * object and uses it to perform the udpate.  If no primary key is
1116      * specified an Exception will be thrown.
1117      * <p>
1118      * Use this method for performing an update of the kind:
1119      * <p>
1120      * "WHERE primary_key_id = an int"
1121      * <p>
1122      * To perform an update with non-primary key fields in the WHERE
1123      * clause use doUpdate(criteria, criteria).
1124      *
1125      * @param updateValues A Criteria object containing values used in
1126      * set clause.
1127      * @param con A Connection.
1128      * @throws TorqueException Any exceptions caught during processing will be
1129      *         rethrown wrapped into a TorqueException.
1130      */
1131     public static void doUpdate(Criteria updateValues, Connection con)
1132         throws TorqueException
1133     {
1134         ColumnMap pk = getPrimaryKey(updateValues);
1135         Criteria selectCriteria = null;
1136 
1137         if (pk != null && updateValues.containsKey(pk.getFullyQualifiedName()))
1138         {
1139             selectCriteria = new Criteria(2);
1140             selectCriteria.put(pk.getFullyQualifiedName(),
1141                 updateValues.remove(pk.getFullyQualifiedName()));
1142         }
1143         else
1144         {
1145             throw new TorqueException("No PK specified for database update");
1146         }
1147 
1148         doUpdate(selectCriteria, updateValues, con);
1149     }
1150 
1151     /***
1152      * Method used to update rows in the DB.  Rows are selected based
1153      * on selectCriteria and updated using values in updateValues.
1154      * <p>
1155      * Use this method for performing an update of the kind:
1156      * <p>
1157      * WHERE some_column = some value AND could_have_another_column =
1158      * another value AND so on...
1159      *
1160      * @param selectCriteria A Criteria object containing values used in where
1161      *        clause.
1162      * @param updateValues A Criteria object containing values used in set
1163      *        clause.
1164      * @throws TorqueException Any exceptions caught during processing will be
1165      *         rethrown wrapped into a TorqueException.
1166      */
1167     public static void doUpdate(Criteria selectCriteria, Criteria updateValues)
1168         throws TorqueException
1169     {
1170         Connection con = null;
1171         try
1172         {
1173             con = Transaction.beginOptional(
1174                     selectCriteria.getDbName(),
1175                     updateValues.isUseTransaction());
1176             doUpdate(selectCriteria, updateValues, con);
1177             Transaction.commit(con);
1178         }
1179         catch (TorqueException e)
1180         {
1181             Transaction.safeRollback(con);
1182             throw e;
1183         }
1184     }
1185 
1186     /***
1187      * Method used to update rows in the DB.  Rows are selected based
1188      * on criteria and updated using values in updateValues.
1189      * <p>
1190      * Use this method for performing an update of the kind:
1191      * <p>
1192      * WHERE some_column = some value AND could_have_another_column =
1193      * another value AND so on.
1194      *
1195      * @param criteria A Criteria object containing values used in where
1196      *        clause.
1197      * @param updateValues A Criteria object containing values used in set
1198      *        clause.
1199      * @param con A Connection.
1200      * @throws TorqueException Any exceptions caught during processing will be
1201      *         rethrown wrapped into a TorqueException.
1202      */
1203     public static void doUpdate(
1204         Criteria criteria,
1205         final Criteria updateValues,
1206         Connection con)
1207         throws TorqueException
1208     {
1209         String dbName = criteria.getDbName();
1210         DB db = Torque.getDB(dbName);
1211         DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
1212 
1213         Set tables = SQLBuilder.getTableSet(criteria, null);
1214 
1215         try
1216         {
1217             processTables(criteria, tables, con, new ProcessCallback() {
1218                     public void process (String table, String dbName, Record rec)
1219                         throws Exception
1220                     {
1221                         // Callback must be called with table name without Schema!
1222                         BasePeer.insertOrUpdateRecord(rec, table, dbName, updateValues);
1223                     }
1224                 });
1225         }
1226         catch (Exception e)
1227         {
1228             throwTorqueException(e);
1229         }
1230     }
1231 
1232     /***
1233      * Utility method which executes a given sql statement.  This
1234      * method should be used for update, insert, and delete
1235      * statements.  Use executeQuery() for selects.
1236      *
1237      * @param statementString A String with the sql statement to execute.
1238      * @return The number of rows affected.
1239      * @throws TorqueException Any exceptions caught during processing will be
1240      *         rethrown wrapped into a TorqueException.
1241      */
1242     public static int executeStatement(String statementString) throws TorqueException
1243     {
1244         return executeStatement(statementString, Torque.getDefaultDB());
1245     }
1246 
1247     /***
1248      * Utility method which executes a given sql statement.  This
1249      * method should be used for update, insert, and delete
1250      * statements.  Use executeQuery() for selects.
1251      *
1252      * @param statementString A String with the sql statement to execute.
1253      * @param dbName Name of database to connect to.
1254      * @return The number of rows affected.
1255      * @throws TorqueException Any exceptions caught during processing will be
1256      *         rethrown wrapped into a TorqueException.
1257      */
1258     public static int executeStatement(String statementString, String dbName)
1259         throws TorqueException
1260     {
1261         Connection con = null;
1262         int rowCount = -1;
1263         try
1264         {
1265             con = Torque.getConnection(dbName);
1266             rowCount = executeStatement(statementString, con);
1267         }
1268         finally
1269         {
1270             Torque.closeConnection(con);
1271         }
1272         return rowCount;
1273     }
1274 
1275     /***
1276      * Utility method which executes a given sql statement.  This
1277      * method should be used for update, insert, and delete
1278      * statements.  Use executeQuery() for selects.
1279      *
1280      * @param statementString A String with the sql statement to execute.
1281      * @param con A Connection.
1282      * @return The number of rows affected.
1283      * @throws TorqueException Any exceptions caught during processing will be
1284      *         rethrown wrapped into a TorqueException.
1285      */
1286     public static int executeStatement(String statementString, Connection con)
1287         throws TorqueException
1288     {
1289         int rowCount = -1;
1290         Statement statement = null;
1291         try
1292         {
1293             statement = con.createStatement();
1294             rowCount = statement.executeUpdate(statementString);
1295         }
1296         catch (SQLException e)
1297         {
1298             throw new TorqueException(e);
1299         }
1300         finally
1301         {
1302             if (statement != null)
1303             {
1304                 try
1305                 {
1306                     statement.close();
1307                 }
1308                 catch (SQLException e)
1309                 {
1310                     throw new TorqueException(e);
1311                 }
1312             }
1313         }
1314         return rowCount;
1315     }
1316 
1317     /***
1318      * If the user specified that (s)he only wants to retrieve a
1319      * single record and multiple records are retrieved, this method
1320      * is called to handle the situation.  The default behavior is to
1321      * throw an exception, but subclasses can override this method as
1322      * needed.
1323      *
1324      * @param ds The DataSet which contains multiple records.
1325      * @exception TorqueException Couldn't handle multiple records.
1326      */
1327     protected static void handleMultipleRecords(DataSet ds)
1328         throws TorqueException
1329     {
1330         throw new TorqueException("Criteria expected single Record and "
1331                 + "Multiple Records were selected");
1332     }
1333 
1334     /***
1335      * This method returns the MapBuilder specified in the name
1336      * parameter.  You should pass in the full path to the class, ie:
1337      * org.apache.torque.util.db.map.TurbineMapBuilder.  The
1338      * MapBuilder instances are cached in this class for speed.
1339      *
1340      * @param name name of the MapBuilder
1341      * @return A MapBuilder, not null
1342      * @throws TorqueException if the Map Builder cannot be instantiated
1343      */
1344     public static MapBuilder getMapBuilder(String name)
1345         throws TorqueException
1346     {
1347         synchronized (mapBuilders)
1348         {
1349             try
1350             {
1351                 MapBuilder mb = (MapBuilder) mapBuilders.get(name);
1352 
1353                 if (mb == null)
1354                 {
1355                     mb = (MapBuilder) Class.forName(name).newInstance();
1356                     // Cache the MapBuilder before it is built.
1357                     mapBuilders.put(name, mb);
1358                 }
1359 
1360                 // Build the MapBuilder in its own synchronized block to
1361                 //  avoid locking up the whole Hashtable while doing so.
1362                 // Note that *all* threads need to do a sync check on isBuilt()
1363                 //  to avoid grabing an uninitialized MapBuilder. This, however,
1364                 //  is a relatively fast operation.
1365 
1366                 if (mb.isBuilt())
1367                 {
1368                     return mb;
1369                 }
1370 
1371                 try
1372                 {
1373                     mb.doBuild();
1374                 }
1375                 catch (Exception e)
1376                 {
1377                     // need to think about whether we'd want to remove
1378                     //  the MapBuilder from the cache if it can't be
1379                     //  built correctly...?  pgo
1380                     throw e;
1381                 }
1382 
1383                 return mb;
1384             }
1385             catch (Exception e)
1386             {
1387                 log.error("BasePeer.MapBuilder failed trying to instantiate: "
1388                         + name, e);
1389                 throw new TorqueException(e);
1390             }
1391         }
1392     }
1393 
1394     /***
1395      * Performs a SQL <code>select</code> using a PreparedStatement.
1396      * Note: this method does not handle null criteria values.
1397      *
1398      * @param criteria
1399      * @param con
1400      * @return a List of Record objects.
1401      * @throws TorqueException Error performing database query.
1402      */
1403     public static List doPSSelect(Criteria criteria, Connection con)
1404         throws TorqueException
1405     {
1406         List v = null;
1407 
1408         StringBuffer qry = new StringBuffer();
1409         List params = new ArrayList(criteria.size());
1410 
1411         createPreparedStatement(criteria, qry, params);
1412 
1413         PreparedStatement statement = null;
1414         try
1415         {
1416             statement = con.prepareStatement(qry.toString());
1417 
1418             for (int i = 0; i < params.size(); i++)
1419             {
1420                 Object param = params.get(i);
1421                 if (param instanceof java.sql.Date)
1422                 {
1423                     statement.setDate(i + 1, (java.sql.Date) param);
1424                 }
1425                 else if (param instanceof NumberKey)
1426                 {
1427                     statement.setBigDecimal(i + 1,
1428                         ((NumberKey) param).getBigDecimal());
1429                 }
1430                 else
1431                 {
1432                     statement.setString(i + 1, param.toString());
1433                 }
1434             }
1435 
1436             QueryDataSet qds = null;
1437             try
1438             {
1439                 qds = new QueryDataSet(statement.executeQuery());
1440                 v = getSelectResults(qds);
1441             }
1442             finally
1443             {
1444                 VillageUtils.close(qds);
1445             }
1446         }
1447         catch (Exception e)
1448         {
1449             throwTorqueException(e);
1450         }
1451         finally
1452         {
1453             if (statement != null)
1454             {
1455                 try
1456                 {
1457                     statement.close();
1458                 }
1459                 catch (SQLException e)
1460                 {
1461                     throw new TorqueException(e);
1462                 }
1463             }
1464         }
1465         return v;
1466     }
1467 
1468     /***
1469      * Do a Prepared Statement select according to the given criteria
1470      *
1471      * @param criteria
1472      * @return a List of Record objects.
1473      * @throws TorqueException Any exceptions caught during processing will be
1474      *         rethrown wrapped into a TorqueException.
1475      */
1476     public static List doPSSelect(Criteria criteria) throws TorqueException
1477     {
1478         Connection con = Torque.getConnection(criteria.getDbName());
1479         List v = null;
1480 
1481         try
1482         {
1483             v = doPSSelect(criteria, con);
1484         }
1485         finally
1486         {
1487             Torque.closeConnection(con);
1488         }
1489         return v;
1490     }
1491 
1492     /***
1493      * Create a new PreparedStatement.  It builds a string representation
1494      * of a query and a list of PreparedStatement parameters.
1495      *
1496      * @param criteria
1497      * @param queryString
1498      * @param params
1499      * @throws TorqueException Any exceptions caught during processing will be
1500      *         rethrown wrapped into a TorqueException.
1501      */
1502     public static void createPreparedStatement(
1503         Criteria criteria,
1504         StringBuffer queryString,
1505         List params)
1506         throws TorqueException
1507     {
1508         Query query = SQLBuilder.buildQueryClause(criteria, params, new SQLBuilder.QueryCallback() {
1509                 public String process(Criteria.Criterion criterion, List params)
1510                 {
1511                     StringBuffer sb = new StringBuffer();
1512                     criterion.appendPsTo(sb, params);
1513                     return sb.toString();
1514                 }
1515             });
1516 
1517         String sql = query.toString();
1518         log.debug(sql);
1519 
1520         queryString.append(sql);
1521     }
1522 
1523     /***
1524      * Process the result of a Table list generation.
1525      * This runs the statements onto the list of tables and
1526      * provides a callback hook to add functionality.
1527      *
1528      * This method should've been in SQLBuilder, but is uses the handleMultipleRecords callback thingie..
1529      *
1530      * @param crit The criteria
1531      * @param tables A set of Tables to run on
1532      * @param con The SQL Connection to run the statements on
1533      * @param pc A ProcessCallback object
1534      *
1535      * @throws Exception An Error occured (should be wrapped into TorqueException)
1536      */
1537     private static void processTables(Criteria crit, Set tables, Connection con, ProcessCallback pc)
1538             throws Exception
1539     {
1540         String dbName = crit.getDbName();
1541         DB db = Torque.getDB(dbName);
1542         DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
1543 
1544         // create the statements for the tables
1545         for (Iterator it = tables.iterator(); it.hasNext(); )
1546         {
1547             String table = (String) it.next();
1548             KeyDef kd = new KeyDef();
1549             Set whereClause = new HashSet();
1550 
1551             ColumnMap[] columnMaps = dbMap.getTable(table).getColumns();
1552 
1553             for (int j = 0; j < columnMaps.length; j++)
1554             {
1555                 ColumnMap colMap = columnMaps[j];
1556                 if (colMap.isPrimaryKey())
1557                 {
1558                     kd.addAttrib(colMap.getColumnName());
1559                 }
1560 
1561                 String key = new StringBuffer(colMap.getTableName())
1562                         .append('.')
1563                         .append(colMap.getColumnName())
1564                         .toString();
1565 
1566                 if (crit.containsKey(key))
1567                 {
1568                     if (crit
1569                             .getComparison(key)
1570                             .equals(Criteria.CUSTOM))
1571                     {
1572                         whereClause.add(crit.getString(key));
1573                     }
1574                     else
1575                     {
1576                         whereClause.add(
1577                                 SqlExpression.build(
1578                                         colMap.getColumnName(),
1579                                         crit.getValue(key),
1580                                         crit.getComparison(key),
1581                                         crit.isIgnoreCase(),
1582                                         db));
1583                     }
1584                 }
1585             }
1586 
1587             // Execute the statement for each table
1588             TableDataSet tds = null;
1589             try
1590             {
1591                 String tableName = SQLBuilder.getFullTableName(table, dbName);
1592 
1593                 // Get affected records.
1594                 tds = new TableDataSet(con, tableName, kd);
1595                 String sqlSnippet = StringUtils.join(whereClause.iterator(), " AND ");
1596 
1597                 if (log.isDebugEnabled())
1598                 {
1599                     log.debug("BasePeer: whereClause=" + sqlSnippet);
1600                 }
1601 
1602                 tds.where(sqlSnippet);
1603                 tds.fetchRecords();
1604 
1605                 if (tds.size() > 1 && crit.isSingleRecord())
1606                 {
1607                     handleMultipleRecords(tds);
1608                 }
1609 
1610                 for (int j = 0; j < tds.size(); j++)
1611                 {
1612                     Record rec = tds.getRecord(j);
1613 
1614                     if (pc != null)
1615                     {
1616                         // Table name _without_ schema!
1617                         pc.process(table, dbName, rec);
1618                     }
1619                 }
1620             }
1621             finally
1622             {
1623                 VillageUtils.close(tds);
1624             }
1625         }
1626     }
1627 
1628     /***
1629      * Inner Interface that defines the Callback method for
1630      * the Record Processing
1631      */
1632     protected interface ProcessCallback
1633     {
1634         void process (String table, String dbName, Record rec)
1635                 throws Exception;
1636     }
1637 }