地铁1

1.开发一套石家庄地铁线路查询系统。

2.功能设计

（1）数据库设计：将石家庄地铁线路图的各个线路，各个站点，换乘信息等用数据库的形式保存起来，应该保存的信息有 {线路号，线路的各个站名，车站的换乘信息}。

（2）站点查询：用户可以输入任一一条线路或输入出发地和目的地信息，可以查询到相关内容。

例如输入出发地：石家庄铁道大学   目的地 博物院

返回经历的站名的个数，和路径，如果有换乘，请列出换乘的线路

package com.demo;
import util.util;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import javax.swing.plaf.synth.SynthSpinnerUI;

public class LoginerDaolmpl {
 static Connection conn;
    static PreparedStatement ps = null;
    static ResultSet rs;
    static String sql = "select * from station";
    static util ut= new util();
 static Scanner in = new Scanner(System.in);
 
 public User loadUser(String a) {
  conn=ut.getConn();
  ps=null;
  ResultSet rs=null;
  User user=null;
  sql="select * from station where Name=?";
  try {
   ps=conn.prepareStatement(sql);
   ps.setString(1, a);
   rs=ps.executeQuery();
   if(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
   }
  }catch(SQLException e) {
   e.printStackTrace();
  }finally {
   try {
     if(ps!=null)ps.close();
     if(conn!=null)conn.close();
    }catch(Exception e2) {
     e2.printStackTrace();
    }
  }
  return user;
 }
 public User Select_Id(int a) {
  conn=ut.getConn();
  ps=null;
  ResultSet rs=null;
  User user=null;
  sql="select * from station where Id=?";
  try {
   ps=conn.prepareStatement(sql);
   ps.setInt(1, a);
   rs=ps.executeQuery();
   if(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
   }
  }catch(SQLException e) {
   e.printStackTrace();
  }finally {
   try {
     if(ps!=null)ps.close();
     if(conn!=null)conn.close();
    }catch(Exception e2) {
     e2.printStackTrace();
    }
  }
  return user;
 }
 
 public List<User> load(){
  conn=ut.getConn();
  ps=null;
  ResultSet rs=null;
  String id;
  sql="select * from station order by Id ";
//  sql="select * from station where Id between ? and ? order by Id";
  List<User> users=new ArrayList<User>();
  User user=null;
  try {
   ps=conn.prepareStatement(sql);
   rs=ps.executeQuery();
   while(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
             users.add(user);
   }
  }catch(SQLException e) {
   e.printStackTrace();
  }finally {
   try {
     if(ps!=null)ps.close();
     if(conn!=null)conn.close();
    }catch(Exception e2) {
     e2.printStackTrace();
    }
  }
  return users;
 }
 //只能返回站数，能判断一条线上，有两个转战口，但不能判断 三条最近
 public int Num1(String a,String b)
 {
  User start=new User();
  start=loadUser(a);
  User end=new User();
  end=loadUser(b);
  User Mid=new User();
  int num=0;
  if(start.getLine()==end.getLine())
  {
  num=Math.abs(start.getNum()-end.getNum());
  
  }
  if(start.getLine()==1&&end.getLine()==2)
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-4)+Math.abs(end.getNum()-6);
   M=Math.abs(start.getNum()-20)+Math.abs(end.getNum()-20);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==1&&end.getLine()==3)
  {
   num=Math.abs(start.getNum()-23)+Math.abs(end.getNum()-13);
  }
  if(start.getLine()==1&&end.getLine()==4)
  {
   num=Math.abs(start.getNum()-17)+Math.abs(end.getNum()-13);
  }
  if(start.getLine()==1&&end.getLine()==5)
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-10)+Math.abs(end.getNum()-19);
   M=Math.abs(start.getNum()-25)+Math.abs(end.getNum()-7);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==1&&end.getLine()==6)
  {
   num=Math.abs(start.getNum()-13)+Math.abs(end.getNum()-14);
  }
  if(start.getLine()==2&&end.getLine()==1)
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-6)+Math.abs(end.getNum()-4);
   M=Math.abs(start.getNum()-20)+Math.abs(end.getNum()-20);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==2&&end.getLine()==3)
  {
   num=Math.abs(start.getNum()-25)+Math.abs(end.getNum()-17);
  }
  if(start.getLine()==2&&end.getLine()==4)
  {
   num=Math.abs(start.getNum()-27)+Math.abs(end.getNum()-5);
  }
  if(start.getLine()==2&&end.getLine()==5)
  {
   num=Math.abs(start.getNum()-18)+Math.abs(end.getNum()-13);
  }
  if(start.getLine()==2&&end.getLine()==6)
  {
   num=Math.abs(start.getNum()-22)+Math.abs(end.getNum()-8);
  }
  if(start.getLine()==3&&end.getLine()==1)
  {
   num=Math.abs(start.getNum()-13)+Math.abs(end.getNum()-23);
  }
  if(start.getLine()==3&&end.getLine()==2)
  {
   num=Math.abs(start.getNum()-17)+Math.abs(end.getNum()-25);
  }
  if(start.getLine()==3&&end.getLine()==4)
  {
   num=Math.abs(start.getNum()-22)+Math.abs(end.getNum()-9);
  }
  if(start.getLine()==3&&end.getLine()==5)
  {
   num=Math.abs(start.getNum()-11)+Math.abs(end.getNum()-11);
  }
  if(start.getLine()==3&&end.getLine()==6)
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-26)+Math.abs(end.getNum()-16);
   M=Math.abs(start.getNum()-15)+Math.abs(end.getNum()-5);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==4&&end.getLine()==1)
  {
   num=Math.abs(start.getNum()-13)+Math.abs(end.getNum()-17);
  }
  if(start.getLine()==4&&end.getLine()==2)
  {
   num=Math.abs(start.getNum()-5)+Math.abs(end.getNum()-27);
  }
  if(start.getLine()==4&&end.getLine()==3)
  {
   num=Math.abs(start.getNum()-9)+Math.abs(end.getNum()-22);
  }
  if(start.getLine()==4&&end.getLine()==5)
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-15)+Math.abs(end.getNum()-15);
   M=Math.abs(start.getNum()-3)+Math.abs(end.getNum()-2);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==4&&end.getLine()==6)
  {
   num=Math.abs(start.getNum()-11)+Math.abs(end.getNum()-11);
  }
  if(start.getLine()==5&&end.getLine()==1)
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-19)+Math.abs(end.getNum()-10);
   M=Math.abs(start.getNum()-25)+Math.abs(end.getNum()-7);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==5&&end.getLine()==2)
  {
   num=Math.abs(start.getNum()-13)+Math.abs(end.getNum()-18);
  }
  if(start.getLine()==5&&end.getLine()==3)
  {
   num=Math.abs(start.getNum()-11)+Math.abs(end.getNum()-11);
  }
  if(start.getLine()==5&&end.getLine()==4  )
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-19)+Math.abs(end.getNum()-10);
   M=Math.abs(start.getNum()-25)+Math.abs(end.getNum()-7);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==5&&end.getLine()==6)
  {
   num=Math.abs(start.getNum()-4)+Math.abs(end.getNum()-3);
  }
  if(start.getLine()==6&&end.getLine()==1)
  {
   num=Math.abs(start.getNum()-14)+Math.abs(end.getNum()-13);
  }
  if(start.getLine()==6&&end.getLine()==2)
  {
   num=Math.abs(start.getNum()-8)+Math.abs(end.getNum()-22);
  }
  if(start.getLine()==6&&end.getLine()==3  )
  {
   int N=0,M=0;
   N=Math.abs(start.getNum()-16)+Math.abs(end.getNum()-26);
   M=Math.abs(start.getNum()-5)+Math.abs(end.getNum()-15);
   if(N>M)num=M;
   else num=N;
  }
  if(start.getLine()==6&&end.getLine()==4)
  {
   num=Math.abs(start.getNum()-11)+Math.abs(end.getNum()-11);
  }
  if(start.getLine()==6&&end.getLine()==5)
  {
   num=Math.abs(start.getNum()-3)+Math.abs(end.getNum()-4);
  }
  System.out.println(num);
  return num;
 }

 //只能返回站名，不能判断两个转战口，同时不能判断三条最近
 
 public List<User> Num2(String a,String b)
 {
 User start=new User();
 User end=new User();
 start=loadUser(a);
 end=loadUser(b);
 conn=ut.getConn();
 ps=null;
 ResultSet rs=null;
 String id;
 sql="select * from station where Id between ? and ? order by Id";
 List<User> users=new ArrayList<User>();
 User user=null;
 
 if(start.getLine()==end.getLine())//一条线
 {
  if(start.getId()<=end.getId())
  {
   try {
  ps=conn.prepareStatement(sql);
  ps.setInt(1, start.getId());
  ps.setInt(2, end.getId());
  
  rs=ps.executeQuery();
  while(rs.next()) {
   user=new User();
   user.setName(rs.getString("Name"));
   user.setId(rs.getInt("Id"));
            user.setLine(rs.getInt("Line")) ;
            user.setNum(rs.getInt("Num"));
            user.setExchange(rs.getInt("Exchange"));
            users.add(user);
  }
 }catch(SQLException e) {
  e.printStackTrace();
 }
 }
  else
  {
   
   try {
    sql="select * from station where Id between ? and ? order by Id desc";
    ps=conn.prepareStatement(sql);
    ps.setInt(2, start.getId());
    ps.setInt(1, end.getId());
    rs=ps.executeQuery();
    while(rs.next()) {
     user=new User();
     user.setName(rs.getString("Name"));
     user.setId(rs.getInt("Id"));
              user.setLine(rs.getInt("Line")) ;
              user.setNum(rs.getInt("Num"));
              user.setExchange(rs.getInt("Exchange"));
              users.add(user);
    }
   }catch(SQLException e) {
    e.printStackTrace();
   }
  }
  
  
 }
 if(start.getLine()!=end.getLine())//不在一条线
 {
  System.out.println(start.getLine()+end.getLine());
  int mid=0;
  try {
   
   sql="select * from station where Line=? and Exchange=?";
   ps=conn.prepareStatement(sql);
   ps.setInt(1, start.getLine());
   ps.setInt(2, end.getLine());
   rs=ps.executeQuery();
   User Mid=new User();//找到中间值（在始发站那条线路上）
   if(rs.next())
   {
   Mid.setName(rs.getString("Name"));
   Mid.setId(rs.getInt("Id"));
            Mid.setLine(rs.getInt("Line")) ;
            Mid.setNum(rs.getInt("Num"));
            Mid.setExchange(rs.getInt("Exchange"));
   }
            sql="select * from station where Line=? and Exchange=?";  //找到在终点站那条线路上
   ps=conn.prepareStatement(sql);
   ps.setInt(2, start.getLine());
   ps.setInt(1, end.getLine());
   rs=ps.executeQuery();
   User Mid2=new User();
   if(rs.next())
   {
   Mid2.setName(rs.getString("Name"));
   Mid2.setId(rs.getInt("Id"));
            Mid2.setLine(rs.getInt("Line")) ;
            Mid2.setNum(rs.getInt("Num"));
            Mid2.setExchange(rs.getInt("Exchange"));
   }
            if(Mid.getId()>start.getId())//始发站->中转站
            { 
             sql="select * from station where Id between ? and ? order by Id";
    ps=conn.prepareStatement(sql);
    ps.setInt(1, start.getId());
    ps.setInt(2, Mid.getId());
    rs=ps.executeQuery();
    while(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
             users.add(user);      
   }
     if(Mid2.getId()>end.getId())//始发站->中转站 终点站->中转站
              {
      System.out.println("");
      sql="select * from station where Id between ? and ? order by Id desc";
      ps=conn.prepareStatement(sql);
      ps.setInt(1, end.getId());
      ps.setInt(2, Mid2.getId()-1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
              }
     else//始发站->中转站->终点站
     {
      sql="select * from station where Id between ? and ? order by Id ";
      ps=conn.prepareStatement(sql);
      ps.setInt(2, end.getId());
      ps.setInt(1, Mid2.getId()+1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
     }
    
            }
            else//中转站<-始发站
            { 
             
             sql="select * from station where Id between ? and ? order by Id desc";
    ps=conn.prepareStatement(sql);
    ps.setInt(2, start.getId());
    ps.setInt(1, Mid.getId());
    rs=ps.executeQuery();
    while(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
             users.add(user);      
   }
     if(Mid2.getId()>end.getId())//中转站<-始发站 终点站->中转站 
              {
      sql="select * from station where Id between ? and ? order by Id desc";
      ps=conn.prepareStatement(sql);
      ps.setInt(1, end.getId());
      ps.setInt(2, Mid2.getId()-1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
              }
     else//中转站<-始发站 
     {
      sql="select * from station where Id between ? and ? order by Id ";
      ps=conn.prepareStatement(sql);
      ps.setInt(2, end.getId());
      ps.setInt(1, Mid2.getId()+1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
     }
    
            }
           
           
           
           
           
           
           
           
           
           
  }catch(SQLException e) {
   e.printStackTrace();
  }
  
  
  
  
  
  
 }
 return users;
}

 //能返回站名，同时实现两个转战口，但是不能三条最近
 
 public List<User> Num3(String a,String b)
 {
 User start=new User();
 User end=new User();
 start=loadUser(a);
 end=loadUser(b);
 conn=ut.getConn();
 ps=null;
 ResultSet rs=null;
 String id;
 sql="select * from station where Id between ? and ? order by Id";
 List<User> users=new ArrayList<User>();
 List<User> Mid1s=new ArrayList<User>();//做出一个始发站中转站点集
 List<User> Mid2s=new ArrayList<User>();//做出一个终点站中转站点集
 User user=null;
 User Mid1=null;
 User Mid2=null;
 if(start.getLine()==end.getLine())//一条线
 {
  if(start.getId()<=end.getId())
  {
   try {
             conn=ut.getConn();
  ps=conn.prepareStatement(sql);
  ps.setInt(1, start.getId());
  ps.setInt(2, end.getId());
  
  rs=ps.executeQuery();
  while(rs.next()) {
   user=new User();
   user.setName(rs.getString("Name"));
   user.setId(rs.getInt("Id"));
            user.setLine(rs.getInt("Line")) ;
            user.setNum(rs.getInt("Num"));
            user.setExchange(rs.getInt("Exchange"));
            users.add(user);
  }
 }catch(SQLException e) {
  e.printStackTrace();
 }
 }
  else
  {
   
   try {
    sql="select * from station where Id between ? and ? order by Id desc";
             conn=ut.getConn();
    ps=conn.prepareStatement(sql);
    ps.setInt(2, start.getId());
    ps.setInt(1, end.getId());
    rs=ps.executeQuery();
    while(rs.next()) {
     user=new User();
     user.setName(rs.getString("Name"));
     user.setId(rs.getInt("Id"));
              user.setLine(rs.getInt("Line")) ;
              user.setNum(rs.getInt("Num"));
              user.setExchange(rs.getInt("Exchange"));
              users.add(user);
    }
   }catch(SQLException e) {
    e.printStackTrace();
   }
  }
  
  
 }
 if(start.getLine()!=end.getLine())//不在一条线
 {
  int mid=0;
  try {
   
   sql="select * from station where Line=? and Exchange=?";//始发站一条线
         conn=ut.getConn();
   ps=conn.prepareStatement(sql);
   ps.setInt(1, start.getLine());
   ps.setInt(2, end.getLine());
   rs=ps.executeQuery();
   while(rs.next())
   {
   Mid1=new User();
   Mid1.setName(rs.getString("Name"));
   Mid1.setId(rs.getInt("Id"));
            Mid1.setLine(rs.getInt("Line")) ;
            Mid1.setNum(rs.getInt("Num"));
            Mid1.setExchange(rs.getInt("Exchange"));
            Mid1s.add(Mid1);
   }
            sql="select * from station where Line=? and Exchange=?";  //找到在终点站那条线路上
         conn=ut.getConn();
   ps=conn.prepareStatement(sql);
   ps.setInt(2, start.getLine());
   ps.setInt(1, end.getLine());
   rs=ps.executeQuery();
   while(rs.next())
   {
    Mid2=new User();
   Mid2.setName(rs.getString("Name"));
   Mid2.setId(rs.getInt("Id"));
            Mid2.setLine(rs.getInt("Line")) ;
            Mid2.setNum(rs.getInt("Num"));
            Mid2.setExchange(rs.getInt("Exchange"));
            Mid2s.add(Mid2);
   }
   int min=100;//选出最近转战口
   int Min1=Mid1.getId();//最近转战口ID 与始发站相同
   int Min2=Mid2.getId();//与终点站相同
   for(User mid1:Mid1s)
   {
    for(User mid2:Mid2s)
    {
     if(mid1.getName().equals(mid2.getName()))
     {
      int n=Math.abs(mid1.getId()-start.getId())+Math.abs(mid2.getId()-end.getId());
      if(min>n)
      {
       min=n;
       Min1=mid1.getId();
       Min2=mid2.getId();
      }
     }
    }
    
   }
   System.out.println(Min1);
   User mid1=Select_Id(Min1);
   User mid2=Select_Id(Min2);
   System.out.println(mid2.getName()+"asdfasdfasdgasdgasdf"+mid1.getName());////////////
   if(mid1.getId()>start.getId())//始发站->中转站
            { 
             sql="select * from station where Id between ? and ? order by Id";
             conn=ut.getConn();
    ps=conn.prepareStatement(sql);
    ps.setInt(1, start.getId());
    ps.setInt(2, mid1.getId());
    rs=ps.executeQuery();
    while(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
             users.add(user);      
   }
     if(mid2.getId()>end.getId())//始发站->中转站 终点站->中转站
              {
      System.out.println("");
      sql="select * from station where Id between ? and ? order by Id desc";
               conn=ut.getConn();
      ps=conn.prepareStatement(sql);
      ps.setInt(1, end.getId());
      ps.setInt(2, mid2.getId()-1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
              }
     else//始发站->中转站->终点站
     {
      sql="select * from station where Id between ? and ? order by Id ";
               conn=ut.getConn();
      ps=conn.prepareStatement(sql);
      ps.setInt(2, end.getId());
      ps.setInt(1, mid2.getId()+1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
     }
    
            }
            else//中转站<-始发站
            { 
             
             sql="select * from station where Id between ? and ? order by Id desc";
             conn=ut.getConn();
    ps=conn.prepareStatement(sql);
    ps.setInt(2, start.getId());
    ps.setInt(1, mid1.getId());
    rs=ps.executeQuery();
    while(rs.next()) {
    user=new User();
    user.setName(rs.getString("Name"));
    user.setId(rs.getInt("Id"));
             user.setLine(rs.getInt("Line")) ;
             user.setNum(rs.getInt("Num"));
             user.setExchange(rs.getInt("Exchange"));
             users.add(user);      
   }
     if(mid2.getId()>end.getId())//中转站<-始发站 终点站->中转站 
              {
      sql="select * from station where Id between ? and ? order by Id desc";
               conn=ut.getConn();
      ps=conn.prepareStatement(sql);
      ps.setInt(1, end.getId());
      ps.setInt(2, mid2.getId()-1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
              }
     else//中转站<-始发站 
     {
      sql="select * from station where Id between ? and ? order by Id ";
               conn=ut.getConn();
      ps=conn.prepareStatement(sql);
      ps.setInt(2, end.getId());
      ps.setInt(1, mid2.getId()+1);
      rs=ps.executeQuery();
      while(rs.next()) {
      user=new User();
      user.setName(rs.getString("Name"));
      user.setId(rs.getInt("Id"));
               user.setLine(rs.getInt("Line")) ;
               user.setNum(rs.getInt("Num"));
               user.setExchange(rs.getInt("Exchange"));
               users.add(user);      
     }
     }
    
            }
           
  
           
           
           
           
           
           
  }catch(SQLException e) {
   e.printStackTrace();
  }
  
  
  
  
  
  
 }
 return users;
}

 
 
 

 

}