地铁站查询(独自)
设计思想:在网页上输入起点站和终点站后,通过servlet方法传至到servlet里,在通过dao层将数据库中值提取出来,目前制作出来起点和终点在一条线上的方法,首先将起点和终点name通过数据库提取出对应着线路名number,判断两个值是不是相等,如果相等,那么就在一条线路上,但不相等那么就不再一条线路上,需要换乘
但是这样有个小bug 起点和终点不能是换乘站,那样输出不出来。解决方法是 求name对应number的max和min 判断是不是相等,如果相等那么不是换乘站,直接比较number是不是相等,若不相等,那么取ma和min与另一个name对应的number相等的number,直接在一条直线上。可以解决。
package com.hjf.servlet; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.hjf.dao.DitieDao; @WebServlet("/DitieServlet") public class DitieServlet extends HttpServlet{ private static final long serialVersionUID = 1L; DitieDao dao= new DitieDao(); /** * 方法选择 */ protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("search".equals(method)) { search(req, resp); } } /** * 查找 * @param req * @param resp * @throws IOException * @throws ServletException */ private void search(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // TODO Auto-generated method stub req.setCharacterEncoding("utf-8"); String sname=req.getParameter("sname"); String ename=req.getParameter("ename"); int zhanhao1=dao.getZhanhao(sname); int zhanhao2=dao.getZhanhao(ename); int number1=dao.getNum(sname); int number2=dao.getNum(ename); int xnumber1=dao.getNumber(sname); int xnumber2=dao.getNumber(ename); int nnumber1=dao.getNumber1(sname); int nnumber2=dao.getNumber1(ename); if(number1==number2) { if(zhanhao1<zhanhao2) { String line=dao.getLine1(zhanhao1, zhanhao2,number1); System.out.print(line); req.setAttribute("line",line ); req.setAttribute("num",number1); req.getRequestDispatcher("chaxunlist.jsp").forward(req,resp); req.getRequestDispatcher("chaxunlist.jsp").forward(req,resp); }else if(zhanhao1>zhanhao2) { String line=dao.getLine2(zhanhao2, zhanhao1,number1); System.out.print(line); req.setAttribute("num",number1); req.setAttribute("line",line ); req.getRequestDispatcher("chaxunlist.jsp").forward(req,resp); } } else if(!(number1==number2)) { } }}
package com.hjf.entity; public class Ditie { private int id; private int number; private String name; private int zhanhao; private String huancheng; public int getId() { return id; } public void setId(int id) { this.id = id; } public int getNumber() { return number; } public void setNumber(int number) { this.number = number; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getZhanhao() { return zhanhao; } public void setZhanhao(int zhanhao) { this.zhanhao = zhanhao; } public String getHuancheng() { return huancheng; } public void setHuancheng(String huancheng) { this.huancheng = huancheng; } public Ditie(){ } public Ditie(int id,int number,String name,int zhanhao,String huancheng) { this.id=id; this.number=number; this.name=name; this.zhanhao=zhanhao; this.huancheng=huancheng; } public Ditie(int number,String name,int zhanhao,String huancheng) { this.number=number; this.name=name; this.zhanhao=zhanhao; this.huancheng=huancheng; } }
package com.hjf.dao; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import com.hjf.util.DBUtil; public class DitieDao { /** * 通过name得到number(线路号) * @param name * @return */ public int getNum(String name) { String sql = "select number,count(number) from ditie where name ='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; int number=0; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { number=rs.getInt("number"); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return number; } public int getNumber(String name) {//number最大值 String sql = "select max(number) from ditie where name ='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; int c = 0; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { c=rs.getInt("max(number)"); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return c; } public int getNumber1(String name) { String sql = "select min(number) from ditie where name ='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; int number=0; int c1 = 0; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { c1=rs.getInt("min(number)"); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return c1; } /** * 通过name得到zhanhao(站台号) * @param name * @return */ public int getZhanhao(String name) { String sql = "select zhanhao from ditie where name ='" + name + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; int zhanhao=0; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { zhanhao = rs.getInt("zhanhao"); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return zhanhao; } public String getLine1(int zhanhao1,int zhanhao2,int number) { String line=""; String sql = "select name from ditie where (zhanhao between '"+zhanhao1+"' and '"+zhanhao2+"') and number='"+number+"' order by zhanhao ASC ";//降序 Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); if(rs.next()) line=rs.getString("name"); while (rs.next()) { String name=rs.getString("name"); line=line+"->"+name; } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return line; } public String getLine2(int zhanhao1,int zhanhao2,int number) { String line=""; String sql = "select name from ditie where (zhanhao between '"+zhanhao1+"' and '"+zhanhao2+"') and number='"+number+"' order by zhanhao DESC ";//升序 Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); if(rs.next()) line=rs.getString("name"); while (rs.next()) { String name=rs.getString("name"); line=line+"->"+name; } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return line; } //换乘 }
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; width: 160px; color: white; background-color: greenyellow; } </style> </head> <body> <table> <tr> <td > <img src="D:\大学\地铁\WebContent/ditie.jpg" width="800" height="500" ></td> <td > <h1 style="color: green;">地铁查询</h1> <a href="index.jsp">返回主页</a> <form action="DitieServlet?method=search" method="post" onsubmit="return check()"> <div class="a"> 起点地址<input type="text" id="sname" name="sname"/> </div> <div class="a"> 起点地址<input type="text" id="ename" name="ename"/> </div> <div class="a"> <button type="submit" class="b">查 询</button> </div> </form></td> </tr> </table> </body> </html>
package com.hjf.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 数据库连接工具 * @author zlj * */ public class DBUtil { public static String db_url = "jdbc:mysql://localhost:3306/course?useSSL=false"; public static String db_user = "root"; public static String db_pass = "199126"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver");//加载驱动 conn = DriverManager.getConnection(db_url, db_user, db_pass); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }