?? borroweroper.java
字號:
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;
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -