categoryoper.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.Category;
import com.lib.db.Category;

public class CategoryOper {
	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_category order by to_number(category_id)");
			while (set.next()) {
				String category_id = set.getString("category_id");
				String category = set.getString("category");
				v.addElement(new Category(category_id, category));
			}
		} 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 category_id,category from lib_category where category_id=?";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);

			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				String category_id = set.getString("category_id");
				String category = set.getString("category");
				v.addElement(new Category(category_id, category));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return v;
	}
	
	
	//用于复合框选择
	//传入category查到 categroy_id
	public static String getId(String k) {
		String category_id  = null;
		String getSQL = "select category_id from lib_category where category=?";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);
			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				category_id = set.getString("category_id");				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return category_id;
	}

	public static boolean add(Category value) {
		boolean flag = false;
		String insertSQL = "insert into lib_category(category_id,category) values(to_char(lib_category_id.nextval),?)";

		PreparedStatement psmt = null;
		int rows = 0;
		try {
			psmt = con.prepareStatement(insertSQL);
			psmt.setString(1, value.getCategory());

			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_category where category_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(Category value) {
		String updateSQL = "update  lib_category  set category=? where category_id=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(2, value.getCategory_id());
			psmt.setString(1, value.getCategory());
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return true;
	}
}