bookoper.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.Book;

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

	// 用flag来标识查找方式
	// 0----查找全部
	// 1----新书推荐
	// 2----模糊查找
	public static Vector getInfo(int flag, String queryString) {
		Vector v = new Vector();
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			String querySQL = null;
			if (flag == 0)
				querySQL = "select * from lib_bok order by to_number(isbn)";
			if (flag == 1)
				querySQL = "select * from lib_bok where isNew=1 order by to_number(isbn)";
			// 模糊查找
			if (flag == 2)
				querySQL = "select * from lib_bok where " + "book like '%'||'"
						+ queryString + "'||'%' or " + "author like '%'||'"
						+ queryString + "'||'%' or " + "category like '%'||'"
						+ queryString + "'||'%' or " + "publisher like '%'||'"
						+ queryString + "'||'%' " + "order by to_number(isbn)";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			while (set.next()) {
				String isbn = set.getString("isbn");
				String book = set.getString("book");
				String author = set.getString("author");
				String category_id = set.getString("category_id");
				String publisher_id = set.getString("publisher_id");
				float unitprice = set.getFloat("unitprice");
				int copies = set.getInt("copies");
				int stocks = set.getInt("stocks");
				float fine = set.getFloat("fine");
				String isNew = set.getString("isNew");

				String category = set.getString("category");
				String publisher = set.getString("publisher");
				v.addElement(new Book(isbn, book, author, category_id,
						publisher_id, unitprice, copies, stocks, fine, isNew,
						category, publisher));
			}
		} 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_bok where isbn=?";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);

			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				String isbn = set.getString("isbn");
				String book = set.getString("book");
				String author = set.getString("author");
				String category_id = set.getString("category_id");
				String publisher_id = set.getString("publisher_id");
				float unitprice = set.getFloat("unitprice");
				int copies = set.getInt("copies");
				int stocks = set.getInt("stocks");
				float fine = set.getFloat("fine");
				String isNew = set.getString("isNew");

				String category = set.getString("category");
				String publisher = set.getString("publisher");
				v.addElement(new Book(isbn, book, author, category_id,
						publisher_id, unitprice, copies, stocks, fine, isNew,
						category, publisher));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return v;
	}

	// 添加一条记录
	// 新书目添加,故自动设置为 NEW
	// 可借数量设为库存数量
	public static boolean add(Book value) {
		boolean flag = false;
		String insertSQL = "insert into lib_book(isbn, book,author,category_id,publisher_id,unitprice,copies,stocks,fine,isNew) values(to_char(lib_book_id.nextval),?,?,?,?,?,?,?,?,1)";

		PreparedStatement psmt = null;
		int rows = 0;
		try {
			psmt = con.prepareStatement(insertSQL);
			psmt.setString(1, value.getBook());
			psmt.setString(2, value.getAuthor());
			psmt.setString(3, value.getCategory_id());
			psmt.setString(4, value.getPublisher_id());
			psmt.setFloat(5, value.getUnitprice());
			psmt.setInt(6, value.getStocks());// !!!!!!!
			psmt.setInt(7, value.getStocks());
			psmt.setFloat(8, value.getFine());

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

	public static boolean delete(String id) {
		// Connection con = DBConnection.getConnection();
		String deleteSQL = "delete from lib_book where isbn=? ";
		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;
	}

	// 书目编辑时,不能修改此书籍在库副本数量copies
	// 只能修改总库存数量stocks
	// 由前台得到的 已借出数量 来计算 新可借数量
	public static boolean update(Integer borrowedNum, Book value) {
		// 要改book,author,category_id,unitprice,copies,stocks,fine,isNew
		String updateSQL = "update  lib_book  set book=?,author=?,category_id=?,publisher_id=?,unitprice=?,stocks=?,fine=?,isNew=?,copies=? where isbn=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(1, value.getBook());
			psmt.setString(2, value.getAuthor());
			psmt.setString(3, value.getCategory_id());
			psmt.setString(4, value.getPublisher_id());
			psmt.setFloat(5, value.getUnitprice());
			psmt.setInt(6, value.getStocks());

			psmt.setFloat(7, value.getFine());
			String isNew = value.getIsNew();

			if (isNew.equals("1")) {
				psmt.setInt(8, 1);
			} else {
				psmt.setInt(8, 0);
			}

			// !!!!
			psmt.setInt(9, value.getStocks() - borrowedNum);

			psmt.setString(10, value.getIsbn());

			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
				return false;
			}
		}
		return true;
	}

	// 可借数量(副本数量修改),用于借书还书流程
	public static boolean updateNum(Book value) {
		String updateSQL = "update  lib_book  set copies=? where isbn=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setInt(1, value.getCopies());
			psmt.setString(2, value.getIsbn());
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
				return false;
			}
		}
		return true;
	}

	// 查询此书的罚金,用于借阅者罚金计算
	public static float getFine(String isbn) {
		Statement stmt = null;
		float fine = 0f;
		try {
			stmt = con.createStatement();
			String querySQL = "select fine from lib_book where isbn = '" + isbn
					+ "'";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			while (set.next()) {
				fine = set.getFloat("fine");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return fine;
	}

	// 传入publisher_id 得到 book,用于 出版社删除时检查
	public static Vector queryPublisher(String k) {
		Vector v = new Vector();
		String getSQL = "select * from lib_bok where publisher_id=?";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);

			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				String isbn = set.getString("isbn");
				String book = set.getString("book");
				String author = set.getString("author");
				String category_id = set.getString("category_id");
				String publisher_id = set.getString("publisher_id");
				float unitprice = set.getFloat("unitprice");
				int copies = set.getInt("copies");
				int stocks = set.getInt("stocks");
				float fine = set.getFloat("fine");
				String isNew = set.getString("isNew");

				String category = set.getString("category");
				String publisher = set.getString("publisher");
				v.addElement(new Book(isbn, book, author, category_id,
						publisher_id, unitprice, copies, stocks, fine, isNew,
						category, publisher));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return v;
	}

	// 分页显示
	public static Vector pagination(int currentPage, int pageSize) {

		Vector v = new Vector();
		String shou = "select * from (select rownum rowNO,tablea.* from (select * from lib_bok) tablea) where rowNO>"
				+ (currentPage - 1)
				* pageSize
				+ " and rowNO<="
				+ currentPage
				* pageSize + "  order by to_number(isbn)";
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet set = stmt.executeQuery(shou);
			while (set.next()) {
				String isbn = set.getString("isbn");
				String book = set.getString("book");
				String author = set.getString("author");
				String category_id = set.getString("category_id");
				String publisher_id = set.getString("publisher_id");
				float unitprice = set.getFloat("unitprice");
				int copies = set.getInt("copies");
				int stocks = set.getInt("stocks");
				float fine = set.getFloat("fine");
				String isNew = set.getString("isNew");

				String category = set.getString("category");
				String publisher = set.getString("publisher");
				v.addElement(new Book(isbn, book, author, category_id,
						publisher_id, unitprice, copies, stocks, fine, isNew,
						category, publisher));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return v;
	}

	public static int getRow() {

		Vector v = new Vector();
		int row = 0;
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet set = stmt.executeQuery("select * from lib_bok ");
			while (set.next()) {
				row++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return row;
	}
}