1 package org.apache.torque.util;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
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
339
340
341
342 SQLBuilder.TableCallback tc = new SQLBuilder.TableCallback() {
343 public void process (Set tables, String key, Criteria crit)
344 {
345 if (crit.isCascade())
346 {
347
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
356
357
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
468
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
490
491 if (keyGen != null && keyGen.isPriorToInsert())
492 {
493
494
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
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
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
528
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
730 return executeQuery(query.toString(),
731 0,
732 -1,
733 criteria.isSingleRecord(),
734 con);
735 }
736 else
737 {
738
739
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
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
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
1012 if (start > 0 && numberOfResults <= 0)
1013 {
1014 startRecord = start;
1015 }
1016
1017
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
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
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
1357 mapBuilders.put(name, mb);
1358 }
1359
1360
1361
1362
1363
1364
1365
1366 if (mb.isBuilt())
1367 {
1368 return mb;
1369 }
1370
1371 try
1372 {
1373 mb.doBuild();
1374 }
1375 catch (Exception e)
1376 {
1377
1378
1379
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
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
1588 TableDataSet tds = null;
1589 try
1590 {
1591 String tableName = SQLBuilder.getFullTableName(table, dbName);
1592
1593
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
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 }