地铁站查询进度1
这次测试我与赵欣卉一组。由于时间的限制还有技术的原因，此次测试到目前为止我只完成不换乘和一次换乘的部分，而且代码还有些bug，就是起始站或终点站不能为换乘站，并且代码十分冗杂。代码如下：
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; } }
