detailoper.java

基于javaSwing的图书馆信息管理系统 使用oracle数据库连接

package com.lib.db.oper;

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.Vector;

import com.lib.DBConnection;
import com.lib.db.Detail;

public class DetailOper {
	public static Connection con = DBConnection.getConnection();

	//用flag来标识查找方式
	//0----查找全部 no use
	//1----查找借阅者的 未归还书 记录(传入借阅者id)
	//2----查找借阅者的 所有 记录(传入借阅者id)
	//3----根据流水号查找(传入detail_id)
	public static Vector getInfo(int flag,String condition) {
		Vector v = new Vector();
		Statement stmt = null;
		String querySQL = null;
		try {
			stmt = con.createStatement();
			if(flag == 0)	querySQL = "select * from lib_deta order by to_number(detail_id)";
			if(flag == 1)	querySQL = "select * from lib_deta where borrower_id='"+condition+"' and isReturn='0' order by to_number(detail_id)";
			if(flag == 2)	querySQL = "select * from lib_deta where borrower_id='"+condition+"' order by to_number(detail_id)";
			if(flag == 3)	querySQL = "select * from lib_deta where detail_id='"+condition+"' order by to_number(detail_id)";
			
			
			System.out.println("~~~~~~~~~~" + querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			while (set.next()) {					
				String detail_id = set.getString("detail_id");
				String borrower_id = set.getString("borrower_id");
				String isbn = set.getString("isbn");
				Date borrow_date = set.getDate("borrow_date");
				String isRenew = set.getString("isRenew");
				Date renew_date = set.getDate("renew_date");
				String isReturn = set.getString("isReturn");
				Date return_date = set.getDate("return_date");
				Float fine = set.getFloat("fine");
				String borrower = set.getString("borrower");
				String book = set.getString("book");
				v.addElement(new Detail(detail_id, borrower_id,isbn,borrow_date,isRenew,renew_date,isReturn,return_date,fine,borrower,book));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return v;
	}
	
	//查询 当前借阅者 要提醒 催还的书
	/**
	 * id -当前借阅者id
	 * down - 条件查询时 日期差下限
	 * up - 条件查询时 日期差上限
	 */
	public static Vector getUrgeInfo(String id,int down,int up) {
		Vector v = new Vector();
		Statement stmt = null;
		String querySQL = null;
		try {
			stmt = con.createStatement();
			querySQL = "select * from lib_deta  " +
						"where borrower_id = '"+id+
						"' and " +
							"(isRenew = '0' and (sysdate - borrow_date) between "+down+" and "+up+"" +
							" or " +
							"isRenew = '1' and (sysdate - renew_date) between "+down+" and "+up+")";			
			
			System.out.println("~~~~~getUrgeInfo~~~~~" + querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			while (set.next()) {					
				String detail_id = set.getString("detail_id");
				String borrower_id = set.getString("borrower_id");
				String isbn = set.getString("isbn");
				Date borrow_date = set.getDate("borrow_date");
				String isRenew = set.getString("isRenew");
				Date renew_date = set.getDate("renew_date");
				String isReturn = set.getString("isReturn");
				Date return_date = set.getDate("return_date");
				Float fine = set.getFloat("fine");
				String borrower = set.getString("borrower");
				String book = set.getString("book");
				v.addElement(new Detail(detail_id, borrower_id,isbn,borrow_date,isRenew,renew_date,isReturn,return_date,fine,borrower,book));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return v;
	}
	

	//新增一条记录
	public static boolean add(Detail value) {
		boolean flag = false;
		String insertSQL = "insert into lib_detail(detail_id, borrower_id,isbn,borrow_date,isRenew,renew_date,isReturn,return_date) values(to_char(lib_detail_id.nextval),?,?,?,?,?,?,?)";

		PreparedStatement psmt = null;
		int rows = 0;
		try {			
			psmt = con.prepareStatement(insertSQL);
			psmt.setString(1, value.getBorrower_id());
			psmt.setString(2, value.getIsbn());
			psmt.setDate(3, value.getBorrow_date());
			psmt.setString(4, value.getIsRenew());	
			psmt.setDate(5, value.getRenew_date());			
			psmt.setString(6, value.getIsReturn());
			psmt.setDate(7, value.getReturn_date());
			rows = psmt.executeUpdate();
			if (rows != 0) {
				flag = true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();			
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return flag;
	}

	
	/**
	 * 未用到
	 * @param id
	 * @return
	 */
	public static boolean delete(String id) {
		// Connection con = DBConnection.getConnection();
		String deleteSQL = "delete from lib_detail where detail_id=? ";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(deleteSQL);
			psmt.setString(1, id);
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return true;
	}

	//用于 还书流程
	public static boolean update(Detail value) {		
		String updateSQL = "update  lib_detail  set borrower_id=?,isbn=?,borrow_date=?,isRenew=?,renew_date=?,isReturn=?,return_date=?,fine=? where detail_id=?";
		PreparedStatement psmt = null;
		try {			
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(1, value.getBorrower_id());
			psmt.setString(2, value.getIsbn());
			psmt.setDate(3, value.getBorrow_date());
			psmt.setString(4, value.getIsRenew());
			psmt.setDate(5,  value.getRenew_date());
			psmt.setString(6, value.getIsReturn());
			psmt.setDate(7,value.getReturn_date());
			psmt.setFloat(8,value.getFine());
			psmt.setString(9, value.getDetail_id());
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();			
			}
		}
		return true;
	}
	
	//当借阅者已在近期(5天内)借阅记录上 借过此书,并归还
	//此时,要作特殊处理
	//若未续借,则说明此次借书为 续借!!!!
	//若已续借,则借阅者 在5天内不可再续借!!!
	
	
	//查询此书在5天前是否已经续借过并归还,已保证 借阅者不能再续借
	//用于借书时  
	//检查 是否在 续借缓冲期 返回 借阅者可借书之日
	public static Date getActiveDate(String isbn,String borrower_id) {
		Statement stmt = null;
		Date active = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select 5 + return_date active from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and isrenew = '1' and (sysdate - return_date) between 0 and 5";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				active = set.getDate("active");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return active;	
	}
	
	//查询此书是否已经续借过并未归还,已保证 借阅者不能再借
	public static boolean isRenewed(String isbn,String borrower_id,int upDate) {
		Statement stmt = null;
		boolean flag = false;
		try {
			stmt = con.createStatement();
			String querySQL = "select detail_id from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and isrenew = '1' and (sysdate - renew_date) between 0 and " + upDate;
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return flag;	
	}
	
	
	//如果 此书在5天前归还了 (或者未归还),且未续借过,则说明此次借书为 续借
	//这里只处理 5天前归还了的情况
	//返回流水号
	public static String isRenewAction0(String isbn,String borrower_id) {
		Statement stmt = null;
		String detail_id = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select detail_id from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and isrenew = '0' and (sysdate - return_date) between 0 and 5";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				detail_id = set.getString("detail_id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return detail_id;	
	}
	
	//如果 此书在5天前归还了 (或者未归还),且未续借过,则说明此次借书为 续借
	//这里只处理 未归还的情况
	//返回流水号
	public static String isRenewAction(String isbn,String borrower_id,int upDate) {
		Statement stmt = null;
		String detail_id = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select detail_id from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and " +
							"isRenew = '0' and (sysdate - borrow_date) between 0 and "+upDate;	
			
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				detail_id = set.getString("detail_id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return detail_id;	
	}
	
	//得到服务器系统时间
	public static Date getSysdate() {
		Statement stmt = null;
		Date sysdate = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select sysdate from dual";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				sysdate = set.getDate("sysdate");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return sysdate;	
	}
}