View Javadoc

1   package org.apache.torque.oid;
2   
3   /*
4    * Copyright 2001-2004 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
17   */
18  
19  import java.math.BigDecimal;
20  import java.sql.Connection;
21  import java.sql.ResultSet;
22  import java.sql.Statement;
23  import java.util.ArrayList;
24  import java.util.Hashtable;
25  import java.util.Iterator;
26  import java.util.List;
27  
28  import org.apache.commons.configuration.Configuration;
29  
30  import org.apache.commons.logging.Log;
31  import org.apache.commons.logging.LogFactory;
32  
33  import org.apache.torque.Torque;
34  import org.apache.torque.TorqueException;
35  import org.apache.torque.map.DatabaseMap;
36  import org.apache.torque.map.TableMap;
37  import org.apache.torque.util.Transaction;
38  
39  //!!
40  // NOTE:
41  // It would be nice to decouple this from
42  // Torque. This is a great stand-alone utility.
43  
44  /***
45   * This method of ID generation is used to ensure that code is
46   * more database independent.  For example, MySQL has an auto-increment
47   * feature while Oracle uses sequences.  It caches several ids to
48   * avoid needing a Connection for every request.
49   *
50   * This class uses the table ID_TABLE defined in
51   * conf/master/id-table-schema.xml.  The columns in ID_TABLE are used as
52   * follows:<br>
53   *
54   * ID_TABLE_ID - The PK for this row (any unique int).<br>
55   * TABLE_NAME - The name of the table you want ids for.<br>
56   * NEXT_ID - The next id returned by IDBroker when it queries the
57   *           database (not when it returns an id from memory).<br>
58   * QUANTITY - The number of ids that IDBroker will cache in memory.<br>
59   * <p>
60   * Use this class like this:
61   * <pre>
62   * int id = dbMap.getIDBroker().getNextIdAsInt(null, "TABLE_NAME");
63   *  - or -
64   * BigDecimal[] ids = ((IDBroker)dbMap.getIDBroker())
65   *     .getNextIds("TABLE_NAME", numOfIdsToReturn);
66   * </pre>
67   *
68   * NOTE: When the ID_TABLE must be updated we must ensure that
69   * IDBroker objects running in different JVMs do not overwrite each
70   * other.  This is accomplished using using the transactional support
71   * occuring in some databases.  Using this class with a database that
72   * does not support transactions should be limited to a single JVM.
73   *
74   * @author <a href="mailto:frank.kim@clearink.com">Frank Y. Kim</a>
75   * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
76   * @version $Id: IDBroker.java,v 1.27.2.4 2004/08/26 16:49:33 henning Exp $
77   */
78  public class IDBroker implements Runnable, IdGenerator
79  {
80      /*** Name of the ID_TABLE = ID_TABLE */
81      public static final String ID_TABLE = "ID_TABLE";
82  
83      /*** Table_Name column name */
84      public static final String COL_TABLE_NAME = "TABLE_NAME";
85  
86      /*** Fully qualified Table_Name column name */
87      public static final String TABLE_NAME = ID_TABLE + "." + COL_TABLE_NAME;
88  
89      /*** ID column name */
90      public static final String COL_TABLE_ID = "ID_TABLE_ID";
91  
92      /*** Fully qualified ID column name */
93      public static final String TABLE_ID = ID_TABLE + "." + COL_TABLE_ID;
94  
95      /*** Next_ID column name */
96      public static final String COL_NEXT_ID = "NEXT_ID";
97  
98      /*** Fully qualified Next_ID column name */
99      public static final String NEXT_ID = ID_TABLE + "." + COL_NEXT_ID;
100 
101     /*** Quantity column name */
102     public static final String COL_QUANTITY = "QUANTITY";
103 
104     /*** Fully qualified Quantity column name */
105     public static final String QUANTITY = ID_TABLE + "." + COL_QUANTITY;
106 
107     /*** The TableMap referencing the ID_TABLE for this IDBroker. */
108     private TableMap tableMap;
109 
110     /***
111      * The default size of the per-table meta data <code>Hashtable</code>
112      * objects.
113      */
114     private static final int DEFAULT_SIZE = 40;
115 
116     /***
117      * The cached IDs for each table.
118      *
119      * Key: String table name.
120      * Value: List of Integer IDs.
121      */
122     private Hashtable ids = new Hashtable(DEFAULT_SIZE);
123 
124     /***
125      * The quantity of ids to grab for each table.
126      *
127      * Key: String table name.
128      * Value: Integer quantity.
129      */
130     private Hashtable quantityStore = new Hashtable(DEFAULT_SIZE);
131 
132     /***
133      * The last time this IDBroker queried the database for ids.
134      *
135      * Key: String table name.
136      * Value: Date of last id request.
137      */
138     private Hashtable lastQueryTime = new Hashtable(DEFAULT_SIZE);
139 
140     /***
141      * Amount of time for the thread to sleep
142      */
143     private static final int SLEEP_PERIOD = 1 * 60000;
144 
145     /***
146      * The safety Margin
147      */
148     private static final float SAFETY_MARGIN = 1.2f;
149 
150     /***
151      * The houseKeeperThread thread
152      */
153     private Thread houseKeeperThread = null;
154 
155     /***
156      * Are transactions supported?
157      */
158     private boolean transactionsSupported = false;
159 
160     /***
161      * The value of ONE!
162      */
163     private static final BigDecimal ONE = new BigDecimal("1");
164 
165     /*** the configuration */
166     private Configuration configuration;
167 
168     /*** property name */
169     private static final String DB_IDBROKER_CLEVERQUANTITY =
170         "idbroker.clever.quantity";
171 
172     /*** property name */
173     private static final String DB_IDBROKER_PREFETCH =
174         "idbroker.prefetch";
175 
176     /*** property name */
177     private static final String DB_IDBROKER_USENEWCONNECTION =
178         "idbroker.usenewconnection";
179 
180     /*** the log */
181     private Log log = LogFactory.getLog(IDBroker.class);
182 
183     /***
184      * Creates an IDBroker for the ID table.
185      *
186      * @param tMap A TableMap.
187      */
188     public IDBroker(TableMap tMap)
189     {
190         this.tableMap = tMap;
191         configuration = Torque.getConfiguration();
192 
193         // Start the housekeeper thread only if prefetch has not been disabled
194         if (configuration.getBoolean(DB_IDBROKER_PREFETCH, true))
195         {
196             houseKeeperThread = new Thread(this);
197             // Indicate that this is a system thread. JVM will quit only when
198             // there are no more active user threads. Settings threads spawned
199             // internally by Torque as daemons allows commandline applications
200             // using Torque terminate in an orderly manner.
201             houseKeeperThread.setDaemon(true);
202             houseKeeperThread.start();
203         }
204 
205         // Check for Transaction support.  Give warning message if
206         // IDBroker is being used with a database that does not
207         // support transactions.
208         String dbName = tMap.getDatabaseMap().getName();
209         Connection dbCon = null;
210         try
211         {
212             dbCon = Torque.getConnection(dbName);
213             transactionsSupported = dbCon.getMetaData().supportsTransactions();
214         }
215         catch (Exception e)
216         {
217             transactionsSupported = false;
218         }
219         finally
220         {
221             try
222             {
223                 // Return the connection to the pool.
224                 dbCon.close();
225             }
226             catch (Exception e)
227             {
228             }
229         }
230         if (!transactionsSupported)
231         {
232             log.warn("IDBroker is being used with db '" + dbName
233                     + "', which does not support transactions. IDBroker "
234                     + "attempts to use transactions to limit the possibility "
235                     + "of duplicate key generation.  Without transactions, "
236                     + "duplicate key generation is possible if multiple JVMs "
237                     + "are used or other means are used to write to the "
238                     + "database.");
239         }
240     }
241 
242     /***
243      * Set the configuration
244      *
245      * @param configuration the configuration
246      */
247     public void setConfiguration(Configuration configuration)
248     {
249         this.configuration = configuration;
250     }
251 
252     /***
253      * Returns an id as a primitive int.  Note this method does not
254      * require a Connection, it just implements the KeyGenerator
255      * interface.  if a Connection is needed one will be requested.
256      * To force the use of the passed in connection set the configuration
257      * property torque.idbroker.usenewconnection = false
258      *
259      * @param connection A Connection.
260      * @param tableName an Object that contains additional info.
261      * @return An int with the value for the id.
262      * @exception Exception Database error.
263      */
264     public int getIdAsInt(Connection connection, Object tableName)
265         throws Exception
266     {
267         return getIdAsBigDecimal(connection, tableName).intValue();
268     }
269 
270 
271     /***
272      * Returns an id as a primitive long. Note this method does not
273      * require a Connection, it just implements the KeyGenerator
274      * interface.  if a Connection is needed one will be requested.
275      * To force the use of the passed in connection set the configuration
276      * property torque.idbroker.usenewconnection = false
277      *
278      * @param connection A Connection.
279      * @param tableName a String that identifies a table.
280      * @return A long with the value for the id.
281      * @exception Exception Database error.
282      */
283     public long getIdAsLong(Connection connection, Object tableName)
284         throws Exception
285     {
286         return getIdAsBigDecimal(connection, tableName).longValue();
287     }
288 
289     /***
290      * Returns an id as a BigDecimal. Note this method does not
291      * require a Connection, it just implements the KeyGenerator
292      * interface.  if a Connection is needed one will be requested.
293      * To force the use of the passed in connection set the configuration
294      * property torque.idbroker.usenewconnection = false
295      *
296      * @param connection A Connection.
297      * @param tableName a String that identifies a table..
298      * @return A BigDecimal id.
299      * @exception Exception Database error.
300      */
301     public BigDecimal getIdAsBigDecimal(Connection connection,
302                                         Object tableName)
303         throws Exception
304     {
305         BigDecimal[] id = getNextIds((String) tableName, 1, connection);
306         return id[0];
307     }
308 
309     /***
310      * Returns an id as a String. Note this method does not
311      * require a Connection, it just implements the KeyGenerator
312      * interface.  if a Connection is needed one will be requested.
313      * To force the use of the passed in connection set the configuration
314      * property torque.idbroker.usenewconnection = false
315      *
316      * @param connection A Connection should be null.
317      * @param tableName a String that identifies a table.
318      * @return A String id
319      * @exception Exception Database error.
320      */
321     public String getIdAsString(Connection connection, Object tableName)
322         throws Exception
323     {
324         return getIdAsBigDecimal(connection, tableName).toString();
325     }
326 
327 
328     /***
329      * A flag to determine the timing of the id generation     *
330      * @return a <code>boolean</code> value
331      */
332     public boolean isPriorToInsert()
333     {
334         return true;
335     }
336 
337     /***
338      * A flag to determine the timing of the id generation
339      *
340      * @return a <code>boolean</code> value
341      */
342     public boolean isPostInsert()
343     {
344         return false;
345     }
346 
347     /***
348      * A flag to determine whether a Connection is required to
349      * generate an id.
350      *
351      * @return a <code>boolean</code> value
352      */
353     public boolean isConnectionRequired()
354     {
355         return false;
356     }
357 
358     /***
359      * This method returns x number of ids for the given table.
360      *
361      * @param tableName The name of the table for which we want an id.
362      * @param numOfIdsToReturn The desired number of ids.
363      * @return A BigDecimal.
364      * @exception Exception Database error.
365      */
366     public synchronized BigDecimal[] getNextIds(String tableName,
367                                                 int numOfIdsToReturn)
368         throws Exception
369     {
370         return getNextIds(tableName, numOfIdsToReturn, null);
371     }
372 
373     /***
374      * This method returns x number of ids for the given table.
375      * Note this method does not require a Connection.
376      * If a Connection is needed one will be requested.
377      * To force the use of the passed in connection set the configuration
378      * property torque.idbroker.usenewconnection = false
379      *
380      * @param tableName The name of the table for which we want an id.
381      * @param numOfIdsToReturn The desired number of ids.
382      * @param connection A Connection.
383      * @return A BigDecimal.
384      * @exception Exception Database error.
385      */
386     public synchronized BigDecimal[] getNextIds(String tableName,
387                                                 int numOfIdsToReturn,
388                                                 Connection connection)
389         throws Exception
390     {
391         if (tableName == null)
392         {
393             throw new Exception("getNextIds(): tableName == null");
394         }
395 
396         // A note about the synchronization:  I (jmcnally) looked at
397         // the synchronized blocks to avoid thread issues that were
398         // being used in this and the storeId method.  I do not think
399         // they were being effective, so I synchronized the method.
400         // I have left the blocks that did exist commented in the code
401         // to make it easier for others to take a look, because it
402         // would be preferrable to avoid the synchronization on the
403         // method
404 
405         List availableIds = (List) ids.get(tableName);
406 
407         if (availableIds == null || availableIds.size() < numOfIdsToReturn)
408         {
409             if (availableIds == null)
410             {
411                 log.debug("Forced id retrieval - no available list");
412             }
413             else
414             {
415                 log.debug("Forced id retrieval - " + availableIds.size());
416             }
417             storeIDs(tableName, true, connection);
418             availableIds = (List) ids.get(tableName);
419         }
420 
421         int size = availableIds.size() < numOfIdsToReturn
422                 ? availableIds.size() : numOfIdsToReturn;
423 
424         BigDecimal[] results = new BigDecimal[size];
425 
426         // We assume that availableIds will always come from the ids
427         // Hashtable and would therefore always be the same object for
428         // a specific table.
429         //        synchronized (availableIds)
430         //        {
431         for (int i = size - 1; i >= 0; i--)
432         {
433             results[i] = (BigDecimal) availableIds.get(i);
434             availableIds.remove(i);
435         }
436         //        }
437 
438         return results;
439     }
440 
441     /***
442      * Describe <code>exists</code> method here.
443      *
444      * @param tableName a <code>String</code> value that is used to identify
445      * the row
446      * @return a <code>boolean</code> value
447      * @exception TorqueException if an error occurs
448      * @exception Exception a generic exception.
449      */
450     public boolean exists(String tableName)
451         throws TorqueException, Exception
452     {
453         String query = new StringBuffer(100)
454             .append("select ")
455             .append(TABLE_NAME)
456             .append(" where ")
457             .append(TABLE_NAME).append("='").append(tableName).append('\'')
458             .toString();
459 
460         boolean exists = false;
461         Connection dbCon = null;
462         try
463         {
464             String databaseName = tableMap.getDatabaseMap().getName();
465 
466             dbCon = Torque.getConnection(databaseName);
467             Statement statement = dbCon.createStatement();
468             ResultSet rs = statement.executeQuery(query);
469             exists = rs.next();
470             statement.close();
471         }
472         finally
473         {
474             // Return the connection to the pool.
475             try
476             {
477                 dbCon.close();
478             }
479             catch (Exception e)
480             {
481                 log.error("Release of connection failed.", e);
482             }
483         }
484         return exists;
485     }
486 
487     /***
488      * A background thread that tries to ensure that when someone asks
489      * for ids, that there are already some loaded and that the
490      * database is not accessed.
491      */
492     public void run()
493     {
494         log.debug("IDBroker thread was started.");
495 
496         Thread thisThread = Thread.currentThread();
497         while (houseKeeperThread == thisThread)
498         {
499             try
500             {
501                 Thread.sleep(SLEEP_PERIOD);
502             }
503             catch (InterruptedException exc)
504             {
505                 // ignored
506             }
507 
508             // logger.info("IDBroker thread checking for more keys.");
509             Iterator it = ids.keySet().iterator();
510             while (it.hasNext())
511             {
512                 String tableName = (String) it.next();
513                 if (log.isDebugEnabled())
514                 {
515                     log.debug("IDBroker thread checking for more keys "
516                             + "on table: " + tableName);
517                 }
518                 List availableIds = (List) ids.get(tableName);
519                 int quantity = getQuantity(tableName, null).intValue();
520                 if (quantity > availableIds.size())
521                 {
522                     try
523                     {
524                         // Second parameter is false because we don't
525                         // want the quantity to be adjusted for thread
526                         // calls.
527                         storeIDs(tableName, false, null);
528                         if (log.isDebugEnabled())
529                         {
530                             log.debug("Retrieved more ids for table: " + tableName);
531                         }
532                     }
533                     catch (Exception exc)
534                     {
535                         log.error("There was a problem getting new IDs "
536                                      + "for table: " + tableName, exc);
537                     }
538                 }
539             }
540         }
541         log.debug("IDBroker thread finished.");
542     }
543 
544     /***
545      * Shuts down the IDBroker thread.
546      *
547      * Calling this method stops the thread that was started for this
548      * instance of the IDBroker. This method should be called during
549      * MapBroker Service shutdown.
550      */
551     public void stop()
552     {
553         houseKeeperThread = null;
554     }
555 
556     /***
557      * Check the frequency of retrieving new ids from the database.
558      * If the frequency is high then we increase the amount (i.e.
559      * quantity column) of ids retrieved on each access.  Tries to
560      * alter number of keys grabbed so that IDBroker retrieves a new
561      * set of ID's prior to their being needed.
562      *
563      * @param tableName The name of the table for which we want an id.
564      */
565     private void checkTiming(String tableName)
566     {
567         // Check if quantity changing is switched on.
568         // If prefetch is turned off, changing quantity does not make sense
569         if (!configuration.getBoolean(DB_IDBROKER_CLEVERQUANTITY, true)
570             || !configuration.getBoolean(DB_IDBROKER_PREFETCH, true))
571         {
572             return;
573         }
574 
575         // Get the last id request for this table.
576         java.util.Date lastTime = (java.util.Date) lastQueryTime.get(tableName);
577         java.util.Date now = new java.util.Date();
578 
579         if (lastTime != null)
580         {
581             long thenLong = lastTime.getTime();
582             long nowLong = now.getTime();
583             int timeLapse = (int) (nowLong - thenLong);
584             if (timeLapse < SLEEP_PERIOD && timeLapse > 0)
585             {
586                 if (log.isDebugEnabled())
587                 {
588                     log.debug("Unscheduled retrieval of more ids for table: "
589                             + tableName);
590                 }
591                 // Increase quantity, so that hopefully this does not
592                 // happen again.
593                 float rate = getQuantity(tableName, null).floatValue()
594                     / (float) timeLapse;
595                 quantityStore.put(tableName, new BigDecimal(
596                     Math.ceil(SLEEP_PERIOD * rate * SAFETY_MARGIN)));
597             }
598         }
599         lastQueryTime.put(tableName, now);
600     }
601 
602     /***
603      * Grabs more ids from the id_table and stores it in the ids
604      * Hashtable.  If adjustQuantity is set to true the amount of id's
605      * retrieved for each call to storeIDs will be adjusted.
606      *
607      * @param tableName The name of the table for which we want an id.
608      * @param adjustQuantity True if amount should be adjusted.
609      * @param connection a Connection
610      * @exception Exception a generic exception.
611      */
612     private void storeIDs(String tableName,
613                           boolean adjustQuantity,
614                           Connection connection)
615         throws Exception
616     {
617         BigDecimal nextId = null;
618         BigDecimal quantity = null;
619         DatabaseMap dbMap = tableMap.getDatabaseMap();
620 
621         // Block on the table.  Multiple tables are allowed to ask for
622         // ids simultaneously.
623         //        TableMap tMap = dbMap.getTable(tableName);
624         //        synchronized(tMap)  see comment in the getNextIds method
625         //        {
626         if (adjustQuantity)
627         {
628             checkTiming(tableName);
629         }
630 
631         boolean useNewConnection = (connection == null) || (configuration
632                 .getBoolean(DB_IDBROKER_USENEWCONNECTION, true));
633         try
634         {
635             if (useNewConnection)
636             {
637                 connection = Transaction.beginOptional(dbMap.getName(),
638                     transactionsSupported);
639             }
640 
641             // Write the current value of quantity of keys to grab
642             // to the database, primarily to obtain a write lock
643             // on the table/row, but this value will also be used
644             // as the starting value when an IDBroker is
645             // instantiated.
646             quantity = getQuantity(tableName, connection);
647             updateQuantity(connection, tableName, quantity);
648 
649             // Read the next starting ID from the ID_TABLE.
650             BigDecimal[] results = selectRow(connection, tableName);
651             nextId = results[0]; // NEXT_ID column
652 
653             // Update the row based on the quantity in the
654             // ID_TABLE.
655             BigDecimal newNextId = nextId.add(quantity);
656             updateNextId(connection, tableName, newNextId.toString());
657 
658             if (useNewConnection)
659             {
660                 Transaction.commit(connection);
661             }
662         }
663         catch (Exception e)
664         {
665             if (useNewConnection)
666             {
667                 Transaction.rollback(connection);
668             }
669             throw e;
670         }
671 
672         List availableIds = (List) ids.get(tableName);
673         if (availableIds == null)
674         {
675             availableIds = new ArrayList();
676             ids.put(tableName, availableIds);
677         }
678 
679         // Create the ids and store them in the list of available ids.
680         int numId = quantity.intValue();
681         for (int i = 0; i < numId; i++)
682         {
683             availableIds.add(nextId);
684             nextId = nextId.add(ONE);
685         }
686         //        }
687     }
688 
689     /***
690      * This method allows you to get the number of ids that are to be
691      * cached in memory.  This is either stored in quantityStore or
692      * read from the db. (ie the value in ID_TABLE.QUANTITY).
693      *
694      * Though this method returns a BigDecimal for the quantity, it is
695      * unlikey the system could withstand whatever conditions would lead
696      * to really needing a large quantity, it is retrieved as a BigDecimal
697      * only because it is going to be added to another BigDecimal.
698      *
699      * @param tableName The name of the table we want to query.
700      * @param connection a Connection
701      * @return An int with the number of ids cached in memory.
702      */
703     private BigDecimal getQuantity(String tableName, Connection connection)
704     {
705         BigDecimal quantity = null;
706 
707         // If prefetch is turned off we simply return 1
708         if (!configuration.getBoolean(DB_IDBROKER_PREFETCH, true))
709         {
710             quantity = new BigDecimal(1);
711         }
712         // Initialize quantity, if necessary.
713         else if (quantityStore.containsKey(tableName))
714         {
715             quantity = (BigDecimal) quantityStore.get(tableName);
716         }
717         else
718         {
719             Connection dbCon = null;
720             try
721             {
722                 if (connection == null || configuration
723                     .getBoolean(DB_IDBROKER_USENEWCONNECTION, true))
724                 {
725                     String databaseName = tableMap.getDatabaseMap().getName();
726                     // Get a connection to the db
727                     dbCon = Torque.getConnection(databaseName);
728                 }
729 
730                 // Read the row from the ID_TABLE.
731                 BigDecimal[] results = selectRow(dbCon, tableName);
732 
733                 // QUANTITY column.
734                 quantity = results[1];
735                 quantityStore.put(tableName, quantity);
736             }
737             catch (Exception e)
738             {
739                 quantity = new BigDecimal(10);
740             }
741             finally
742             {
743                 // Return the connection to the pool.
744                 try
745                 {
746                     dbCon.close();
747                 }
748                 catch (Exception e)
749                 {
750                     log.error("Release of connection failed.", e);
751                 }
752             }
753         }
754         return quantity;
755     }
756 
757     /***
758      * Helper method to select a row in the ID_TABLE.
759      *
760      * @param con A Connection.
761      * @param tableName The properly escaped name of the table to
762      * identify the row.
763      * @return A BigDecimal[].
764      * @exception Exception a generic exception.
765      */
766     private BigDecimal[] selectRow(Connection con, String tableName)
767         throws Exception
768     {
769         StringBuffer stmt = new StringBuffer();
770         stmt.append("SELECT ")
771             .append(COL_NEXT_ID)
772             .append(", ")
773             .append(COL_QUANTITY)
774             .append(" FROM ")
775             .append(ID_TABLE)
776             .append(" WHERE ")
777             .append(COL_TABLE_NAME)
778             .append(" = '")
779             .append(tableName)
780             .append('\'');
781 
782         Statement statement = null;
783 
784         BigDecimal[] results = new BigDecimal[2];
785         try
786         {
787             statement = con.createStatement();
788             ResultSet rs = statement.executeQuery(stmt.toString());
789 
790             if (rs.next())
791             {
792                 // work around for MySQL which appears to support
793                 // getBigDecimal in the source code, but the binary
794                 // is throwing an NotImplemented exception.
795                 results[0] = new BigDecimal(rs.getString(1)); // next_id
796                 results[1] = new BigDecimal(rs.getString(2)); // quantity
797             }
798             else
799             {
800                 throw new TorqueException("The table " + tableName
801                         + " does not have a proper entry in the " + ID_TABLE);
802             }
803         }
804         finally
805         {
806             if (statement != null)
807             {
808                 statement.close();
809             }
810         }
811 
812         return results;
813     }
814 
815     /***
816      * Helper method to update a row in the ID_TABLE.
817      *
818      * @param con A Connection.
819      * @param tableName The properly escaped name of the table to identify the
820      * row.
821      * @param id An int with the value to set for the id.
822      * @exception Exception Database error.
823      */
824     private void updateNextId(Connection con, String tableName, String id)
825         throws Exception
826     {
827 
828 
829         StringBuffer stmt = new StringBuffer(id.length()
830                                              + tableName.length() + 50);
831         stmt.append("UPDATE " + ID_TABLE)
832             .append(" SET ")
833             .append(COL_NEXT_ID)
834             .append(" = ")
835             .append(id)
836             .append(" WHERE ")
837             .append(COL_TABLE_NAME)
838             .append(" = '")
839             .append(tableName)
840             .append('\'');
841 
842         Statement statement = null;
843 
844         if (log.isDebugEnabled())
845         {
846             log.debug("updateNextId: " + stmt.toString());
847         }
848 
849         try
850         {
851             statement = con.createStatement();
852             statement.executeUpdate(stmt.toString());
853         }
854         finally
855         {
856             if (statement != null)
857             {
858                 statement.close();
859             }
860         }
861     }
862 
863     /***
864      * Helper method to update a row in the ID_TABLE.
865      *
866      * @param con A Connection.
867      * @param tableName The properly escaped name of the table to identify the
868      * row.
869      * @param quantity An int with the value of the quantity.
870      * @exception Exception Database error.
871      */
872     private void updateQuantity(Connection con, String tableName,
873                                 BigDecimal quantity)
874         throws Exception
875     {
876         StringBuffer stmt = new StringBuffer(quantity.toString().length()
877                                              + tableName.length() + 50);
878         stmt.append("UPDATE ")
879             .append(ID_TABLE)
880             .append(" SET ")
881             .append(COL_QUANTITY)
882             .append(" = ")
883             .append(quantity)
884             .append(" WHERE ")
885             .append(COL_TABLE_NAME)
886             .append(" = '")
887             .append(tableName)
888             .append('\'');
889 
890         Statement statement = null;
891 
892         if (log.isDebugEnabled())
893         {
894             log.debug("updateQuantity: " + stmt.toString());
895         }
896 
897         try
898         {
899             statement = con.createStatement();
900             statement.executeUpdate(stmt.toString());
901         }
902         finally
903         {
904             if (statement != null)
905             {
906                 statement.close();
907             }
908         }
909     }
910 }