bookdao.java

图书馆管理的源代码文件

package com.nitpro.webmvc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.nitpro.jdbc.ConnnectManager;
import com.nitpro.webmvc.bean.Book;

public class BookDao {
	private Connection conn;

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public void insert(Book book) throws Exception {
		StringBuffer sb = new StringBuffer("INSERT INTO book");
		sb.append("(book_id, book_name, author, idbn, pub_date, publisher, price)")
		.append(" VALUES(book_id_seq.nextVal, ?, ?, ?, ?, ?, ?)");

		PreparedStatement stat = conn.prepareStatement(sb.toString());
		stat.setString(1, book.getBook_name());
		stat.setString(2, book.getAuthor());
		stat.setString(3, book.getIdbn());
		stat.setDate(4, new java.sql.Date(book.getPub_date().getTime()));
		stat.setString(5, book.getPublisher());
		stat.setDouble(6, book.getPrice());

		stat.executeUpdate();

		stat.close();
	}

	public void update(Book book) throws Exception {
		Connection conn = ConnnectManager.getConn();
		StringBuffer sb = new StringBuffer("UPDATE book SET ");
		sb.append("book_name = ?,").append("author = ?,").append("idbn = ?,")
				.append("pub_date = ?,").append("publisher = ?,").append(
						"price = ?").append(" WHERE book_id = ?");
		String sql = sb.toString();
		PreparedStatement stat = conn.prepareStatement(sb.toString());

		stat.setString(1, book.getBook_name());
		stat.setString(2, book.getAuthor());
		stat.setString(3, book.getIdbn());
		stat.setDate(4, new java.sql.Date(book.getPub_date().getTime()));
		stat.setString(5, book.getPublisher());
		stat.setDouble(6, book.getPrice());
		stat.setInt(7, book.getBook_id());

		System.out.println(sql);
		stat.executeUpdate();
		stat.close();
	}

	public void deleteById(int bookId) throws Exception {
		Connection conn = ConnnectManager.getConn();
		String sql = "DELETE FROM book WHERE book_id = ?";
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setInt(1, bookId);
		stat.executeUpdate();
		stat.close();
	}

	public List<Book> selectAll() throws Exception {
		List<Book> l = new ArrayList<Book>();
		Connection conn = ConnnectManager.getConn();
		Statement stat = conn.createStatement();
		String sql = "SELECT book_id, book_name, author, idbn, pub_date, publisher, price FROM book";
		ResultSet rs = stat.executeQuery(sql);
		while (rs.next()) {
			Book b = new Book();
			b.setBook_id(rs.getInt("book_id"));
			b.setBook_name(rs.getString("book_name"));
			b.setAuthor(rs.getString("author"));
			b.setIdbn(rs.getString("idbn"));
			b.setPub_date(rs.getDate("pub_date"));
			b.setPublisher(rs.getString("publisher"));
			b.setPrice(rs.getDouble("price"));
			l.add(b);
		}
		rs.close();
		stat.close();
		return l;
	}

	public Book selectById(int id) throws Exception {
		Book b = null;
		Connection conn = ConnnectManager.getConn();
		String sql = "SELECT book_id, book_name, author, idbn, pub_date, publisher, price FROM book WHERE book_id = ?";
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setInt(1, id);
		ResultSet rs = stat.executeQuery();
		if (rs.next()) {
			b = new Book();
			b.setBook_id(rs.getInt("book_id"));
			b.setBook_name(rs.getString("book_name"));
			b.setAuthor(rs.getString("author"));
			b.setIdbn(rs.getString("idbn"));
			b.setPub_date(rs.getDate("pub_date"));
			b.setPublisher(rs.getString("publisher"));
			b.setPrice(rs.getDouble("price"));
		}
		rs.close();
		stat.close();
		return b;
	}

	private String dateToStr(Date d) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		return sdf.format(d);
	}
}