public void createNewItem(Item newItem, String sellername)
      throws SQLException, Comp9321Assign2Exception {
    // pstmt1 for inserting item table, pstmt2 inserting solditem table
    PreparedStatement pstmt1 =
        connection.prepareStatement(
            "INSERT INTO `Item` "
                + "(`title`, `author`, `editor`, `year`, `type`, `journal`, `volume`, `number`, `publisher`, `isbn`, `pages`, `picture`, `booktitle`, `price`, `pause`) "
                + "VALUES "
                + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \n",
            Statement.RETURN_GENERATED_KEYS);
    pstmt1.setString(1, newItem.getTitle());
    pstmt1.setString(2, newItem.getAuthor());
    pstmt1.setString(3, newItem.getEditor());
    pstmt1.setInt(4, newItem.getYear());
    pstmt1.setString(5, newItem.getType());
    pstmt1.setString(6, newItem.getJournal());
    pstmt1.setInt(7, newItem.getVolume());
    pstmt1.setInt(8, newItem.getNumber());
    pstmt1.setString(9, newItem.getPublisher());
    pstmt1.setString(10, newItem.getIsbn());
    pstmt1.setString(11, newItem.getPages());
    pstmt1.setString(12, newItem.getPicture());
    pstmt1.setString(13, newItem.getBooktitle());
    pstmt1.setFloat(14, newItem.getPrice());
    // pause value of a new item should be 0 while it is being created
    pstmt1.setInt(15, 0);
    int rst1 = pstmt1.executeUpdate();

    PreparedStatement pstmt2 =
        connection.prepareStatement(
            "INSERT INTO `sellitem` " + "(`sellername`, `itemid`) " + "VALUES " + "(?, ?) \n");
    ResultSet rs = pstmt1.getGeneratedKeys();
    if (rs.next()) {
      int id = Integer.parseInt(rs.getObject(1).toString());
      System.out.println(id);
      pstmt2.setString(1, sellername);
      pstmt2.setInt(2, id);
    }
    int rst2 = pstmt2.executeUpdate();
    connection.close();
    if (rst1 != 1 || rst2 != 1) {
      throw new Comp9321Assign2Exception("failed in creating new item");
    }
  }
  public void updateSellingItem(Item newItem, int id)
      throws SQLException, Comp9321Assign2Exception {
    // pstmt1 for inserting item table, pstmt2 inserting solditem table
    PreparedStatement pstmt1 =
        connection.prepareStatement(
            "update item set title = ?, author = ?, editor = ?, year = ?, type = ?, journal = ?, volume = ?, number = ?, publisher = ?, isbn = ?, pages = ?, picture = ?, booktitle = ?, price = ?, pause = ? where id = ?;");
    pstmt1.setString(1, newItem.getTitle());
    pstmt1.setString(2, newItem.getAuthor());
    pstmt1.setString(3, newItem.getEditor());
    pstmt1.setInt(4, newItem.getYear());
    pstmt1.setString(5, newItem.getType());
    pstmt1.setString(6, newItem.getJournal());
    pstmt1.setInt(7, newItem.getVolume());
    pstmt1.setInt(8, newItem.getNumber());
    pstmt1.setString(9, newItem.getPublisher());
    pstmt1.setString(10, newItem.getIsbn());
    pstmt1.setString(11, newItem.getPages());
    pstmt1.setString(12, newItem.getPicture());
    pstmt1.setString(13, newItem.getBooktitle());
    pstmt1.setFloat(14, newItem.getPrice());
    // pause value of a new item should be 0 while it is being created
    pstmt1.setInt(15, 0);
    pstmt1.setInt(16, id);
    int rst1 = pstmt1.executeUpdate();

    //		PreparedStatement pstmt2 = connection.prepareStatement(
    //				"update sellitem set sellername = ?, itemid = ? where buyername = ? and itemid = ?;");
    //		ResultSet rs = pstmt1.getGeneratedKeys();
    //		if (rs.next()) {
    //			int id = Integer.parseInt(rs.getObject(1).toString());
    //			System.out.println(id);
    //			pstmt2.setString(1, sellername);
    //			pstmt2.setInt(2, id);
    //
    //		}
    //		int rst2 = pstmt2.executeUpdate();
    connection.close();
    if (rst1 != 1) {
      throw new Comp9321Assign2Exception("failed in creating new item");
    }
  }