borroweroper.java

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

package com.lib.db.oper;

import java.sql.Connection;
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.Borr;
import com.lib.db.Borrower;
import com.lib.db.Publisher;

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

	public static Vector getInfo() {
		Vector v = new Vector();
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet set = stmt.executeQuery("select * from lib_borr order by to_number(card_id)");

			while (set.next()) {
				String card_id = set.getString("card_id");

				String borrower_id = set.getString("borrower_id");
				String borrower = set.getString("borrower");
				String pwd = set.getString("pwd");
				String email = set.getString("email");
				String phone = set.getString("phone");
				String isStudent = set.getString("isStudent");
				int borrow_num = set.getInt("borrow_num");
				String login = set.getString("login");

				v.addElement(new Borr(card_id, borrower_id, borrower,login, pwd,
						email, phone, isStudent, borrow_num));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return v;
	}

	public static Vector query(String k) {
		Vector v = new Vector();
		String getSQL = "select * from lib_borr where borrower_id=? order by to_number(card_id)";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);

			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				String card_id = set.getString("card_id");

				String borrower_id = set.getString("borrower_id");
				String borrower = set.getString("borrower");
				String pwd = set.getString("pwd");
				String email = set.getString("email");
				String phone = set.getString("phone");
				String isStudent = set.getString("isStudent");
				int borrow_num = set.getInt("borrow_num");
				String login = set.getString("login");
				v.addElement(new Borr(card_id, borrower_id, borrower,login, pwd,
						email, phone, isStudent, borrow_num));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return v;
	}

	public static boolean add(Borrower value) {
		boolean flag = false;
		String insertSQL = "insert into lib_borrower(borrower_id,borrower,login,pwd,email,phone,isStudent,borrow_num) values(to_char(lib_borrower_id.nextval),?,?,?,?,?,?,0)";

		PreparedStatement psmt = null;
		int rows = 0;
		try {
			psmt = con.prepareStatement(insertSQL);
			psmt.setString(1, value.getBorrower());
			psmt.setString(2, value.getLogin());
			psmt.setString(3, value.getPwd());
			psmt.setString(4, value.getEmail());
			psmt.setString(5, value.getPhone());
			if (value.getIsStudent().equals("1"))
				psmt.setInt(6, 1);
			else
				psmt.setInt(6, 0);
			;

			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;
	}

	// 级联删除
	// 考虑效率问题,这里只关系到 lib_card一个表,所以手动级联删除记录
	public static boolean delete(String id) {
		// Connection con = DBConnection.getConnection();
		String deleteSQL0 = "delete from lib_card where borrower_id=? ";
		String deleteSQL = "delete from lib_borrower where borrower_id=? ";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(deleteSQL0);
			psmt.setString(1, id);
			psmt.executeUpdate();

			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;
	}

	// 重新办理新借阅卡业务
	// 从lib_card表中删除对应的记录
	// 并新建一条借阅卡记录
	// 即update lib_card
	// 返回新卡号
	public static String updateCard(String id) {
		/**
		 * 原始方法
		 * 
		 * boolean flag = false; Statement stmt = null; String borrower_id =
		 * null; String newCard_id = null; try { stmt = con.createStatement();
		 * String query = "select * from lib_card where card_id = '" + card +
		 * "'"; String delete = "delete from lib_card where card_id = '" + card +
		 * "'"; ResultSet set = stmt.executeQuery(query); if(set.next()) {
		 * borrower_id = set.getString(borrower_id); } else {
		 * System.out.println("未找到该记录,数据库出错!!"); //应抛出异常 } flag =
		 * stmt.execute(delete); if (flag && !borrower_id.isEmpty()) {
		 * stmt.executeUpdate("insert into lib_card
		 * values(to_char(lib_card_id.nextval),'"+borrower_id +"');"); } else {
		 * System.out.println("未能删除记录,数据库出错!!"); //应抛出异常 }
		 * 
		 * }catch (Exception e) { e.printStackTrace(); } finally { try {
		 * stmt.close(); // con.close(); } catch (Exception ee) {
		 * ee.printStackTrace(); System.out.println(ee.getMessage()); } } return
		 * newCard_id;
		 * 
		 */
		Statement stmt = null;
		String updatedCard = null;
		String updateSQL = "update  lib_card  set card_id = to_char(lib_card_id.nextval) where borrower_id='"
				+ id + "'";
		String querySQL = "select lib_card_id.currval from dual";
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(updateSQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				updatedCard = set.getString(1);
			}
		} catch (Exception e) {
			updatedCard = "ERROR";
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
				
			}
		}
		return updatedCard;

	}

	// 密码修改
	// 密码用String型传输是否安全??
	public static boolean updatePwd(String id, String pwd) {
		String updateSQL = "update lib_borrower set pwd=? where borrower_id=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(1, pwd);
			psmt.setString(2, 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(Borrower value) {
		String updateSQL = "update lib_borrower set borrower=?,login=?,email=?,phone=?,isStudent=? where borrower_id=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(1, value.getBorrower());
			psmt.setString(2, value.getLogin());
			psmt.setString(3, value.getEmail());
			psmt.setString(4, value.getPhone());
			psmt.setString(5, value.getIsStudent());
			psmt.setString(6, value.getBorrower_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 updateNum(Borrower value) {
		String updateSQL = "update lib_borrower set borrow_num=? where borrower_id=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setInt(1, value.getBorrow_num());
			psmt.setString(2, value.getBorrower_id());			
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return true;
	}

}