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 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;
}
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> 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;
}
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;
}
}