JDBC详解(3)

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>

JDBC详解(3),布布扣,bubuko.com

JDBC详解(3)

上一篇:lab-bill-sys_NginxTomcatLoadbalance


下一篇:solaris11 安装oracle 11g