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;
}
public int updateBook(Book bo) {
int x = 0;
try {
con = JdbcUtil.getMysqlConnection();
System.out.println("update 2");
ps =
con.prepareStatement(
"update jlcbooks set bname=?,author=?,pub=?,cost=?,edition=?,isbn=? where bid=?");
System.out.println("update 3");
System.out.println(bo.getBid());
ps.setString(7, bo.getBid());
ps.setString(1, bo.getBname());
ps.setString(2, bo.getAuthor());
ps.setString(3, bo.getPub());
ps.setString(4, bo.getCost());
ps.setString(5, bo.getEdi());
ps.setString(6, bo.getIsbn());
x = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return x;
}
public List getBooksByCost(String cost) {
List al = new ArrayList();
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("select *from jlcbooks where cost=?");
ps.setString(1, cost);
rs = ps.executeQuery();
while (rs.next()) {
Book bo = new Book();
bo.setBid(rs.getString(1));
bo.setBname(rs.getString(2));
bo.setAuthor(rs.getString(3));
bo.setPub(rs.getString(4));
bo.setCost(rs.getString(5));
bo.setEdi(rs.getString(6));
bo.setIsbn(rs.getString(7));
al.add(bo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return al;
}
public void changeAvail(String username, int bookId) {
Connection connection = null;
Book book = getBook(username, bookId);
try {
connection = getConnection();
if (book.getAvailability().equals("in")) {
PreparedStatement statement =
connection.prepareStatement(
"UPDATE " + username + " set availability = 'out' where id = " + bookId);
statement.executeUpdate();
} else {
PreparedStatement statement =
connection.prepareStatement(
"UPDATE " + username + " set availability = 'in' where id = " + bookId);
statement.executeUpdate();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(connection);
}
}
public int addBook(Book bo) {
int x = 0;
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("insert into jlcbooks values(?,?,?,?,?,?,?)");
ps.setString(1, bo.getBid());
ps.setString(2, bo.getBname());
ps.setString(3, bo.getAuthor());
ps.setString(4, bo.getPub());
ps.setString(5, bo.getCost());
ps.setString(6, bo.getEdi());
ps.setString(7, bo.getIsbn());
x = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return x;
}
public Book getBookByBid(String bid) {
Book bo = null;
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("select *from jlcbooks where bid=?");
ps.setString(1, bid);
rs = ps.executeQuery();
if (rs.next()) {
bo = new Book();
bo.setBid(rs.getString(1));
bo.setBname(rs.getString(2));
bo.setAuthor(rs.getString(3));
bo.setPub(rs.getString(4));
bo.setCost(rs.getString(5));
bo.setEdi(rs.getString(6));
bo.setIsbn(rs.getString(7));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return bo;
}
public void addBook(String username, Book book) {
Connection connection = null;
try {
connection = getConnection();
PreparedStatement statement =
connection.prepareStatement(
"insert into "
+ username
+ " (title, author, category, year, color, level, availability)"
+ " values (?, ?, ?, ?, ?, ?, ?)");
statement.setString(1, book.getTitle());
statement.setString(2, book.getAuthor());
statement.setString(3, book.getCategory());
statement.setInt(4, book.getYear());
statement.setString(5, book.getColor());
statement.setInt(6, book.getLevel());
statement.setString(7, book.getAvailability());
statement.executeUpdate();
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(connection);
}
}