package org.sourceforge.vlibrary.user.dao;
   
   
   import java.sql.Types;
   import java.util.ArrayList;
   import java.util.Iterator;
   import java.util.List;
   import java.util.Locale;
   import java.util.Map;
  
  import org.apache.commons.lang.StringUtils;
  import org.apache.log4j.Logger;
  import org.sourceforge.vlibrary.Constants;
  import org.sourceforge.vlibrary.exceptions.LibraryException;
  import org.sourceforge.vlibrary.user.domain.Author;
  import org.sourceforge.vlibrary.user.domain.Book;
  import org.sourceforge.vlibrary.user.domain.Subject;
  import org.sourceforge.vlibrary.util.Utils;
  import org.springframework.context.support.ResourceBundleMessageSource;
  import org.springframework.jdbc.core.JdbcTemplate;
  
  
  /**
   * Book DAO.
   *
   * @version $Revision$ $Date$
   */
  
  public class BookDAO {
  
      /** log4j Logger */
      private static Logger logger = Logger.getLogger(
              BookDAO.class.getName());
  
      private ResourceBundleMessageSource resourceBundleMessageSource;
  
      private JdbcTemplate jdbcTemplate;
      private String insertBookSQL;
      private String selectBookByISBNSQL;
      private String countCopiesSQL;
      private String selectBookByIdSQL;
      private String updateBookSQL;
      private String selectBookAuthorByBookIdAuthoridSQL;
      private String insertBookAuthorSQL;
      private String selectAuthorsByBookIdSQL;
      private String selectSubjectBySubjectIdBookIdSQL;
      private String insertSubjectBookSQL;
      private String selectSubjectsByBookIdSQL;
      private String selectAllBooksSQL;
      private String selectBookByTitleSQL;
      private String selectBookByOwnerSQL;
      private String selectBookByAuthorSQL;
      private String selectSubjectOrSearchMainSQL;
      private String selectSubjectOrSearchOrderBySQL;
      private String selectSubjectAndSearchSelectSQL;
      private String selectSubjectAndSearchWhereSQL;
      private String selectSubjectAndSearchOrderBySQL;
      private AuthorDAO authorDAO;
      private SubjectDAO subjectDAO;
  
      /**
       * Create a BookDAO.
       */
      public BookDAO() {
      }
  
      /**
       * Used for Spring Dependency Injection
       * @param jdbcTemplate The jdbcTemplate to set.
       */
      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
          this.jdbcTemplate = jdbcTemplate;
      }
  
      /**
       * Used for Spring Dependency Injection
       */
      public void setResourceBundleMessageSource(
              ResourceBundleMessageSource resourceBundleMessageSource) {
          this.resourceBundleMessageSource = resourceBundleMessageSource;
      }
  
      /**
       * Used for Spring Dependency Injection
       */
      public void setSelectSubjectAndSearchSelectSQL(
              String selectSubjectAndSearchSelectSQL) {
          this.selectSubjectAndSearchSelectSQL = selectSubjectAndSearchSelectSQL;
      }
  
      /**
       * Used for Spring Dependency Injection
       */
      public void setSelectSubjectAndSearchWhereSQL(
              String selectSubjectAndSearchWhereSQL) {
          this.selectSubjectAndSearchWhereSQL = selectSubjectAndSearchWhereSQL;
      }
  
      /**
      * Used for Spring Dependency Injection
      */
     public void setSelectSubjectAndSearchOrderBySQL(
             String selectSubjectAndSearchOrderBySQL) {
         this.selectSubjectAndSearchOrderBySQL =
                 selectSubjectAndSearchOrderBySQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setInsertBookSQL( String insertBookSQL) {
         this.insertBookSQL = insertBookSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectSubjectOrSearchMainSQL(
             String selectSubjectOrSearchMainSQL) {
         this.selectSubjectOrSearchMainSQL = selectSubjectOrSearchMainSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectSubjectOrSearchOrderBySQL(
             String selectSubjectOrSearchOrderBySQL) {
         this.selectSubjectOrSearchOrderBySQL = selectSubjectOrSearchOrderBySQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectBookByISBNSQL( String selectBookByISBNSQL) {
         this.selectBookByISBNSQL = selectBookByISBNSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setCountCopiesSQL( String countCopiesSQL) {
         this.countCopiesSQL = countCopiesSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectBookByIdSQL( String selectBookByIdSQL) {
         this.selectBookByIdSQL = selectBookByIdSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setUpdateBookSQL( String updateBookSQL) {
         this.updateBookSQL = updateBookSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectBookAuthorByBookIdAuthoridSQL(
             String selectBookAuthorByBookIdAuthoridSQL) {
         this.selectBookAuthorByBookIdAuthoridSQL =
                 selectBookAuthorByBookIdAuthoridSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setInsertBookAuthorSQL( String insertBookAuthorSQL) {
         this.insertBookAuthorSQL = insertBookAuthorSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectAuthorsByBookIdSQL(String selectAuthorsByBookIdSQL) {
         this.selectAuthorsByBookIdSQL = selectAuthorsByBookIdSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectSubjectBySubjectIdBookIdSQL(
             String selectSubjectBySubjectIdBookIdSQL) {
         this.selectSubjectBySubjectIdBookIdSQL =
                 selectSubjectBySubjectIdBookIdSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setInsertSubjectBookSQL( String insertSubjectBookSQL) {
         this.insertSubjectBookSQL = insertSubjectBookSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectSubjectsByBookIdSQL(
             String selectSubjectsByBookIdSQL) {
         this.selectSubjectsByBookIdSQL = selectSubjectsByBookIdSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectAllBooksSQL( String selectAllBooksSQL) {
         this.selectAllBooksSQL = selectAllBooksSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectBookByTitleSQL( String selectBookByTitleSQL) {
         this.selectBookByTitleSQL = selectBookByTitleSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectBookByOwnerSQL( String selectBookByOwnerSQL) {
         this.selectBookByOwnerSQL = selectBookByOwnerSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSelectBookByAuthorSQL( String selectBookByAuthorSQL) {
         this.selectBookByAuthorSQL = selectBookByAuthorSQL;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setAuthorDAO(AuthorDAO authorDAO) {
         this.authorDAO = authorDAO;
     }
 
     /**
      * Used for Spring Dependency Injection
      */
     public void setSubjectDAO(SubjectDAO subjectDAO) {
         this.subjectDAO = subjectDAO;
     }
 
     /**
      * <p>Inserts a book into the database and returns the result of retrieving
      * the inserted book. The returned book instance therefore has the ID of
      * the newly generated record in the database. The ID field in the book
      * passed in as actual parameter is ignored.
      * </p>
      * <p>The supplied book must have minimally a title and isbn, otherwise
      * a LibraryException is thrown.
      * </p>
      * <p>NB: this method does NOT set subjects or authors</p>
      *
      * @throws LibraryException if an error occurs
      */
     public Book insert(Book book) throws LibraryException {
         if (book == null || book.getTitle() == null ) {
             final String message = resourceBundleMessageSource.getMessage(
                     "error.book.insert.missing.data",
                     new Object[] {book==null?"":book.toString()},
                     Locale.US);
             logger.error(message);
             throw new LibraryException(message);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug(resourceBundleMessageSource.getMessage(
                     "inserting.book",
                     new Object[] {book.toString()},
                     Locale.US));
         }
 
         try {
             jdbcTemplate.update(insertBookSQL,
                     new Object [] {
                             StringUtils.replace
                             (book.getTitle(),Constants.APOSTROPHE,
                                     Constants.ESCAPED_APOSTROPHE)
                             ,
                             StringUtils.replace
                             (book.getPublisher(),Constants.APOSTROPHE,
                                     Constants.ESCAPED_APOSTROPHE)
                             ,
                             StringUtils.replace
                             (book.getPub_date(),Constants.APOSTROPHE,
                                     Constants.ESCAPED_APOSTROPHE)
                             ,
                             new Long(book.getOwner())
                             ,
                             book.normalizeIsbn(book.getIsbn())
             },
                     new int[] {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                             Types.INTEGER, Types.VARCHAR}
                     );
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.insert.sql",
                     new Object[] { book.toString() },
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         //FIXME: this is bogus - should use getGeneratedKeys from statement itself to be sure
         book = retrieveByIsbn(book.normalizeIsbn(book.getIsbn()));
 
         // Count the number of books with this ISBN.
         // If there are others, make this one count - 1.
         final int count = countCopies(book.getIsbn());
         if (count > 1) {
             book.setCopy(count - 1);
             update(book);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.insert.successful",
                     new Object[] { book.toString() },
                     Locale.US));
         }
 
         return book;
     }
 
     /**
      *  <p>Updates <code>book</code> in the database. Assumes that all required
      *  fields are set, including <code>id</code>, which is used to locate the
      *  record to update.
      *  </p>
      *
      *  <p>NB: this method does NOT set subjects or authors.</p>
      *
      * @throws LibraryException
      */
     public void update(Book book) throws LibraryException {
         if (book == null || book.getTitle() == null ) {
             final String message = resourceBundleMessageSource.getMessage(
                     "error.book.update.missing.data",
                     new Object[] {book==null?"":book.toString()},
                     Locale.US);
             logger.error(message);
             throw new LibraryException(message);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "updating.book",
                     new Object[] { book.toString() },
                     Locale.US));
         }
 
         try {
             jdbcTemplate.update(updateBookSQL,
                     new Object [] {
                             StringUtils.replace
                             (book.getTitle(),Constants.APOSTROPHE,
                                     Constants.ESCAPED_APOSTROPHE)
                             ,
                             StringUtils.replace
                             (book.getPublisher(),Constants.APOSTROPHE,
                                     Constants.ESCAPED_APOSTROPHE)
                             ,
                             StringUtils.replace
                             (book.getPub_date(),Constants.APOSTROPHE,
                                     Constants.ESCAPED_APOSTROPHE)
                             ,
                             new Long(book.getOwner())
                             ,
                             book.normalizeIsbn(book.getIsbn())
                             ,
                             new Integer(book.getCopy())
                             ,
                             new Long(book.getId())
             },
                     new int[] {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                             Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER}
                     );
 
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.update",
                     new Object[] { book.toString() },
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.update.successful",
                     new Object[] { book.toString() },
                     Locale.US));
         }
     }
 
     /**
      * Retrieve Book by unique criteria from the database.  If there
      * is more than one book satisfying the query, the first record
      * in the resultset is used to populate the book record.
      *
      * @param sqlString SQL String to be used for the specific criteria Object
      * @param criteria Unique criteria to search the reader from, such as id,
      * isbn
      * @return Book Fully populated Book object, or NULL if a record
      * matching criteria could not be located in the DB
      * @throws LibraryException
      */
     public Book retrieveByGenericCriteria(String sqlString,
             Object[] criteria) throws LibraryException {
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "retrieving.book",
                     criteria,
                     Locale.US));
         }
 
         Book book = null;
 
         try {
             final List rows = jdbcTemplate.queryForList(sqlString, criteria);
             book = loadBookProperties(rows);
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.retrieve",
                     criteria,
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug(resourceBundleMessageSource.getMessage(
                     "book.retrieve.successful",
                     new Object[] {book==null?null:book.toString() },
                     Locale.US));
         }
 
         return book;
     }
 
     /**
      * Populates Book from for first record in rs.
      *
      * @param rs List containing DB records (each record is a Map)
      * @return Book Populated Book object
      * @throws LibraryException - If there is no record returned from DB
      */
     private Book loadBookProperties(List rs)
             throws LibraryException {
         if (rs == null) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.retrieve",
                     new Object[] { rs },
                     Locale.US);
             logger.error(errString);
             throw new LibraryException(errString);
         }
 
         Book book = null;
         final Iterator it = rs.iterator();
         if (it.hasNext()) {
             final Map record = (Map)it.next();
             book = loadBookPropertiesfromRecord(record);
         }
 
         return book;
     }
 
     /**
      * Populates Book from DB record
      *
      * @param List List containing DB records (each record is a Map)
      * @return Book Populated Book object
      * @throws LibraryException - If there is no record returned from DB
      */
     private Book loadBookPropertiesfromRecord(Map<String, Object> record)
             throws LibraryException {
         final Book book = new Book();
         book.setId(new Long(((Integer) Utils.getIgnoreCase(
                 record, "ID")).longValue()));
         book.setCopy(((Integer) Utils.getIgnoreCase(record, "COPY")).intValue());
         book.setTitle((String) Utils.getIgnoreCase(record, "TITLE"));
         book.setPublisher((String) Utils.getIgnoreCase(record, "PUBLISHER"));
         book.setIsbn((String) Utils.getIgnoreCase(record, "ISBN"));
         book.setPub_date((String) Utils.getIgnoreCase(record, "PUB_DATE"));
         book.setOwner(new Long(((Integer) Utils.getIgnoreCase(
                 record, "OWNER")).longValue()));
 
         return book;
     }
 
     /**
      * Gets the ISBN of the book with the given ID if there is such a book,
      * null otherwise.
      *
      * @param bookID ID of book
      * @return ISBN of the book with the given ID
      * @throws LibraryException if an error occurs searching for the book
      */
     public String getIsbn(long bookID) throws LibraryException {
         final Book book = retrieve(bookID);
         if (null == book) {
             return null;
         } else {
             return retrieve(bookID).getIsbn();
         }
     }
 
     /**
      * Retrieves the book with the given ID, if there is such a book;
      * otherwise Null.
      *
      * @param bookID ID to lookup
      * @return book with the given ID
      * @throws LibraryException
      */
     public Book retrieve(long bookID) throws LibraryException {
         return retrieve(Long.toString( bookID));
     }
 
     /**
      * Retrieves the book having the ID represented by the input string, if
      * there is such a book; otherwise Null.
      *
      * @param bookID string ID
      * @return book with the given ID
      * @throws LibraryException
      */
     public Book retrieve(String bookID) throws LibraryException {
         return retrieveByGenericCriteria(selectBookByIdSQL,
                 new Object[] { bookID } );
     }
 
     /**
      * Retrieves the book having the given ISBN,
      * there is such a book; otherwise Null.
      *
      * @param bookID string ID
      * @return book with the given ID
      * @throws LibraryException
      */
     public Book retrieveByIsbn( String isbn ) throws LibraryException {
         return retrieveByGenericCriteria(selectBookByISBNSQL,
                 new Object[] {isbn});
     }
 
     /**
      * Counts the number of copies of the book, checked in or out, any location.
      *
      * @param isbn ISBN of the book
      * @return the total number of copies that have been added of the given book.
      * @throws LibraryException
      */
     public int countCopies(String isbn) throws LibraryException {
         int count;
         final Object[] criteria = new Object[] {isbn};
         if (logger.isDebugEnabled()) {
             logger.debug(resourceBundleMessageSource.getMessage(
                     "counting.book.copies",
                     criteria,
                     Locale.US));
         }
         try {
             count = jdbcTemplate.queryForInt(countCopiesSQL, criteria);
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.counting",
                     criteria,
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug(resourceBundleMessageSource.getMessage(
                     "book.count.successful",
                     new Object[] {Integer.valueOf(count)},
                     Locale.US));
         }
         return count;
     }
 
     /**
      *  Adds <code>author</code> to the authors of <code>book</code>.
      *  Will create a record for <code>author</code> if no author with
      *  the given name exists yet. Requires that <code>book</code> exist
      *  already in the database and that the book ID is set.
      *
      * @param author Author to add
      * @param book book to associate author with
      * @throws LibraryException
      */
     public void addAuthor(Book book, Author author) throws
     LibraryException {
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "inserting.book.author",
                     new Object[] {
                             new Long(author.getId()),
                             new Long(book.getId())
                     }
                     ,
                     Locale.US));
         }
 
         authorDAO.insert( author ); // will not add record if already there
 
         try {
             // See if record already exists
             List rows;
 
             rows = jdbcTemplate.queryForList(
                     selectBookAuthorByBookIdAuthoridSQL,
                     new Object[] {
                             new Long(author.getId()),
                             new Long(book.getId())
                     }
                     );
 
             final Iterator it = rows.iterator();
 
             if ( it.hasNext()) {
                 return;
             }
 
             // make sure Author exists
             if (authorDAO.findAuthorByFirstLastName(author) == null) {
                 authorDAO.insert(author);
             }
 
             jdbcTemplate.update(    insertBookAuthorSQL,
                     new Object [] {
                             new Long(author.getId()),
                             new Long(book.getId())
             },
                     new int[] {Types.INTEGER, Types.INTEGER}
                     );
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.inserting.book.author",
                     new Object[] {
                             new Long(author.getId()),
                             new Long(book.getId())
                     },
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "inserting.book.author.successful",
                     new Object[] {
                             new Long(author.getId()),
                             new Long(book.getId())
                     }
                     ,
                     Locale.US));
         }
     }
 
     /**
      * Returns an ArrayList consisting of the authors of the given book.
      * Assumes that book.id is set. Returns an empty list if there are no
      * authors associated with the given book.
      *
      * @throws LibraryException
      */
     public ArrayList<Author> getAuthors(Book book) throws LibraryException {
         if (book == null ||  book.getTitle() == null ) {
             final String message = resourceBundleMessageSource.getMessage(
                     "error.book.retrieve.missing.data",
                     new Object[] { book==null?"":book.toString() },
                     Locale.US);
             logger.error(message);
             throw new LibraryException( message );
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "retrieving.book.authors",
                     new Object[] { book.toString() },
                     Locale.US));
         }
 
         final ArrayList<Author> outList = new ArrayList<Author>();
 
         try {
             List rows;
 
             rows = jdbcTemplate.queryForList(selectAuthorsByBookIdSQL,
                     new Object[] {new Long(book.getId()) }
                     );
 
             final Iterator<Map<String, Object>> it = rows.iterator();
 
             while(it.hasNext()) {
                 final Map<String, Object> record = it.next();
                 final Author au = new Author(
                         (String) Utils.getIgnoreCase(record, "FIRSTNAME"),
                         (String) Utils.getIgnoreCase(record, "LASTNAME"));
                 au.setId(new Long(((Integer) Utils.getIgnoreCase(
                         record, "ID")).longValue()));
                 outList.add(au);
             }
 
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.retrieve.sql",
                     new Object[] { book.toString() },
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.author.retrieve.successful",
                     new Object[] { outList },
                     Locale.US));
         }
 
         return outList;
     }
 
     /**
      *  Adds <code>subject</code> to the subjects associated with
      *  <code>book</code>. Will create a record for <code>subject</code> if no
      *  subject with the given description exists yet. Requires that
      *  <code>book</code> exist already in the database and that the book ID
      *  is set.
      *
      * @param subject Subject to add
      * @param book book to associate subject with
      * @throws LibraryException
      */
     public void addSubject(Book book, Subject subject) throws LibraryException {
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "updating.book.subjects",
                     new Object[] { book.toString() + subject.toString()},
                     Locale.US));
         }
 
         if (book.getId() > 0) {
             //See if record already exists
             try {
                 List rows;
 
                 rows = jdbcTemplate.queryForList(
                         selectSubjectBySubjectIdBookIdSQL,
                         new Object[] {
                                 new Long(subject.getId()),
                                 new Long(book.getId())
                         }
                         );
 
                 final Iterator it = rows.iterator();
 
                 if (it.hasNext()) {
                     return;
                 }
             } catch (final Exception se) {
                 final String errString = resourceBundleMessageSource.getMessage(
                         "error.book.retrieve.sql",
                         new Object[] { book.toString() + subject.toString()},
                         Locale.US);
                 logger.error(errString,se);
                 throw new LibraryException(errString,se);
             }
 
             // Make sure that the subject exists
             final Long id = subjectDAO.getSubjectIdByDescription(subject);
 
             if ( id == null ) {
                 subject = subjectDAO.insert(subject);
             } else {
                 subject.setId(id.longValue());
             }
 
             try {
                 jdbcTemplate.update(insertSubjectBookSQL,
                         new Object [] {
                                 new Long(subject.getId()),
                                 new Long(book.getId())
                 },
                         new int[] {Types.INTEGER, Types.INTEGER}
                         );
             } catch (final Exception se) {
                 final String errString = resourceBundleMessageSource.getMessage(
                         "error.book.update.sql",
                         new Object[] { book.toString() + subject.toString()},
                         Locale.US);
                 logger.error(errString,se);
                 throw new LibraryException(errString,se);
             }
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.subjects.update.successful",
                     new Object[] { book.toString() },
                     Locale.US));
         }
     }
 
     /**
      * Returns an ArrayList consisting of the subject classifications
      * associated with the the given book. Assumes that book.id is set.
      * Returns an empty list if there are no subjects associated with the
      * given book.
      *
      * @throws LibraryException
      */
     public ArrayList<Subject> getSubjects(Book book) throws LibraryException {
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "retrieving.book.subjects",
                     new Object[] { book.toString() },
                     Locale.US));
         }
         final ArrayList<Subject> outList = new ArrayList<Subject>();
         try {
             List rows;
 
             rows = jdbcTemplate.queryForList(
                     selectSubjectsByBookIdSQL,
                     new Object[] {
                             new Long(book.getId())
                     }
                     );
 
             final Iterator<Map<String, Object>> it = rows.iterator();
 
             while (it.hasNext()) {
                 final Map<String, Object> record = it.next();
                 final Subject su = new Subject(
                         (String) Utils.getIgnoreCase(record, "DESCRIPTION"));
                 su.setId(((Integer) Utils.getIgnoreCase(record, "ID")).longValue());
                 outList.add(su);
             }
 
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.retrieve.sql",
                     new Object[] { book.toString() },
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.subject.retrieve.successful",
                     new Object[] { outList },
                     Locale.US));
         }
 
         return outList;
     }
 
     /**
      * Sets the list of authors associated with the given book. The book must
      * exist in the database, but authors will be created if necessary. This
      * method is equivalent to repeated application of
      * {@link #addAuthor(Book, Author)} for each Author instance in the
      * <code>authors</code> list.
      *
      * @param authors Author instances to associate with the book
      * @param book book to set authors for
      * @throws LibraryException
      */
     public void setAuthors(Book book, ArrayList<Author> authors)
             throws LibraryException {
         for (int i=0;i<authors.size();i++) {
             final Author author = authors.get(i);
             addAuthor(book,author );
         }
     }
 
     /**
      * Sets the list of subjects associated with the given book. The book must
      * exist in the database, but subjects will be created if necessary. This
      * method is equivalent to repeated application of
      * {@link #addSubject(Book, Subject)} for each Subject instance in the
      * <code>subjects</code> list.
      *
      * @param subjects Subject instances to associate with the book
      * @param book book to set subjects for
      * @throws LibraryException
      */
     public void setSubjects(Book book, ArrayList<Subject> subjects)
             throws LibraryException {
         for( int i=0; i<subjects.size(); i++) {
             final Subject su = subjects.get(i);
 
             addSubject(book, su);
         }
     }
 
     /**
      * Returns a list of all books in the library.
      *
      * @return list of all books
      * @throws LibraryException
      */
     public ArrayList dump()
             throws LibraryException {
         return executeQuery(selectAllBooksSQL, null);
     }
 
     /**
      * Returns a list of books with the given title. Returns an empty list
      * if there are no books with the given title.
      *
      * @param title title to search
      * @return list of books with the given title
      * @throws LibraryException
      */
     public  ArrayList titleSearch(String title)
             throws LibraryException {
         final Object[] criteria = new Object[] {
                 "%" +
                         StringUtils.replace(title, Constants.APOSTROPHE,
                                 Constants.ESCAPED_APOSTROPHE) +
         "%"};
 
         return executeQuery(selectBookByTitleSQL, criteria);
     }
 
     /**
      * Returns a list of books owned by the reader with the given ID.  Returns
      * an empty list if the ID belongs to a reader who does not own any books,
      * or is not a valid ID.
      *
      * @param owner ID of owner
      * @return list of books owned
      * @throws LibraryException
      */
     public ArrayList ownerSearch(long owner)
             throws LibraryException {
         return executeQuery( selectBookByOwnerSQL,
                 new Object[] { new Long(owner) } );
     }
 
 
     /**
      * Returns a list of <code>Book</code> instances association with all of
      * the subjects in the input <code>subjects</code> array. The list returned
      * is the intersection of the books associated with each of the individual
      * subjects. A book must be associated with every subject in the
      * list to be included in the returned list.
      *
      * @param subjects list of subjects to seach on
      * @return list of books associated with each of the subjects in the list
      * @throws LibraryException
      */
     public ArrayList<Book> subjectAndSearch(ArrayList subjects)
             throws LibraryException {
         final StringBuffer buf = new StringBuffer(selectSubjectAndSearchSelectSQL);
 
         for (int i=0;i<subjects.size();i++) {
             final Subject su = (Subject)subjects.get(i);
 
             buf.append(selectSubjectAndSearchWhereSQL);
 
             buf.append("'%");
             buf.append(StringUtils.replace
                     (stripEndLineChar(su.getDescription()),
                             Constants.APOSTROPHE, Constants.ESCAPED_APOSTROPHE));
             buf.append("%'))");
 
             if (i<subjects.size()-1) {
                 buf.append(" AND ");
             }
         }
 
         buf.append( selectSubjectAndSearchOrderBySQL);
         return executeQuery(buf.toString(), null);
     }
 
     /**
      *The strings coming from subject selection text area seem to have a
      * CR char ('^M') at the end, this spoils the search, remove
      */
     private String stripEndLineChar(String input) {
         String output = input;
 
         if (input.substring(input.length() -1).equalsIgnoreCase("\r" )) {
             output = input.substring(0, input.length() -1);
         }
 
         return output;
     }
 
     /**
      * Returns a list of <code>Book</code> instances association with all of
      * the subjects in the input <code>subjects</code> array. The list returned
      * is the intersection of the books associated with each of the individual
      * subjects. A book must be associated with every subject in the
      * list to be included in the returned list.
      *
      * @param subjects list of subjects to seach on
      * @return list of books associated with each of the subjects in the list
      * @throws LibraryException
      */
     public ArrayList<Book> subjectOrSearch(ArrayList<Subject> subjects)
             throws LibraryException {
         // Construct "IN" clause
         final StringBuffer inBuff = new StringBuffer("");
 
         for (int i=0;i<subjects.size();i++) {
             final Subject su = subjects.get(i);
 
             inBuff.append((new Long(su.getId())).toString());
 
             if (i<subjects.size()-1) {
                 inBuff.append(",");
             }
         }
 
         return executeQuery(
                 selectSubjectOrSearchMainSQL + inBuff.toString() +
                 selectSubjectOrSearchOrderBySQL, null);
     }
 
     /**
      * Returns a list of <code>Book</code> instances whose author lists contain
      * an author with the given last name. Returns an empty list if there are
      * no such books.
      *
      * @param authorLastName last name of author to search
      * @return list of books written by authors with the given last name
      * @throws LibraryException
      */
     public ArrayList<Book> authorSearch(String authorLastName)
             throws LibraryException {
         final Object[] criteria = new Object[] {
                 StringUtils.replace(authorLastName,Constants.APOSTROPHE,
                         Constants.ESCAPED_APOSTROPHE)};
 
         return executeQuery(selectBookByAuthorSQL, criteria );
     }
 
     /**
      * Executes the given query with the supplied parameters and
      * returns a list of Book instances populated from the returned
      * record set.
      *
      * @param query parameterized query string
      * @param criteria parameters for the query
      * @return list of Book instances populated from the returned records
      * @throws LibraryException
      */
     private ArrayList<Book> executeQuery(String query, Object[] criteria )
             throws LibraryException {
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.search.executing",
                     new Object[] { query },
                     Locale.US));
         }
 
         final ArrayList<Book> outList = new ArrayList();
 
         try {
             List rows;
             if ( criteria != null ) {
                 rows = jdbcTemplate.queryForList(query, criteria);
             } else {
                 rows = jdbcTemplate.queryForList(query);
             }
 
             final Iterator<Map<String, Object>> it = rows.iterator();
 
             while (it.hasNext()) {
                 final Map<String, Object> record = it.next();
                 final Book bk = loadBookPropertiesfromRecord( record );
                 outList.add(bk);
             }
         } catch (final Exception se) {
             final String errString = resourceBundleMessageSource.getMessage(
                     "error.book.search.sql",
                     new Object[] { query },
                     Locale.US);
             logger.error(errString,se);
             throw new LibraryException(errString,se);
         }
 
         if (logger.isDebugEnabled()) {
             logger.debug( resourceBundleMessageSource.getMessage(
                     "book.search.successful",
                     new Object[] { outList },
                     Locale.US));
         }
         return outList;
     }
 
     /**
      * Determines whether or not the ID provided corresponds to a book in the
      * library.
      *
      * @param book ID to validate
      * @return true if there is a book with the given ID; false otherwise
      * @throws LibraryException
      */
     public boolean bookExists(long book)
             throws LibraryException {
         if (retrieve( book ) != null) {
             return true;
         } else {
             return false;
         }
     }
 }