地铁站查询进度1

package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.Subway;
import util.Util;

public class SDao {
    /**
     * 查询线路信息
     */
    public List<Subway> getSubwayByNumber(int number) {
        String sql = "select * from subway where number = ‘" + number + "‘";
        List<Subway> list = new ArrayList<>();
        Connection conn = Util.getConnection();
        Statement state = null;
        ResultSet rs = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            Subway subway = null;
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String message = rs.getString("message");
                subway = new Subway(id, number, name, message);
                list.add(subway);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(rs, state, conn);
        }    
        return list;
    }
    /**
     * 通过name得到地铁信息
     */
    public Subway getSubwayByName(String name) {
        String sql = "select * from subway where name =‘" + name + "‘";
        Connection conn = Util.getConnection();
        Statement state = null;
        ResultSet rs = null;
        Subway subway = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                int id = Integer.parseInt(rs.getString("id"));
                int number = Integer.parseInt(rs.getString("number"));
                String message = rs.getString("message");
                subway = new Subway(id, number, name, message);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Util.close(rs, state, conn);
        }
        return subway;
    }
    /**
     * 查询路线
     */
    @SuppressWarnings("resource")
    public List<Subway> search(String name1,String name2) {
        List<Subway> list = new ArrayList<Subway>();
        Connection conn = Util.getConnection();
        Statement state1 = null;
        Statement state2 = null;
        Statement state3 = null;
        Statement state4 = null;
        Statement state5 = null;
        Statement state6 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        ResultSet rs4 = null;
        ResultSet rs5 = null;
        ResultSet rs6 = null;
        try {
            Subway subway1 = null;
            Subway subway2 = null;
            String sql1 = "select * from subway where name =‘" + name1 + "‘";
            String sql2 = "select * from subway where name =‘" + name2 + "‘";
            state1 = conn.createStatement();
            rs1 = state1.executeQuery(sql1);
            while (rs1.next()) {
                int id1 = rs1.getInt("id");
                int number1 = rs1.getInt("number");
                String message1 = rs1.getString("message");
                subway1 = new Subway(id1, number1, name1, message1);
            }
            state2 = conn.createStatement();
            rs2 = state2.executeQuery(sql2);
            while (rs2.next()) {
                int id2 = rs2.getInt("id");
                int number2 = rs2.getInt("number");
                String message2 = rs2.getString("message");
                subway2 = new Subway(id2, number2, name2, message2);
            }
            if(subway1.getNumber()==subway2.getNumber()){//在同一条线路上
                Subway subway3 = null;
                if(subway1.getId()<subway2.getId()){
                    String sql3 = "select * from subway where id between ‘" + subway1.getId() + "‘ and ‘" + subway2.getId() + "‘";
                    state3 = conn.createStatement();
                    rs3 = state3.executeQuery(sql3);
                    while (rs3.next()) {
                        int id3 = rs3.getInt("id");
                        int number3 = rs3.getInt("number");
                        String name3 = rs3.getString("name");
                        String message3 = rs3.getString("message");
                        subway3 = new Subway(id3, number3, name3, message3);
                        list.add(subway3);
                    }
                } else if(subway1.getId()>subway2.getId()){
                    String sql3 = "select * from subway where id between ‘" + subway2.getId() + "‘ and ‘" + subway1.getId() + "‘";
                    state3 = conn.createStatement();
                    rs3 = state3.executeQuery(sql3);
                    while (rs3.next()) {
                        int id3 = rs3.getInt("id");
                        int number3 = rs3.getInt("number");
                        String name3 = rs3.getString("name");
                        String message3 = rs3.getString("message");
                        subway3 = new Subway(id3, number3, name3, message3);
                        list.add(subway3);
                    }
                } 
            }
            else {//不在同一条线路上
                String sql4 = "select name from subway where number = ‘" + subway1.getNumber() + "‘ and message = ‘换乘" + subway2.getNumber() + "号线‘";
                List<String> station=new ArrayList<String>(); 
                state4 = conn.createStatement();
                rs4 = state4.executeQuery(sql4);
                while (rs4.next()) {
                    String name = rs4.getString("name");
                    station.add(name);
                }
                List<Subway> station1 = new ArrayList<Subway>();
                Subway subway4 = null;
                for(int i=0;i<station.size();i++){
                    String sql5 = "select * from subway where name =‘" + station.get(i) + "‘";
                    state5 = conn.createStatement();
                    rs5 = state5.executeQuery(sql5);
                    while (rs5.next()) {
                        int id4 = rs5.getInt("id");
                        int number4 = rs5.getInt("number");
                        String name4 = rs5.getString("name");
                        String message4 = rs5.getString("message");
                        subway4 = new Subway(id4, number4, name4, message4);
                        station1.add(subway4);
                    }
                }
                int[] sum1 = new int[4];
                int[] sum2 = new int[4];
                for(int i=0;i<station1.size();i++){
                    int m = Math.abs(subway1.getId()-station1.get(i).getId());
                    int n = Math.abs(subway2.getId()-station1.get(i).getId());
                    sum1[i]=m;
                    sum2[i]=n;
                }
                int flag1=getFlag(sum1);
                int flag2=getFlag(sum2);
                Subway subway5 = null;
                if(subway1.getId()<station1.get(flag1).getId()&&subway2.getId()<station1.get(flag2).getId()){
                    String sql6 = "select * from subway where id between ‘" + subway1.getId() + "‘ and ‘" + station1.get(flag1).getId() + "‘";
                    String sql7 = "select * from subway where id between ‘" + subway2.getId() + "‘ and ‘" + station1.get(flag2).getId() + "‘";
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql6);
                    while (rs6.next()) {
                        int id5 = rs6.getInt("id");
                        int number5 = rs6.getInt("number");
                        String name5 = rs6.getString("name");
                        String message5 = rs6.getString("message");
                        subway5 = new Subway(id5, number5, name5, message5);
                        list.add(subway5);
                    }
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql7);
                    while (rs6.next()) {
                        int id = rs6.getInt("id");
                        int number = rs6.getInt("number");
                        String name = rs6.getString("name");
                        String message = rs6.getString("message");
                        subway5 = new Subway(id, number, name, message);
                        list.add(subway5);
                    }
                } else if(subway1.getId()>station1.get(flag1).getId()&&subway2.getId()<station1.get(flag2).getId()){
                    String sql6 = "select * from subway where id between ‘" + station1.get(flag1).getId() + "‘ and ‘" + subway1.getId() + "‘";
                    String sql7 = "select * from subway where id between ‘" + subway2.getId() + "‘ and ‘" + station1.get(flag2).getId() + "‘";
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql6);
                    while (rs6.next()) {
                        int id5 = rs6.getInt("id");
                        int number5 = rs6.getInt("number");
                        String name5 = rs6.getString("name");
                        String message5 = rs6.getString("message");
                        subway5 = new Subway(id5, number5, name5, message5);
                        list.add(subway5);
                    }
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql7);
                    while (rs6.next()) {
                        int id = rs6.getInt("id");
                        int number = rs6.getInt("number");
                        String name = rs6.getString("name");
                        String message = rs6.getString("message");
                        subway5 = new Subway(id, number, name, message);
                        list.add(subway5);
                    }
                } else if(subway1.getId()<station1.get(flag1).getId()&&subway2.getId()>station1.get(flag2).getId()){
                    String sql6 = "select * from subway where id between ‘" + subway1.getId() + "‘ and ‘" + station1.get(flag1).getId() + "‘";
                    String sql7 = "select * from subway where id between ‘" + station1.get(flag2).getId() + "‘ and ‘" + subway2.getId() + "‘";
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql6);
                    while (rs6.next()) {
                        int id5 = rs6.getInt("id");
                        int number5 = rs6.getInt("number");
                        String name5 = rs6.getString("name");
                        String message5 = rs6.getString("message");
                        subway5 = new Subway(id5, number5, name5, message5);
                        list.add(subway5);
                    }
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql7);
                    while (rs6.next()) {
                        int id = rs6.getInt("id");
                        int number = rs6.getInt("number");
                        String name = rs6.getString("name");
                        String message = rs6.getString("message");
                        subway5 = new Subway(id, number, name, message);
                        list.add(subway5);
                    }
                } else if(subway1.getId()>station1.get(flag1).getId()&&subway2.getId()>station1.get(flag2).getId()){
                    String sql6 = "select * from subway where id between ‘" + station1.get(flag1).getId() + "‘ and ‘" + subway1.getId() + "‘";
                    String sql7 = "select * from subway where id between ‘" + station1.get(flag2).getId() + "‘ and ‘" + subway2.getId() + "‘";
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql6);
                    while (rs6.next()) {
                        int id5 = rs6.getInt("id");
                        int number5 = rs6.getInt("number");
                        String name5 = rs6.getString("name");
                        String message5 = rs6.getString("message");
                        subway5 = new Subway(id5, number5, name5, message5);
                        list.add(subway5);
                    }
                    state6 = conn.createStatement();
                    rs6 = state6.executeQuery(sql7);
                    while (rs6.next()) {
                        int id = rs6.getInt("id");
                        int number = rs6.getInt("number");
                        String name = rs6.getString("name");
                        String message = rs6.getString("message");
                        subway5 = new Subway(id, number, name, message);
                        list.add(subway5);
                    }    
                }
                for (int i = 0; i < 1; i++) {
                    list.remove(list.size() - 1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Util.close(rs1, state1, conn);
            Util.close(rs2, state2, conn);
            Util.close(rs3, state3, conn);
            Util.close(rs4, state4, conn);
            Util.close(rs5, state5, conn);
            Util.close(rs6, state6, conn);
        }
        return list;
    }
    /**
     * 查询站数
     */
    public int num(String name1,String name2) {
        Connection conn = Util.getConnection();
        Statement state1 = null;
        Statement state2 = null;
        Statement state3 = null;
        Statement state4 = null;
        Statement state5 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        ResultSet rs4 = null;
        ResultSet rs5 = null;
        int num = 0;
        try {
            Subway subway1 = null;
            Subway subway2 = null;
            String sql1 = "select * from subway where name =‘" + name1 + "‘";
            String sql2 = "select * from subway where name =‘" + name2 + "‘";
            state1 = conn.createStatement();
            rs1 = state1.executeQuery(sql1);
            while (rs1.next()) {
                int id1 = rs1.getInt("id");
                int number1 = rs1.getInt("number");
                String message1 = rs1.getString("message");
                subway1 = new Subway(id1, number1, name1, message1);
            }
            state2 = conn.createStatement();
            rs2 = state2.executeQuery(sql2);
            while (rs2.next()) {
                int id2 = rs2.getInt("id");
                int number2 = rs2.getInt("number");
                String message2 = rs2.getString("message");
                subway2 = new Subway(id2, number2, name2, message2);
            }
            if(subway1.getNumber()==subway2.getNumber()){//在同一条线路上
                num = Math.abs(subway1.getId()-subway2.getId());
            }
            else {//不在同一条线路上
                String sql4 = "select name from subway where number = ‘" + subway1.getNumber() + "‘ and message = ‘换乘" + subway2.getNumber() + "号线‘";
                List<String> station=new ArrayList<String>(); 
                state4 = conn.createStatement();
                rs4 = state4.executeQuery(sql4);
                while (rs4.next()) {
                    String name = rs4.getString("name");
                    station.add(name);
                }
                List<Subway> station1 = new ArrayList<Subway>();
                Subway subway4 = null;
                for(int i=0;i<station.size();i++){
                    String sql5 = "select * from subway where name =‘" + station.get(i) + "‘";
                    state5 = conn.createStatement();
                    rs5 = state5.executeQuery(sql5);
                    while (rs5.next()) {
                        int id4 = rs5.getInt("id");
                        int number4 = rs5.getInt("number");
                        String name4 = rs5.getString("name");
                        String message4 = rs5.getString("message");
                        subway4 = new Subway(id4, number4, name4, message4);
                        station1.add(subway4);
                    }
                }
                int[] sum1 = new int[4];
                int[] sum2 = new int[4];
                for(int i=0;i<station1.size();i++){
                    int m = Math.abs(subway1.getId()-station1.get(i).getId());
                    int n = Math.abs(subway2.getId()-station1.get(i).getId());
                    sum1[i]=m;
                    sum2[i]=n;
                }
                num=getMin(sum1) + getMin(sum2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Util.close(rs1, state1, conn);
            Util.close(rs2, state2, conn);
            Util.close(rs3, state3, conn);
            Util.close(rs4, state4, conn);
            Util.close(rs5, state5, conn);
        }
        return num;
    }
    public static int getMin(int[] arr)    {//获得最小值
        int min = arr[0];    
        for (int i=1; i<arr.length; i++){            
            if (arr[i]<min){                
                min = arr[i];
            }        
        }        
        return min;    
    }
    public static int getFlag(int[] arr) {//获得最小值下标
        int minflag=0;
        int min = arr[0];
        for (int i=1; i<arr.length; i++){            
            if (arr[i]<min){                
                min = arr[i];
                minflag=i;
            }        
        }        
        return minflag;    
    }
}