private void deleteBookGenre(Book book) { Connection conn; try { conn = getConnection(); GenreDAO authorDAO = new GenreDAO(conn); BookDAO bookDAO = new BookDAO(conn); List<Genre> allGenres = (List<Genre>) authorDAO.read( "SELECT * FROM tbl_genre JOIN tbl_book_genres ON tbl_genre.genre_id = tbl_book_genres.genre_id WHERE bookId = ?", new Object[] {book.getBookId()}); ArrayList<Genre> toDelete = new ArrayList<Genre>(); ArrayList<String> actions = new ArrayList<String>(); actions.add("Cancel"); ArrayList<String> questions = new ArrayList<String>(); questions.add("Yes"); questions.add("No"); boolean more = true; do { System.out.println("Choose Genre"); int genre = getChoiceNumber(allGenres, actions); if (genre == -1) { more = false; } else { toDelete.add(allGenres.get(genre - 1)); allGenres.remove(genre - 1); if (allGenres.isEmpty()) { more = false; break; } System.out.println("Delete more genres?"); displayOptions(questions); int next = getInputInt(1, 2); more = next == 1; } } while (more); List<Author> curAuthors = book.getAuthors(); for (Genre genre : toDelete) { authorDAO.save( "DELETE FROM tbl_book_genres WHERE genre_id = ? AND bookId =?", new Object[] {genre.getGenreId(), book.getBookId()}); } conn.commit(); conn.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
private void addBookGenre(Book book) { Connection conn; try { conn = getConnection(); GenreDAO genreDAO = new GenreDAO(conn); BookDAO bookDAO = new BookDAO(conn); List<Genre> allGenres = (List<Genre>) genreDAO.read( "SELECT * FROM tbl_author WHERE tbl_author.authorId NOT IN (SELECT tbl_book_authors.authorId FROM tbl_book_authors WHERE bookId=?)", new Object[] {book.getBookId()}); ArrayList<Genre> newGenres = new ArrayList<Genre>(); ArrayList<String> actions = new ArrayList<String>(); actions.add("Cancel"); ArrayList<String> questions = new ArrayList<String>(); questions.add("Yes"); questions.add("No"); boolean more = true; do { System.out.println("Choose Genre"); int author = getChoiceNumber(allGenres, actions); if (author == -1) { more = false; } else { newGenres.add(allGenres.get(author - 1)); allGenres.remove(author - 1); if (allGenres.isEmpty()) { more = false; break; } System.out.println("Add more genres?"); displayOptions(questions); int next = getInputInt(1, 2); more = next == 1; } } while (more); for (Genre genre : newGenres) { bookDAO.save( "INSERT INTO tbl_book_genres (bookId,genre_id) VALUES(?,?)", new Object[] {book.getBookId(), genre.getGenreId()}); } conn.commit(); conn.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
// a method updating the genre table public void update(Genre genre) throws Exception { save( "update tbl_genre set genre_name = ? where genre_id = ?", new Object[] {genre.getGenreName(), genre.getGenreId()}); }
// a method to delete the genre table public void delete(Genre genre) throws Exception { save("delete from tbl_genre where genre_id = ?", new Object[] {genre.getGenreId()}); }
// a method to create the Genre table public void create(Genre genre) throws Exception { save( "insert into tbl_genre (genre_id, genre_name) values(?,?)", new Object[] {genre.getGenreId(), genre.getGenreName()}); }