jsp单页面数据库查询模板:支持分页、排序、简单搜索、设置每页显示页数功能

最新推荐文章于 2024-07-05 16:59:23 发布

CetinLuo

最新推荐文章于 2024-07-05 16:59:23 发布

阅读量2.9k

点赞数 1

分类专栏： jsp sql

jsp 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

sql

5 篇文章 0 订阅

订阅专栏

show.jsp（显示页面）

[java]view plaincopy 
   
print?
 <%@page import="java.sql.ResultSet"%>  
 <%@page import="java.sql.PreparedStatement"%>  
 <%@page import="analysis.JDBCUtils"%>  
 <%@page import="java.sql.Connection"%>  
 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>  
 <%  
 String path = request.getContextPath();  
 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
 %>  
   
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
 <html>  
   <head>  
     <base href="<%=basePath%>">  
       
     <title>My JSP 'show.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>  
     <form action="servlet/DatabaseSearch" method="post">  
             <td> 输入搜索词 <input type="text" name="keyWord"></td>  
             <td><input type="submit" value="so easy" ></td>  
     </form>  
     <hr>  
     <form action="servlet/DatabaseProcess" method="post">  
      功能列表 : <a href="servlet/DatabaseProcess?choose=1&subchoose1=0">默认排序</a>  
             <a href="servlet/DatabaseProcess?choose=1&subchoose1=1">按价格降序排序</a>  
             <a href="servlet/DatabaseProcess?choose=1&subchoose1=2">按价格升序排序</a>  
      价格区间<input  style="width:45px" type="text" name="minprice">-<input style="width:45px" type="text" name="maxprice">  
      <input type="submit" value="Go" >  
      </form>  
     <hr><br>  
       
     <!--   <tr><td>商品名称</td><td>价格</td><td>超链接</td></tr> -->  
       <%  
         String temp = null;  
         String keyWord = (String) request.getSession().getAttribute("keyword");  
         //haven't input keyword  
         if(keyWord == null || keyWord.equals("")){  
             %><h2><font color="red">no keyword input</font></h2><%  
             return;  
         }   
           
         //the lack of the select sentence is for the easy use of the follow sql query  
         String sql = " from goods where name like '%"+keyWord+"%'";  
           
           
         //determin where is price in  
         String sqlBetween = "";  
         String minprice = (String)request.getSession().getAttribute("minprice");  
         String maxprice = (String)request.getSession().getAttribute("maxprice");          
         if(minprice!=null&&maxprice!=null){  
             sqlBetween = " and price>="+minprice+" and price<="+maxprice+" ";  
         }  
           
       
           
         //determine which orderby to choose  
         String sqlOrderBy = "";  
         if((temp=(String)request.getSession().getAttribute("orderby"))!=null&&!temp.equals("0")){  
             if(temp.equals("1")){  
                 sqlOrderBy = " order by price desc";  
             }  
             else if(temp.equals("2")){  
                 sqlOrderBy = " order by price asc";  
             }  
         }  
           
           
         //get the pagesize  
         int myPageSize = 5;//default  
         if(request.getSession().getAttribute("mypagesize")!=null){  
             myPageSize = (Integer)request.getSession().getAttribute("mypagesize");  
             //System.out.println("myPageSize:"+myPageSize);  
         }  
           
         //determine which page to show  
         String sqlLimit = "";  
         String t = null;  
         if((t=(String)request.getAttribute("page"))==null){  
             sqlLimit = " limit "+myPageSize+" offset 0";  
         }  
         else{  
             sqlLimit = " limit "+myPageSize+" offset " +(Integer.parseInt(t)-1)*5;  
         }  
   
           
           
         Connection conn = JDBCUtils.getConnection();  
         System.out.println("select * "+sql+sqlBetween+sqlOrderBy+sqlLimit);//for debug  
         PreparedStatement ps = conn.prepareStatement("select * "+sql+sqlBetween+sqlOrderBy+sqlLimit);  
         ResultSet rs = ps.executeQuery();     
         while(rs.next()){  
   
     %>  
    
     商品名称  <font color="red"><u><%=rs.getString("name") %></u></font><br>  
     价格 <font color="green"> <b><%=rs.getInt("price") %></b></font><br>  
     超链接  <a target="_blank" href="<%=rs.getString("url") %>"><%=rs.getString("url") %></a><br><br>  
   
     <%} %>  
     <%  
         int sum = 0;//record the sum of the records  
         ps = conn.prepareStatement("select count(*) as sum "+sql+sqlBetween);  
         rs = ps.executeQuery();  
         if(rs.next()){  
             sum = Integer.parseInt(rs.getString("sum"));  
         }  
           
           // no suitable records  
          if(sum==0){  
             %><h2><font  color="red">no suitable records</font></h2><%   
             return ;  
          }  
               
             
      %>  
      <hr>  
         <form action="servlet/Temp" method="get" name="form1">  
         <select name="rn" οnchange="form1.submit()">  
         <option value="0">请选择每页显示的记录数</option>  
         <option value="5">每页显示5条</option>  
         <option value="10">每页显示10条</option>  
         <option value="20">每页显示20条</option>  
         </select>  
        
         totally <font color="red"><%=sum %></font>  records . goto page  
      <%   
             //show five records per page  
             for(int i=1;i<Math.ceil(sum)/myPageSize+1;i++)  
             {   
                 //high light show the current page  
                 if(t!=null&&i==Integer.parseInt(t)){  
                 %>  
                 <a style="color:red" href="servlet/DatabaseProcess?choose=2&subchoose2=<%=i%>"><%=i%></a>  
                 <%   
                 continue;  
                 }  
      %>  
          <a href="servlet/DatabaseProcess?choose=2&subchoose2=<%=i%>"><%=i%></a>  
     <%} %>  
       
             </form>  
               
   
   
   </body>  
 </html>  

