public ArrayList<Book> searchBooks(String username, String keyword) {
ArrayList<Book> bookList = new ArrayList();
Connection connection = null;
try {
connection = getConnection();
PreparedStatement statement =
connection.prepareStatement(
"select * from "
+ username
+ " where (title like '%"
+ keyword.trim()
+ "%'"
+ "OR author like '%"
+ keyword.trim()
+ "%')");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Book book = new Book();
book.setId(resultSet.getInt("Id"));
book.setColor(resultSet.getString("Color"));
book.setLevel(resultSet.getInt("Level"));
book.setTitle(resultSet.getString("Title"));
book.setCategory(resultSet.getString("Category"));
book.setAuthor(resultSet.getString("Author"));
book.setYear(resultSet.getInt("Year"));
book.setAvailability(resultSet.getString("Availability"));
bookList.add(book);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(connection);
}
return bookList;
}
public Book getBook(String username, int id) {
Connection connection = null;
Book book = new Book();
try {
connection = getConnection();
PreparedStatement statement =
connection.prepareStatement("select * from " + username + " where id = ?");
statement.setInt(1, id);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
book.setId(id);
book.setColor(resultSet.getString("Color"));
book.setLevel(resultSet.getInt("Level"));
book.setTitle(resultSet.getString("Title"));
book.setCategory(resultSet.getString("Category"));
book.setAuthor(resultSet.getString("Author"));
book.setYear(resultSet.getInt("Year"));
book.setAvailability(resultSet.getString("Availability"));
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(connection);
}
return book;
}
public List<Book> allBooks(String username) {
ArrayList<Book> bookList = new ArrayList();
String sql = "select * from " + username;
Connection conn = null;
try {
conn = getConnection();
PreparedStatement statement = conn.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Book book = new Book();
book.setId(resultSet.getInt("Id"));
book.setColor(resultSet.getString("Color"));
book.setLevel(resultSet.getInt("Level"));
book.setTitle(resultSet.getString("Title"));
book.setCategory(resultSet.getString("Category"));
book.setAuthor(resultSet.getString("Author"));
book.setYear(resultSet.getInt("Year"));
book.setAvailability(resultSet.getString("Availability"));
bookList.add(book);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(conn);
}
return bookList;
}
/**
* Import books
*
* @param nodes
* @return
*/
private Boolean processBooks(NodeList nodes) {
NodeList books = nodes.item(0).getChildNodes();
for (int i = 0; i < books.getLength(); i++) {
if (books.item(i).getNodeType() == Node.ELEMENT_NODE) {
Element bookNode = (Element) books.item(i);
Book book = new Book();
book.setIdbook(Integer.parseInt(getTagValue("idbook", bookNode)));
book.setCode(getTagValue("code", bookNode));
book.setEdition(Integer.parseInt(getTagValue("edition", bookNode)));
book.setPages(Integer.parseInt(getTagValue("pages", bookNode)));
book.setPlace(getTagValue("place", bookNode));
book.setYear(DatatypeConverter.parseDateTime(getTagValue("year", bookNode)).getTime());
book.setType(getTagValue("type", bookNode));
book.setName(getTagValue("name", bookNode));
// find and set publisher
Publisher publisher =
publisherMgr.findByIdpublisher(Integer.parseInt(getTagValue("publisher", bookNode)));
if (publisher == null) {
continue;
}
book.setPublisher(publisher);
// find and set genre
Genre genre = genreMgr.findByIdgenre(Integer.parseInt(getTagValue("genre", bookNode)));
if (genre == null) {
continue;
}
book.setGenre(genre);
// setup book authors
List<String> authors = getTagsValues("authorCollection", bookNode);
if (book.getAuthorCollection() == null) {
book.setAuthorCollection(new ArrayList<Author>());
}
for (String authorId : authors) {
Author author = authorMgr.findByIdauthor(Integer.parseInt(authorId));
if (author != null) {
// book.getAuthorCollection().add(author);
author.getBooksCollection().add(book);
authorMgr.save(author);
}
}
try {
bookMgr.save(book);
} catch (EJBException ex) {
ex.printStackTrace(System.out);
}
}
}
return true;
}
public ArrayList<Book> advancedSearchBooks(
String username, String title, String author, String color, String level) {
ArrayList<Book> bookList = new ArrayList();
Connection connection = null;
String sql = "select * from " + username + " where ";
String sqlTitle = "(title like '%";
String sqlAuthor = "(author like '%";
String sqlColor = "(color like '%";
String sqlLevel = "(level like '%";
boolean addOn = false;
try {
connection = getConnection();
if (title.length() > 0) {
sql = sql + sqlTitle + title + "%')";
addOn = true;
}
if (author.length() > 0 && addOn) {
sql = sql + "AND" + sqlAuthor + author + "%')";
} else if (author.length() > 0) {
sql = sql + sqlAuthor + author + "%')";
addOn = true;
}
if (color.length() > 0 && addOn) {
sql = sql + "AND" + sqlColor + color + "%')";
} else if (color.length() > 0) {
sql = sql + sqlColor + color + "%')";
addOn = true;
}
if (level.length() > 0 && addOn) {
sql = sql + "AND" + sqlLevel + level + "%')";
} else if (level.length() > 0) {
sql = sql + sqlLevel + level + "%')";
}
sql = sql + ";";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Book book = new Book();
book.setId(resultSet.getInt("Id"));
book.setColor(resultSet.getString("Color"));
book.setLevel(resultSet.getInt("Level"));
book.setTitle(resultSet.getString("Title"));
book.setCategory(resultSet.getString("Category"));
book.setAuthor(resultSet.getString("Author"));
book.setYear(resultSet.getInt("Year"));
book.setAvailability(resultSet.getString("Availability"));
bookList.add(book);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(connection);
}
return bookList;
}
public Book readBookByTitle(String title) throws SQLException {
PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Books WHERE title=?;");
stmt.setString(1, title);
ResultSet rs = stmt.executeQuery();
Book book = new Book();
if (rs.next()) {
book.setId_book(rs.getInt("id_book"));
book.setTitle(rs.getString("title"));
book.setYear(rs.getInt("year"));
} else {
book = null;
}
getConnection().close();
return book;
}
public List<Book> readBooksOfAuthor(Author author) throws SQLException {
PreparedStatement stmt =
getConnection()
.prepareStatement(
"SELECT b.id_book, b.title, b.year FROM Books b JOIN Books_has_authors ba ON b.id_book=ba.Books_id_book WHERE ba.Authors_id_author=?;");
stmt.setInt(1, author.getId_author());
ResultSet rs = stmt.executeQuery();
List<Book> books = new ArrayList<Book>();
while (rs.next()) {
Book book = new Book();
book.setId_book(rs.getInt("id_book"));
book.setTitle(rs.getString("title"));
book.setYear(rs.getInt("year"));
books.add(book);
}
getConnection().close();
return books;
}
