r.bookid=book.id join tb_publishing pub on book.isbn=pub.isbn join tb_bookcase bs on book.bookcase=bs.id join tb_reader r on borr.readerid=r.id where r.barcode='"+str+"'";
ResultSet rs=conn.executeQuery(sql);
Collection coll=new ArrayList();
BorrowForm form=null;
try {
while (rs.next()) {
form = new BorrowForm();
form.setId(Integer.valueOf(rs.getInt(1)));
form.setBorrowTime(rs.getString(4));
form.setBackTime(rs.getString(5));
form.setBookName(rs.getString(8));
form.setPrice(Float.valueOf(rs.getFloat(9)));
form.setPubName(rs.getString(10));
form.setBookcaseName(rs.getString(11));
coll.add(form);
}
} catch (SQLException ex) {
System.out.println("借阅信息:"+ex.getMessage());
}
conn.close();
return coll;
}
//*************************到期提醒******************************************
public Collection bremind(){
Date dateU = new Date();
java.sql.Date date = new java.sql.Date(dateU.getTime());
String sql="select borr.borrowTime,borr.backTime,book.barcode,book.bookname,r.name readername,r.barcode readerbarcode from tb_borrow borr join tb_bookinfo book on book.id=borr.bookid join tb_reader r on r.id=borr.readerid where borr.backTime <='"+date+"'";
ResultSet rs=conn.executeQuery(sql);
System.out.println("到时提醒的SQL:"+sql);
Collection coll=new ArrayList();
BorrowForm form=null;
try {
while (rs.next()) {
form = new BorrowForm();
form.setBorrowTime(rs.getString(1));
form.setBackTime(rs.getString(2));
form.setBookBarcode(rs.getString(3));
form.setBookName(rs.getString(4));
form.setReaderName(rs.getString(5));
form.setReaderBarcode(rs.getString(6));
coll.add(form);
System.out.println("图书条形码:"+rs.getString(3));
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
conn.close();
return coll;
}
//*************************图书借阅查询******************************************
public Collection borrowQuery(String strif){
String sql="";
if(strif!="all" && strif!=null && strif!=""){
sql="select * from (select borr.borrowTime,borr.backTime,book.barcode,book.bookname,r.name readername,r.barcode readerbarcode,borr.ifback from tb_borrow borr join tb_bookinfo book on book.id=borr.bookid join tb_reader r on r.id=borr.readerid) as borr where borr."+strif+"";
}else{
sql="select * from (select borr.borrowTime,borr.backTime,book.barcode,book.bookname,r.name readername,r.barcode readerbarcode,borr.ifback from tb_borrow borr join tb_bookinfo book on book.id=borr.bookid join tb_reader r on r.id=borr.readerid) as borr";
}
ResultSet rs=conn.executeQuery(sql);
System.out.println("图书借阅查询的SQL:"+sql);
Collection coll=new ArrayList();
BorrowForm form=null;
try {
while (rs.next()) {
form = new BorrowForm();
form.setBorrowTime(rs.getString(1));
form.setBackTime(rs.getString(2));
form.setBookBarcode(rs.getString(3));
form.setBookName(rs.getString(4));
form.setReaderName(rs.getString(5));
form.setReaderBarcode(rs.getString(6));
form.setIfBack(rs.getInt(7));
coll.add(form);
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
conn.close();
return coll;
}
//*************************图书借阅排行******************************************
public Collection bookBorrowSort() {
String sql = "select * fro