04_web基础（八）之车票实现增删改查初级版本

43.web页面显示车票列表简略完成

　　代码:

　　控制层代码

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.model.Ticket;
 4 import com.day03.station.service.impl.TicketService;
 5 
 6 import javax.servlet.ServletException;
 7 import javax.servlet.annotation.WebServlet;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 import java.io.IOException;
12 import java.util.List;
13 
14 /**
15  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
16  * 疑问咨询wx:851298348
17  */
18 @WebServlet("/list")
19 public class TicketListServlet extends HttpServlet {
20     //业务对象
21     private TicketService ticketService=new TicketService();
22     /**
23      * 查询车票列表
24      * @param req
25      * @param resp
26      * @throws ServletException
27      * @throws IOException
28      */
29     @Override
30     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
31         //1.接收参数
32         //2.调用业务方法
33         List<Ticket> list = ticketService.queryAll();
34         req.setAttribute("list",list);
35         //3.控制跳转
36         req.getRequestDispatcher("/WEB-INF/views/ticketList.jsp").forward(req,resp);
37 
38     }
39 }
TicketListServlet

　　jsp代码

 1 <%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
 2 <html>
 3 <head>
 4     <title>车票列表</title>
 5 </head>
 6 <body>
 7 我是车票列表页面
 8 ${list}
 9 <%--
10 表格标签
11 行   <tr>
12 列    <td>
13 --%>
14 <table align="center" border="1">
15     <tr>
16         <td>编号</td>
17         <td>start_station</td>
18         <td>stop_station</td>
19         <td>start_time</td>
20         <td>ticket_price</td>
21     </tr>
22     <tr>
23         <td>${list[0].id}</td>
24         <td>${list[0].startStation}</td>
25         <td>${list[0].stopStation}</td>
26         <td>${list[0].startTime}</td>
27         <td>${list[0].ticketPrice}</td>
28     </tr>
29     <tr>
30         <td>${list[1].id}</td>
31         <td>${list[1].startStation}</td>
32         <td>${list[1].stopStation}</td>
33         <td>${list[1].startTime}</td>
34         <td>${list[1].ticketPrice}</td>
35     </tr>
36     <tr>
37         <td>${list[2].id}</td>
38         <td>${list[2].startStation}</td>
39         <td>${list[2].stopStation}</td>
40         <td>${list[2].startTime}</td>
41         <td>${list[2].ticketPrice}</td>
42     </tr>
43 
44 </table>
45 </body>
46 </html>
ticketList.jsp

 44.web页面显示车票列表实现

　　1.使用c标签前的准备

　　为了完全在JSP中消除Java代码,学习JAVA的标签库:
  Java标签库:
     标准标签库(JSTL).(SUN预先提供好的,我们使用的.)
    以后学习MVC框架(Struts2/SpringMVC)的时候,都会学习一套标签.
=====================================================================
准备:
    1:准备JSTL相关的jar(Tomcat根/webapps/examples/WEB-INF/lib).
       jstl.jar     standard.jar
       el-api.jar   jsp-api.jar

    2:使用JSTL的JSP页面中,引人标签库:
       比如引人核心JSTL:
       <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    3:注意:有的idea没有添加c标签库,所以必须手动添加

 

 2.ide配置

　　

　　

　　

　　jsp代码:

 1 <%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
 2 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
 3 <html>
 4 <head>
 5     <title>车票列表</title>
 6 </head>
 7 <body>
 8 我是车票列表页面
 9 
10 <%--
11 表格标签
12 行   <tr>
13 列    <td>
14 --%>
15 <table align="center" border="1">
16     <tr>
17         <td>编号</td>
18         <td>start_station</td>
19         <td>stop_station</td>
20         <td>start_time</td>
21         <td>ticket_price</td>
22     </tr>
23    <c:forEach items="${list}" var="ticket">
24       <tr>
25           <td>${ticket.id}</td>
26           <td>${ticket.startStation}</td>
27           <td>${ticket.stopStation}</td>
28           <td>${ticket.startTime}</td>
29           <td>${ticket.ticketPrice}</td>
30       </tr>
31    </c:forEach>
32 
33 </table>
34 </body>
35 </html>
View Code

 　　列表页面显示效果:

　　

45.web页面实现车票增加

　　实现步骤如下:

　　1.在列表页面显示新增按钮,列表jsp页面代码如下:

　　　　

