地铁查询系统团队进度(五)

package com.dao;

import com.alibaba.druid.sql.visitor.functions.Char;
import com.bean.Line;
import com.util.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class OneTwoDao {
    ArrayList<String> array=new ArrayList<String>();
    /*
     *     按线路查询
     */
    public ArrayList<String> select_line(Line line) throws Exception {
        int num = 0;
        Connection connection = jdbcUtil.getconnection();
        String sql = "select * from firstline  WHERE line=?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, line.getTemporaryline());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            String station = (rs.getString("StopName"));
            array.add(station);
        }

        String sql1 = "select * from changeline  WHERE ID1=? or ID2=?";
        PreparedStatement pstmt1 = connection.prepareStatement(sql1);
        pstmt1.setInt(1, line.getTemporaryline());
        pstmt1.setInt(2, line.getTemporaryline());
        ResultSet rs1 = pstmt1.executeQuery();
        while (rs1.next()) {
            int ID1 = (rs1.getInt("ID1"));
            int ID2 = (rs1.getInt("ID2"));
            String station = (rs1.getString("ChangeStopName")) +"--->" + ID1 + "号线" + "转"  + ID2 + "号线";
            array.add(station);
        }

        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt);
        jdbcUtil.close(rs);
        jdbcUtil.close(pstmt1);
        jdbcUtil.close(rs1);
        return  array;
    }
    public ArrayList<String> select_station(Line line) throws Exception {
        int flag = 0;
        Connection connection = jdbcUtil.getconnection();

        String sql1 = "select * from changeline  WHERE ChangeStopName=?";
        PreparedStatement pstmt1 = connection.prepareStatement(sql1);
        pstmt1.setString(1, line.getStation());
        ResultSet rs1 = pstmt1.executeQuery();
        while (rs1.next()) {
            int ID1 = (rs1.getInt("ID1"));
            int ID2 = (rs1.getInt("ID2"));
            String line2 = String.valueOf(ID1) ;
            String line3 = String.valueOf(ID2) ;
            String line4 = line2 + "号线" + "转" + line3 + "号线";
            array.add(line4);
            flag = 1;
        }
        if(flag == 0)
        {
            String sql = "select * from firstline  WHERE StopName=?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, line.getStation());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int line1 = (rs.getInt("line"));
                String line2 = String.valueOf(line1)+ "号线";
                array.add(line2);
            }
            jdbcUtil.close(pstmt);
            jdbcUtil.close(rs);
            return  array;
        }
        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt1);
        jdbcUtil.close(rs1);
        return  array;
    }
}

package com.dao;

