bookdao.java

beginJsp2.0外文书籍源代码

package com.wrox.publish.db;

import com.wrox.publish.om.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

/** 
 * Data Access Object responsible for reading, writing and deleting
 * Books in the database.
 */
public class BookDAO {
    
    private final Connection conn;
    
    /** 
     * Creates a new BookDAO with the given database connection.
     * @param conn The database connection to use.
     */
    public BookDAO(Connection conn) {
        super();
        this.conn = conn;
    }
    
    /**
     * Creates a new Book in the database.
     * @param book The Book to create.
     * @throws SQLException If there was a problem accessing the database.
     * @throws InvalidFieldException If creation failed because one of the 
     *  Book fields did not satisfy a database constraint, especially the 
     *  unique constraint on the title.
     */    
    public void create(Book book) 
    throws SQLException, InvalidFieldException {
        PreparedStatement insert = conn.prepareStatement(
            "insert into book (title, authors, editor, chapters, " + 
            "page_count, price, status) values (?, ?, ?, ?, ?, ?, ?)", 
            Statement.RETURN_GENERATED_KEYS);
        
        try {
            populate(insert, book);
            insert.executeUpdate();

            ResultSet key = insert.getGeneratedKeys();
            if (!key.next()) {
                throw new RuntimeException(
                    "Can't retrieve generated primary key!");
            }
            book.setId(key.getLong(1));
            key.close();
        } catch (SQLException e) {
            if (InvalidFieldException.SQL_STATE.equals(e.getSQLState())) {
                throw new InvalidFieldException(e);
            } else {
                throw e;
            }
        } finally {
            insert.close();
        }
    }
    
    /**
     * Updates a Book in the database.
     * @param book The Book to update.
     * @throws SQLException If there was a problem accessing the database.
     * @throws InvalidFieldException If creation failed because one of the 
     *  Book fields did not satisfy a database constraint, especially the 
     *  unique constraint on the title.
     * @throws EntityNotFoundException If the Book was not found.
     */    
    public void update(Book book) 
    throws SQLException, InvalidFieldException, EntityNotFoundException {
        PreparedStatement update = conn.prepareStatement(
            "update book set title=?, authors=?, editor=?, chapters=?, " +
            "page_count=?, price=?, status=? where id=?");
        
        try {
            populate(update, book);
            update.setLong(8, book.getId());
            if (update.executeUpdate() != 1) {
                throw new EntityNotFoundException(
                    "Book " + book.getId() + " not found");
            }
        } catch (SQLException e) {
            if (InvalidFieldException.SQL_STATE.equals(e.getSQLState())) {
                throw new InvalidFieldException(e);
            } else {
                throw e;
            }
        } finally {
            update.close();
        }
    }
    
    private void populate(PreparedStatement stmt, Book book) 
    throws SQLException {
        stmt.setString(1, book.getTitle());
        stmt.setString(2, book.getAuthors());
        stmt.setString(3, book.getEditor());
        stmt.setString(4, book.getChapters());
        stmt.setInt(5, book.getPageCount());
        stmt.setDouble(6, book.getPrice());
        stmt.setString(7, String.valueOf(book.getStatus()));
    }
    
    /**
     * Removes a Book from the database.
     * @param book The Book to remove.
     * @throws SQLException If there was a problem accessing the database.
     * @throws EntityNotFoundException If the Book was not found.
     */    
    public void remove(Book book) 
    throws SQLException, EntityNotFoundException {
        remove(book.getId());
    }
    
    /**
     * Removes a Book from the database.
     * @param id The id (primary key) of the Book to remove.
     * @throws SQLException If there was a problem accessing the database.
     * @throws EntityNotFoundException If the Book was not found.
     */    
    public void remove(long id) 
    throws SQLException, EntityNotFoundException {
        PreparedStatement delete = conn.prepareStatement(
            "delete from book where id=?");
        try {
            delete.setLong(1, id);
            if (delete.executeUpdate() != 1) {
                throw new EntityNotFoundException(
                    "Book " + id + " not found");
            }
        } finally {
            delete.close();
        }
    }

    /**
     * Finds a single Book by its id (primary key). This methods loads the
     * entire object graph including the dependent BookNews objects for the
     * book.
     * @param id The id value to look for
     * @throws SQLException If there was a problem accessing the database
     * @throws EntityNotFoundException If the Book was not found.
     * @return The Book object
     */    
    public Book findById(long id) 
    throws SQLException, EntityNotFoundException {
        Book book = loadById(id);
        Collection news = new BookNewsDAO(conn).findByBook(book);
        book.getNews().addAll(news);
        return book;
    }
    
    /** 
     * Version of findById which just loads the Book and not its dependent
     * objects. Used by BookNewsDAO.
     */
    Book loadById(long id) 
    throws SQLException, EntityNotFoundException {
        PreparedStatement select = conn.prepareStatement(
            "select title, authors, editor, chapters, " +
            "page_count, price, status " + 
            "from book where id=?");
        try {
            select.setLong(1, id);
            ResultSet results = select.executeQuery();
            if (!results.next()) {
                throw new EntityNotFoundException(
                    "Book " + id + " not found");
            } else {
                Book book = new Book();
                book.setId(id);
                book.setTitle(results.getString(1));
                book.setAuthors(results.getString(2));
                book.setEditor(results.getString(3));
                book.setChapters(results.getString(4));
                book.setPageCount(results.getInt(5));
                book.setPrice(results.getDouble(6));
                book.setStatus(results.getString(7).charAt(0));
                return book;
            }
        } finally {
            select.close();
        }
    }
}