　　     列表界面效果如图:

　　　　

　　2.获取新增页面的servlet代码如下:　　

 1 package com.day03.station.controller;
 2 
 3 import javax.servlet.ServletException;
 4 import javax.servlet.annotation.WebServlet;
 5 import javax.servlet.http.HttpServlet;
 6 import javax.servlet.http.HttpServletRequest;
 7 import javax.servlet.http.HttpServletResponse;
 8 import java.io.IOException;
 9 
10 /**
11  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
12  * 疑问咨询wx:851298348
13  */
14 @WebServlet("/addPage")
15 public class AddTicketPageServlet extends HttpServlet {
16     @Override
17     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
18         //接收参数
19         //调用业务方法
20         //控制跳转
21         req.getRequestDispatcher("/WEB-INF/views/addTicket.jsp").forward(req,resp);
22     }
23 }
AddTicketPageServlet

 

　　3.新增页面jsp代码:

 1 <%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
 2 <html>
 3 <head>
 4     <title>新增页面</title>
 5 </head>
 6 <body>
 7 
 8 我是增加页面
 9 <form action="/addData" method="post">
10     开始车站:<input id="startStation" type="text" name="startStation" value=""/>
11     <br/>
12     <br/>
13     到达车站:<input id="stopStation" type="text" name="stopStation" value=""/>
14     <br/>
15     <br/>
16     <input type="submit" value="保存"/>
17 
18 </form>
19 </body>
20 </html>
View Code

 　　页面效果图:

　　

　　4.点击时后端servlet代码如下:

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.model.Ticket;
 4 import com.day03.station.service.impl.TicketService;
 5 
 6 import javax.servlet.ServletException;
 7 import javax.servlet.annotation.WebServlet;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 import java.io.IOException;
12 
13 /**
14  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
15  * 疑问咨询wx:851298348
16  */
17 @WebServlet("/addData")
18 public class AddDataServlet extends HttpServlet {
19     private TicketService ticketService=new TicketService();
20     @Override
21     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
22         //接收参数
23         String startStation = req.getParameter("startStation");
24         String stopStation = req.getParameter("stopStation");
25         //调用业务方法
26         Ticket ticket = new Ticket();
27         ticket.setStartStation(startStation);
28         ticket.setStopStation(stopStation);
29         ticketService.save(ticket);
30         //控制跳转
31         resp.sendRedirect("/list");
32     }
33 }
AddDataServlet

 　　新增完成!

 46.web页面实现删除

　　实现步骤如下:

 　　1.添加操作列效果如下:

　　

　　　　代码如下:

　　

　　2.servlet实现

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.model.Ticket;
 4 import com.day03.station.service.impl.TicketService;
 5 
 6 import javax.servlet.ServletException;
 7 import javax.servlet.annotation.WebServlet;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 import java.io.IOException;
12 
13 /**
14  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
15  * 疑问咨询wx:851298348
16  */
17 @WebServlet("/delete")
18 public class DeleteTicketServlet extends HttpServlet {
19     private TicketService ticketService=new TicketService();
20     @Override
21     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
22         //接收参数
23         String idStr = req.getParameter("id");
24         //如何将字符串类型转变为整数类型
25         Integer id = Integer.valueOf(idStr);
26         //调用业务方法
27         ticketService.delete(id);
28         //控制跳转
29         resp.sendRedirect("/list");
30     }
31 }
DeleteTicketServlet

 　　删除完成!

 47.web页面实现修改

　　实现步骤如下:

　　1.在列表上显示修改按钮,代码如下

　　

　　页面效果如下:

　　

　　2.点击修改时获取修改页面的servlet代码如下:

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.model.Ticket;
 4 import com.day03.station.service.impl.TicketService;
 5 
 6 import javax.servlet.ServletException;
 7 import javax.servlet.annotation.WebServlet;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 import java.io.IOException;