import com.bean.Line;
import com.util.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserDao {
    int demostartid=0,demoendid=0;
    ArrayList<String> array=new ArrayList<String>();
    /*
     *     起始点与终点在一条线上且非换乘站
     */
    public ArrayList<String> Connectonlyoneline(Line line) throws Exception {
        ArrayList<String> array1 = new ArrayList<String>();
        int num = 0;
        Connection connection = jdbcUtil.getconnection();
        String sql = "select * from firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName=? AND f2.StopName=?";
        //保证起始站（同时有可能是中转站）与终点站（同时有可能是中转站）在同一条线路上
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, line.getStartstopname());
        pstmt.setString(2, line.getEndstopname());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            line.setTemporaryline(rs.getInt("Line"));
        }
        System.out.println(line.getTemporaryline());
        array.add("乘坐" + line.getTemporaryline() + "号线");

        num = select(line);
        //起始站编号小于终点站，即起始站在前，终点站在后
        if (num == 1) {
            String sql1 = "select * from firstline where StopID>=(select StopID from firstline where StopName=? ) and StopID<=(select StopID from firstline where StopName=? )  and Line=?";
            /*
             * 找到这样的一些站：它们的ID号大于起始站的ID号，
             * 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
             */
            PreparedStatement pstmt1 = connection.prepareStatement(sql1);
            pstmt1.setString(1, line.getStartstopname());
            pstmt1.setString(2, line.getEndstopname());
            pstmt1.setInt(3, line.getTemporaryline());

            ResultSet rs1 = pstmt1.executeQuery();
            while (rs1.next()) {
                //遍历结果集
                array.add(rs1.getString("StopName"));
            }

            jdbcUtil.close(pstmt1);
            jdbcUtil.close(rs1);
        } else if (num == 2) {
            //起始站编号大于终点站，即起始站在后，终点站在前
            String sql2 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID from firstline where StopName=? )  and Line=?";
            /*
             * 同上
             */
            PreparedStatement pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setString(1, line.getStartstopname());
            pstmt2.setString(2, line.getEndstopname());
            pstmt2.setInt(3, line.getTemporaryline());

            ResultSet rs2 = pstmt2.executeQuery();
            while (rs2.next()) {
                array1.add(rs2.getString("StopName"));
            }
            for (int i = 0; i < array1.size(); i++) {//测试是否有值
                System.out.print(array1.get(i) + " ");
            }
            for (int j = array1.size() - 1; j >= 0; j--) {//将站名倒序传入
                array.add(array1.get(j));
            }
            jdbcUtil.close(pstmt2);
            jdbcUtil.close(rs2);
        }
        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt);
        jdbcUtil.close(rs);
        return  array;
    }
    /*
     *     两条不同的、有交点的线路
     */
    public ArrayList<String> Connecttwoline(Line line) throws Exception {
        int num = 0;
        Connection connection = jdbcUtil.getconnection();
        String sql = "select * from changeline where ID1=? and ID2=? ";
        //根据交点线路查询中转站
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, line.getOriginline());
        pstmt.setInt(2, line.getFinishline());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next())
        {
            line.setMiddlestop(rs.getString("ChangeStopName"));
        }
        //当前起始点与结束点,存放
        line.setTemporarystartstopname(line.getStartstopname());
        line.setTemporaryendstopname(line.getEndstopname());
        String sql1="select * from firstline where Line=? and StopName=? ";
        //根据起始线路和中转站名称标出中转站位置
        PreparedStatement pstmt1 = connection.prepareStatement(sql1);
        pstmt1.setInt(1, line.getOriginline());
        pstmt1.setString(2, line.getMiddlestop());
        ResultSet rs1 = pstmt1.executeQuery();
        while(rs1.next()) {
            line.setEndstopname(rs1.getString("StopName"));
            //起始点不变，结束点设置为中转点
        }
        //将起始点和中转点所在线路一致的放入临时站线路
        line.setStartstopname(line.getTemporarystartstopname());
        line.setTemporaryline(line.getOriginline());

        array.add("乘坐"+line.getOriginline()+"号线");
        num = select(line);
        if (num == 1) {
            String sql2 = "select * from firstline where StopID>=(select StopID from firstline where StopName=? ) and StopID<=(select StopID from firstline where StopName=? ) and Line=? ";
            /*
             * 找到这样的一些站：它们的ID号大于起始站的ID号，
             * 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
             */
            PreparedStatement pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setString(1, line.getStartstopname());
            pstmt2.setString(2, line.getEndstopname());
            pstmt2.setInt(3, line.getTemporaryline());
            ResultSet rs2 = pstmt2.executeQuery();
            while (rs2.next()) {
                //遍历结果集
                array.add(rs2.getString("StopName"));
            }
            jdbcUtil.close(pstmt2);
            jdbcUtil.close(rs2);
        } else if (num == 2) {
            ArrayList<String> array1=new ArrayList<String>();
            //起始站编号大于终点站，即起始站在后，终点站在前
            String sql2 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID from firstline where StopName=? )  and Line=?";
            /*
             * 同上
             */
            PreparedStatement pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setString(1, line.getStartstopname());
            pstmt2.setString(2, line.getEndstopname());
            pstmt2.setInt(3, line.getTemporaryline());
            ResultSet rs2 = pstmt2.executeQuery();
            while (rs2.next()) {
                array1.add(rs2.getString("StopName"));
            }
            for (int i = 0; i < array1.size(); i++) {//测试是否有值
                System.out.print(array1.get(i) + " ");
            }
            for (int j = array1.size() - 1; j >= 0; j--) {//将站名倒序传入
                array.add(array1.get(j));
            }
            jdbcUtil.close(pstmt2);
            jdbcUtil.close(rs2);
        }
        array.add("转乘"+line.getFinishline()+"号线");
        /*
         *
         *
         *
         *
         */
        line.setStartstopname(line.getMiddlestop());
        //将起始点设为中转点
        line.setEndstopname(line.getTemporaryendstopname());
        //将结束点回归
        line.setTemporaryline(line.getFinishline());
        //将中转点和结束点所在线路一致的放入临时站线路
        num = select(line);
        if (num == 1) {
            String sql3 = "select * from firstline where StopID>=(select StopID2 from firstline where StopName=?) and StopID<=(select StopID from firstline where StopName=? )  and Line=? ";
            /*
             * 找到这样的一些站：它们的ID号大于起始站的ID号，
             * 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
             */
            PreparedStatement pstmt3 = connection.prepareStatement(sql3);
            pstmt3.setString(1, line.getStartstopname());
            pstmt3.setString(2, line.getEndstopname());
            pstmt3.setInt(3, line.getTemporaryline());
            ResultSet rs3= pstmt3.executeQuery();
            while (rs3.next()) {
                //遍历结果集
                array.add(rs3.getString("StopName"));
            }
            System.out.println(line.getStartstopname());
            System.out.println(line.getTemporaryline());
            System.out.println(line.getEndstopname());
            System.out.println(line.getFinishline());

            jdbcUtil.close(pstmt3);
            jdbcUtil.close(rs3);
        } else if (num == 2) {
            ArrayList<String> array1=new ArrayList<String>();
            //起始站编号大于终点站，即起始站在后，终点站在前
            String sql3 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID2 from firstline where StopName=? ) and Line=?";
            /*
             * 同上
             */
            PreparedStatement pstmt3 = connection.prepareStatement(sql3);
            pstmt3.setString(1, line.getStartstopname());
            pstmt3.setString(2, line.getEndstopname());
            pstmt3.setInt(3, line.getTemporaryline());
            ResultSet rs3 = pstmt3.executeQuery();
            while (rs3.next()) {
                array1.add(rs3.getString("StopName"));
            }
            for (int i = 0; i < array1.size(); i++) {//测试是否有值
                System.out.print(array1.get(i) + " ");
            }
            for (int j = array1.size() - 1; j >= 0; j--) {//将站名倒序传入
                array.add(array1.get(j));
            }

            jdbcUtil.close(pstmt3);
            jdbcUtil.close(rs3);
        }
        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt);
        jdbcUtil.close(rs);
        jdbcUtil.close(pstmt1);
        jdbcUtil.close(rs1);
        return array;
    }
    /*
     * 解决两条线之间没有换乘站，只能通过第三条线换乘
     * 即A（普通站）->B（中转站）->C（中转站）->D（普通站）
     *
     */
    public ArrayList<String> Connectthreeline(Line line) throws Exception {
        ArrayList<Integer> array1 = new ArrayList<Integer>();
        int num = 0;
        int min = 0;
        Connection connection = jdbcUtil.getconnection();
        PreparedStatement pstmt = null;
        String sql = "SELECT * FROM    changeline c1 JOIN changeline c2 ON c1.ID2 = c2.ID1 WHERE trim(c1.ChangeStopName) != '' AND trim(c2.ChangeStopName) != '' AND c1.ID1 =?  AND c2.ID2 = ?";
        /*
         * 找到与起始站名称相同，结束站名称相同，且同时属于同一条线的两个站
         */
        pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, line.getOriginline());
        pstmt.setInt(2, line.getFinishline());

        ResultSet rs = pstmt.executeQuery();
        while (rs.next())
        {
            if(line.getOriginline()==rs.getInt("ID1"))
            {
                array1.add(rs.getInt("ID2"));
            }
        }
        line.setTemporarystartstopname(line.getStartstopname());
        line.setTemporaryendstopname(line.getEndstopname());
        /*
         *
         * 第一次转乘
         * A（普通站）->B（中转站）
         *
         */
        for(int j=0;j<array1.size();j++)
        {
            ArrayList<String> array3 = new ArrayList<String>();
            System.out.println("也许这是我想要的起始站" + line.getTemporarystartstopname());
            String sql1 ="select * from changeline where ID1=? and ID2=? ";

            PreparedStatement pstmt1 = connection.prepareStatement(sql1);
            pstmt1.setInt(1, line.getOriginline());
            pstmt1.setInt(2, array1.get(j));
            ResultSet rs1= pstmt1.executeQuery();
            while (rs1.next())
            {
                line.setEndstopname(rs1.getString("ChangeStopName"));
            }
            //System.out.println(line.getEndstopname());
            jdbcUtil.close(rs1);
            jdbcUtil.close(pstmt1);
            array3.add("乘坐"+line.getOriginline()+"号线");

            String sql2 = "select * from firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName=? AND f2.StopName=?";
            //保证起始站（同时有可能是中转站）与终点站（同时有可能是中转站）在同一条线路上
            PreparedStatement pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setString(1, line.getStartstopname());
            pstmt2.setString(2, line.getEndstopname());
            ResultSet rs2 = pstmt2.executeQuery();
            while (rs2.next()) {
                line.setTemporaryline(rs2.getInt("Line"));
            }
            jdbcUtil.close(rs2);
            jdbcUtil.close(pstmt2);
            num = select(line);
            //起始站编号小于终点站，即起始站在前，终点站在后
            if (num == 1) {
                String sql3 = "select * from firstline where StopID>=(select StopID from firstline where StopName=?   ) and StopID<=(select StopID from firstline where StopName=?  ) and Line=?";
                /*
                 * 找到这样的一些站：它们的ID号大于起始站的ID号，
                 * 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
                 */
                PreparedStatement pstmt3 = connection.prepareStatement(sql3);
                pstmt3.setString(1, line.getStartstopname());
                pstmt3.setString(2, line.getEndstopname());
                pstmt3.setInt(3, line.getTemporaryline());
                ResultSet rs3 = pstmt3.executeQuery();
                while (rs3.next()) {
                    //遍历结果集
                    array3.add(rs3.getString("StopName"));
                }
               // System.out.println(array);
                jdbcUtil.close(pstmt3);
                jdbcUtil.close(rs3);
            } else if (num == 2) {
                ArrayList<String> array2=new ArrayList<String>();
                //起始站编号大于终点站，即起始站在后，终点站在前
                String sql3 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID from firstline where StopName=?)  and Line=? ";
                /*
                 * 同上
                 */
                PreparedStatement pstmt3 = connection.prepareStatement(sql3);
                pstmt3.setString(1, line.getStartstopname());
                pstmt3.setString(2, line.getEndstopname());
                pstmt3.setInt(3, line.getTemporaryline());
                ResultSet rs3 = pstmt3.executeQuery();
                while (rs3.next()) {
                    array2.add(rs3.getString("StopName"));
                }
                for (int i = 0; i < array2.size(); i++) {//测试是否有值
                    System.out.print(array2.get(i) + " ");
                }
                for (int i = array2.size() - 1; i >= 0; i--) {//将站名倒序传入
                    array3.add(array2.get(i));
                }
                jdbcUtil.close(pstmt3);
                jdbcUtil.close(rs3);
            }
            /*
             * 第二次换乘
             * B（中转站）->C（中转站）
             *
             */
            line.setStartstopname(line.getEndstopname());
            String sql4="select * from changeline where ID1=?  and ID2=? ";
            PreparedStatement pstmt4 = connection.prepareStatement(sql4);
            pstmt4.setInt(1, array1.get(j));
            pstmt4.setInt(2, line.getFinishline());
            ResultSet rs4= pstmt4.executeQuery();
            while(rs4.next())
            {
                line.setEndstopname(rs4.getString("ChangeStopName"));
            }

            jdbcUtil.close(rs4);
            jdbcUtil.close(pstmt4);
