View Javadoc

1   package org.sourceforge.vlibrary.user.dao;
2   
3   
4   import java.sql.Timestamp;
5   import java.sql.Types;
6   import java.util.ArrayList;
7   import java.util.Date;
8   import java.util.Iterator;
9   import java.util.List;
10  import java.util.Locale;
11  import java.util.Map;
12  
13  import org.apache.log4j.Logger;
14  import org.sourceforge.vlibrary.Constants;
15  import org.sourceforge.vlibrary.exceptions.LibraryException;
16  import org.sourceforge.vlibrary.user.valuebeans.LibraryTransaction;
17  import org.sourceforge.vlibrary.util.Utils;
18  import org.springframework.context.support.ResourceBundleMessageSource;
19  import org.springframework.jdbc.core.JdbcTemplate;
20  
21  import edu.emory.mathcs.backport.java.util.Arrays;
22  
23  
24  /**
25   * @version $Revision$ $Date$
26   *
27   *
28   */
29  public class LibraryTransactionDAO {
30  
31      /** log4j Logger */
32      private static Logger logger =
33              Logger.getLogger(LibraryTransactionDAO.class.getName());
34  
35      private ResourceBundleMessageSource resourceBundleMessageSource;
36      private JdbcTemplate jdbcTemplate;
37      private String selectTransactionBytransactionIdReaderIdBookIdSQL;
38      private String insertReaderBookActionTransactionSQL;
39      private String deleteReaderBookRequestActionTransactionSQL;
40      private String selectTransactionBookIdNotActionIdSQL;
41      private String selectReaderByBookIdActionIdSQL;
42      private String selectAllBookTransactionByBookIdSQL;
43      private String getLastLocationSQL;
44      private String getCopiesSQL;
45      private String selectReaderISBNActionIdLocationIdSQL;
46  
47      /**
48       * Used for Spring Dependency Injection
49       */
50      public void setSelectAllBookTransactionByBookIdSQL(
51              String selectAllBookTransactionByBookIdSQL) {
52          this.selectAllBookTransactionByBookIdSQL =
53                  selectAllBookTransactionByBookIdSQL;
54      }
55  
56      /**
57       * Used for Spring Dependency Injection
58       */
59      public void setSelectReaderByBookIdActionIdSQL(
60              String selectReaderByBookIdActionIdSQL) {
61          this.selectReaderByBookIdActionIdSQL =
62                  selectReaderByBookIdActionIdSQL;
63      }
64  
65      /**
66       * Used for Spring Dependency Injection
67       */
68      public void setSelectTransactionBookIdActionIdLocationIdSQL(
69              String selectTransactionBookIdActionIdLocationIdSQL) {
70      }
71  
72      /**
73       * Used for Spring Dependency Injection
74       */
75      public void setSelectTransactionBookIdNotActionIdSQL(
76              String selectTransactionBookIdNotActionIdSQL) {
77          this.selectTransactionBookIdNotActionIdSQL =
78                  selectTransactionBookIdNotActionIdSQL;
79      }
80  
81      /**
82       * Used for Spring Dependency Injection
83       */
84      public void setDeleteReaderBookRequestActionTransactionSQL(
85              String deleteReaderBookRequestActionTransactionSQL) {
86          this.deleteReaderBookRequestActionTransactionSQL =
87                  deleteReaderBookRequestActionTransactionSQL;
88      }
89  
90      /**
91       * Used for Spring Dependency Injection
92       */
93      public void setSelectTransactionBytransactionIdReaderIdBookIdSQL(
94              String selectTransactionBytransactionIdReaderIdBookIdSQL) {
95          this.selectTransactionBytransactionIdReaderIdBookIdSQL =
96                  selectTransactionBytransactionIdReaderIdBookIdSQL;
97      }
98  
99      /**
100      * Used for Spring Dependency Injection
101      */
102     public void setInsertReaderBookActionTransactionSQL(
103             String insertReaderBookActionTransactionSQL) {
104         this.insertReaderBookActionTransactionSQL =
105                 insertReaderBookActionTransactionSQL;
106     }
107 
108     /**
109      * Used for Spring Dependency Injection
110      * @param jdbcTemplate The jdbcTemplate to set.
111      */
112     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
113         this.jdbcTemplate = jdbcTemplate;
114     }
115 
116     /**
117      * @param getLastLocationSQL the getLastLocationSQL to set
118      */
119     public void setGetLastLocationSQL(String getLastLocationSQL) {
120         this.getLastLocationSQL = getLastLocationSQL;
121     }
122 
123     /**
124      * @param getCopiesSQL the getCopiesSQL to set
125      */
126     public void setGetCopiesSQL(String getCopiesSQL) {
127         this.getCopiesSQL = getCopiesSQL;
128     }
129 
130     /**
131      * Used for Spring Dependency Injection
132      */
133     public void setResourceBundleMessageSource(
134             ResourceBundleMessageSource resourceBundleMessageSource) {
135         this.resourceBundleMessageSource = resourceBundleMessageSource;
136     }
137 
138     /**
139      * @param selectReaderISBNActionIdLocationIdSQL the selectReaderISBNActionIdLocationIdSQL to set
140      */
141     public void setSelectReaderISBNActionIdLocationIdSQL(String selectReaderISBNActionIdLocationIdSQL) {
142         this.selectReaderISBNActionIdLocationIdSQL = selectReaderISBNActionIdLocationIdSQL;
143     }
144 
145     public LibraryTransactionDAO() {
146     }
147 
148     /**
149      * Retrieves a LibraryTransaction using its transaction id.
150      * Fills in reader, book data by joining with reader, book tables.
151      *
152      * @param id the ID of the transaction to look up
153      * @throws LibraryException if there is a problem looking
154      * up the transaction or the transaction does not exist
155      */
156     @SuppressWarnings("unchecked")
157     public LibraryTransaction retrieve(long id)
158             throws LibraryException {
159         final LibraryTransaction libTrans = new LibraryTransaction();
160 
161         if (logger.isDebugEnabled()) {
162             logger.debug(
163                     resourceBundleMessageSource.getMessage("trans.retrieve",
164                             new Object[] {new Long( id )}, Locale.US));
165         }
166 
167         List<Map<String, Object>> rows;
168         try {
169             rows = jdbcTemplate.queryForList(
170                     selectTransactionBytransactionIdReaderIdBookIdSQL,
171                     new Object[] {new Long( id )});
172         } catch (final Exception se) {
173             final String errString = resourceBundleMessageSource.getMessage(
174                     "error.trans.retrieve", null, Locale.US);
175             logger.error(errString,se);
176             throw new LibraryException(errString,se);
177         }
178 
179         final Iterator<Map<String, Object>> it = rows.iterator();
180         if (it.hasNext()) {
181             final Map<String, Object> record = it.next();
182 
183             // Set fields
184             libTrans.setId(((Integer) Utils.getIgnoreCase(
185                     record, "ID")).longValue());
186             libTrans.setBook(((Integer) Utils.getIgnoreCase(
187                     record, "BOOK")).longValue());
188             libTrans.setReader(((Integer) Utils.getIgnoreCase(
189                     record, "READER")).longValue());
190             libTrans.setBookTitle((String) Utils.getIgnoreCase(
191                     record, "TITLE"));
192             libTrans.setReaderName(
193                     (String) Utils.getIgnoreCase(record, "FIRSTNAME") +
194                     " " + (String) Utils.getIgnoreCase(record, "LASTNAME"));
195             libTrans.setReaderPhone(
196                     (String) Utils.getIgnoreCase(record, "DESK_PHONE"));
197             libTrans.setTransTime(
198                     (Timestamp) Utils.getIgnoreCase(record, "TRANS_TIME"));
199             libTrans.setTimeString(libTrans.getTransTime().toString());
200             libTrans.setAction(((Integer) Utils.getIgnoreCase(
201                     record, "ACTION")).longValue());
202             libTrans.setLocation(((Integer) Utils.getIgnoreCase(
203                     record, "LOCATION")).longValue());
204             libTrans.setLocationDescription(
205                     (String) Utils.getIgnoreCase(record, "DESCRIPTION"));
206             if (libTrans.getAction() == Constants.CHECKIN_ACTION) {
207                 libTrans.setActionString(
208                         resourceBundleMessageSource.getMessage("trans.checkin.label",
209                                 null, Locale.US));
210             } else if (libTrans.getAction() == Constants.CHECKOUT_ACTION) {
211                 libTrans.setActionString(
212                         resourceBundleMessageSource.getMessage("trans.checkout.label",
213                                 null, Locale.US));
214             } else if (libTrans.getAction() == Constants.REQUEST_ACTION) {
215                 libTrans.setActionString(
216                         resourceBundleMessageSource.getMessage( "trans.request.label",
217                                 null, Locale.US));
218             } else {
219                 final String errString =
220                         resourceBundleMessageSource.getMessage( "error.trans.bad.action",
221                                 new Object[] { (new Long(libTrans.getAction())).toString()},
222                                 Locale.US);
223                 logger.error(errString);
224                 throw new LibraryException(errString);
225             }
226         } else {  //Transaction, reader or book does not exist
227             final String errString =
228                     resourceBundleMessageSource.getMessage( "error.trans.retrieve",
229                             new Object[] {new Long(id)}, Locale.US);
230             logger.error(errString);
231             throw new LibraryException(errString);
232         }
233 
234         if (logger.isDebugEnabled()) {
235             logger.debug(resourceBundleMessageSource.getMessage(
236                     "trans.retrieve.successful",
237                     new Object[] {libTrans.toString()}, Locale.US));
238         }
239 
240         return libTrans;
241     }
242 
243 
244     /**
245      * Records a book checkin in the transaction table. Does not send
246      * notifications.
247      *
248      * @param book the id of the book being checked in
249      * @param reader the id of the reader checking in the book
250      * @param location the id of the location of the transaction
251      * @exception LibraryException if an error occurs updating the transaction
252      * table
253      */
254     public void processCheckin(long reader, long book, long location)
255             throws LibraryException {
256         if (logger.isDebugEnabled()) {
257             final String message = resourceBundleMessageSource.getMessage(
258                     "trans.checkin.inserting",
259                     new Object[] {(new Long(reader)).toString(),
260                             (new Long(book)).toString(), Long.toString(location)}, Locale.US);
261             logger.debug(message);
262         }
263 
264         try {
265             jdbcTemplate.update(insertReaderBookActionTransactionSQL,
266                     new Object [] {
267                             new Long(reader),
268                             new Long(book),
269                             new Long(Constants.CHECKIN_ACTION),
270                             new Timestamp((new Date()).getTime()),
271                             new Long(location)
272             },
273                     new int[] {
274                             Types.INTEGER,
275                             Types.INTEGER,
276                             Types.INTEGER,
277                             Types.TIMESTAMP,
278                             Types.INTEGER
279             }
280                     );
281         } catch (final Exception se) {
282             final String errString =
283                     resourceBundleMessageSource.getMessage("error.trans.checkin",
284                             new Object[] {  (new Long(reader)).toString(),
285                                     (new Long(book)).toString()}, Locale.US);
286             logger.error(errString,se);
287             throw new LibraryException(errString,se);
288         }
289 
290         if (logger.isDebugEnabled()) {
291             logger.debug( resourceBundleMessageSource.getMessage(
292                     "trans.checkin.inserted",
293                     new Object[] {  (new Long(reader)).toString(),
294                             (new Long(book)).toString()},Locale.US));
295         }
296     }
297 
298     /**
299      * <p>Records a book checkout in the transaction table. Inserts a
300      * checkout transaction and removes the pending request. Does not
301      * send notifications.
302      * </p>
303      * <p>If the book is not checked in, throws <code>LibraryException</code>.
304      * </p>
305      *
306      * @param book the id of the book being checked out
307      * @param reader the id of the reader checking out the book
308      * @param location the id of the location of the transaction
309      * @param isbn of the book
310      * @exception LibraryException if the book is not checked in, or an
311      * error occurs recording the transaction
312      */
313     public void processCheckout(long reader, long book, String isbn, long location)
314             throws LibraryException {
315         if (logger.isDebugEnabled()) {
316             final String message = resourceBundleMessageSource.getMessage(
317                     "trans.checking.out",
318                     new Object[] {(new Long(reader)).toString(),
319                             (new Long(book)).toString()}, Locale.US);
320             logger.debug(message);
321         }
322 
323         final long possessor = getPossessor(book);
324 
325         // if the book is not checked-in, throw
326         if (possessor != Constants.CHECKED_IN) {
327             String errString = null;
328             if (possessor == Constants.NO_TRANSACTIONS) {
329                 errString =
330                         resourceBundleMessageSource.getMessage(
331                                 "error.trans.checkout.checkin.missing",
332                                 new Object[] {
333                                         (new Long(book)).toString()},
334                                 Locale.US);
335             } else { // must be checked out
336                 errString =
337                         resourceBundleMessageSource.getMessage(
338                                 "error.trans.book.already.checkout",
339                                 new Object[] {
340                                         (new Long(book)).toString(),
341                                         (new Long(possessor)).toString() },
342                                 Locale.US);
343             }
344             logger.error(errString);
345             throw new LibraryException(errString);
346         }
347 
348         // Record the checkout
349         try {
350             jdbcTemplate.update(insertReaderBookActionTransactionSQL,
351                     new Object [] {
352                             new Long(reader),
353                             new Long(book),
354                             new Long(Constants.CHECKOUT_ACTION),
355                             new Timestamp((new Date()).getTime()),
356                             new Long(location)
357             },
358                     new int[] {
359                             Types.INTEGER,
360                             Types.INTEGER,
361                             Types.INTEGER,
362                             Types.TIMESTAMP,
363                             Types.INTEGER
364             }
365                     );
366         } catch (final Exception se) {
367             final String errString =
368                     resourceBundleMessageSource.getMessage(
369                             "error.trans.checkout",
370                             new Object[] {  (new Long(reader)).toString(),
371                                     (new Long(book)).toString()},
372                             Locale.US);
373             logger.error(errString,se);
374             throw new LibraryException(errString,se);
375         }
376 
377         // Delete any pending requests for this <isbn,reader> pair
378         try {
379             jdbcTemplate.update(deleteReaderBookRequestActionTransactionSQL,
380                     new Object [] {
381                             new Long(reader),
382                             isbn,
383                             new Long(Constants.REQUEST_ACTION)
384             },
385                     new int[] {Types.INTEGER, Types.VARCHAR, Types.INTEGER}
386                     );
387         } catch (final Exception se) {
388             final String errString =
389                     resourceBundleMessageSource.getMessage(
390                             "error.trans.checkout",
391                             new Object[] {  (new Long(reader)).toString(),
392                                     (new Long(book)).toString()},Locale.US);
393             logger.error(errString,se);
394             throw new LibraryException(errString,se);
395         }
396 
397         if (logger.isDebugEnabled()) {
398             logger.debug(resourceBundleMessageSource.getMessage(
399                     "trans.checkout.inserted",
400                     new Object[] {(new Long(book)).toString(),
401                             (new Long(reader)).toString()}, Locale.US));
402         }
403     }
404 
405     /**
406      * <p>Queries the transaction table to determine the whereabouts of a book.
407      * </p>
408      * <p>Returns the id of the reader who is currently holding the book.
409      * If the book is "checked in", returns CHECKED_IN. If there are no
410      * transactions associated with the book, returns NO_TRANSACTIONS.
411      * </p>
412      *
413      * @param book the id of the book being sought
414      * @throws LibraryException if the book does not exist
415      * or an SQL processing error occurs
416      */
417     public long getPossessor(long book) throws LibraryException {
418 
419         long ret = Constants.NO_TRANSACTIONS;
420 
421         if (logger.isDebugEnabled()) {
422             logger.debug( resourceBundleMessageSource.getMessage(
423                     "trans.finding.possessor",
424                     new Object[] {new Long(book)}, Locale.US));
425         }
426 
427         List rows;
428 
429         // Find the most recent transaction for this book that is not a request
430         try {
431             rows = jdbcTemplate.queryForList(
432                     selectTransactionBookIdNotActionIdSQL,
433                     new Object[] {
434                             new Long(book),
435                             new Long(Constants.REQUEST_ACTION)});
436         } catch (final Exception se) {
437             final String errString = resourceBundleMessageSource.getMessage(
438                     "error.trans.book.last.retrieving",
439                     new Object[] {new Long(book)}, Locale.US);
440             logger.error(errString,se);
441             throw new LibraryException(errString,se);
442         }
443 
444         final Iterator it = rows.iterator();
445         if (it.hasNext()) {
446             final Map record = (Map) it.next();
447 
448             final long action = ((Integer) Utils.getIgnoreCase(record,"ACTION")).longValue();
449             if (action == Constants.CHECKIN_ACTION) {
450                 // Book has been dropped off, awaiting pickup
451                 ret = Constants.CHECKED_IN;
452             } else {
453                 // Must be a checkout transaction, so reader is possessor
454                 ret = ((Integer) Utils.getIgnoreCase(record,"READER")).longValue();
455             }
456         }
457 
458         if (logger.isDebugEnabled()) {
459             logger.debug( resourceBundleMessageSource.getMessage(
460                     "trans.possessor.found",
461                     new Object[] {new Long(book), new Long(ret)},
462                     Locale.US));
463         }
464 
465         return ret;
466     }
467 
468     /**
469      * Records book request transaction.
470      * @param book = the id of the book being requested
471      * @param reader = the id of the reader making the request
472      *
473      * @throws LibraryException if an error occurs updating the database
474      */
475     public void processRequest(long reader,long book, long location) throws LibraryException {
476         if (logger.isDebugEnabled()) {
477             final String message = resourceBundleMessageSource.getMessage(
478                     "trans.requesting",
479                     new Object[] {(new Long(reader)).toString(),
480                             (new Long(book)).toString()}, Locale.US);
481             logger.debug(message);
482         }
483 
484         // Record the request
485         try {
486             jdbcTemplate.update(insertReaderBookActionTransactionSQL,
487                     new Object [] {
488                             new Long(reader),
489                             new Long(book),
490                             new Long(Constants.REQUEST_ACTION),
491                             new Timestamp((new Date()).getTime()),
492                             new Long(location)
493             },
494                     new int[] {Types.INTEGER, Types.INTEGER, Types.INTEGER,
495                             Types.TIMESTAMP, Types.INTEGER}
496                     );
497         } catch (final Exception se) {
498             final String errString =
499                     resourceBundleMessageSource.getMessage(
500                             "error.trans.request",
501                             new Object[] {  (new Long(reader)).toString(),
502                                     (new Long(book)).toString()}, Locale.US);
503             logger.error(errString,se);
504             throw new LibraryException(errString,se);
505         }
506 
507         if (logger.isDebugEnabled()) {
508             logger.debug( resourceBundleMessageSource.getMessage(
509                     "trans.request.inserted",
510                     new Object[] {(new Long(book)).toString(),
511                             (new Long(reader)).toString()}, Locale.US));
512         }
513     }
514 
515     /**
516      * Retrieves a List of requesters for a book (identified by isbn) in a given location.
517      * Returns an empty list if there are no pending requests for the book in the given location.
518      *
519      * @param isbn the isbn of the book being requested
520      * @param location the id of the location
521      * @throws LibraryException if an error occurs accessing the database
522      */
523     public List<Long> getRequestors(String isbn, long location)  throws LibraryException {
524         final ArrayList<Long> outList = new ArrayList<Long>();
525         if (logger.isDebugEnabled()) {
526             logger.debug( resourceBundleMessageSource.getMessage(
527                     "trans.finding.requestors",
528                     new Object[] {isbn}, Locale.US));
529         }
530 
531         // Get the ISBN for the book
532 
533 
534         List rows;
535         try {
536             rows = jdbcTemplate.queryForList(
537                     selectReaderISBNActionIdLocationIdSQL,
538                     new Object[] {isbn,
539                             new Long(Constants.REQUEST_ACTION),
540                             new Long(location)});
541         } catch (final Exception se) {
542             final String errString = resourceBundleMessageSource.getMessage(
543                     "error.trans.finding.requestors",
544                     new Object[] {isbn}, Locale.US);
545             logger.error(errString,se);
546             throw new LibraryException(errString,se);
547         }
548 
549         final Iterator it = rows.iterator();
550         while(it.hasNext()) {
551             final Map record = (Map) it.next();
552             final Long reader =  new Long(
553                     ((Integer) Utils.getIgnoreCase(record,"ID")).longValue());
554             outList.add(reader);
555         }
556 
557         if (logger.isDebugEnabled()) {
558             logger.debug(resourceBundleMessageSource.getMessage(
559                     "trans.requestors.found",
560                     new Object[] { isbn, outList}, Locale.US));
561         }
562 
563         return outList;
564     }
565 
566     /**
567      * Returns whether a reader has a pending request for a particular book (id)
568      *
569      * @param book = the id of the book
570      * @param reader = the id of the reader
571      * @throws LibraryException
572      * @return true if reader has a pending request for book; false otherwise
573      */
574     public boolean requestPending(long reader,long book)
575             throws LibraryException {
576 
577         if (logger.isDebugEnabled()) {
578             logger.debug( resourceBundleMessageSource.getMessage(
579                     "trans.finding.request",
580                     new Object[] {new Long(book), new Long(reader)}, Locale.US));
581         }
582 
583         List rows;
584         try {
585             rows = jdbcTemplate.queryForList(selectReaderByBookIdActionIdSQL,
586                     new Object[] {new Long(reader), new Long(book),
587                             new Long( Constants.REQUEST_ACTION )});
588         } catch (final Exception se) {
589             final String errString = resourceBundleMessageSource.getMessage(
590                     "error.trans.finding.request",
591                     new Object[] {new Long(book), new Long(reader)}, Locale.US);
592             logger.error(errString,se);
593             throw new LibraryException(errString,se);
594         }
595 
596         final Iterator it = rows.iterator();
597         boolean result = false;
598         if (it.hasNext()) {
599             result = true;
600         }
601 
602         if (logger.isDebugEnabled()) {
603             logger.debug(resourceBundleMessageSource.getMessage(
604                     "trans.requestors.found",
605                     new Object[] {new Long(book),
606                             new Long(reader),
607                             Boolean.valueOf(result)}, Locale.US));
608         }
609 
610         return result;
611     }
612 
613     /**
614      * Cancels a pending request by a reader for a book
615      *
616      * @param isbn = the isbn of the book being requested
617      * @param reader = the id of the reader making the request
618      * @throws LibraryException
619      */
620     public void cancelRequest(long reader, String isbn) throws LibraryException {
621         if (logger.isDebugEnabled()) {
622             final String message = resourceBundleMessageSource.getMessage(
623                     "trans.deleting",
624                     new Object[] {(new Long(reader)).toString(),
625                             isbn}, Locale.US);
626             logger.debug(message);
627         }
628 
629         try {
630             jdbcTemplate.update(deleteReaderBookRequestActionTransactionSQL,
631                     new Object [] {
632                             new Long(reader),
633                             isbn,
634                             new Long(Constants.REQUEST_ACTION)
635             },
636                     new int[] {Types.INTEGER, Types.INTEGER, Types.INTEGER}
637                     );
638         } catch (final Exception se) {
639             final String errString =
640                     resourceBundleMessageSource.getMessage("error.trans.delete",
641                             new Object[] {(new Long(reader)).toString(),
642                                     isbn}, Locale.US);
643             logger.error(errString,se);
644             throw new LibraryException(errString,se);
645         }
646 
647         if (logger.isDebugEnabled()) {
648             logger.debug( resourceBundleMessageSource.getMessage(
649                     "trans.delete.successful",
650                     new Object[] {(new Long(reader)).toString(),
651                             isbn}, Locale.US));
652         }
653     }
654 
655 
656 
657     /**
658      * Gets the entire transaction history for a book and returns
659      * a List of LibraryTransaction objects or empty if none found
660      *
661      * @param book = id of the book to retrieve transactions for
662      * @exception LibraryException
663      */
664     @SuppressWarnings("unchecked")
665     public List getTransactions(long book) throws LibraryException {
666         if (logger.isDebugEnabled()) {
667             logger.debug( resourceBundleMessageSource.getMessage(
668                     "trans.book.all.retrieving",
669                     new Object[] { (new Long(book)).toString()}, Locale.US));
670         }
671 
672         final ArrayList<LibraryTransaction> outList = new ArrayList<LibraryTransaction>();
673         List<Map<String, Object>> rows;
674         try {
675             rows = jdbcTemplate.queryForList(
676                     selectAllBookTransactionByBookIdSQL,
677                     new Object [] {new Long(book)});
678         } catch (final Exception se) {
679             final String errString =
680                     resourceBundleMessageSource.getMessage(
681                             "error.trans.book.all.retrieving",
682                             new Object[] {(new Long(book)).toString()}, Locale.US);
683             logger.error(errString,se);
684             throw new LibraryException(errString,se);
685         }
686 
687         final Iterator<Map<String, Object>> it = rows.iterator();
688         while (it.hasNext()) {
689             // Walk resultset, adding to outlist
690             final Map<String, Object> record = it.next();
691             LibraryTransaction trans = new LibraryTransaction();
692             // Set fields
693             trans = retrieve(((Integer) Utils.getIgnoreCase(
694                     record,"ID")).longValue());
695             outList.add(trans);
696         }
697 
698         if (logger.isDebugEnabled()) {
699             logger.debug( resourceBundleMessageSource.getMessage(
700                     "trans.book.all.retrieving.successful",
701                     new Object[] {(new Long(book)).toString(), outList},
702                     Locale.US));
703         }
704 
705         return outList;
706     }
707 
708     /**
709      * Queries the transaction table to determine the last location where a book
710      * was checked in or out.  Returns the id of the location or a negative value
711      * (Constants.UNKNOWN_LOCATION) if the book has never been checked in or out.
712      *
713      * @param book
714      * @return location id of last known location of the book
715      */
716     public long getLastLocation(long book) throws LibraryException {
717         if (logger.isDebugEnabled()) {
718             logger.debug( resourceBundleMessageSource.getMessage(
719                     "trans.finding.location",
720                     new Object[] {Long.toString(book)}, Locale.US));
721         }
722         List rows;
723         try {
724             rows = jdbcTemplate.queryForList(getLastLocationSQL, new Object [] {new Long(book)});
725         } catch (final Exception se) {
726             final String errString =
727                     resourceBundleMessageSource.getMessage(
728                             "error.trans.book.last.location.retrieving",
729                             new Object[] {Long.toString(book)}, Locale.US);
730             logger.error(errString,se);
731             throw new LibraryException(errString,se);
732         }
733         if (rows.isEmpty()) {
734             return Constants.UNKNOWN_LOCATION;
735         } else {
736             final Map record = (Map) rows.get(0);
737             final long out =  ((Integer) Utils.getIgnoreCase(
738                     record,"location")).longValue();
739             if (logger.isDebugEnabled()) {
740                 logger.debug( resourceBundleMessageSource.getMessage(
741                         "trans.book.last.location.retrieved.successfully",
742                         new Object[] {Long.toString(book), Long.toString(out)},
743                         Locale.US));
744             }
745             return out;
746         }
747     }
748 
749     /**
750      * Returns a list of ids of copies of the book with the given isbn
751      * whose last known location is the given location. Returns an empty
752      * array if there are no such books.
753      *
754      * @param isbn ISBN of the book being sought
755      * @return array of book IDs
756      */
757     public long[] getCopies(String isbn, long location) throws LibraryException {
758         if (logger.isDebugEnabled()) {
759             logger.debug( resourceBundleMessageSource.getMessage(
760                     "trans.finding.copies",
761                     new Object[] {isbn, Long.toString(location)}, Locale.US));
762         }
763         List rows;
764         try {
765             rows = jdbcTemplate.queryForList(getCopiesSQL, new Object [] {new Long(location), isbn});
766         } catch (final Exception se) {
767             final String errString =
768                     resourceBundleMessageSource.getMessage(
769                             "error.trans.copies.retrieving",
770                             new Object[] {isbn, Long.toString(location)}, Locale.US);
771             logger.error(errString,se);
772             throw new LibraryException(errString,se);
773         }
774         final int len = rows.size();
775         final long[] out = new long[len];
776         for (int i = 0; i < len; i++) {
777             final Map record = (Map) rows.get(i);
778             out[i] = ((Integer) Utils.getIgnoreCase(record,"ID")).longValue();
779         }
780         if (logger.isDebugEnabled()) {
781             logger.debug( resourceBundleMessageSource.getMessage(
782                     "trans.copies.retrieve.successful",
783                     new Object[] {isbn, Long.toString(location), Arrays.toString(out)},
784                     Locale.US));
785         }
786         return out;
787     }
788 }
789