// UPDATE
public void update(Author author) throws Exception {
Query query = new Query();
query.addCriteria(Criteria.where("_id").is(author.getAuthorId()));
Update update = new Update();
update.set("authorName", author.getAuthorName());
// FindAndModifyOptions().returnNew(true) = newly updated document
// FindAndModifyOptions().returnNew(false) = old document (not update yet)
mongoOps.findAndModify(
query, update, new FindAndModifyOptions().returnNew(true), Author.class, AUTHOR_COLLECTION);
}
@Override
public List<Author> extractDataFirstLevel(ResultSet rs) throws Exception {
List<Author> authors = new ArrayList<Author>();
while (rs.next()) {
Author a = new Author();
a.setAuthorId(rs.getInt("authorId"));
a.setAuthorName(rs.getString("authorName"));
authors.add(a);
}
return authors;
}
private void deleteBookAuthor(Book book) {
Connection conn;
try {
conn = getConnection();
AuthorDAO authorDAO = new AuthorDAO(conn);
BookDAO bookDAO = new BookDAO(conn);
List<Author> allAuthors =
(List<Author>)
authorDAO.read(
"SELECT * FROM tbl_author JOIN tbl_book_authors ON tbl_author.authorId = tbl_book_authors.authorId WHERE bookId = ?",
new Object[] {book.getBookId()});
ArrayList<Author> toDelete = new ArrayList<Author>();
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 Author");
int author = getChoiceNumber(allAuthors, actions);
if (author == -1) {
more = false;
} else {
toDelete.add(allAuthors.get(author - 1));
allAuthors.remove(author - 1);
System.out.println("Delete more authors?");
displayOptions(questions);
int next = getInputInt(1, 2);
more = next == 1;
}
} while (more);
List<Author> curAuthors = book.getAuthors();
for (Author auth : toDelete) {
authorDAO.save(
"DELETE FROM tbl_book_authors WHERE authorId = ? AND bookId =?",
new Object[] {auth.getAuthorId(), book.getBookId()});
}
conn.commit();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void addBookAuthor(Book book) {
Connection conn;
try {
conn = getConnection();
AuthorDAO authorDAO = new AuthorDAO(conn);
BookDAO bookDAO = new BookDAO(conn);
List<Author> allAuthors =
(List<Author>)
authorDAO.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<Author> newAuthors = new ArrayList<Author>();
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 Author");
int author = getChoiceNumber(allAuthors, actions);
if (author == -1) {
more = false;
} else {
newAuthors.add(allAuthors.get(author - 1));
allAuthors.remove(author - 1);
System.out.println("Add more authors?");
displayOptions(questions);
int next = getInputInt(1, 2);
more = next == 1;
}
} while (more);
for (Author auth : newAuthors) {
bookDAO.save(
"INSERT INTO tbl_book_authors (bookId,authorId) VALUES(?,?)",
new Object[] {book.getBookId(), auth.getAuthorId()});
}
conn.commit();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void UpdateAuthor2(int AuthorId) throws SQLException {
boolean exit_Add = false;
String name;
do {
System.out.println("Enter the new Author Name you want to Update:");
try {
name = sc.nextLine().trim();
Map<Integer, String> m = as.listAuthorsFirstLevel2();
if (name != null && name.length() > 0 && name.length() <= 45 && m.containsKey(AuthorId)) {
Author a = new Author();
a.setAuthorName(name);
a.setAuthorId(AuthorId);
as.updateAuthor(a);
System.out.println(" Author Updated Successfully");
} else {
System.out.println("This Id does not exist or is more than 45 caracters long.");
}
} catch (Exception e) {
System.out.println("INFO:An error has occured!");
}
if (!exit_Add) {
System.out.println();
System.out.println();
System.out.println("Press ' q ' to return or any other key to continue");
String option = (sc.nextLine());
if (option.length() > 0) {
switch (option.charAt(0)) {
case 'q':
exit_Add = true;
break;
default:
exit_Add = false;
break;
}
}
}
} while (!exit_Add);
}
@Override
public List<Author> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<Author> authors = new ArrayList<Author>();
while (rs.next()) {
Author a = new Author();
// a.setAuthorId(rs.getInt("authorId"));
a.setAuthorName(rs.getString("authorName"));
// try {
// List<Book> books = template.query("select * from tbl_book where bookId In"
// + "(select bookId from tbl_book_authors where authorId=?)", new Object[]
// {rs.getInt("authorId")}, bookDAO);
// a.setBooks(books);
// } catch (Exception e) {
// e.printStackTrace();
// }
authors.add(a);
}
return authors;
}
@Override
public List<Author> extractData(ResultSet rs) throws Exception {
List<Author> authors = new ArrayList<Author>();
BookDAO bDao = new BookDAO(getConnection());
while (rs.next()) {
Author a = new Author();
a.setAuthorId(rs.getInt("authorId"));
a.setAuthorName(rs.getString("authorName"));
@SuppressWarnings("unchecked")
List<Book> books =
(List<Book>)
bDao.readFirstLevel(
"select * from tbl_book where bookId In"
+ "(select bookId from tbl_book_authors where authorId=?)",
new Object[] {rs.getInt("authorId")});
a.setBooks(books);
authors.add(a);
}
return authors;
}
/** *************************************************************************************** */
public void DeleteAuthor(Author author) throws Exception {
ConnectionUtil c = new ConnectionUtil();
Connection conn = c.createConnection();
try {
if (author == null
|| author.getAuthorName() == null
|| author.getAuthorName().length() == 0
|| author.getAuthorName().length() > 45) {
throw new Exception("The Author cannot be null");
} else {
AuthorDAO adao = new AuthorDAO(conn);
adao.delete(author);
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
conn.rollback();
} finally {
conn.close();
}
}
private void addAuthor() {
System.out.println("Enter new author's name: [N/A to cancel]");
String name = getInputString();
if (!name.equals("N/A")) {
Author author = new Author();
author.setAuthorName(name);
try {
Connection conn = getConnection();
try {
AuthorDAO authorDAO = new AuthorDAO(conn);
authorDAO.create(author);
conn.commit();
conn.close();
} catch (Exception e) {
conn.rollback();
}
} catch (Exception e) {
System.err.println("Error while connecting to Database");
e.printStackTrace();
}
}
}
private void deleteAuthor(Author author) {
try {
Connection conn = getConnection();
try {
AuthorDAO authorDAO = new AuthorDAO(conn);
BookDAO bookDAO = new BookDAO(conn);
List<Book> authorBooks =
(List<Book>)
bookDAO.read(
"SELECT * FROM tbl_book WHERE tbl_book.bookId IN (SELECT tbl_book_authors.bookId FROM tbl_book_authors WHERE authorId = ?)",
new Object[] {author.getAuthorId()});
if (authorBooks.size() > 0) {
ArrayList<String> answers = new ArrayList<String>();
answers.add("No, nevermind");
answers.add("Yes, delete this author");
System.out.println(
author + " has (co)authored " + authorBooks.size() + " books in our records");
System.out.println(
"Are you sure you still want to delete? Some books may remain with no author");
displayOptions(answers);
int in = getInputInt(1, 2);
if (in == 1) {
return;
}
}
System.out.println("here");
authorDAO.delete(author);
conn.commit();
conn.close();
} catch (Exception e) {
conn.rollback();
conn.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
System.err.println("Error while connecting to database");
e.printStackTrace();
}
}
private void editAuthor(Author author) {
try {
Connection conn = getConnection();
System.out.println("Enter new name for " + author + " :[N/A to cancel]");
String name = getInputString();
if (!name.equals("N/A")) {
try {
System.out.println("Committed!!!!!!!!!!!!!!!!!!!");
AuthorDAO authorDAO = new AuthorDAO(conn);
author.setAuthorName(name);
authorDAO.update(author);
conn.commit();
conn.close();
} catch (Exception e) {
conn.rollback();
conn.close();
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
System.err.println("Error while connecting to database");
e.printStackTrace();
}
}
private void AddAuthor() throws Exception {
boolean exit_Add = false;
String name;
int pubId;
Set<Book> set = as.listbook();
Author a = new Author();
Publisher p;
Set<Integer> choices = new HashSet<Integer>();
System.out.println("\nBook ID | Book Title ");
System.out.println("----------------------------------------------------------------------");
Set<Integer> sk = new HashSet<Integer>();
Hashtable<Integer, Book> m = new Hashtable<Integer, Book>(); // used to retrieve later one book
for (Book s : set) {
sk.add(s.getBookId());
m.put(s.getBookId(), s);
System.out.println(s.getBookId() + "\t " + s.getBookId() + "\t ");
}
do {
boolean exit = false;
do {
System.out.println();
System.out.println("Enter the Book Id from this Author:");
try {
int bookId = Integer.parseInt(sc.nextLine().trim());
if (sk.contains(bookId)) {
choices.add(bookId);
}
System.out.println("Press 'q' to stop adding or any key to continue adding");
String option = (sc.nextLine());
if (option.length() > 0) {
switch (option.charAt(0)) {
case 'q':
exit = true;
break;
default:
exit = false;
break;
}
}
} catch (Exception e) {
System.out.println(" Bad Id");
}
} while (!exit);
try {
System.out.println("Enter the Author Name:");
name = sc.nextLine();
if (name != null && name.length() > 0 && name.length() <= 45) {
if (choices.size() > 0) {
a.setAuthorName(name);
for (Map.Entry<Integer, Book> map : m.entrySet()) {
if (choices.contains(map.getKey())) {
a.addBook(map.getValue());
}
}
as.createAuthor(a);
System.out.println("Author added successfuly");
exit_Add = true;
} else {
a.setAuthorName(name);
as.createAuthor(a);
exit_Add = true;
System.out.println("Author with no book added successfully ");
}
} else {
System.out.println();
System.out.println("Error: The Name cannot be empty or more than 45 characters");
}
} catch (SQLException e) {
System.out.println(e);
e.printStackTrace();
}
if (!exit_Add) {
System.out.println();
System.out.println();
System.out.println("Press ' q ' to return or any other key to continue");
String option = (sc.nextLine());
if (option.length() > 0) {
switch (option.charAt(0)) {
case 'q':
exit_Add = true;
break;
default:
exit_Add = false;
break;
}
}
}
} while (!exit_Add);
}
// CREATE
public void create(Author author) throws Exception {
// template.update("insert into tbl_author (authorName) values(?)",
// new Object[] { author.getAuthorName() });
author.setAuthorId(getNextSequenceId(AUTHOR_COLLECTION));
mongoOps.insert(author, AUTHOR_COLLECTION);
}
public void delete(Author author) throws Exception {
save("delete from tbl_author where authorId = ?", new Object[] {author.getAuthorId()});
}
public void update(Author author) throws Exception {
save(
"update tbl_author set authorName = ? where authorId = ?",
new Object[] {author.getAuthorName(), author.getAuthorId()});
}
public void create(Author author) throws Exception {
save("insert into tbl_author (authorName) values(?)", new Object[] {author.getAuthorName()});
}