bookdaoimpl.java

dao模式结合mvc,dbcp连接池 log4j日志

package com.bookstore.dao;

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

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.bookstore.data.Book;
import com.bookstore.data.BookImpl;
import com.bookstore.pool.MyConnectionPool;
import com.bookstore.util.BookStoreUtil;

public class BookDAOImpl implements BookDAO {

	private DataSource ds;

	private Connection conn;

	private PreparedStatement pst;

	private ResultSet rset;
	
	public static Logger log = Logger.getLogger
										(BookDAOImpl.class);
	
	public BookDAOImpl() {
		ds = MyConnectionPool.getDataSource();
	}

	
	
	public void queryBook() {
		String querySql = "select * from books;";
		try {
			conn = ds.getConnection();
			pst = conn.prepareStatement(querySql);
			rset = pst.executeQuery();

	int numcols = rset.getMetaData().getColumnCount();

			while (rset.next()) {
				for (int i = 1; i <= numcols; i++) {
			System.out.print("\t" + rset.getString(i));
				}
				System.out.println("");
			}
			
			log.info("queryBook()");
		} catch (SQLException e) {
			log.error("queryBook is fail..");
			e.printStackTrace();
		} finally {
			BookStoreUtil.closeResultSet(rset);
			BookStoreUtil.closeStatement(pst);
			BookStoreUtil.closeConnection(conn);
		}
	}

public Book createBook(String title, String type, double price, String notes) {
		
	Book result = null;
		
		String insertSql = "insert into books "
				 + "(title, type, price, notes)"
			     + "values(?,?,?,?);";
		try {
			conn = ds.getConnection();
			pst = conn.prepareStatement(insertSql);

			pst.setString(1, title);
			pst.setString(2, type);
			pst.setDouble(3, price);
			pst.setString(4, notes);

			int rows = pst.executeUpdate();

			if (rows != 1) {
				result = null;
				log.error("create books is fail");
			} else {
				result = 
			new BookImpl(title, type, price, notes);
				log.info("create books is succeed");
			}
		} catch (SQLException e) {
			log.error("create books is fail");
		} finally {
			BookStoreUtil.closeStatement(pst);
			BookStoreUtil.closeConnection(conn);
		}
		return result;
	}//结束createBook()

	public Book findBookById(String id) {

		BookImpl result = null;
		String findId = "select * from books where id=?;";

		try {
			conn = ds.getConnection();
			pst = conn.prepareStatement(findId);
			pst.setString(1, id);

			rset = pst.executeQuery();

			if (rset.next()) {
				result = new BookImpl();

				result.setId(rset.getInt(1));
				result.setTitle(rset.getString(2));
				result.setType(rset.getString(3));
				result.setPrice(rset.getDouble(4));
				result.setNotes(rset.getString(5));

			} else
				result = null;
		} catch (SQLException e) {
			result = null;
			e.printStackTrace();
		} finally {
			BookStoreUtil.closeResultSet(rset);
			BookStoreUtil.closeStatement(pst);
			BookStoreUtil.closeConnection(conn);
		}
		return result;
	}

	public java.util.Collection findBookByNotes(String notes) {
		Collection result = null;
		String findNotes = 
			"select * from books where notes like ?";
		try {
			conn = ds.getConnection();
			pst = conn.prepareStatement(findNotes);
			pst.setString(1, "%" + notes + "%");

			rset = pst.executeQuery();

			result =
	BookStoreUtil.makeBookObjectsFromResultSet(rset);
	
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			BookStoreUtil.closeStatement(pst);
		}
		return result;
	}

	public void deleteBook(int id) {
		String delSql = "delete from books where id=?;";
		try {
			conn = ds.getConnection();
			pst = conn.prepareStatement(delSql);
			pst.setInt(1, id);

			int rows = pst.executeUpdate();

			if (rows != 1) {
				System.out.println("delete books is fail");

			} else {
				System.out.println("delete books is succeed");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookStoreUtil.closeStatement(pst);
			BookStoreUtil.closeConnection(conn);
		}
	}

	public void updateBook(int id, String title, String type, double price,
			String notes) {
	 String upSql = "update books set title=?,"
			+ "type=?,price=?,notes=? where id=?;";


		try {
			conn = ds.getConnection();
			pst = conn.prepareStatement(upSql);
			pst.setString(1, title);
			pst.setString(2, type);
			pst.setDouble(3, price);
			pst.setString(4, notes);
			pst.setInt(5, id);

			int rows = pst.executeUpdate();

			if (rows != 1) {
				System.out.println("update books is fail");

			} else {
				System.out.println("update books is succeed");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}