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