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 }
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>
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>
列表页面显示效果:
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 }
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>
页面效果图:
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 }
新增完成!
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 }
删除完成!
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 }
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>
修改界面效果如下:
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 }
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 }
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 }
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 }
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>
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 }
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 }
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 }
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 }
总结:分页的重点在于:
1.前端传递两个参数 当前页和每页显示条数
2.后端必须发送两条sql语句 一条查询分页列表,另一条查询总条数
51.js函数补充
js代码:
52.字符串比较是否相等