bookdao.java

本光盘包含了本书各章中出现的所有程序的源代码。 1. 如果是Java程序

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.domain.Book;

public class BookDAO implements IBookDAO {
	private static Connection con;

	public BookDAO() {
		try {
			if (con == null) {
				Class.forName("com.mysql.jdbc.Driver");
				String url = "jdbc:mysql://localhost:3306/bookman";
				String user = "root";
				String password = "123";
				con = DriverManager.getConnection(url, user, password);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	protected static final String FIELDS_INSERT = "book_name, author, "
			+ "price, remark";

	protected static final String FIELDS_RETURN = "book_id, " + FIELDS_INSERT;

	protected static String INSERT_SQL = "insert into book ( " + FIELDS_INSERT
			+ " ) " + "values ( ?, ?, ?)";

	protected static String SELECT_SQL = "select " + FIELDS_RETURN
			+ " from book where book_id = ? ";

	protected static String UPDATE_SQL = "update book set book_name = ?, "
			+ "author = ?, price = ?" + "where book_id = ? ";

	protected static String DELETE_SQL = "delete from book where book_id = ? ";

	public Book create(Book book) throws Exception {

		PreparedStatement prepStmt = null;
		try {
			// create and setup statement
			prepStmt = con.prepareStatement(INSERT_SQL);
			int i = 1;
			prepStmt.setString(i++, book.getName());
			prepStmt.setString(i++, book.getAuthor());
			// . . .

			prepStmt.executeUpdate();

		} catch (Exception e) {
			// 
		} finally {
			prepStmt.close();
			con.close();
		}
		return book;
	}

	public void remove(Book b) throws Exception {
		PreparedStatement prepStmt = null;
		try {
			prepStmt = con.prepareStatement(DELETE_SQL);
			prepStmt.setInt(1, b.getId());
			prepStmt.executeUpdate();
		} catch (Exception e) {
			//
		} finally {
			prepStmt.close();
			con.close();
		}
	}

	public Book find(Book b) throws Exception {
		Book book = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;

		try {
			prepStmt = con.prepareStatement(SELECT_SQL);
			prepStmt.setInt(1, b.getId());
			rs = prepStmt.executeQuery();
			if (rs.next()) {
				// create the transfer object using data from rs
				book = new Book();
				book.setId(rs.getInt(1));
				book.setName(rs.getString(2));
				// . . .
			}
		} catch (Exception e) {
			// handle exception
		} finally {
			prepStmt.close();
			con.close();
		}
		return book;
	}
    
    public List findAll() throws Exception {
        Statement sta = con.createStatement();
        ResultSet rs = sta.executeQuery("select * from books");
        
        List books = new ArrayList();
        while(rs.next()) {
            Book tb = new Book();
            
            tb.setId(rs.getInt("id"));
            tb.setName(rs.getString("name"));
            tb.setAuthor(rs.getString("author"));
            tb.setPrice(rs.getDouble("price"));
            tb.setRemark(rs.getString("remark"));
            
            books.add(tb);
        }
        rs.close();
        sta.close();
        con.close();
        
        return books;
    }
    
	public void update(Book b) throws Exception {
		PreparedStatement prepStmt = null;
		try {
			prepStmt = con.prepareStatement(UPDATE_SQL);
			int i = 1;

			prepStmt.setString(i++, b.getName());
			prepStmt.setString(i++, b.getAuthor());
			// . . .

			prepStmt.setInt(i++, b.getId());
			int rowCount = prepStmt.executeUpdate(UPDATE_SQL);
			prepStmt.close();
			if (rowCount == 0) {
				throw new Exception("Update Error:Book Id:" + b.getId());
			}
		} catch (Exception e) {
			// handle exception
		} finally {
			// close all
		}
	}

}