// add book info
  public int insert(BookForm bookForm) {

    String sql = "";
    int falg = findTitle(bookForm.getIsbn());
    try {
      if (falg != 2 && falg != 0) {
        sql =
            "Insert into titles (isbn,title,typeid,author,translator,price,page,inTime,operator) values('"
                + bookForm.getIsbn()
                + "','"
                + bookForm.getTitle()
                + "',"
                + bookForm.getTypeId()
                + ",'"
                + bookForm.getAuthor()
                + "','"
                + bookForm.getTranslator()
                + "','"
                + bookForm.getPrice()
                + "','"
                + bookForm.getPage()
                + "','"
                + bookForm.getInTime()
                + "','"
                + bookForm.getOperator()
                + "')";
        falg = conn.executeUpdate(sql);
      }

    } catch (Exception ex) {
      falg = 0;
    } finally {
      conn.close();
    }
    System.out.println("falg:" + falg);
    return falg;
  }
 public int update(BookForm bookForm) {
   String sql =
       "Update titles set typeid="
           + bookForm.getTypeId()
           + ", title='"
           + bookForm.getTitle()
           + "'"
           + ",author='"
           + bookForm.getAuthor()
           + "',translator='"
           + bookForm.getTranslator()
           + "',price="
           + bookForm.getPrice()
           + ",page="
           + bookForm.getPage()
           + " where isbn='"
           + bookForm.getIsbn()
           + "'";
   int falg = conn.executeUpdate(sql);
   conn.close();
   return falg;
 }
  public BookForm findItem(String f, String key) {
    BookForm bookForm = null;
    String sql =
        "select b.*, p.pubname as publishing,t.typename from titles b left join "
            + " publishing_info p on b.ISBN=p.ISBN join booktypes t on b.typeid=t.id where b."
            + f
            + " ='"
            + key
            + "'";
    ResultSet rs = conn.executeQuery(sql);
    try {
      if (rs.next()) {
        bookForm = new BookForm();
        bookForm.setIsbn(rs.getString(1));
        bookForm.setTitle(rs.getString(2));
        bookForm.setTypeId(rs.getInt(3));
        bookForm.setAuthor(rs.getString(4));
        bookForm.setTranslator(rs.getString(5));
        bookForm.setPrice(Float.valueOf(rs.getString(6))); //
        bookForm.setPage(rs.getInt(7));
        bookForm.setInTime(rs.getString(8));
        bookForm.setOperator(rs.getString(9));
        bookForm.setDel(rs.getInt(10));

        bookForm.setPublishing(rs.getString(12));
        bookForm.setTypeName(rs.getString(13));
      }
    } catch (SQLException ex) {
    }
    conn.close();
    return bookForm;
  }
  public BookForm queryM(BookForm bookForm1) {
    BookForm bookForm = null;
    String sql =
        "select b.*,t.typename from titles b left join "
            + "booktypes t on b.typeid=t.id where b.isbn='"
            + bookForm1.getIsbn()
            + "'";
    ResultSet rs = conn.executeQuery(sql);
    try {
      while (rs.next()) {
        bookForm = new BookForm();
        bookForm.setIsbn(rs.getString(1));
        bookForm.setTitle(rs.getString(2));
        bookForm.setTypeId(rs.getInt(3));
        bookForm.setAuthor(rs.getString(4));
        bookForm.setTranslator(rs.getString(5));
        bookForm.setPrice(Float.valueOf(rs.getString(6)));
        bookForm.setPage(rs.getInt(7));
        bookForm.setInTime(rs.getString(8));
        bookForm.setOperator(rs.getString(9));
        bookForm.setDel(rs.getInt(10));

        bookForm.setPublishing(rs.getString(11));
        bookForm.setTypeName(rs.getString(12));
      }
    } catch (SQLException ex) {
    }
    conn.close();
    return bookForm;
  }
 public int delete(BookForm bookForm) {
   String sql = "Delete from titles  where isbn='" + bookForm.getIsbn() + "'";
   int falg = conn.executeUpdate(sql);
   return falg;
 }
 public Collection query(String strif) {
   BookForm bookForm = null;
   Collection bookColl = new ArrayList();
   String sql = "";
   if (strif != "all" && strif != null && strif != "") {
     sql =
         " SELECT * from( SELECT b.*, p.pubname AS publishing, t.typename FROM titles b LEFT JOIN publishing_info p ON"
             + " b.ISBN = p.ISBN JOIN booktypes t ON b.typeid = t.id ) as book WHERE book."
             + strif
             + "'";
   } else {
     sql =
         "select b.*, p.pubname as publishing,t.typename from titles b "
             + "left join publishing_info p on b.ISBN=p.ISBN join booktypes t on b.typeid=t.id ";
   }
   System.out.println("book query sql" + sql);
   ResultSet rs = conn.executeQuery(sql);
   try {
     while (rs.next()) {
       bookForm = new BookForm();
       bookForm.setIsbn(rs.getString(1));
       bookForm.setTitle(rs.getString(2));
       bookForm.setTypeId(rs.getInt(3));
       bookForm.setAuthor(rs.getString(4));
       bookForm.setTranslator(rs.getString(5));
       bookForm.setPrice(Float.valueOf(rs.getString(6)));
       bookForm.setPage(rs.getInt(7));
       bookForm.setInTime(rs.getString(8));
       bookForm.setOperator(rs.getString(9));
       bookForm.setDel(rs.getInt(10));
       bookForm.setTypeName(rs.getString("typename"));
       bookColl.add(bookForm);
     }
   } catch (SQLException ex) {
     ex.printStackTrace();
   }
   conn.close();
   return bookColl;
 }