Ejemplo n.º 1
0
  @Override
  public void updateBook(String oldisbn, Book book) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String date = sdf.format(book.getPubDate());
    this.jdbcTemplate.update(
        new PreparedStatementCreator() {

          @Override
          public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            String query =
                "update "
                    + bookTable
                    + " set isbn = ?, title = ?, description = ?, price = ?, publisher = ?, pubDate = ?, edition = ?, pages = ? where isbn = ?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, book.getIsbn());
            ps.setString(2, book.getTitle());
            ps.setString(3, book.getDescription());
            ps.setDouble(4, book.getPrice());
            ps.setString(5, book.getPublisher());
            ps.setString(6, date);
            ps.setInt(7, book.getEdition());
            ps.setInt(8, book.getPages());
            ps.setString(9, oldisbn);
            return ps;
          }
        });

    this.jdbcTemplate.update(getPSCForRemoving(bookCategoriesTable, "isbn", book.getIsbn()));
    this.jdbcTemplate.update(getPSCForRemoving(bookAuthorTable, "isbn", book.getIsbn()));

    List<Category> categories = book.getCategories();
    for (Category category : categories) {
      this.jdbcTemplate.update(
          getPSCForInsertingBookCategory(book.getIsbn(), category.getCategoryId()));
    }

    List<Author> authors = book.getAuthors();
    for (Author author : authors) {
      this.jdbcTemplate.update(getPSCForInsertingBookAuthor(book.getIsbn(), author.getAuthorID()));
    }
  }
Ejemplo n.º 2
0
  @Override
  public void insertBook(Book book) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String date = sdf.format(book.getPubDate());
    this.jdbcTemplate.update(
        new PreparedStatementCreator() {

          @Override
          public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            String query =
                "insert into "
                    + bookTable
                    + " (isbn, title, description, price, publisher, pubdate, edition, pages) values (?, ?, ?, ?, ?, ?, ?, ?);";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, book.getIsbn());
            ps.setString(2, book.getTitle());
            ps.setString(3, book.getDescription());
            ps.setDouble(4, book.getPrice());
            ps.setString(5, book.getPublisher());
            ps.setString(6, date);
            ps.setInt(7, book.getEdition());
            ps.setInt(8, book.getPages());
            return ps;
          }
        });

    List<Category> categories = book.getCategories();
    for (Category category : categories) {

      this.jdbcTemplate.update(
          getPSCForInsertingBookCategory(book.getIsbn(), category.getCategoryId()));
    }

    List<Author> authors = book.getAuthors();
    for (Author author : authors) {
      this.jdbcTemplate.update(getPSCForInsertingBookAuthor(book.getIsbn(), author.getAuthorID()));
    }
  }
Ejemplo n.º 3
0
  /**
   * DANIEL: THIS IS IMPORTANnew BadUserIDExceptionT the books will be passed with the default copy
   * no (0) If it already exists, please throw a new BadCopyNumberException with the first free
   * copyno included. Might want to search for # of copies first, rather than trying to sort through
   * the SQLExceptions coming back to see if that was the reason
   *
   * @param newBook - the book to be added to the DB
   * @throws SQLException - if Oracle complains
   * @throws BadCopyNumberException - if the copy number is already taken.
   */
  public int createNewBook(Book newBook) throws SQLException, BadCopyNumberException {
    try {
      String statement;
      ResultSet rs;

      // Copy Number

      // Check if there is a
      statement =
          "select count(*) from BookCopy where callnumber ='"
              + newBook.getCallNumber()
              + "' and copyNo="
              + newBook.getCopyNo();
      rs = sql(statement, SQLType.query);
      rs.next();

      int copyCount = rs.getInt(1);

      if (copyCount == 0) {

        // book

        statement =
            "INSERT INTO Book VALUES ('"
                + newBook.getCallNumber()
                + "', "
                + newBook.getISBN()
                + ", '"
                + newBook.getTitle()
                + "', '"
                + newBook.getMainAuthor()
                + "', '"
                + newBook.getPublisher()
                + "', "
                + newBook.getYear()
                + ")";

        System.out.println(statement);
        System.out.println(newBook.getCopyNo());
        sql(statement, SQLType.insert);

        // CopyNo

        statement =
            "INSERT INTO BookCopy VALUES ('"
                + newBook.getCallNumber()
                + "',"
                + newBook.getCopyNo()
                + ",'in')";

        System.out.println(statement);
        sql(statement, SQLType.insert);
        System.out.println(statement);
        // Author

        // Add Main Author
        statement =
            "INSERT INTO HasAuthor VALUES ('"
                + newBook.getCallNumber()
                + "', '"
                + newBook.getMainAuthor()
                + "')";
        System.out.println(statement);
        sql(statement, SQLType.insert);
        System.out.println("Size: " + newBook.getAuthors().size());
        // Add Secondary Authors
        for (String s : newBook.getAuthors()) {
          statement =
              "INSERT INTO HasAuthor VALUES ('" + newBook.getCallNumber() + "', '" + s + "')";
          System.out.println(statement);
          sql(statement, SQLType.insert);
        }

        // Subject

        for (String s : newBook.getSubjects()) {
          s = s.toLowerCase();
          statement =
              "INSERT INTO HasSubject VALUES ('" + newBook.getCallNumber() + "', '" + s + "')";
          System.out.println(statement);
          sql(statement, SQLType.insert);
        }

      } else if (copyCount >= 1) {
        System.out.println("Test: " + newBook.getCopyNo());
        statement =
            "select max(copyNo) from bookCopy where callNumber='" + newBook.getCallNumber() + "'";
        rs = sql(statement, SQLType.query);
        rs.next();
        copyCount = rs.getInt(1);

        String message =
            "The copy number"
                + newBook.getCopyNo()
                + "does not exist. The next available copy number is: ";
        copyCount++;

        statement =
            "INSERT INTO BookCopy VALUES ('"
                + newBook.getCallNumber()
                + "', "
                + copyCount
                + ", "
                + "'in' "
                + ")";
        System.out.println(statement);
        sql(statement, SQLType.insert);
        throw new BadCopyNumberException(message, copyCount);
      } else {
        throw new SQLException("wasn't suposed to get here");
      }

      return newBook.getCopyNo();

    } catch (BadCopyNumberException BDCPY) {
      throw BDCPY;
    }
  }