JSP学习之------>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);
 
 }
 
}