lenddao.java

STRUTS2的入门 教程

package com.dao;

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


import com.bean.Tlend;
import com.db.DBHelper;
import com.vo.History;
import com.vo.LendBook;
import com.vo.MatureBook;

public class LendDAO {
	/**
	 * 
	 * 取得所有书籍借阅记录
	 */
	public List<Tlend> getAllLend() {
		List<Tlend> list = new ArrayList<Tlend>();
		String sql = "SELECT * FROM T_LEND";
		Statement stat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Tlend tlend = new Tlend();
				tlend.setLendid(rs.getInt(1));
				tlend.setBookid(rs.getInt(2));
				tlend.setUserid(rs.getInt(3));
				tlend.setLendtime(rs.getDate(4));
				tlend.setEngagetime(rs.getDate(5));
				tlend.setIsreturn(rs.getInt(6));
				tlend.setReturntime(rs.getDate(7));
				list.add(tlend);
			}
		} 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;
	}

	/**
	 * 
	 * 增加一条借阅记录//is_return字段 0 未还 1 已还//
	 */
	public void lengBook(int userid, int bookid, Date Engagetime) {
		String sql = "INSERT INTO T_LEND VALUES(null,?,?,?,?,0,null)";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, bookid);
			pstat.setInt(2, userid);
			pstat.setDate(3, new java.sql.Date(new Date().getTime()));
			pstat.setDate(4, new java.sql.Date(Engagetime.getTime()));
			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 returnBook(int lendid) {
		String sql = "UPDATE T_LEND SET IS_RETURN=1,RETURN_TIME=? WHERE LEND_ID=?";
		PreparedStatement pstat = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setDate(1, new java.sql.Date(new Date().getTime()));
			pstat.setInt(2, lendid);
			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();
			}
		}

	}

	/**
	 * 
	 * 根据userid查询该USER的未还书籍记录
	 */
	public List<LendBook> noReturn(int userid) {
		List<LendBook> list = new ArrayList<LendBook>();
		String sql = "SELECT * FROM T_Lend WHERE user_id=? AND is_return=0 ORDER BY engage_time";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, userid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				LendBook lendBook = new LendBook();
				lendBook.setLendid(rs.getInt(1));
				lendBook.setBookid(rs.getInt(2));
				lendBook.setLendtime(rs.getDate(4));
				lendBook.setEngagetime(rs.getDate(5));
				lendBook.setBookname(new BookDAO().getBook(rs.getInt(2)).getBookname());
				list.add(lendBook);
			}
		} 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 list;
	}

	/**
	 * 
	 * 根据userid查询该USER的到期书籍数量
	 */
	public int maturenum(int userid) {
		int num = 0;
		String sql = "SELECT COUNT(*) FROM T_Lend WHERE  user_id=? and is_return=0 and TO_DAYS(engage_time) - TO_DAYS(current_date) <= 1 ";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, userid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				num = rs.getInt(1);
			}
		} 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 num;
	}
	
	
	/**
	 * 
	 * 根据userid查询该USER的到期书籍(小于当前日期3天)
	 */
	public List<MatureBook> matureBook(int userid) {
		List<MatureBook> list = new ArrayList<MatureBook>();
		String sql = "SELECT lend_id,book_id,lend_time,TO_DAYS(engage_time) - TO_DAYS(current_date) FROM T_Lend WHERE user_id=? and is_return=0 and TO_DAYS(engage_time) - TO_DAYS(current_date) <= 1 order by engage_time";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, userid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				MatureBook matureBook = new MatureBook();
				matureBook.setLendid(rs.getInt(1));
				matureBook.setBookname(new BookDAO().getBook(rs.getInt(2)).getBookname());
				matureBook.setBookid(rs.getInt(2));
				matureBook.setLenddate(rs.getDate(3));
				matureBook.setMaturenum(rs.getInt(4));
				list.add(matureBook);
			}
		} 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 list;
	}

	/**
	 * 
	 * 根据userid查询该USER的所有历史记录
	 */
	public List<History> getHistory(int userid) {
		List<History> list = new ArrayList<History>();
		String sql = "SELECT LEND_ID,BOOK_ID,LEND_TIME,IS_RETURN,RETURN_TIME FROM T_Lend WHERE USER_ID=?";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, userid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				History history = new History();
				history.setLendid(rs.getInt(1));
				history.setBookname(new BookDAO().getBook(rs.getInt(2)).getBookname());
				history.setBookid(rs.getInt(2));
				history.setLenddate(rs.getDate(3));
				if (rs.getInt(4) == 1) {
					history.setBookstat("已还");
					history.setReturndate(rs.getDate(5).toString());
				}else {
					history.setBookstat("未还");
					history.setReturndate("未知");
				}
				list.add(history);
			}
		} 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 list;
	}
	public static void main(String[] args) {
//		LendDAO lendDAO = new LendDAO();
//		try {
//			lendDAO.lengBook(2, 4, new SimpleDateFormat("yy-MM-dd").parse("2007-7-07"));
//		} catch (ParseException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		lendDAO.returnBook(3);
//		for (Tlend tlend : list) {
//			System.out.println("*************************");
//			System.out.println(tlend.getLendid());
//			System.out.println(tlend.getBookid());
//			System.out.println(tlend.getUserid());
//			System.out.println(tlend.getLendtime());
//			System.out.println(tlend.getEngagetime());
//			System.out.println(tlend.getIsreturn());
//			System.out.println(tlend.getReturntime());
//
//		}
//		List<Tlend> list = lendDAO.noReturn(1);
//		for (Tlend tlend : list) {
//			System.out.println("********未还书籍*********");
//			System.out.println("借阅ID为：" + tlend.getLendid());
//			System.out.println("借阅用户为：" + tlend.getUserid());
//			System.out.println("借阅书籍为：" + tlend.getBookid());
//			System.out.println("借阅时间为：" + tlend.getLendtime());
//			System.out.println("欲还时间为：" + tlend.getEngagetime());
//		}
	}
}