borinfobean.java

JSP+SQL 图书馆管理系统

package beans;

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.Calendar;
import java.util.Collection;

public class BorInfoBean {
	private Connection con=null;
	public BorInfoBean() {
		con = DBConnection.getConnection();
	}
	public Collection query(String readerID) throws Exception{
		String s = "select BookName,OutDate,YHDate,Fine,CLState from Borrow where ReaNo='"+readerID+"'";
		
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(s);
			Collection ret = new ArrayList();
			while(rs.next()){
				Borrow b = new Borrow();
				b.setBookName(rs.getString(1));
				b.setOutDate(rs.getDate(2));
				b.setYHDate(rs.getDate(3));
				b.setFine(rs.getString(4));
				b.setState(rs.getString(5));
				ret.add(b);
			}
		return ret;
	}
	public Collection queryBook(String bookID) throws Exception{
		String s = "select BookName,ReaNo,OutDate,YHDate,Fine from Borrow where BookNo='"+bookID+"'";
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(s);
		Collection ret = new ArrayList();
		while(rs.next()){
			Borrow b = new Borrow();
			b.setBookName(rs.getString(1));
			b.setReaID(rs.getString(2));
			
			b.setOutDate(rs.getDate(3));
			b.setYHDate(rs.getDate(4));
			b.setFine(rs.getString(5));
			ret.add(b);
		}
	return ret;
	}
	public int modify(String readerID,String oldPsw,String newPsw) throws Exception{
//		int flag;
		String old = this.psw(readerID).trim();
		if(old.equals(oldPsw)){
			PreparedStatement ps = con.prepareStatement("update Reader set ReaCode=? where ReaID=?");
			ps.setString(1, newPsw);
			ps.setString(2, readerID);
			ps.execute();
			return 1;
		}
		else
			return 0;
	}
	public String psw(String readerID) throws Exception{
		String s="";
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select ReaCode from Reader where ReaID='"+readerID+"'");
		while(rs.next()){
			s = rs.getString(1);
		}
		return s;
	}
	public boolean borrowBook(String readerID,String bookID) throws Exception{
		String bookName = this.borrowBookName(bookID);
		Calendar cd;
		cd = java.util.Calendar.getInstance();
		java.util.Date date1 = cd.getTime(); 
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");	
		String s1 = sf.format(date1);
		cd.add(java.util.Calendar.DAY_OF_MONTH, 30);
		java.util.Date date2 = cd.getTime();
		String s2 = sf.format(date2);
		PreparedStatement ps = con.prepareStatement("insert into Borrow(BookNo,ReaNo,BookName,OutDate,YHDate) values(?,?,?,?,?)");
		ps.setString(1, bookID);
		ps.setString(2, readerID);
		ps.setString(3, bookName);
		ps.setDate(4, java.sql.Date.valueOf(s1));
		ps.setDate(5, java.sql.Date.valueOf(s2));
		ps.execute();
		return true;
	}
	public String borrowBookName(String bookID) throws Exception{
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select BookName from Book where BookNo='"+bookID+"'");
		rs.next();
		return rs.getString(1);
	}
	public boolean returnBook(String bookID,String readerID) throws Exception{
		Statement st = con.createStatement();
		st.execute("delete from Borrow where BookNo='"+bookID+"' and ReaNo='"+readerID+"'");
		return true;
	}
	public void modifyState(String readerID,String proState) throws Exception{
		Statement st = con.createStatement();
		st.executeUpdate("update Borrow set CLState='"+proState+"' where ReaNo='"+readerID+"'");
	}
//	public ResultSet stateCheck(String readerID) throws Exception{
//		Statement st = con.createStatement();
//		ResultSet rs = st.executeQuery("select BookName,Fine,CLState from Borrow where ReaNo='"+readerID+"'");
//		
//	}
}