public void closeTStatement() {
try {
if (stmt != null) {
if (stmt instanceof Statement) {
Statement st = (Statement) stmt;
if (st.isClosed() == false) {
st.close();
if (LOG.isDebugEnabled())
LOG.debug(serverWorkerName + ". T2 st.close(" + stmtLabel + ")");
}
} else if (stmt instanceof PreparedStatement) {
PreparedStatement pst = (PreparedStatement) stmt;
if (pst.isClosed() == false) {
pst.close();
if (LOG.isDebugEnabled())
LOG.debug(serverWorkerName + ". T2 pst.close(" + stmtLabel + ")");
}
}
}
} catch (SQLException sql) {
}
reset();
}
public void _jspService(HttpServletRequest request, HttpServletResponse response)
throws java.io.IOException, ServletException {
PageContext pageContext = null;
HttpSession session = null;
ServletContext application = null;
ServletConfig config = null;
JspWriter out = null;
Object page = this;
JspWriter _jspx_out = null;
PageContext _jspx_page_context = null;
try {
response.setContentType("text/html;charset=UTF-8");
pageContext = _jspxFactory.getPageContext(this, request, response, null, true, 8192, true);
_jspx_page_context = pageContext;
application = pageContext.getServletContext();
config = pageContext.getServletConfig();
session = pageContext.getSession();
out = pageContext.getOut();
_jspx_out = out;
_jspx_resourceInjector =
(org.glassfish.jsp.api.ResourceInjector)
application.getAttribute("com.sun.appserv.jsp.resource.injector");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("<!DOCTYPE html>\n");
out.write("<html>\n");
out.write(" <head>\n");
out.write(
" <meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n");
out.write(" <title>Fine</title>\n");
out.write(" <link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\"> \n");
out.write("\n");
out.write(" </head>\n");
out.write(" <body style = \"background-image: url(lib2.jpg)\"> \n");
out.write(" <center>\n");
out.write(" <h1>Update Fines information</h1>\n");
out.write(" <form name=\"Update\" action=\"Fines_upd.jsp\">\n");
out.write(" <table border=\"0\" width=\"3\" cellspacing=\"2\">\n");
out.write(" <thead>\n");
out.write(" <tr>\n");
out.write(" <th>Update Fines</th>\n");
out.write(" <th></th>\n");
out.write(" </tr>\n");
out.write(" </thead>\n");
out.write(" <tbody>\n");
out.write(" <tr>\n");
out.write(" <td>Update Fine table with todays Data</td>\n");
out.write(
" <td><input type=\"submit\" value=\"Update / View Fines\" name=\"SUBMIT\"/></td>\n");
out.write(" </tr>\n");
out.write(" </tbody>\n");
out.write(" </table> \n");
out.write(" </form>\n");
out.write(" <h1>Check your Fines Here</h1>\n");
out.write(" <form name=\"Fines\" action=\"Fines.jsp\">\n");
out.write(" <table border=\"0\" width=\"3\" cellspacing=\"2\">\n");
out.write(" <thead>\n");
out.write(" <tr>\n");
out.write(" <th>Get Fine Details</th>\n");
out.write(" <th></th>\n");
out.write(" </tr>\n");
out.write(" </thead>\n");
out.write(" <tbody>\n");
out.write(" <tr>\n");
out.write(" <td>Card No</td>\n");
out.write(
" <td><input type=\"text\" name=\"Card_no\" value=\"\"/></td>\n");
out.write(" </tr>\n");
out.write(" <tr>\n");
out.write(" <td></td>\n");
out.write(
" <td><input type=\"submit\" value=\"Get Fines\" name=\"SUBMIT\" /></td>\n");
out.write(" </tr>\n");
out.write(" </tbody>\n");
out.write(" </table> \n");
out.write(" ");
Connection con = null;
String[] selected_Checkboxes = request.getParameterValues("chk");
PreparedStatement pst = null;
ResultSet result = null;
ResultSet resUpd = null;
con =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/lbms_db?zeroDateTimeBehavior=convertToNull",
"root",
"admin12");
String Card_no = request.getParameter("Card_no");
String button = null;
Date dt = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
String current_date = sdf.format(dt);
if (Card_no != null && selected_Checkboxes == null) {
String selSql =
"SELECT l.card_no, SUM(f.fine_amt) AS total_fine, f.paid "
+ "FROM book_loans l, fines f "
+ "WHERE l.loan_id = f.loan_id AND "
+ "l.card_no = "
+ Card_no
+ " "
+ "GROUP BY l.card_no";
pst = con.prepareStatement(selSql);
result = pst.executeQuery();
String box = null;
String paid;
String pay;
Boolean chk = false;
out.println("<table>");
pay = "<form action='Fines.jsp'>";
out.println(pay);
out.println("<tr>");
out.println("<th>Card No</th>");
out.println("<th>Fine_Amt</th>");
out.println("<th>Paid OR Not</th>");
out.println("</tr>");
while (result.next()) {
chk = true;
paid = "No";
if (result.getBoolean("f.paid")) {
paid = "Yes";
}
out.println("<tr>");
out.println(
"<td>"
+ result.getInt("l.card_no")
+ "</td><td>"
+ result.getString("total_fine")
+ "</td><td>"
+ paid
+ "</td>");
out.print("<td>");
box = "<input name='chk' value=" + result.getInt("l.card_no") + " type='checkbox'>";
out.print(box);
out.print("</td>");
out.print("</tr>");
}
if (chk == true) {
out.println("<tr>");
out.print("<td>");
button = "<input type='submit' value='Pay Fine' name='Pay'>";
out.print(button);
out.print("</td>");
out.println("</tr>");
} else {
out.write(
"<dialog open> <font color = 'green'>No Fine information. You owe nothing! Thank You</font> </dialog>");
}
out.println("</form>");
out.println("</table>");
} else if (selected_Checkboxes != null) {
String sqlLoan = null;
ResultSet resultLoan = null;
String sqlUpdFine = null;
PreparedStatement pstUpd = null;
String sqlBook = null;
ResultSet rsltBook = null;
char chkouts = 'N';
int length_chk = selected_Checkboxes.length;
for (int i = 0; i < length_chk; i++) {
// Check whether the Book is returned before paying the fine.
sqlBook =
"SELECT COUNT(loan_id) AS no_chkouts FROM book_loans WHERE card_no = "
+ selected_Checkboxes[i]
+ " AND date_in = '0000-00-00' AND due_date < "
+ current_date
+ "";
pst = con.prepareStatement(sqlBook);
rsltBook = pst.executeQuery();
while (rsltBook.next()) {
if (rsltBook.getInt("no_chkouts") > 0) {
chkouts = 'Y';
}
}
if (chkouts == 'Y') {
out.write(
"<dialog open> <font color = 'red'>You have outstanding due checkouts!. Please return the books and then Pay the fine</font> </dialog>");
}
// Get the corresponding loan_Ids for each customer from Fines table
sqlLoan =
"SELECT loan_id FROM book_loans WHERE card_no = "
+ selected_Checkboxes[i]
+ " AND date_in IS NOT NULL AND due_date < date_in";
pst = con.prepareStatement(sqlLoan);
resultLoan = pst.executeQuery();
while (resultLoan.next()) {
sqlUpdFine =
"UPDATE fines SET paid = true WHERE loan_id = " + resultLoan.getInt("loan_id") + "";
pstUpd = con.prepareStatement(sqlUpdFine);
pstUpd.executeUpdate();
out.println("Payment Updated Successfully");
}
}
}
out.write("\n");
out.write(" </form> \n");
out.write(" </center>\n");
out.write("</body>\n");
out.write("</html>\n");
} catch (Throwable t) {
if (!(t instanceof SkipPageException)) {
out = _jspx_out;
if (out != null && out.getBufferSize() != 0) out.clearBuffer();
if (_jspx_page_context != null) _jspx_page_context.handlePageException(t);
else throw new ServletException(t);
}
} finally {
_jspxFactory.releasePageContext(_jspx_page_context);
}
}
public void actionPerformed(ActionEvent evt) {
String arg = evt.getActionCommand();
if (arg.equals("Query")) { // 用户按下Query按钮
ResultSet rs = null;
try {
String author = (String) authors.getSelectedItem();
String publisher = (String) publishers.getSelectedItem();
if (!author.equals("Any") && !publisher.equals("Any")) {
if (authorPublisherQueryStmt == null) {
// 根据用户选择的出版社名和作者名查询相关的书名和书价
String authorPublisherQuery =
"SELECT Books.Price, Books.Title "
+ "FROM Books, BooksAuthors, Authors, Publishers "
+ "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND "
+ "BooksAuthors.ISBN = Books.ISBN AND "
+ "Books.Publisher_Id = Publishers.Publisher_Id AND "
+ "Authors.Name = ? AND "
+ "Publishers.Name = ?";
authorPublisherQueryStmt = con.prepareStatement(authorPublisherQuery);
}
authorPublisherQueryStmt.setString(1, author);
authorPublisherQueryStmt.setString(2, publisher);
rs = authorPublisherQueryStmt.executeQuery();
} else if (!author.equals("Any") && publisher.equals("Any")) {
if (authorQueryStmt == null) {
// 根据用户选择的作者名查询相关的书名和书价
String authorQuery =
"SELECT Books.Price, Books.Title "
+ "FROM Books, BooksAuthors, Authors "
+ "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND "
+ "BooksAuthors.ISBN = Books.ISBN AND "
+ "Authors.Name = ?";
authorQueryStmt = con.prepareStatement(authorQuery);
}
authorQueryStmt.setString(1, author);
rs = authorQueryStmt.executeQuery();
} else if (author.equals("Any") && !publisher.equals("Any")) {
if (publisherQueryStmt == null) {
// 根据用户选择的出版社名查询相关的书名和书价
String publisherQuery =
"SELECT Books.Price, Books.Title "
+ "FROM Books, Publishers "
+ "WHERE Books.Publisher_Id = Publishers.Publisher_Id AND "
+ "Publishers.Name = ?";
publisherQueryStmt = con.prepareStatement(publisherQuery);
}
publisherQueryStmt.setString(1, publisher);
rs = publisherQueryStmt.executeQuery();
} else {
if (allQueryStmt == null) {
// 若用户未选任何信息，则输出所有的书名和对应的书价
String allQuery = "SELECT Books.Price, Books.Title FROM Books";
allQueryStmt = con.prepareStatement(allQuery);
}
rs = allQueryStmt.executeQuery();
}
result.setText("");
while (rs.next()) result.append(rs.getString(1) + " | " + rs.getString(2) + "\n");
rs.close();
} catch (Exception e) {
result.setText("Error " + e);
}
} else if (arg.equals("Change prices")) { // 用户选择“Change prices”按钮
String publisher = (String) publishers.getSelectedItem();
if (publisher.equals("Any")) result.setText("I am sorry, but I cannot do that.");
else
try {
// 根据用户输入的新的书价更新Books表的数据
String updateStatement =
"UPDATE Books "
+ "SET Price = Price + "
+ priceChange.getText()
+ " WHERE Books.Publisher_Id = "
+ "(SELECT Publisher_Id FROM Publishers WHERE Name = '"
+ publisher
+ "')";
int r = stmt.executeUpdate(updateStatement);
result.setText(r + " records updated.");
} catch (Exception e) {
result.setText("Error " + e);
}
}
}