web课程设计 Servlet+JSP实现的图书管理系统

package com.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import com.actionForm.BookForm; import com.actionForm.BorrowForm; import com.actionForm.ReaderForm; import com.core.ConnDB; public class BorrowDAO { ConnDB conn = new ConnDB(); public int insert() { String sql = "INSERT INTO tb_borrow (bookid) vlaues(1) "; int ret = conn.executeUpdate(sql); return ret; } //*****************************图书借阅****************************** public int insertBorrow(ReaderForm readerForm, BookForm bookForm, String operator) { String sql1 = "select t.days from tb_bookinfo b left join tb_booktype t on" + " b.typeid=t.id where b.id=" + bookForm.getId() + ""; // 获取可借天数的SQL语句 ResultSet rs = conn.executeQuery(sql1); // 执行SQL语句 int days = 0; try { if (rs.next()) { days = rs.getInt(1); // 获取可借天数 } } catch (SQLException ex) { } // 计算归还时间 Calendar calendar = Calendar.getInstance(); // 获取系统日期 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); java.sql.Date date = java.sql.Date.valueOf(format.format(calendar .getTime()));// 借书日期 calendar.add(calendar.DAY_OF_YEAR, days);// 加上可借天数 java.sql.Date backTime = java.sql.Date.valueOf(format.format(calendar .getTime())); // 归还日期 String sql = "Insert into tb_borrow (readerid,bookid,borrowTime,backTime,operator) values(" + readerForm.getId() + "," + bookForm.getId() + ",'" + date + "','" + backTime + "','" + operator + "')"; System.out.println("添加图书借阅信息的SQL：" + sql); int falg = conn.executeUpdate(sql); // 执行更新语句 conn.close();// 关闭数据库连接 return falg; } //*************************************图书继借********************************* public int renew(int id){ String sql0="SELECT bookid FROM tb_borrow WHERE id="+id+""; //根据借阅ID查询图书ID的SQL语句 ResultSet rs1=conn.executeQuery(sql0); //执行查询语句 int flag=0; try { if (rs1.next()) { //获取可借天数 String sql1 = "select t.days from tb_bookinfo b left join" + " tb_booktype t on b.typeid=t.id where b.id=" +rs1.getInt(1) + ""; //获取可借天数的SQL语句 ResultSet rs = conn.executeQuery(sql1); //执行查询语句 int days = 0; try { if (rs.next()) { days = rs.getInt(1); //获取可借天数 } } catch (SQLException ex) { } //计算归还时间 Calendar calendar=Calendar.getInstance(); //获取系统日期 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式 java.sql.Date date=java.sql.Date.valueOf( format.format(calendar.getTime()));//借书日期 calendar.add(calendar.DAY_OF_YEAR, days); //加上可借天数 java.sql.Date backTime= java.sql.Date.valueOf(format.format(calendar.getTime())); //归还日期 String sql = "UPDATE tb_borrow SET backtime='" + backTime + "' where id=" + id + ""; //更新归还时间完成续借 flag = conn.executeUpdate(sql);//执行更新语句 } } catch (Exception ex1) {} conn.close();//关闭数据库连接 return flag; } //*************************************图书归还********************************* public int back(int id,String operator){ //根据借阅ID获取读者ID和图书ID String sql0="SELECT readerid,bookid FROM tb_borrow WHERE id="+id+""; ResultSet rs1=conn.executeQuery(sql0); //执行查询语句 int flag=0; try { if (rs1.next()) { Calendar calendar=Calendar.getInstance(); //获取系统日期 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); java.sql.Date date=java.sql.Date.valueOf(format.format(calendar.getTime()));//还书日期 int readerid=rs1.getInt(1); //获取读者ID int bookid=rs1.getInt(2);//获取图书ID String sql1="INSERT INTO tb_giveback (readerid,bookid,backTime" + ",operator) VALUES("+readerid+","+bookid+",'" +date+"','"+operator+"')"; //保存归还信息 int ret=conn.executeUpdate(sql1); //执行更新语句 if(ret==1){ String sql2 = "UPDATE tb_borrow SET ifback=1 where id=" + id + ""; //将借阅信息标记为已归还 flag = conn.executeUpdate(sql2); //执行更新语句 }else{ flag=0; } } } catch (Exception ex1) { } conn.close();//关闭数据库连接 return flag; } //*****************************查询图书借阅信息************************ public Collection<BorrowForm> borrowinfo(String str){ String sql="select borr.*,book.bookname,book.price,pub.pubname," + "bs.name bookcasename,r.barcode from (select * from tb_borrow " + "where ifback=0) as borr left join tb_bookinfo book on borr.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<BorrowForm> coll=new ArrayList<>(); BorrowForm form=null; try { while (rs.next()) { form = new BorrowForm(); form.setId(Integer.valueOf(rs.getInt(1)));//获取ID号 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);//添加借阅信息到Collection集合中 } } catch (SQLException ex) { System.out.println("借阅信息："+ex.getMessage());//输出异常信息 } conn.close();//关闭数据库连接 return coll; } //*************************到期提醒****************************************** public Collection bremind(){ Calendar calendar=Calendar.getInstance(); //获取系统日期 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); java.sql.Date date=java.sql.Date.valueOf(format.format(calendar.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+"' and borr.ifback=0"; ResultSet rs=conn.executeQuery(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));