12 
13 /**
14  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
15  * 疑问咨询wx:851298348
16  */
17 @WebServlet("/updatePage")
18 public class UpdateTicketPageServlet extends HttpServlet {
19     private TicketService ticketService=new TicketService();
20     @Override
21     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
22         //接收参数
23         String idStr = req.getParameter("id");
24         //调用业务方法  通过id查找到具体的数据
25         Integer id = Integer.valueOf(idStr);
26 
27         Ticket ticket = ticketService.queryById(id);
28         //然后再显示到页面去  放入请求
29         req.setAttribute("ticket",ticket);
30 
31         //控制跳转
32         req.getRequestDispatcher("/WEB-INF/views/updateTicket.jsp").forward(req,resp);
33     }
34 }
UpdateTicketPageServlet

 　　3.修改界面代码如下:

 1 <%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
 2 <html>
 3 <head>
 4     <title>修改页面</title>
 5 </head>
 6 <body>
 7 
 8 我是修改页面
 9 <form action="/updateData" method="post">
10     id:<input id="id" type="text" name="id" value="${ticket.id}"/>
11     <br/>
12     <br/>
13     开始车站:<input id="startStation" type="text" name="startStation" value="${ticket.startStation}"/>
14     <br/>
15     <br/>
16     到达车站:<input id="stopStation" type="text" name="stopStation" value="${ticket.stopStation}"/>
17     <br/>
18     <br/>
19     <input type="submit" value="修改"/>
20 
21 </form>
22 </body>
23 </html>
View Code

 　　修改界面效果如下:

　　

　　4.点击修改按钮时,后端执行的servlet代码如下:

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.model.Ticket;
 4 import com.day03.station.service.impl.TicketService;
 5 
 6 import javax.servlet.ServletException;
 7 import javax.servlet.annotation.WebServlet;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 import java.io.IOException;
12 
13 /**
14  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
15  * 疑问咨询wx:851298348
16  */
17 @WebServlet("/updateData")
18 public class UpdateDataServlet extends HttpServlet {
19     private TicketService ticketService=new TicketService();
20     @Override
21     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
22         //接收参数
23         String idStr = req.getParameter("id");
24         Integer id = Integer.valueOf(idStr);
25 
26         String startStation = req.getParameter("startStation");
27         String stopStation = req.getParameter("stopStation");
28         //调用业务方法
29         Ticket ticket = new Ticket();
30         ticket.setId(id);
31         ticket.setStartStation(startStation);
32         ticket.setStopStation(stopStation);
33         ticketService.update(ticket);
34         //控制跳转
35         resp.sendRedirect("/list");
36     }
37 }
UpdateDataServlet

 48.web页面实现高级查询

　　实现步骤如下:

　　1.在列表界面中添加高级查询输入框,jsp代码如下:

　　

　　界面效果如下:

　　

　　2.控制层代码,

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.model.Ticket;
 4 import com.day03.station.query.TicketQueryObj;
 5 import com.day03.station.service.impl.TicketService;
 6 
 7 import javax.servlet.ServletException;
 8 import javax.servlet.annotation.WebServlet;
 9 import javax.servlet.http.HttpServlet;
10 import javax.servlet.http.HttpServletRequest;
11 import javax.servlet.http.HttpServletResponse;
12 import java.io.IOException;
13 import java.util.List;
14 
15 /**
16  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
17  * 疑问咨询wx:851298348
18  */
19 @WebServlet("/queryList")
20 public class QueryListServlet extends HttpServlet {
21     private TicketService ticketService=new TicketService();
22     @Override
23     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
24         //1.接收参数
25         String startStation = req.getParameter("startStation");
26         String stopStation = req.getParameter("stopStation");
27         String minTicketPrice = req.getParameter("minTicketPrice");
28         String maxTicketPrice = req.getParameter("maxTicketPrice");
29            //把条件封装成查询对象
30         TicketQueryObj ticketQueryObj = new TicketQueryObj();
31         //使用if处理没有传的参数
32         if (startStation!=null && !startStation.equals("")){
33             ticketQueryObj.setStartStation(startStation);
34         }
35        if (stopStation!=null && !stopStation.equals("")){
36            ticketQueryObj.setStopStation(stopStation);
37        }
38         if (minTicketPrice!=null && !minTicketPrice.equals("")){
39             ticketQueryObj.setMinTicketPrice(minTicketPrice);
40         }
41        if (maxTicketPrice!=null && !maxTicketPrice.equals("")){
42            ticketQueryObj.setMaxTicketPrice(maxTicketPrice);
43        }
44         
45         //2.调用业务方法
46         List<Ticket> query = ticketService.query(ticketQueryObj);
47 
48         //3.控制跳转
49         req.setAttribute("list",query);
50         req.setAttribute("ticketQueryObj",ticketQueryObj);
51         //3.控制跳转
52         req.getRequestDispatcher("/WEB-INF/views/ticketList.jsp").forward(req,resp);
53     }
54 }
QueryListServlet

 　　3.业务层代码

　　

