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

 

 

 

 

 

 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 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()) {
                //遍历结果集
                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 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()) {
                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;
    }

团队成员:王洪兵