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

public class PublisherOper {
	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_publisher order by to_number(publisher_id)");
			while (set.next()) {
				String publisher_id = set.getString("publisher_id");
				String publisher = set.getString("publisher");
				v.addElement(new Publisher(publisher_id, 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 publisher_id,publisher from lib_publisher where publisher_id=?";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);

			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				String publisher_id = set.getString("publisher_id");
				String publisher = set.getString("publisher");
				v.addElement(new Publisher(publisher_id, publisher));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return v;
	}
	
	//用于復合框選擇
	//傳入publisher查到 publisher_id
	public static String getId(String k) {
		String publisher_id  = null;
		String getSQL = "select publisher_id from lib_publisher where publisher=?";
		PreparedStatement psmtk = null;
		try {
			psmtk = con.prepareStatement(getSQL);
			psmtk.setString(1, k);
			ResultSet set = psmtk.executeQuery();
			while (set.next()) {
				publisher_id = set.getString("publisher_id");				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				psmtk.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return publisher_id;
	}

	public static boolean add(Publisher value) {
		boolean flag = false;
		String insertSQL = "insert into lib_publisher(publisher_id,publisher) values(to_char(lib_publisher_id.nextval),?)";

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

			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_publisher where publisher_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(Publisher value) {
		String updateSQL = "update  lib_publisher  set publisher=? where publisher_id=?";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(2, value.getPublisher_id());
			psmt.setString(1, value.getPublisher());
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return true;
	}

	public static void main(String args[]) throws SQLException {
		PublisherOper.getInfo().iterator();
		con.close();

	}
}