　　4.dao方法实现

 1  @Override
 2     public List<Ticket> query(TicketQueryObj ticketQueryObj) {
 3         List<Ticket> list=new ArrayList<>();
 4         try {
 5 
 6             //1.加载
 7             Class.forName("com.mysql.jdbc.Driver");
 8             //2.链接
 9             Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
10             //3.创建编译语句
11             String sql="SELECT id,start_station,stop_station,start_time,ticket_price FROM ticket WHERE 1=1";
12             //拿一个袋子装 依次装参数
13             List<Object> param=new ArrayList<>();
14 
15             //如果有开始车站条件,则添加 该查询条件
16             if (ticketQueryObj.getStartStation()!=null){
17                 sql=sql+" AND start_station=?";
18                 param.add(ticketQueryObj.getStartStation());
19             }
20             if (ticketQueryObj.getStopStation()!=null){
21                 sql=sql+" AND stop_station=?";
22                 param.add(ticketQueryObj.getStopStation());
23             }
24             if (ticketQueryObj.getMinTicketPrice()!=null){
25                 sql=sql+" AND ticket_price>?";
26                 param.add(ticketQueryObj.getMinTicketPrice());
27             }
28             if (ticketQueryObj.getMaxTicketPrice()!=null){
29                 sql=sql+" AND ticket_price<?";
30                 param.add(ticketQueryObj.getMaxTicketPrice());
31             }
32 
33             System.out.println(" sql="+sql);
34 
35             PreparedStatement preparedStatement = connection.prepareStatement(sql);
36             // for循环设置参数
37             for (int i=0;i<param.size();i++){
38                 preparedStatement.setObject(i+1,param.get(i));
39                 System.out.println("  参数 ="+param.get(i));
40             }
41 
42 
43             //4.执行
44             ResultSet resultSet = preparedStatement.executeQuery();
45             //解析结果集
46             while (resultSet.next()){
47                 //取id
48                 int id1 = resultSet.getInt("id");
49                 //取开始车站
50                 String startStation = resultSet.getString("start_station");
51                 //取到达车站
52                 String stopStation = resultSet.getString("stop_station");
53                 //取发车时间
54                 String startTime = resultSet.getString("start_time");
55                 //取车票价格
56                 int ticketPrice = resultSet.getInt("ticket_price");
57                 //封装到对象里面去
58                 Ticket ticket = new Ticket();
59                 ticket.setId(id1);
60                 ticket.setStartStation(startStation);
61                 ticket.setStopStation(stopStation);
62                 ticket.setStartTime(startTime);
63                 ticket.setTicketPrice(ticketPrice);
64 
65                 //将车票对象装入集合对象里面
66                 list.add(ticket);
67 
68             }
69 
70             //5.释放资源
71             preparedStatement.close();
72             connection.close();
73         } catch (Exception e) {
74             e.printStackTrace();
75         }
76         return list;
77     }
View Code

 

 　　5.查询封装对象代码

 1 package com.day03.station.query;
 2 
 3 /**
 4  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 5  * 疑问咨询wx:851298348
 6  */
 7 public class TicketQueryObj {
 8    /* String startStation = req.getParameter("startStation");
 9     String stopStation = req.getParameter("stopStation");
10     String minTicketPrice = req.getParameter("minTicketPrice");
11     String maxTicketPrice = req.getParameter("maxTicketPrice");*/
12     private String startStation;
13     private String stopStation;
14     private String minTicketPrice;
15     private String maxTicketPrice;
16 
17     public String getStartStation() {
18         return startStation;
19     }
20 
21     public void setStartStation(String startStation) {
22         this.startStation = startStation;
23     }
24 
25     public String getStopStation() {
26         return stopStation;
27     }
28 
29     public void setStopStation(String stopStation) {
30         this.stopStation = stopStation;
31     }
32 
33     public String getMinTicketPrice() {
34         return minTicketPrice;
35     }
36 
37     public void setMinTicketPrice(String minTicketPrice) {
38         this.minTicketPrice = minTicketPrice;
39     }
40 
41     public String getMaxTicketPrice() {
42         return maxTicketPrice;
43     }
44 
45     public void setMaxTicketPrice(String maxTicketPrice) {
46         this.maxTicketPrice = maxTicketPrice;
47     }
48 }
TicketQueryObj

 

        6.效果演示如下:

　　

 49.50.web页面实现分页

