地铁站查询（独自）

设计思想：在网页上输入起点站和终点站后，通过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;
         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">查&nbsp;&nbsp;&nbsp;询</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();
            }
        }
    }

}