public BookForm findItem(String f, String key) {
BookForm bookForm = null;
String sql =
"select b.*, p.pubname as publishing,t.typename from titles b left join "
+ " publishing_info p on b.ISBN=p.ISBN join booktypes t on b.typeid=t.id where b."
+ f
+ " ='"
+ key
+ "'";
ResultSet rs = conn.executeQuery(sql);
try {
if (rs.next()) {
bookForm = new BookForm();
bookForm.setIsbn(rs.getString(1));
bookForm.setTitle(rs.getString(2));
bookForm.setTypeId(rs.getInt(3));
bookForm.setAuthor(rs.getString(4));
bookForm.setTranslator(rs.getString(5));
bookForm.setPrice(Float.valueOf(rs.getString(6))); //
bookForm.setPage(rs.getInt(7));
bookForm.setInTime(rs.getString(8));
bookForm.setOperator(rs.getString(9));
bookForm.setDel(rs.getInt(10));
bookForm.setPublishing(rs.getString(12));
bookForm.setTypeName(rs.getString(13));
}
} catch (SQLException ex) {
}
conn.close();
return bookForm;
}
public BookForm queryM(BookForm bookForm1) {
BookForm bookForm = null;
String sql =
"select b.*,t.typename from titles b left join "
+ "booktypes t on b.typeid=t.id where b.isbn='"
+ bookForm1.getIsbn()
+ "'";
ResultSet rs = conn.executeQuery(sql);
try {
while (rs.next()) {
bookForm = new BookForm();
bookForm.setIsbn(rs.getString(1));
bookForm.setTitle(rs.getString(2));
bookForm.setTypeId(rs.getInt(3));
bookForm.setAuthor(rs.getString(4));
bookForm.setTranslator(rs.getString(5));
bookForm.setPrice(Float.valueOf(rs.getString(6)));
bookForm.setPage(rs.getInt(7));
bookForm.setInTime(rs.getString(8));
bookForm.setOperator(rs.getString(9));
bookForm.setDel(rs.getInt(10));
bookForm.setPublishing(rs.getString(11));
bookForm.setTypeName(rs.getString(12));
}
} catch (SQLException ex) {
}
conn.close();
return bookForm;
}
public int update(BookForm bookForm) {
String sql =
"Update titles set typeid="
+ bookForm.getTypeId()
+ ", title='"
+ bookForm.getTitle()
+ "'"
+ ",author='"
+ bookForm.getAuthor()
+ "',translator='"
+ bookForm.getTranslator()
+ "',price="
+ bookForm.getPrice()
+ ",page="
+ bookForm.getPage()
+ " where isbn='"
+ bookForm.getIsbn()
+ "'";
int falg = conn.executeUpdate(sql);
conn.close();
return falg;
}
public Collection query(String strif) {
BookForm bookForm = null;
Collection bookColl = new ArrayList();
String sql = "";
if (strif != "all" && strif != null && strif != "") {
sql =
" SELECT * from( SELECT b.*, p.pubname AS publishing, t.typename FROM titles b LEFT JOIN publishing_info p ON"
+ " b.ISBN = p.ISBN JOIN booktypes t ON b.typeid = t.id ) as book WHERE book."
+ strif
+ "'";
} else {
sql =
"select b.*, p.pubname as publishing,t.typename from titles b "
+ "left join publishing_info p on b.ISBN=p.ISBN join booktypes t on b.typeid=t.id ";
}
System.out.println("book query sql" + sql);
ResultSet rs = conn.executeQuery(sql);
try {
while (rs.next()) {
bookForm = new BookForm();
bookForm.setIsbn(rs.getString(1));
bookForm.setTitle(rs.getString(2));
bookForm.setTypeId(rs.getInt(3));
bookForm.setAuthor(rs.getString(4));
bookForm.setTranslator(rs.getString(5));
bookForm.setPrice(Float.valueOf(rs.getString(6)));
bookForm.setPage(rs.getInt(7));
bookForm.setInTime(rs.getString(8));
bookForm.setOperator(rs.getString(9));
bookForm.setDel(rs.getInt(10));
bookForm.setTypeName(rs.getString("typename"));
bookColl.add(bookForm);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
conn.close();
return bookColl;
}
// add book info
public int insert(BookForm bookForm) {
String sql = "";
int falg = findTitle(bookForm.getIsbn());
try {
if (falg != 2 && falg != 0) {
sql =
"Insert into titles (isbn,title,typeid,author,translator,price,page,inTime,operator) values('"
+ bookForm.getIsbn()
+ "','"
+ bookForm.getTitle()
+ "',"
+ bookForm.getTypeId()
+ ",'"
+ bookForm.getAuthor()
+ "','"
+ bookForm.getTranslator()
+ "','"
+ bookForm.getPrice()
+ "','"
+ bookForm.getPage()
+ "','"
+ bookForm.getInTime()
+ "','"
+ bookForm.getOperator()
+ "')";
falg = conn.executeUpdate(sql);
}
} catch (Exception ex) {
falg = 0;
} finally {
conn.close();
}
System.out.println("falg:" + falg);
return falg;
}
// findTitle
public int findTitle(String isbn) {
int val = 1;
String sql1 = "SELECT * FROM titles WHERE isbn='" + isbn + "'";
ResultSet rs = conn.executeQuery(sql1);
try {
if (rs.next()) {
val = 2;
}
} catch (SQLException e) {
e.printStackTrace();
val = 0;
}
return val;
}
// check book reservation
public int checkReservation(String isbn) {
int val = 0;
String sql1 =
"SELECT COUNT(*) from borrow_cases a LEFT JOIN titles b ON a.itemId = b.ISBN WHERE b.ISBN ='"
+ isbn
+ "'";
ResultSet rs = conn.executeQuery(sql1);
try {
if (rs.next()) {
val = rs.getInt(1) + 1;
}
} catch (SQLException e) {
e.printStackTrace();
val = 0;
}
return val;
}
public int delete(BookForm bookForm) {
String sql = "Delete from titles where isbn='" + bookForm.getIsbn() + "'";
int falg = conn.executeUpdate(sql);
return falg;
}