　　实现后的效果:

　　

　　实现步骤:

　　1.页面jsp代码

 1 <%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
 2 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
 3 <html>
 4 <head>
 5     <title>车票列表</title>
 6 </head>
 7 <body>
 8 
 9 
10 <%--
11 表格标签
12 行   <tr>
13 列    <td>
14 --%>
15 
16 
17 <br/>
18 <div align="center">
19     <form action="/queryList" method="post">
20         当前页:<input id="currentPage" name="currentPage"  value="1">
21         每页显示条数:<input id="pageSize" name="pageSize"  value="3">
22         <br/>
23         开始车站:<input id="startStation" type="text" name="startStation" value="${ticketQueryObj.startStation}"/>
24         到达车站:<input id="stopStation" type="text" name="stopStation" value="${ticketQueryObj.stopStation}"/>
25         <br/>
26         最低价格:<input id="minTicketPrice" type="text" name="minTicketPrice" value="${ticketQueryObj.minTicketPrice}"/>
27         最高价格:<input id="maxTicketPrice" type="text" name="maxTicketPrice" value="${ticketQueryObj.maxTicketPrice}"/>
28         <input type="submit" value="查询"/>
29     </form>
30 </div>
31 <div align="center">
32     <a href="/addPage" >增加</a>
33 </div>
34 <br/>
35 <table align="center" border="1">
36     <tr>
37         <td>编号</td>
38         <td>开始车站</td>
39         <td>到达车站</td>
40         <td>发车时间</td>
41         <td>票价</td>
42         <td>操作</td>
43     </tr>
44    <c:forEach items="${pageResult.data}" var="ticket">
45       <tr>
46           <td>${ticket.id}</td>
47           <td>${ticket.startStation}</td>
48           <td>${ticket.stopStation}</td>
49           <td>${ticket.startTime}</td>
50           <td>${ticket.ticketPrice}</td>
51           <td>
52               <a href="/delete?id=${ticket.id}" >删除</a>|
53               <a href="/updatePage?id=${ticket.id}" >修改</a>
54           </td>
55       </tr>
56 
57    </c:forEach>
58 
59     <tr>
60         <td colspan="7">
61             <a href="#" onclick="goPage(1);" >首页</a>
62             <a href="#" onclick="goPage(${pageResult.currentPage}-1);">上页</a>
63             <a href="#"  onclick="goPage(${pageResult.currentPage}+1);">下页</a>
64             <a href="#" onclick="goPage(${pageResult.totalPage});">末页</a>
65             每页显示${pageResult.pageSize}条,当前${pageResult.currentPage}/${pageResult.totalPage}页,共${pageResult.totalNum}条数据
66         </td>
67     </tr>
68 
69 
70 </table>
71 </body>
72 <%--js函数--%>
73 <script type="text/javascript">
74     /*定义函数
75     *
76     * 作用:用来提交表单
77     * */
78     function goPage(currentPage){
79         alert("-----我是第一个函数-------");//向页面弹框
80         if (currentPage<1){
81             currentPage=1;
82         }
83         //给当前页在输入框中赋值
84         document.getElementById("currentPage").value=currentPage;
85         //提交表单
86         document.forms[0].submit();
87     }
88 
89 </script>
90 </html>
View Code

 

 　　2.控制层代码

 1 package com.day03.station.controller;
 2 
 3 import com.day03.station.common.PageResult;
 4 import com.day03.station.query.TicketQueryObj;
 5 import com.day03.station.service.impl.TicketService;
 6 
 7 import javax.servlet.ServletException;
 8 import javax.servlet.annotation.WebServlet;
 9 import javax.servlet.http.HttpServlet;
