bookdao.java

STRUTS2的入门 教程

package com.dao;

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

import com.bean.Tbook;
import com.db.DBHelper;

public class BookDAO {
	/**
	 * 
	 * 取得所有书籍
	 */
	public List<Tbook> getAllBook() {
		List<Tbook> list = new ArrayList<Tbook>();
		String sql = "SELECT * FROM T_BOOK";
		Statement stat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Tbook tbook = new Tbook();
				tbook.setBookid(rs.getInt(1));
				tbook.setCateid(rs.getInt(2));
				tbook.setBookname(rs.getString(3));
				tbook.setBookno(rs.getString(4));
				tbook.setBookauthor(rs.getString(5));
				tbook.setBookconcern(rs.getString(6));
				tbook.setBookallnum(rs.getInt(7));
				tbook.setBooknownum(rs.getInt(8));
				tbook.setBookprice(rs.getDouble(9));
				tbook.setBookdate(rs.getDate(10));
				tbook.setBooklength(rs.getInt(11));
				tbook.setBookamount(rs.getInt(12));
				tbook.setBookstat(rs.getString(13));
				tbook.setBookabout(rs.getString(14));
				list.add(tbook);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (stat != null)
					stat.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 
	 * 根据ID取得对应书籍
	 */
	public Tbook getBook(int bookid) {
		Tbook tbook = new Tbook();
		String sql = "SELECT * FROM T_BOOK WHERE book_id=?";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				tbook.setBookid(rs.getInt(1));
				tbook.setCateid(rs.getInt(2));
				tbook.setBookname(rs.getString(3));
				tbook.setBookno(rs.getString(4));
				tbook.setBookauthor(rs.getString(5));
				tbook.setBookconcern(rs.getString(6));
				tbook.setBookallnum(rs.getInt(7));
				tbook.setBooknownum(rs.getInt(8));
				tbook.setBookprice(rs.getDouble(9));
				tbook.setBookdate(rs.getDate(10));
				tbook.setBooklength(rs.getInt(11));
				tbook.setBookamount(rs.getInt(12));
				tbook.setBookstat(rs.getString(13));
				tbook.setBookabout(rs.getString(14));
				tbook.setBookimg(rs.getString(15));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return tbook;
	}

	/**
	 * 
	 * 增加书籍
	 */
	public boolean addBook(Tbook tbook) {
		String sql = "INSERT INTO T_BOOK VALUES(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, tbook.getCateid());
			pstat.setString(2, tbook.getBookname());
			pstat.setString(3, tbook.getBookno());
			pstat.setString(4, tbook.getBookauthor());
			pstat.setString(5, tbook.getBookconcern());
			pstat.setInt(6, tbook.getBookallnum());
			pstat.setInt(7, tbook.getBookallnum());
			pstat.setDouble(8, tbook.getBookprice());
			pstat.setDate(9, new Date(tbook.getBookdate().getTime()));
			pstat.setInt(10, tbook.getBooklength());
			pstat.setInt(11, tbook.getBookamount());
			pstat.setString(12, "可借");
			pstat.setString(13, tbook.getBookabout());
			pstat.setString(14, tbook.getBookimg());
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	/**
	 * 
	 * 修改书籍
	 */
	public boolean setBook(Tbook tbook) {
		String sql = "UPDATE T_BOOK SET CATE_ID=?,BOOK_NAME=?,BOOK_NO=?,BOOK_AUTHOR=?,BOOK_CONCERN=?,BOOK_ALLNUM=?,BOOK_NOWNUM=?,BOOK_PRICE=?,BOOK_DATE=?,BOOK_LENGTH=?,BOOK_AMOUNT=?,BOOK_STAT=?,BOOK_ABOUT=?,BOOK_IMG=? WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, tbook.getCateid());
			pstat.setString(2, tbook.getBookname());
			pstat.setString(3, tbook.getBookno());
			pstat.setString(4, tbook.getBookauthor());
			pstat.setString(5, tbook.getBookconcern());
			pstat.setInt(6, tbook.getBookallnum());
			pstat.setInt(7, tbook.getBooknownum());
			pstat.setDouble(8, tbook.getBookprice());
			pstat.setDate(9, new Date(tbook.getBookdate().getTime()));
			pstat.setInt(10, tbook.getBooklength());
			pstat.setInt(11, tbook.getBookamount());
			pstat.setString(12, tbook.getBookstat());
			pstat.setString(13, tbook.getBookabout());
			pstat.setString(14, tbook.getBookimg());
			pstat.setInt(15, tbook.getBookid());
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	/**
	 * 
	 * 删除书籍
	 */
	public boolean delBook(int bookid) {
		String sql = "DELETE FROM T_BOOK WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}

	/**
	 * 
	 * 书籍被借阅次数+1
	 */
	public void addAmount(int bookid) {
		String sql = "UPDATE T_BOOK SET BOOK_AMOUNT=BOOK_AMOUNT+1 WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 
	 * 当书籍被被借时，书籍现有数量减一
	 */
	public void numLessen(int bookid) {
		String sql = "UPDATE T_BOOK SET BOOK_NOWNUM=BOOK_NOWNUM-1 WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 
	 * 当书籍被还时，书籍现有数量加一
	 */
	public void numAdd(int bookid) {
		String sql = "UPDATE T_BOOK SET BOOK_NOWNUM=BOOK_NOWNUM+1 WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 
	 * 检查书籍是否可借
	 */
	public boolean checkBook(int bookid) {
		String sql = "SELECT BOOK_STAT FROM T_BOOK WHERE BOOK_ID=?";
		Connection conn = new DBHelper().getConn();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String stat = null;
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				stat = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if ("可借".equals(stat)) {
			return true;
		}
		return false;
	}

	/**
	 * 
	 * 设置书籍状态
	 */
	public void setStat(int bookid, String bookstat) {
		String sql = "UPDATE T_BOOK SET BOOK_STAT=? WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, bookstat);
			pstat.setInt(2, bookid);
			pstat.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 
	 * 书籍被借成功后,检查书籍是否下次可借,如果书籍当前数量小于或等于0设置书籍不可借
	 * 书籍被还成功后,检查书籍是否下次可借,如果书籍当前数量大于或等于1设置书籍可借
	 */
	public void bookStat(int bookid) {
		String sql = "SELECT BOOK_NOWNUM,BOOK_STAT FROM T_BOOK WHERE BOOK_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		ResultSet rs = null;
		int num = 0;
		String stat = null;
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				num = rs.getInt(1);
				stat = rs.getString(2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (num <= 0 && "可借".equals(stat)) {
			setStat(bookid, "不可借");
		}
		if (num >= 1 && "不可借".equals(stat)) {
			setStat(bookid, "可借");
		}
	}
	public static void main(String[] args) {
		// Tbook tbook = new BookDAO().getBook(1);
		// tbook.setCateid(4);
		// tbook.setBookname("aa");
		// tbook.setBookno("aa");
		// tbook.setBookauthor("aa");
		// tbook.setBookconcern("aa");
		// tbook.setBookallnum(11);
		// tbook.setBooknownum(11);
		// tbook.setBookprice(12.32);
		// tbook.setBookdate(new java.util.Date());
		// tbook.setBooklength(333);
		// tbook.setBookamount(333);
		// tbook.setBookstat("aa");
		// tbook.setBookabout("aa");
		// new BookDAO().addBook(tbook);
		// new BookDAO().delBook(8);
		BookDAO bookDAO = new BookDAO();
		Tbook tbook = bookDAO.getBook(10);
		System.out.println("当前状态为： " + tbook.getBookname());
		tbook.setBookname("999999");
		bookDAO.setBook(tbook);
		tbook = bookDAO.getBook(10);
		System.out.println("当前状态为： " + tbook.getBookname());
	}
}