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 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 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;
}
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;
}