jsp的查询和分页

//这是index.jsp

 

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<form action="${pageContext.request.contextPath}/stu.do?p=search" method="post">

编号:<input type="text" name="sid">  <br>
姓名:<input type=text name="sname">  <br>
性别:<input type=radio name="sex" value=1>男
     <input type=radio name="sex" value=0>女 <br>

日期:<input type=text name="start"> 到
     <input type=text name="end">   <br>
    
     <input type="hidden" name="page"/>
     <input type=submit value='搜索'>
     <input type=reset  value='重置'>    

</form>

<c:if test="${not empty map}">
搜索到结果:${map.sum}条，当前${map.page}/${map.count}页
<hr>
<table border="1" align="center" width="80%">
  <tr>
    <th>编号</th>
    <th>姓名</th>
    <th>性别</th>
    <th>出生</th>
  </tr>
 
  <c:forEach items="${map.list}" var="s">
   <tr align="center">
     <td>${s.sid}</td>
     <td>${s.sname}</td>
     <td>${s.sex==1?"男":"女"}</td>
     <td>
        <fmt:formatDate value="${s.sbirthday}" pattern="yyyy年MM月dd日"/>
     </td>
   </tr>
  </c:forEach>
</table>
 <a href="${pageContext.request.contextPath}/stu.do?p=search&page=1">【首 页】</a>
 <a href="${pageContext.request.contextPath}/stu.do?p=search&page=${map.page+1 }">【下一页】</a>
 <a href="${pageContext.request.contextPath}/stu.do?p=search&page=${map.page-1 }">【上一页】</a>
 <a href="${pageContext.request.contextPath}/stu.do?p=search&page=${map.count }">【末 页】</a>

</c:if>

 

 

//这是servlet的代码

import java.io.IOException;
import java.io.PrintWriter;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.StuDAO;

public class StuServlet extends HttpServlet {

 int size = 3;
 StuDAO stuDAO = new StuDAO();
 
 @Override
 public void service(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
  response.setContentType("text/html;charset=utf-8");
  PrintWriter out = response.getWriter();
  
  String p = request.getParameter("p");
  if (p.equals("search"))
   doSearch(request, response);
  
 }

 //分页查询
 public void doSearch(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
     //要显示页数
            String pageString = request.getParameter("page");
            int    page       = 1;
            if (pageString!=null && pageString.trim().length()>0)
                page       = Integer.parseInt(pageString);
           
           
     //其它条件
           String sid  = request.getParameter("sid");
           String sname = request.getParameter("sname");
           String sex   = request.getParameter("sex");
           String start = request.getParameter("start");
           String end   = request.getParameter("end");
          
           //查
           Map map  = stuDAO.fenye(page, size, sid, sname, sex, start, end);
           request.setAttribute("map",  map);
           
           request.getRequestDispatcher("/index.jsp").forward(request, response);
           
 }

}

 

//这是dao层

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.db.DbUtil;
import com.entity.Student;

public class StuDAO {
 DbUtil db = new DbUtil();
 
 /*
  *
  *    select top 3 * from student where 1=1 and sname like ? and sid not in
  *                      (select top 3 sid from student where 1=1 and sname like ? )
  *
  *    select count(*) from student where 1=1 and ....
  */
 
 public Map fenye(Integer page,Integer size,String sid,String sname,String sex,String  start,String end){
  List  ifList = new ArrayList(); //条件
  List  list   = new ArrayList(); //结果
  Map   map    = new HashMap();   //所有结果
     StringBuffer sb = new StringBuffer();
    
     if (sid!=null && sid.trim().length()>0)
     {
      sb.append(" and sid=?");
      ifList.add(Integer.parseInt(sid));
     }
    
     if (sname!=null && sname.length()>0){
      sb.append(" and sname like ?");
      ifList.add("%"+sname+"%");
     }
    
     if (sex!=null && sex.trim().length()>0){
        sb.append(" and sex=?");
        ifList.add(Integer.parseInt(sex));
     }
    
     if (start!=null && start.trim().length()>0){
      sb.append(" and sbirthday>=?");
      ifList.add(java.sql.Date.valueOf(start));
     }
    
     if (end!=null && end.trim().length()>0){
      sb.append(" and sbirthday<=?");
      ifList.add(java.sql.Date.valueOf(end));
     }
     //总条数
     String sqlCount = "select count(*) from student where 1=1 "+sb.toString();
      
     ResultSet rsCount = db.getRs(sqlCount, ifList.toArray());
     int sum = 0;
  try {
   rsCount.next();
   sum = rsCount.getInt(1);
  } catch (SQLException e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
  }
     int      count    = sum%size==0?sum/size : sum/size+1;
     if (page<1) page = 1;
     if (page>count) page=count;
    
     String sqlList  = "select top "+size+" * from student where 1=1 "+sb.toString()+" and sid not in (select top "+(page-1)*size+" sid from student where 1=1 "+sb.toString()+")";
  
     System.out.println("count="+sqlCount);
     System.out.println("list="+sqlList);
     //这句代码很重要
     ifList.addAll(ifList);
     ResultSet rs = db.getRs(sqlList, ifList.toArray());
     try {
   while (rs!=null && rs.next()){
    Student stu = new Student();
    stu.setSid(rs.getInt("sid"));
    stu.setSname(rs.getString("sname"));
    stu.setSex(rs.getInt("sex"));
    stu.setSbirthday(rs.getDate("sbirthday"));
    list.add(stu);
    
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  map.put("page", page);
  map.put("size", size);
  map.put("count", count);
  map.put("list", list);
  map.put("sum", sum);
    
  return map;
 }
 
 
 
 //条件查询，不分页
 public List condition(String sid,String sname,String sex,String  start,String end)
 {
  List  ifList = new ArrayList(); //条件
  List  list   = new ArrayList(); //结果
     StringBuffer sb = new StringBuffer("select * from student  where 1=1 ");
     if (sid!=null && sid.trim().length()>0)
     {
      sb.append(" and sid=?");
      ifList.add(Integer.parseInt(sid));
     }
    
     if (sname!=null && sname.length()>0){
      sb.append(" and sname like ?");
      ifList.add("%"+sname+"%");
     }
    
     if (sex!=null && sex.trim().length()>0){
        sb.append(" and sex=?");
        ifList.add(Integer.parseInt(sex));
     }
    
     if (start!=null && start.trim().length()>0){
      sb.append(" and sbirthday>=?");
      ifList.add(java.sql.Date.valueOf(start));
     }
    
     if (end!=null && end.trim().length()>0){
      sb.append(" and sbirthday<=?");
      ifList.add(java.sql.Date.valueOf(end));
     }
    
   
     ResultSet rs = db.getRs(sb.toString(), ifList.toArray());
     try {
   while (rs!=null && rs.next()){
    Student stu = new Student();
    stu.setSid(rs.getInt("sid"));
    stu.setSname(rs.getString("sname"));
    stu.setSex(rs.getInt("sex"));
    stu.setSbirthday(rs.getDate("sbirthday"));
    list.add(stu);
    
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
    
     return list;
    
 }
 /**
  * @param args
  * jUnit
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  StuDAO  stuDAO = new StuDAO();
  /*List<Student> list = stuDAO.condition(null, "小", "1", null, null);
  for (Student student : list) {
   System.out.println(student.getSid()+"\t"+student.getSname()+"\t"+student.getSex());
  }*/
  
  stuDAO.fenye(1, 3, null, "小", null, null, null);

 }

}