10 import javax.servlet.http.HttpServletRequest;
11 import javax.servlet.http.HttpServletResponse;
12 import java.io.IOException;
13 
14 /**
15  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
16  * 疑问咨询wx:851298348
17  */
18 @WebServlet("/queryList")
19 public class QueryListServlet extends HttpServlet {
20     private TicketService ticketService = new TicketService();
21 
22     @Override
23     public void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
24         //1.接收参数
25         String startStation = req.getParameter("startStation");
26         String stopStation = req.getParameter("stopStation");
27         String minTicketPrice = req.getParameter("minTicketPrice");
28         String maxTicketPrice = req.getParameter("maxTicketPrice");
29         //取出分页用的数据
30         String currentPage = req.getParameter("currentPage");
31         String pageSize = req.getParameter("pageSize");
32 
33         //把条件封装成查询对象
34         TicketQueryObj ticketQueryObj = new TicketQueryObj();
35         //使用if处理没有传的参数
36         if (startStation != null && !startStation.equals("")) {
37             ticketQueryObj.setStartStation(startStation);
38         }
39         if (stopStation != null && !stopStation.equals("")) {
40             ticketQueryObj.setStopStation(stopStation);
41         }
42         if (minTicketPrice != null && !minTicketPrice.equals("")) {
43             ticketQueryObj.setMinTicketPrice(minTicketPrice);
44         }
45         if (maxTicketPrice != null && !maxTicketPrice.equals("")) {
46             ticketQueryObj.setMaxTicketPrice(maxTicketPrice);
47         }
48         if (currentPage != null && !currentPage.equals("")) {
49             ticketQueryObj.setCurrentPage(Integer.valueOf(currentPage));
50         }
51         if (pageSize != null && !pageSize.equals("")) {
52             ticketQueryObj.setPageSize(Integer.valueOf(pageSize));
53         }
54 
55 
56         //2.调用业务方法
57         // List<Ticket> query = ticketService.query(ticketQueryObj);
58 
59         PageResult pageResult = ticketService.queryPageResult(ticketQueryObj);
60 
61 
62         //3.控制跳转
63         req.setAttribute("pageResult", pageResult);
64         req.setAttribute("ticketQueryObj", ticketQueryObj);
65         //3.控制跳转
66         req.getRequestDispatcher("/WEB-INF/views/ticketList.jsp").forward(req, resp);
67     }
68 }
QueryListServlet

 

 　　3.业务层代码

 1    @Override
 2     public PageResult queryPageResult(TicketQueryObj ticketQueryObj) {
 3         //封装分页对象
 4         PageResult pageResult = new PageResult();
 5        // private List<Ticket> data;
 6         List<Ticket> query = ticketDao.query(ticketQueryObj);
 7         pageResult.setData(query);
 8         //当前页
 9        // private Integer currentPage=1;
10         pageResult.setCurrentPage(ticketQueryObj.getCurrentPage());
11         //每页显示条数
12        // private Integer pageSize=3;
13         Integer pageSize = ticketQueryObj.getPageSize();
14 
15         pageResult.setPageSize(pageSize);
16         //共多少条  必须查询数据库
17       //  private Integer totalNum=10;
18         Integer count = ticketDao.count(ticketQueryObj);
19         pageResult.setTotalNum(count);
20         //共多少页
21        // private Integer totalPage=3;
22         Integer totalPage=(count+pageSize-1)/pageSize;
23         pageResult.setTotalPage(totalPage);
24 
25         return pageResult;
26     }

 

 　　4.dao的两条查询语句的实现

  1   /**
  2      * 查询分页列表
  3      * @param ticketQueryObj
  4      * @return
  5      */
  6     @Override
  7     public List<Ticket> query(TicketQueryObj ticketQueryObj) {
  8         List<Ticket> list=new ArrayList<>();
  9         try {
 10 
 11             //1.加载
 12             Class.forName("com.mysql.jdbc.Driver");
 13             //2.链接
 14             Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
 15             //3.创建编译语句
 16             String sql="SELECT id,start_station,stop_station,start_time,ticket_price FROM ticket WHERE 1=1";
 17             //拿一个袋子装 依次装参数
 18             List<Object> param=new ArrayList<>();
 19 
 20             //如果有开始车站条件,则添加 该查询条件
 21             if (ticketQueryObj.getStartStation()!=null){
 22                 sql=sql+" AND start_station=?";
 23                 param.add(ticketQueryObj.getStartStation());
 24             }
 25             if (ticketQueryObj.getStopStation()!=null){
 26                 sql=sql+" AND stop_station=?";
 27                 param.add(ticketQueryObj.getStopStation());
 28             }
 29             if (ticketQueryObj.getMinTicketPrice()!=null){
 30                 sql=sql+" AND ticket_price>?";
 31                 param.add(ticketQueryObj.getMinTicketPrice());
 32             }
 33             if (ticketQueryObj.getMaxTicketPrice()!=null){
 34                 sql=sql+" AND ticket_price<?";
 35                 param.add(ticketQueryObj.getMaxTicketPrice());
 36             }
 37             //拼接分页  LIMIT ?,?
 38             sql=sql+"  LIMIT ?,? ";
 39             param.add(ticketQueryObj.getStartIndex());
 40             param.add(ticketQueryObj.getPageSize());
 41 
 42             System.out.println(" sql="+sql);
 43 
 44             PreparedStatement preparedStatement = connection.prepareStatement(sql);
 45             // for循环设置参数
 46             for (int i=0;i<param.size();i++){
 47                 preparedStatement.setObject(i+1,param.get(i));
 48                 System.out.println("  参数 ="+param.get(i));
 49             }
 50 
 51 
 52             //4.执行
 53             ResultSet resultSet = preparedStatement.executeQuery();
 54             //解析结果集
 55             while (resultSet.next()){
 56                 //取id
 57                 int id1 = resultSet.getInt("id");
 58                 //取开始车站
 59                 String startStation = resultSet.getString("start_station");
 60                 //取到达车站
 61                 String stopStation = resultSet.getString("stop_station");
 62                 //取发车时间
 63                 String startTime = resultSet.getString("start_time");
 64                 //取车票价格
 65                 int ticketPrice = resultSet.getInt("ticket_price");
 66                 //封装到对象里面去
 67                 Ticket ticket = new Ticket();
 68                 ticket.setId(id1);
 69                 ticket.setStartStation(startStation);
 70                 ticket.setStopStation(stopStation);
 71                 ticket.setStartTime(startTime);
 72                 ticket.setTicketPrice(ticketPrice);
 73 
 74                 //将车票对象装入集合对象里面
 75                 list.add(ticket);
 76 
 77             }
 78 
 79             //5.释放资源
 80             preparedStatement.close();
 81             connection.close();
 82         } catch (Exception e) {
 83             e.printStackTrace();
 84         }
 85         return list;
 86     }
 87 
 88     /**
 89      * 查询总记录条数
 90      * @param ticketQueryObj
 91      * @return
 92      */
 93     @Override
 94     public Integer count(TicketQueryObj ticketQueryObj) {
 95 
 96       Integer totalNum=0;
 97         try {
 98 
 99             //1.加载
100             Class.forName("com.mysql.jdbc.Driver");
101             //2.链接
102             Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
103             //3.创建编译语句
104             String sql="SELECT COUNT(1) totalNum FROM ticket WHERE  1=1 ";
105             //拿一个袋子装 依次装参数
106             List<Object> param=new ArrayList<>();
107 
108             //如果有开始车站条件,则添加 该查询条件
109             if (ticketQueryObj.getStartStation()!=null){
110                 sql=sql+" AND start_station=?";
111                 param.add(ticketQueryObj.getStartStation());
112             }
113             if (ticketQueryObj.getStopStation()!=null){
114                 sql=sql+" AND stop_station=?";
115                 param.add(ticketQueryObj.getStopStation());
116             }
117             if (ticketQueryObj.getMinTicketPrice()!=null){
118                 sql=sql+" AND ticket_price>?";
119                 param.add(ticketQueryObj.getMinTicketPrice());
120             }
121             if (ticketQueryObj.getMaxTicketPrice()!=null){
122                 sql=sql+" AND ticket_price<?";
123                 param.add(ticketQueryObj.getMaxTicketPrice());
124             }
125 
126             System.out.println(" sql="+sql);
127 
128             PreparedStatement preparedStatement = connection.prepareStatement(sql);
129             // for循环设置参数
130             for (int i=0;i<param.size();i++){
131                 preparedStatement.setObject(i+1,param.get(i));
132                 System.out.println("  参数 ="+param.get(i));
133             }
134 
135 
136             //4.执行
137             ResultSet resultSet = preparedStatement.executeQuery();
138             //解析结果集
139             while (resultSet.next()){
140                 //取id
141                  totalNum = resultSet.getInt("totalNum");
142 
143 
144             }
145 
146             //5.释放资源
147             preparedStatement.close();
148             connection.close();
149         } catch (Exception e) {
150             e.printStackTrace();
151         }
152         return totalNum;
153     }
View Code

 

 　　5.封装的查询对象

 1 package com.day03.station.query;
 2 
 3 /**
 4  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 5  * 疑问咨询wx:851298348
 6  */
 7 public class TicketQueryObj {
 8    /* String startStation = req.getParameter("startStation");
 9     String stopStation = req.getParameter("stopStation");
10     String minTicketPrice = req.getParameter("minTicketPrice");
11     String maxTicketPrice = req.getParameter("maxTicketPrice");*/
12     private String startStation;
13     private String stopStation;
14     private String minTicketPrice;
15     private String maxTicketPrice;
16     //String currentPage = req.getParameter("currentPage");
17     //String pageSize = req.getParameter("pageSize");
18     private Integer currentPage=1;
19     private Integer pageSize=3;
20     //开始下标
21     private Integer startIndex=0;
22 
23     /**
24      * 开始下标计算
25      * @return
26      */
27     public Integer getStartIndex() {
28          if (currentPage!=0 && pageSize!=0){
29              startIndex=(currentPage-1)*pageSize;
30          }
31         return startIndex;
32     }
33 
34     public void setStartIndex(Integer startIndex) {
35         this.startIndex = startIndex;
36     }
37 
38     public String getStartStation() {
39         return startStation;
40     }
41 
42     public void setStartStation(String startStation) {
43         this.startStation = startStation;
44     }
45 
46     public String getStopStation() {
47         return stopStation;
48     }
49 
50     public void setStopStation(String stopStation) {
51         this.stopStation = stopStation;
52     }
53 
54     public String getMinTicketPrice() {
55         return minTicketPrice;
56     }
57 
58     public void setMinTicketPrice(String minTicketPrice) {
59         this.minTicketPrice = minTicketPrice;
60     }
61 
62     public String getMaxTicketPrice() {
63         return maxTicketPrice;
64     }
65 
66     public void setMaxTicketPrice(String maxTicketPrice) {
67         this.maxTicketPrice = maxTicketPrice;
68     }
69 
70     public Integer getCurrentPage() {
71         return currentPage;
72     }
73 
74     public void setCurrentPage(Integer currentPage) {
75         this.currentPage = currentPage;
76     }
77 
78     public Integer getPageSize() {
79         return pageSize;
80     }
81 
82     public void setPageSize(Integer pageSize) {
83         this.pageSize = pageSize;
84     }
85 }
View Code

 

 　　6.封装的分页对象

 1 package com.day03.station.common;
 2 
 3 import com.day03.station.model.Ticket;
 4 
 5 import java.util.List;
 6 
 7 /**
 8  * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 9  * 疑问咨询wx:851298348
10  */
11 public class PageResult {
12     private List<Ticket> data;
13     //当前页
14     private Integer currentPage=1;
15     //每页显示条数
16     private Integer pageSize=3;
17     //共多少条
18     private Integer totalNum=10;
19     //共多少页
20     private Integer totalPage=3;
21 
22     public List<Ticket> getData() {
23         return data;
24     }
25 
26     public void setData(List<Ticket> data) {
27         this.data = data;
28     }
29 
30     public Integer getCurrentPage() {
31         return currentPage;
32     }
33 
34     public void setCurrentPage(Integer currentPage) {
35         this.currentPage = currentPage;
36     }
37 
38     public Integer getPageSize() {
39         return pageSize;
40     }
41 
42     public void setPageSize(Integer pageSize) {
43         this.pageSize = pageSize;
44     }
45 
46     public Integer getTotalNum() {
47         return totalNum;
48     }
49 
50     public void setTotalNum(Integer totalNum) {
51         this.totalNum = totalNum;
52     }
53 
54     public Integer getTotalPage() {
55         return totalPage;
56     }
57 
58     public void setTotalPage(Integer totalPage) {
59         this.totalPage = totalPage;
60     }
61 }
View Code

 

        总结:分页的重点在于:

　　　　1.前端传递两个参数   当前页和每页显示条数

　　　　2.后端必须发送两条sql语句   一条查询分页列表,另一条查询总条数

51.js函数补充

　　

　　js代码:

　　

52.字符串比较是否相等

　　

 

posted @ 2018-05-08 17:36  李东平|一线码农  阅读(847)  评论(0)  编辑  收藏  举报