//            System.out.println(line.getStartstopname());
//            System.out.println(line.getEndstopname());
           // String sql5="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName=?  AND f2.StopName=? ";
            String sql5="select ID2 from changeline where ChangeStopName = ? ";
            //保证两个中转站在同一条线路上
            PreparedStatement pstmt5 = connection.prepareStatement(sql5);
            pstmt5.setString(1, line.getStartstopname());
            ResultSet rs5 = pstmt5.executeQuery();
            while(rs5.next()) {
                line.setTemporaryline(rs5.getInt("ID2"));
            }
//            System.out.println("hhhhhhhhhhhh");
//            System.out.println( line.getTemporaryline());
            jdbcUtil.close(rs5);
            jdbcUtil.close(pstmt5);
            array3.add("转乘"+line.getTemporaryline()+"号线");

            num = select(line);
            //起始站编号小于终点站，即起始站在前，终点站在后
            if (num == 1) {
                String sql6 = "select * from firstline where StopID>=(select StopID2 from firstline where StopName=? ) and StopID<=(select StopID from firstline where StopName=? ) and Line=? ";
                /*
                 * 找到这样的一些站：它们的ID号大于起始站的ID号，
                 * 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
                 */
                PreparedStatement pstmt6 = connection.prepareStatement(sql6);
                pstmt6.setString(1, line.getStartstopname());
                pstmt6.setString(2, line.getEndstopname());
                pstmt6.setInt(3, line.getTemporaryline());
                ResultSet rs6 = pstmt6.executeQuery();
                while (rs6.next()) {
                    //遍历结果集
                    array3.add(rs6.getString("StopName"));
                  //  System.out.println(rs6.getString("StopName"));
                }
                //System.out.println(array);
                jdbcUtil.close(pstmt6);
                jdbcUtil.close(rs6);
            } else if (num == 2) {
                ArrayList<String> array2=new ArrayList<String>();
                //起始站编号大于终点站，即起始站在后，终点站在前
                String sql6 = "select * from firstline where StopID<=(select StopID from firstline where StopName=?  ) and StopID>=(select Stop from firstline where StopName=? ) and Line=? ";
                /*
                 * 同上
                 */
                PreparedStatement pstmt6 = connection.prepareStatement(sql6);
                pstmt6.setString(1, line.getStartstopname());
                pstmt6.setString(2, line.getEndstopname());
                pstmt6.setInt(3, line.getTemporaryline());
                ResultSet rs6 = pstmt6.executeQuery();
                while (rs6.next()) {
                    array2.add(rs6.getString("StopName"));
                }
                for (int i = 0; i < array2.size(); i++) {//测试是否有值
                    System.out.print(array2.get(i) + " ");
                }
                for (int i = array2.size() - 1; i >= 0; i--) {//将站名倒序传入
                    array3.add(array2.get(i));
                }
                jdbcUtil.close(pstmt6);
                jdbcUtil.close(rs6);
            }
            /*
             *
             * 最后一次换乘
             * 即C（中转站）->D（普通站）
             */
            line.setStartstopname(line.getEndstopname());
            line.setEndstopname(line.getTemporaryendstopname());
            String sql7="select ID2 from changeline where ChangeStopName = ? ";
            //String sql7="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName=?  AND f2.StopName=? ";
            //保证两个中转站在同一条线路上
            PreparedStatement pstmt7 = connection.prepareStatement(sql7);
            pstmt7.setString(1, line.getStartstopname());
           // pstmt7.setString(2, line.getEndstopname());
            ResultSet rs7 = pstmt7.executeQuery();
            while(rs7.next()) {
                line.setTemporaryline(rs7.getInt("ID2"));
            }
            jdbcUtil.close(rs7);
            jdbcUtil.close(pstmt7);

            array3.add("转乘"+line.getTemporaryline()+"号线");
            num = select(line);

            //起始站编号小于终点站，即起始站在前，终点站在后
            if (num == 1) {
                String sql8 = "select * from firstline where StopID>=(select StopID2 from firstline where StopName=? ) and StopID<=(select StopID from firstline where StopName=? ) and Line=? ";
                /*
                 * 找到这样的一些站：它们的ID号大于起始站的ID号，
                 * 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
                 */
                PreparedStatement pstmt8 = connection.prepareStatement(sql8);
                pstmt8.setString(1, line.getStartstopname());
                pstmt8.setString(2, line.getEndstopname());
                pstmt8.setInt(3, line.getTemporaryline());
                ResultSet rs8 = pstmt8.executeQuery();
                while (rs8.next()) {
                    //遍历结果集
                    array3.add(rs8.getString("StopName"));
                }

                jdbcUtil.close(pstmt8);
                jdbcUtil.close(rs8);
            } else if (num == 2) {
                ArrayList<String> array2=new ArrayList<String>();
                //起始站编号大于终点站，即起始站在后，终点站在前
                String sql8 = "select * from firstline where StopID<=(select StopID2 from firstline where StopName=?) and StopID>=(select StopID from firstline where StopName=? )  and Line=?";
                /*
                 * 同上
                 */
                PreparedStatement pstmt8 = connection.prepareStatement(sql8);
                pstmt8.setString(1, line.getStartstopname());
                pstmt8.setString(2, line.getEndstopname());
                pstmt8.setInt(3, line.getTemporaryline());
                ResultSet rs8 = pstmt8.executeQuery();
                while (rs8.next()) {
                    array2.add(rs8.getString("StopName"));
                }
                for (int i = 0; i < array2.size(); i++) {//测试是否有值
                    System.out.print(array2.get(i) + " ");
                }
                for (int i = array2.size() - 1; i >= 0; i--) {//将站名倒序传入
                    array3.add(array2.get(i));
                }
                jdbcUtil.close(pstmt8);
                jdbcUtil.close(rs8);
            }
            if(j==0) {
                min=array3.size();
            }
            /*
             * 第一次：5 min=5 array.size=5
             * 第二次：3 array.size=3 min=5
             * 第三次：7 array.size=7 min=3
             */
            //输出array3测试
            System.out.println();
            System.out.println();
            for(int x=0;x<array3.size();x++) {
                System.out.print(array3.get(x)+" ");
            }
            System.out.println();
            System.out.println(min);
            if(min>=array3.size()) {
                min=array3.size();
                for(int x=0;x<array3.size();x++) {
                    System.out.print(array3.get(x)+" ");
                }
                array=array3;
            }
        }
        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt);
        jdbcUtil.close(rs);
        return array;
    }
    private int select(Line line) throws Exception {
        //返回值，1为顺序（数据库中），2为逆序（数据库中）
        try
        {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement pstmt = null;
            String sql = "select * from firstline where StopName=? and Line=?  or StopName=? and Line=?";
            /*
             * 找到与起始站名称相同，结束站名称相同，且同时属于同一条线的两个站
             */
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, line.getStartstopname());
            pstmt.setInt(2, line.getTemporaryline());
            pstmt.setString(3, line.getEndstopname());
            pstmt.setInt(4, line.getTemporaryline());
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
            {
                if(line.getStartstopname().equals(rs.getString("StopName")))
                {
                    line.setStartstopID(rs.getInt("StopID"));
                }
                if(line.getEndstopname().equals(rs.getString("StopName")))
                {
                    line.setEndstopID(rs.getInt("StopID"));
                }
            }
            jdbcUtil.close(connection);
            jdbcUtil.close(pstmt);
            jdbcUtil.close(rs);
        }catch(SQLException e) {
            throw new RuntimeException(e);
        }
        System.out.println(line.getStartstopID()+" "+line.getEndstopID());
        if(line.getStartstopID() < line.getEndstopID())
        {
            return 1;
        }
        else
            return 2;
    }

    /*
     * 判断路线情况
     */
    public int CheckAll(Line line) throws Exception
    {
            Connection connection = jdbcUtil.getconnection();
            //提到两个站和两个线
            String sql1 = "select * from firstline where StopName=? or StopName=?";
            PreparedStatement pstmt1 = connection.prepareStatement(sql1);
            pstmt1.setString(1, line.getStartstopname());
            pstmt1.setString(2, line.getEndstopname());
            ResultSet rs1 = pstmt1.executeQuery();
            while (rs1.next())
            {
                if(line.getStartstopname().equals(rs1.getString("StopName")))
                {
                    line.setOriginline(rs1.getInt("Line"));
                }
                if(line.getEndstopname().equals(rs1.getString("StopName"))) {
                    line.setFinishline(rs1.getInt("Line"));
                }
            }
            jdbcUtil.close(pstmt1);
            jdbcUtil.close(rs1);
            //在一条直线上
            if( line.getOriginline()!=0 && line.getFinishline()==line.getOriginline() )
            {
                jdbcUtil.close(connection);
                return 1;
            }
            //站点不存在
            if(line.getOriginline()==0 || line.getFinishline()==0)
            {
                jdbcUtil.close(connection);
                return 4;
            }
            System.out.println("这不合理！所以线一为"+line.getOriginline()+",线二为"+line.getFinishline());
            String sql2="select * from changeline where ID1=? and ID2=? ";
            PreparedStatement pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setInt(1, line.getOriginline());
            pstmt2.setInt(2, line.getFinishline());
            ResultSet rs2 = pstmt2.executeQuery();
            while(rs2.next()) {
                line.setMiddlestop(rs2.getString("ChangeStopName"));
            }
            System.out.println(line.getMiddlestop()+"也许不是我想要的");
            jdbcUtil.close(pstmt2);
            jdbcUtil.close(rs2);
            if(line.getMiddlestop() == null) {
                jdbcUtil.close(connection);
                return 3;
            }
            else {
                jdbcUtil.close(connection);
                return 2;
            }
    }
}