borrowbookdao.java

一个简单的图书馆的管理系统,该系统主要是针对学校的图书馆而做的

package librarymanagement.dao.borrowbookdao;

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

import javax.swing.JOptionPane;

import librarymanagement.dao.common.DBConnectionManager;
import librarymanagement.dao.common.DbSql;
import librarymanagement.vo.BorrowBookVo;

/**
 * 借书Dao 实现的功能有：读者借出书籍， 书表中书的在馆数量减一，读者借书信息表中增加一条记录，读者表未还数量加一
 * 
 * @author 虎兴龙
 * 
 */
public class BorrowBookDao {
	DBConnectionManager manager = DBConnectionManager.getInstance();
	private String readerName, bookName;
	public String lendOutTime;
	int notReturn;

	/**
	 * 
	 * @param readerid
	 *            读者id
	 * @param bookid
	 *            图书id
	 * @return 返回Vector型
	 */
	public Vector borrowBook(int readerid, int bookid, String lendDate,
			String shouldReturnDate) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		Vector v = null;
		con = manager.getConnection("oracle");
		v = new Vector();
		try {

			pstmt = con.prepareStatement(DbSql.SELECT_BOOK_AND_READER);

			pstmt.setInt(1, readerid);
			pstmt.setInt(2, bookid);
			set = pstmt.executeQuery();

			pstmt.clearParameters();
			resetVector(set, v);
			if (v.isEmpty()) {
				JOptionPane.showMessageDialog(null, "书编号有误或者没有该会员！", "借书失败",
						JOptionPane.YES_OPTION);
			}
			if (notReturn >= 5) {
				JOptionPane.showMessageDialog(null, "您所借书本已超出限额，请先还书！", "借书失败",
						JOptionPane.YES_OPTION);
				return null;
			} else {

				pstmt = con.prepareStatement(DbSql.BOOKNUMBER_DECREASE);
				pstmt.setInt(1, bookid);
				pstmt.executeUpdate();
				pstmt.clearParameters();

				pstmt = con.prepareStatement(DbSql.INSERTBORROW_BOOK_INFO);
				pstmt.setInt(1, readerid);
				pstmt.setString(2, readerName);
				pstmt.setInt(3, bookid);
				pstmt.setString(4, bookName);
				pstmt.setString(5, lendDate);
				pstmt.setString(6, shouldReturnDate);
				pstmt.executeUpdate();
				pstmt.clearParameters();

				pstmt = con
						.prepareStatement(DbSql.UPDATE_READER_NOTRETURN_NUMBER);
				pstmt.setInt(1, readerid);
				pstmt.executeUpdate();

			}
		} catch (SQLException e) {
			JOptionPane.showConfirmDialog(null, e.getMessage(), "异常提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return v;
	}

	public Vector reBorrowBook(int readerid, int bookid, String shouldReturnDate) {
		Connection con = null;

		ResultSet set = null;
		Vector v = null;
		con = manager.getConnection("oracle");
		v = new Vector();
		try {
			PreparedStatement pstmt = con
					.prepareStatement(DbSql.SELECT_READER_BORROW_INFO);
			pstmt.setInt(1, bookid);
			pstmt.setInt(2, readerid);
			set = pstmt.executeQuery();
			pstmt.clearParameters();
			resetReBorrowVector(set, v);
			if (v.isEmpty()) {
				JOptionPane.showMessageDialog(null, "书编号有误或者没有该会员！", "续借失败",
						JOptionPane.YES_OPTION);
			} else {
				con.prepareStatement(DbSql.UPDATE_RETUTN_DATE);
				pstmt.setString(1, shouldReturnDate);
				pstmt.setInt(2, bookid);
				int i = pstmt.executeUpdate();

			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "数据库异常:" + e.getMessage(),
					"提示", JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return v;
	}

	/**
	 * 获得读者所要续借的书籍的本来应还日期
	 * 
	 * @param bookId
	 *            要续借的图书的编号
	 * @return
	 */
	public String selectShouldBorrowDate(int bookId) {
		Connection con = null;
		String s = null;
		ResultSet set = null;
		Vector v = null;
		con = manager.getConnection("oracle");
		try {
			PreparedStatement pstmt = con
					.prepareStatement(DbSql.SELECT_FORMER_SHOULD_RETURN_DATE);
			pstmt.setInt(1, bookId);
			set = pstmt.executeQuery();
			s = set.getString("borrow_date");
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		}
		return s;
	}

	/**
	 * 构造借阅书籍Dao所需的Vector
	 * 
	 * @param set
	 * @param v
	 * @return
	 * @throws SQLException
	 */
	public Vector resetVector(ResultSet set, Vector v) throws SQLException {
		while (set.next()) {
			int readerId = set.getInt("reader_id");
			int bookId = set.getInt("book_id");
			readerName = set.getString("reader_name");
			bookName = set.getString("book_name");
			String condition = set.getString("condition");
			String position = set.getString("BOOK_LOCATION");
			float bookPrice = set.getFloat("price");
			String acd = set.getString("acd");
			notReturn = set.getInt("not_return_num");
			v.addElement(new BorrowBookVo(readerId, bookId, readerName, acd,
					bookName, condition, position, bookPrice, notReturn));
		}
		return v;
	}

	/**
	 * 构造续借阅书籍Dao所需的Vector
	 * 
	 * @param set
	 * @param v
	 * @return
	 * @throws SQLException
	 */
	public Vector resetReBorrowVector(ResultSet set, Vector v)
			throws SQLException {
		while (set.next()) {
			int readerId = set.getInt("reader_id");
			int bookId = set.getInt("book_id");
			readerName = set.getString("reader_name");
			bookName = set.getString("book_name");
			lendOutTime = set.getString("borrow_date");
			v.addElement(new BorrowBookVo(readerId, bookId, readerName,
					bookName, lendOutTime));
		}
		return v;
	}

}