DatabaseProcess.java(只是简单的设置下属性)

[java]view plaincopy 
   
print?
 package view;  
   
 import java.io.IOException;  
   
 import javax.servlet.ServletException;  
 import javax.servlet.http.HttpServlet;  
 import javax.servlet.http.HttpServletRequest;  
 import javax.servlet.http.HttpServletResponse;  
   
 public class DatabaseProcess extends HttpServlet {  
   
     /** 
      *  
      */  
     private static final long serialVersionUID = 5826464111080351766L;  
   
   
     public void doGet(HttpServletRequest request, HttpServletResponse response)  
             throws ServletException, IOException {  
         doProcess(request, response);  
     }  
   
   
     public void doPost(HttpServletRequest request, HttpServletResponse response)  
             throws ServletException, IOException {  
         doProcess(request, response);  
     }  
       
   
     public void doProcess(HttpServletRequest request, HttpServletResponse response)  
             throws ServletException, IOException {  
         String choose = request.getParameter("choose");  
         //System.out.println("choose=["+choose+"]");  
         if(choose!=null){  
             if(choose.equals("1")){  
                 String subchoose1 = request.getParameter("subchoose1");  
                 request.getSession().setAttribute("orderby", subchoose1);  
                 //System.out.println("subchoose1=["+subchoose+"]");  
             }  
             else if(choose.equals("2")){  
                 String subchoose2 = request.getParameter("subchoose2");  
                 //System.out.println("subchoose2=["+subchoose2+"]");  
                 request.setAttribute("page", subchoose2);//需要从第一页开始输出，所以不能用session  
             }  
         }  
   
           
         String minprice = request.getParameter("minprice");  
         String maxprice = request.getParameter("maxprice");  
           
         if(minprice!=null&&maxprice!=null&&minprice!=""&&maxprice!=""){  
             //System.out.println(minprice+"-"+maxprice);  
               
             if(Integer.parseInt(minprice)>=Integer.parseInt(maxprice))  
                 System.out.println("the minprice and the maxprice input error");  
             else{  
                 request.getSession().setAttribute("minprice",minprice);//价格区间是持续存在的  
                 request.getSession().setAttribute("maxprice",maxprice);  
             }  
   
         }  
           
         getServletContext().getRequestDispatcher("/show.jsp").forward(request, response);  
   
     }  
   
 }  

DatabaseSearch.java(只是简单的设置下属性)

[java]view plaincopy 
   
print?
 package view;  
   
 import java.io.IOException;  
 import java.io.PrintWriter;  
   
 import javax.servlet.ServletException;  
 import javax.servlet.http.HttpServlet;  
 import javax.servlet.http.HttpServletRequest;  
 import javax.servlet.http.HttpServletResponse;  
   
 public class DatabaseSearch extends HttpServlet {  
   
     /** 
      *  
      */  
     private static final long serialVersionUID = 4110448813797919251L;  
   
     public void doGet(HttpServletRequest request, HttpServletResponse response)  
             throws ServletException, IOException {  
         doQuery(request,response);  
     }  
   
     public void doPost(HttpServletRequest request, HttpServletResponse response)  
             throws ServletException, IOException {  
         doQuery(request,response);  
     }  
       
     public void doQuery(HttpServletRequest request, HttpServletResponse response)  
             throws ServletException, IOException {  
         response.setCharacterEncoding("gbk");  
         String keyWord = new String(request.getParameter("keyWord").getBytes("ISO-8859-1"),"UTF-8");  
         //System.out.println("keyword=["+keyWord+"]");  
         request.getSession().setAttribute("keyword", keyWord);  
           
         //清除以往的price区间  
         if(request.getSession().getAttribute("minprice")!=null){  
             request.getSession().setAttribute("minprice",null);  
         }  
         if(request.getSession().getAttribute("maxprice")!=null){  
             request.getSession().setAttribute("maxprice",null);  
         }  
         //清除以往的排序定义  
         if(request.getSession().getAttribute("orderby")!=null){  
             request.getSession().setAttribute("orderby",null);  
         }  
           
           
           
         getServletContext().getRequestDispatcher("/show.jsp").forward(request, response);  
         //response.sendRedirect("../show.jsp");//该方法无法传递attribute  
   
     }  
   
 }  