@Override
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
Book book = new Book();
book.setIsbn(rs.getString("ISBN"));
book.setTitle(rs.getString("title"));
book.setDescription(rs.getString("description"));
book.setPrice(rs.getDouble("price"));
book.setPublisher(rs.getString("publisher"));
book.setPubDate(rs.getDate("pubdate"));
book.setEdition(rs.getInt("edition"));
book.setPages(rs.getInt("pages"));
book.setStock(rs.getInt("stock"));
book.setCategories(getCategoriesbyIsbn(book.getIsbn()));
book.setAuthors(getAuthorsbyIsbn(book.getIsbn()));
return book;
}
@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()));
}
}
@Override
public void updateStock(List<Book> books) {
for (Book book : books) {
if (getStockByIsbn(book.getIsbn()) == 0) {
if (book.getStock() > 0) {
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
String query = "insert into " + stockTable + " (isbn,stock) values(?,?)";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, book.getIsbn());
ps.setInt(2, book.getStock());
return ps;
}
});
}
} else {
if (book.getStock() == 0) {
removeStockEntry(book.getIsbn());
}
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String query = "update " + stockTable + " set stock = ? where isbn = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, book.getStock());
ps.setString(2, book.getIsbn());
return ps;
}
});
}
}
}
@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()));
}
}