package 畢業(yè)設(shè)計(jì);
import java.sql.*;
import java.util.*;
public class BookConDB {
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement pst;
    //**********************************連接數(shù)據(jù)庫(kù)********************************
     public BookConDB() {
         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();
         }
     }

//************************************添加圖書(shū)**********************************
     public void AddBook(String Id, String BarCode, String Name, String Author,
                         String Page, String Publish, String Isbn, String Price,
                         String Sort, String IntoTime, String Location,
                         String Synopsis) {
         try {
             String strSQL = "insert Book values ('" + Id + "', '" + BarCode +
                             "', '" +
                             Name + "', '" + Author + "','" + Page + "', '" +
                             Publish +
                             "', '" + Isbn + "', '" + Price + "', '" + Sort +
                             "', '" +
                             IntoTime + "', '" + Location + "', '在庫(kù)', 0, '" +
                             Synopsis + "')";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

//****************************查詢所有圖書(shū)*************************************
     public Vector SearchAll() {
         Vector vt = new Vector();
         try {
             String str = "select * from Book";
             rs = st.executeQuery(str);
             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;
     }

//**********************************查詢圖書(shū)前50名*******************************
     public Vector SearchTop() {
         Vector vt = new Vector();
         int k = 1;
         try {
             String str = "select TOP 50 * from Book order by LoanCount DESC";
             rs = st.executeQuery(str);
             while (rs.next()) {
                 Vector tempvt = new Vector();
                 tempvt.add(String.valueOf(k++));
                 for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                     tempvt.add(rs.getString(i));
                 }
                 vt.add(tempvt);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

//********************************查詢圖書(shū)****************************************
     public Vector SearchBook(String Id, String BarCode, String Name,
                              String Author,
                              String Publish, String Sort, boolean Isnot) {
         Vector vt = new Vector();
         String str = "";
         if (Isnot) {
             for (int i = 0; i < 6; i++) {
                 String tem = "";
                 if (i == 0) {
                     if (!Id.equals("")) {
                         tem = " and Id = '" + Id + "'";
                     }
                 } else if (i == 1) {
                     if (!BarCode.equals("")) {
                         tem = " and BarCode = '" + BarCode + "'";
                     }
                 } else if (i == 2) {
                     if (!Name.equals("")) {
                         tem = " and Name = '" + Name + "'";
                     }
                 } else if (i == 3) {
                     if (!Author.equals("")) {
                         tem = " and Author = '" + Author + "'";
                     }
                 } else if (i == 4) {
                     if (!Publish.equals("")) {
                         tem = " and Publish = '" + Publish + "'";
                     }
                 } else if (i == 5) {
                     if (!Sort.equals("")) {
                         tem = " and Sort = '" + Sort + "'";
                     }
                 }
                 if (!tem.equals("")) {
                     str = str + tem;
                 }
             }
         } else {
             for (int i = 0; i < 6; i++) {
                 String tem = "";
                 if (i == 0) {
                     if (!Id.equals("")) {
                         tem = " and Id like '%" + Id + "%'";
                     }
                 } else if (i == 1) {
                     if (!BarCode.equals("")) {
                         tem = " and BarCode like '%" + BarCode + "%'";
                     }
                 } else if (i == 2) {
                     if (!Name.equals("")) {
                         tem = " and Name like '%" + Name + "%'";
                     }
                 } else if (i == 3) {
                     if (!Author.equals("")) {
                         tem = " and Author like '%" + Author + "%'";
                     }
                 } else if (i == 4) {
                     if (!Publish.equals("")) {
                         tem = " and Publish like '%" + Publish + "%'";
                     }
                 } else if (i == 5) {
                     if (!Sort.equals("")) {
                         tem = " and Sort like '%" + Sort + "%'";
                     }
                 }
                 if (!tem.equals("")) {
                     str = str + tem;
                 }
             }
         }
         try {
             String strSQL = "select * from Book where 1=1" + str;
             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;
     }

    //**************************借書(shū)還書(shū)時(shí)查詢圖書(shū)信息*****************************
     public Vector SearchLRBook(String Id, String BarCode, boolean Isnot) {
         Vector vt = new Vector();
         String strSQL = "";
         if (Isnot) {
             strSQL = "select * from Book where Id = '" + Id + "'";
         } else {
             strSQL = "select * from Book where BarCode = '" + BarCode + "'";
         }
         try {
             rs = st.executeQuery(strSQL);
             while (rs.next()) {
                 Book bk = new Book();
                 bk.setId(rs.getString(1));
                 bk.setBarCode(rs.getString(2));
                 bk.setName(rs.getString(3));
                 bk.setAuthor(rs.getString(4));
                 bk.setPage(rs.getString(5));
                 bk.setPublish(rs.getString(6));
                 bk.setPrice(rs.getString(7));
                 bk.setIsbn(rs.getString(8));
                 bk.setSort(rs.getString(9));
                 bk.setIntoTime(rs.getString(10));
                 bk.setLocation(rs.getString(11));
                 bk.setIsin(rs.getString(12));
                 bk.setLoanCount(rs.getString(13));
                 bk.setSynopsis(rs.getString(14));
                 vt.add(bk);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

    //*****************************查詢要更新的圖書(shū)*********************************
     public Vector SearchUpdateBook(String content, String sort, boolean isnot) {
         Vector vt = new Vector();
         String strSQL = "";
         if (isnot) {
             if (sort.equals("圖書(shū)編號(hào)")) {
                 strSQL = "select * from Book where Id = '" + content + "'";
             } else if (sort.equals("圖書(shū)條形碼")) {
                 strSQL = "select * from Book where BarCode = '" + content +
                          "'";
             } else if (sort.equals("圖書(shū)名稱")) {
                 strSQL = "select * from Book where Name = '" + content + "'";
             } else if (sort.equals("圖書(shū)作者")) {
                 strSQL = "select * from Book where Author = '" + content + "'";
             } else if (sort.equals("圖書(shū)出版社")) {
                 strSQL = "select * from Book where Publish = '" + content +
                          "'";
             } else if (sort.equals("圖書(shū)類別")) {
                 strSQL = "select * from Book where Sort = '" + content + "'";
             }
         } else {
             if (sort.equals("圖書(shū)編號(hào)")) {
                 strSQL = "select * from Book where Id like '%" + content +
                          "%'";
             } else if (sort.equals("圖書(shū)條形碼")) {
                 strSQL = "select * from Book where BarCode like '%" + content +
                          "%'";
             } else if (sort.equals("圖書(shū)名稱")) {
                 strSQL = "select * from Book where Name like '%" + content +
                          "%'";
             } else if (sort.equals("圖書(shū)作者")) {
                 strSQL = "select * from Book where Author like '%" + content +
                          "%'";
             } else if (sort.equals("圖書(shū)出版社")) {
                 strSQL = "select * from Book where Publish like '%" + content +
                          "%'";
             } else if (sort.equals("圖書(shū)類別")) {
                 strSQL = "select * from Book where Sort like '%" + content +
                          "%'";
             }
         }
         try {
             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;
     }

    //*******************************更新圖書(shū)*******************************
     public boolean UpdateBook(String Id, String BarCode, String Name,
                               String Author,
                               String Page, String Publish, String Isbn,
                               String Price,
                               String Sort, String IntoTime, String Location,
                               String Isin,
                               String LoanCount, String Synopsis) {
         boolean Success = true;
         try {
             String strSQL = "update Book set BarCode = '" + BarCode +
                             "', Name = '" +
                             Name + "', Author = '" + Author + "', Page = '" +
                             Page +
                             "', Publish = '" + Publish + "', Isbn = '" + Isbn +
                             "', Price = '" + Price + "', Sort = '" + Sort +
                             "', IntoTime = '"
                             + IntoTime + "', Location = '" + Location +
                             "', Isin = '" +
                             Isin + "', LoanCount = '" + LoanCount +
                             "', Synopsis = '" +
                             Synopsis + "' where Id = '" + Id + "'";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
             Success = false;
         }
         return Success;
     }

    //*******************************借書(shū)時(shí)更新圖書(shū)*******************************
     public void UpdateBorrowBook(String Id, String BarCode) {
         String strSQL;
         if (Id.equals(""))
             strSQL =
                     "update Book set Isin = '不在庫(kù)', LoanCount = LoanCount + 1 where BarCode = '" +
                     BarCode + "'";
         else {
             strSQL =
                     "update Book set Isin = '不在庫(kù)', LoanCount = LoanCount + 1 where Id = '" +
                     Id + "'";
         }
         try {
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //********************************還書(shū)時(shí)更新圖書(shū)******************************
     public void UpdateReturnBook(String Id, String BarCode) {
         String strSQL;
         if (Id.equals(""))
             strSQL = "update Book set Isin = '在庫(kù)' where BarCode = '" +
                      BarCode + "'";
         else {
             strSQL = "update Book set Isin = '在庫(kù)' where Id = '" + Id + "'";
         }
         try {
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //**********************************刪除圖書(shū)****************************
     public void DeleteBook(String Id) {
         try {
             String strSQL = "delete from Book where Id = '" + Id + "'";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //***************************關(guān)閉數(shù)據(jù)庫(kù)連接***********************************
     public void CloseBookDB() {
         try {
             st.close();
             con.close();
         } catch (SQLException ex) {
         }
     }

    private void jbInit() throws Exception {
    }
}