package dao;
public void returnBook(Integer studentNo, String bookNo) throws Exception {
int renew;
String borrowDate;
String backDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
Connection connection = ConnectDbase.getConnect();
//删已借书记录并记录借阅日期
String sql = "select time,renew from student_book_relation where Sno = ? and Bno = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1,studentNo);
ps.setString(2,bookNo);
ResultSet r = ps.executeQuery();
r.next();
borrowDate = r.getString(1);
renew = r.getInt(2);
sql = "delete from student_book_relation where Sno = ? and Bno = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1,studentNo);
ps.setString(2,bookNo);
ps.execute();
//更新所借图书数量
Book book = (new BookDao()).find(bookNo);
int bookNum = book.getBnum();
sql = "update book set Bnum = ? where Bno = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1,bookNum+1);
ps.setString(2,bookNo);
ps.execute();
//更新个人借书状态
sql = "select num , state from student_borrow_relation where Sno = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1,studentNo);
r = ps.executeQuery();
r.next();
int num = r.getInt(1);
int state = r.getInt(2);
if(num == 8) {
state = 1;
}
num--;
sql = "update student_borrow_relation set num = ?,state = ? where Sno = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1,num);
ps.setInt(2,state);
ps.setInt(3,studentNo);
ps.executeUpdate();
//更新罚款信息
java.sql.Date borrow = java.sql.Date.valueOf(borrowDate);
java.sql.Date back = java.sql.Date.valueOf(backDate);
int duration =(int) ((back.getTime()-borrow.getTime())/(1000*60*60*24));
int fineMoney = 0;
if(renew == 1) {
if(duration > 30) {
fineMoney += duration-30;
}
} else {
if(duration > 15) {
fineMoney += duration-15;
}
}
sql = "select Sno , money from student_fine_relation where Sno = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1,studentNo);
r = ps.executeQuery();
if(r.next()) {
int money = r.getInt(2);
sql = "update student_fine_relation set money = ? where Sno = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1,money+fineMoney);
ps.setInt(2,studentNo);
ps.executeUpdate();
} else {
if(fineMoney != 0) {
sql = "insert into student_fine_relation(Sno,money) values(?,?)";
ps = connection.prepareStatement(sql);
ps.setInt(1,studentNo);
ps.setInt(2,fineMoney);
ps.execute();
}
}
ConnectDbase.close(r, ps, connection);;
}
以上是dao
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int sno=Integer.parseInt(request.getParameter("sno"));
String bno = request.getParameter("bno");
ReturnBookDao rbd=new ReturnBookDao();
try {
rbd.returnBook(sno, bno);
} catch (Exception e) {
e.printStackTrace();
}
request.getRequestDispatcher("success.jsp").forward(request, response);
}以上是servlet
最好是用打印语句排查一下问题的范围。检查一下如下几个问题：
1.打印SQL语句，看看SQL语句的值是否正常；
2.检查逻辑是否存在问题。
debug
1.查看参数是否接收得到
2.查看sql执行完毕影响行数是否大于0