package 畢業(yè)設(shè)計(jì);
import java.sql.*;
import java.util.*;
public class RecordConDB {
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement pst;
    public RecordConDB() {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ex) {
            System.out.println("Driver 出錯(cuò)");
        }
        try {
            String url = "jdbc:odbc:chenhaiLibrary";
            con = DriverManager.getConnection(url);
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);
        } catch (SQLException ex1) {
            System.out.println("lib 出錯(cuò)");
        }
        try {
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public boolean AddRecord(String ReaId, String LibId, String BarCode,
                          String LoanTime, String RevertibleTime,
                          String Operator) {
        boolean Success = true;
        try {
            String strSQL = "insert Record values ( '" + ReaId + "', '" +
                            LibId + "', '" + BarCode + "', '" + LoanTime +
                            "', '', '" +
                            RevertibleTime + "', '" + Operator + "')";
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
            Success = false;
        }
        return Success;
    }

    public Vector SearchRecord(String LibId, String BarCode, String ReaId) {
        Vector vt = new Vector();
        String strSQL = "";
        if (BarCode.equals(""))
            strSQL = "select * from Record where ReaId = '" + ReaId +
                     "' and LibId = '" + LibId + "' and ReturnTime = ''";
        else
            strSQL = "select * from Record where ReaId = '" + ReaId +
                     "' and BarCode = '" + BarCode + "' and ReturnTime = ''";
        try {
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                Record rd = new Record();
                rd.setId(rs.getString(1));
                rd.setReaId(rs.getString(2));
                rd.setLibId(rs.getString(3));
                rd.setBarCode(rs.getString(4));
                rd.setLoanTime(rs.getString(5));
                rd.setReturnTime(rs.getString(6));
                rd.setRevertibleTime(rs.getString(7));
                rd.setOperator(rs.getString(8));
                vt.add(rd);
            }
            rs.close();
        } catch (SQLException ex) {
        }
        return vt;
    }

    public Vector SearchLoanRecord() {
        Vector vt = new Vector();
        try {
            String strSQL = "select * from Record where ReturnTime = ''";
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                Vector tempvt = new Vector();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    tempvt.add(rs.getString(i));
                }
                vt.add(tempvt);
            }
            rs.close();
        } catch (SQLException ex) {
        }
        return vt;
    }

    public Vector SearchAlreadyRecord(String ReaId) {
        Vector vt = new Vector();
        try {
            String strSQL = "select * from Record where ReaId = '" + ReaId +
                            "' and ReturnTime = ''";
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                Vector tempvt = new Vector();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    tempvt.add(rs.getString(i));
                }
                vt.add(tempvt);
            }
            rs.close();
        } catch (SQLException ex) {
        }
        return vt;
    }

    public void ReturnBookRecord(int Id, String ReturnTime) {
        try {
            String strSQL = "update Record set ReturnTime = '" + ReturnTime +
                            "' where Id = " + Id;
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
        }
    }

    public void CloseRecordDB() {
        try {
            st.close();
            con.close();
        } catch (SQLException ex) {
        }
    }

    private void jbInit() throws Exception {
    }
}