12.6 searchPerson.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="java.sql.*" %> <%@ page import="java.sql.Date" %> <%@ page import="com.helloben.util.*" %> <%! public String forSQL(String sql){ return sql.replace("‘", "\\‘"); } %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>My JSP ‘searchPerson.jsp‘ starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body> <% request.setCharacterEncoding("UTF-8"); final int pageSize = 5; int pageNum = 1; try{ pageNum = new Integer(request.getParameter("pageNum")); }catch(Exception e){} String nameSearch = request.getParameter("name"); String sexSearch = request.getParameter("sex"); String englishNameSearch = request.getParameter("englishName"); String descriptionSearch = request.getParameter("description"); String whereClause = ""; // 模糊匹配 if(nameSearch!=null && nameSearch.trim().length()!=0){ if(whereClause.length() == 0) whereClause += " name LIKE ‘%" + forSQL(nameSearch) + "%‘"; else whereClause += " AND name LIKE ‘%" + forSQL(nameSearch) + "%‘"; } // 精确匹配 if(sexSearch!=null && sexSearch.trim().length()!=0){ if(whereClause.length() == 0) whereClause += " sex = ‘" + forSQL(sexSearch) + "‘ "; else whereClause += " AND sex = ‘" + forSQL(sexSearch) + "‘ "; } if(englishNameSearch!=null && englishNameSearch.trim().length()!=0){ if(whereClause.length() == 0) whereClause += " english_name LIKE ‘%" + forSQL(englishNameSearch) + "%‘ "; else whereClause += " AND english_name LIKE ‘%" + forSQL(englishNameSearch) + "%‘ "; } if(descriptionSearch!=null && descriptionSearch.trim().length()!=0){ if(whereClause.length() == 0) whereClause += " description LIKE ‘%" + forSQL(descriptionSearch) + "%‘ "; else whereClause += " AND description LIKE ‘%" + forSQL(descriptionSearch) + "%‘ "; } if(whereClause.length() != 0){ whereClause = " WHERE " + whereClause; } String countSQL = " SELECT count(*) FROM tb_person " + whereClause; int recordCount = DbManager.getCount(countSQL); int pageCount = (recordCount + pageSize) / pageSize; String querySQL = " SELECT * FROM tb_person " + whereClause + " LIMIT " + (pageNum-1)*pageSize + ", " + pageSize; Connection conn = null; Statement stmt = null; ResultSet rs = null; try{ conn = DbManager.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(querySQL); %> <form action="searchPerson.jsp" method=get> <fieldset style=‘width:80%‘> <legend>查询条件</legend> <table > <tr> <td style="text-align:right; ">姓名</td> <td style="text-align:left; "> <input type=‘text‘ name=‘name‘ value="${ param.name }"/> </td> <td style="text-align:right; ">性别</td> <td style="text-align:left; "> <select name=‘sex‘ /> <option value="">无限制</option> <option value="男" ${ ‘男‘==param.sex ? ‘selected‘ : ‘‘ }>男</option> <option value="女" ${ ‘女‘==param.sex ? ‘selected‘ : ‘‘ }>女</option> </select> </td> </tr> <tr> <td style="text-align:right; ">英文名</td> <td style="text-align:left; "> <input type=‘text‘ name=‘englishName‘ value="${ param.englishName }"/> </td> <td style="text-align:right; ">备注</td> <td style="text-align:left; "> <input type=‘text‘ name=‘description‘ value="${ param.description }"/> </td> </tr> <tr> <td colspan=4> <input type="submit" value="提交查询"> <input type="reset" value="复位"> </td> </tr> </table> </fieldset> <br/> <table bgcolor="#CCCCCC" cellspacing=1 cellpadding=5 width=100%> <tr bgcolor=#DDDDDD> <th></th> <th>ID</th> <th>Name</th> <th>English Name</th> <th>Gender</th> <th>Age</th> <th>Birthday</th> <th>Comment</th> <th>Created Time</th> <th>Operation</th> </tr> <% while(rs.next()){ int id = rs.getInt("id"); int age = rs.getInt("age"); String name = rs.getString("name"); String englishName = rs.getString("english_name"); String sex = rs.getString("sex"); String description = rs.getString("description"); Date birthday = rs.getDate("birthday"); Timestamp createTime = rs.getTimestamp("create_time"); %> <tr bgcolor=#FFFFFF> <td><input type="checkbox" name="id" value="<%= id %>"/></td> <td><%= id %></td> <td><%= name %></td> <td><%= englishName %></td> <td><%= sex %></td> <td><%= age %></td> <td><%= birthday %></td> <td><%= description %></td> <td><%= createTime %></td> <td> <a href=‘operatePerson.jsp?action=del&id=" + <%= id %> + "‘ onclick=‘delete_Click()‘>Delete</a> <a href=‘operatePerson.jsp?action=edit&id=" + <%= id %> + "‘>Edit</a> </td> </tr> <% } %> </table> <table align=right> <tr> <td><%=Pagination.getPagination(pageNum, pageCount, recordCount, request.getRequestURI())%> </td> </tr> </table> <br /> <br /> <br /> <table width=‘100%‘> <tr> <td style=‘text-align:center; ‘><br /> <br /> <%="Count SQL: " + countSQL%> <br /> <br /> <%="Query SQL: " + querySQL%></td> </tr> </table> </form> <% } catch (Exception e){ out.println("执行SQL：" + querySQL + "时出错：" + e.getMessage()); } finally { if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); } %> </body> </html>