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;
}
public List<User>Num4(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>();//做出一個終點站中轉站點集
List<User> Mid3s=new ArrayList<User>();//1在轉戰線上的位置
List<User> Mid4s=new ArrayList<User>();//2在轉戰上的位置
User user=null;
User Mid1=null;
User Mid2=null;
User Mid3=null;
User Mid4=null;
int Zhuan;//最近的轉戰是第幾條線
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, 0);
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);
}
for(User mid1:Mid1s)
{
sql="select * from station where Name=?";//同名 但是Id 不一樣
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setString(1, mid1.getName());
rs=ps.executeQuery();
while(rs.next())
{
Mid3=new User();
Mid3.setName(rs.getString("Name"));
Mid3.setId(rs.getInt("Id"));
Mid3.setLine(rs.getInt("Line")) ;
Mid3.setNum(rs.getInt("Num"));
Mid3.setExchange(rs.getInt("Exchange"));
if(Mid3.getId()!=mid1.getId())
{
Mid3s.add(Mid3);
}
}
}
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);
}
for(User mid2:Mid2s)
{
sql="select * from station where Name=?";//同名 但是Id 不一樣
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setString(1, mid2.getName());
rs=ps.executeQuery();
while(rs.next())
{
Mid4=new User();
Mid4.setName(rs.getString("Name"));
Mid4.setId(rs.getInt("Id"));
Mid4.setLine(rs.getInt("Line")) ;
Mid4.setNum(rs.getInt("Num"));
Mid4.setExchange(rs.getInt("Exchange"));
if(Mid4.getId()!=mid2.getId())
{
Mid4s.add(Mid4);
}
}
}
int min=100;//選出最近轉戰口
int Min1=Mid1.getId();//最近轉戰口ID 與始發站相同
int Min2=Mid2.getId();//與終點站相同
int Min3=Mid3.getId();
int Min4=Mid4.getId();
for(User mid1:Mid1s)
{
for(User mid3:Mid3s)
{
for(User mid2:Mid2s)
{
for(User mid4:Mid4s)
{
if((mid1.getExchange()==mid2.getExchange())&&(mid3.getLine()==mid4.getLine())&&(mid1.getExchange()==mid3.getLine()))
{
int n=Math.abs(mid1.getId()-start.getId())+Math.abs(mid2.getId()-end.getId())+Math.abs(mid3.getId()-mid4.getId());
if(min>n)
{
min=n;
Min1=mid1.getId();
Min2=mid2.getId();
Min3=mid3.getId();
Min4=mid4.getId();
}
}
}
}
}
}
System.out.println(Min1);
User mid1=Select_Id(Min1);
User mid2=Select_Id(Min2);
User mid3=Select_Id(Min3);
User mid4=Select_Id(Min4);
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(mid3.getId()>mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id desc";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid4.getId());
ps.setInt(2, mid3.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(mid3.getId()<mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id ";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid3.getId());
ps.setInt(2, mid4.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(mid3.getId()>mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id desc";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid4.getId());
ps.setInt(2, mid3.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(mid3.getId()<mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id ";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid3.getId());
ps.setInt(2, mid4.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>Num5(String a,String b)
{
int NN=0;
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>();//做出一個終點站中轉站點集
List<User> Mid3s=new ArrayList<User>();//1在轉戰線上的位置
List<User> Mid4s=new ArrayList<User>();//2在轉戰上的位置
User user=null;
User Mid1=null;
User Mid2=null;
User Mid3=null;
User Mid4=null;
int Zhuan;//最近的轉戰是第幾條線
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, 0);
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);
}
for(User mid1:Mid1s)
{
sql="select * from station where Name=?";//同名 但是Id 不一樣
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setString(1, mid1.getName());
rs=ps.executeQuery();
while(rs.next())
{
Mid3=new User();
Mid3.setName(rs.getString("Name"));
Mid3.setId(rs.getInt("Id"));
Mid3.setLine(rs.getInt("Line")) ;
Mid3.setNum(rs.getInt("Num"));
Mid3.setExchange(rs.getInt("Exchange"));
if(Mid3.getId()!=mid1.getId())
{
Mid3s.add(Mid3);
}
}
}
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);
}
for(User mid2:Mid2s)
{
sql="select * from station where Name=?";//同名 但是Id 不一樣
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setString(1, mid2.getName());
rs=ps.executeQuery();
while(rs.next())
{
Mid4=new User();
Mid4.setName(rs.getString("Name"));
Mid4.setId(rs.getInt("Id"));
Mid4.setLine(rs.getInt("Line")) ;
Mid4.setNum(rs.getInt("Num"));
Mid4.setExchange(rs.getInt("Exchange"));
if(Mid4.getId()!=mid2.getId())
{
Mid4s.add(Mid4);
}
}
}
int min=100;//選出最近轉戰口
int Min1=Mid1.getId();//最近轉戰口ID 與始發站相同
int Min2=Mid2.getId();//與終點站相同
int Min3=Mid3.getId();
int Min4=Mid4.getId();
for(User mid1:Mid1s)
{
for(User mid3:Mid3s)
{
for(User mid2:Mid2s)
{
for(User mid4:Mid4s)
{
if((mid1.getExchange()==mid2.getExchange())&&(mid3.getLine()==mid4.getLine())&&(mid1.getExchange()==mid3.getLine()))
{
int n=Math.abs(mid1.getId()-start.getId())+Math.abs(mid2.getId()-end.getId())+Math.abs(mid3.getId()-mid4.getId());
if(min>n)
{
min=n;
Min1=mid1.getId();
Min2=mid2.getId();
Min3=mid3.getId();
Min4=mid4.getId();
}
}
}
}
}
}
System.out.println(Min1);
User mid1=Select_Id(Min1);
User mid2=Select_Id(Min2);
User mid3=Select_Id(Min3);
User mid4=Select_Id(Min4);
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()) {
NN=NN+1;
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(mid3.getId()>mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id desc";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid4.getId());
ps.setInt(2, mid3.getId()-1);/////////////////////
rs=ps.executeQuery();
while(rs.next()) {
NN=NN+1;
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(mid3.getId()<mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id ";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid3.getId()+1);
ps.setInt(2, mid4.getId());
rs=ps.executeQuery();
while(rs.next()) {
NN=NN+1;
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()) {
NN=NN+1;
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()) {
NN=NN+1;
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()) {
NN=NN+1;
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(mid3.getId()>mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id desc";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid4.getId());
ps.setInt(2, mid3.getId()-1);
rs=ps.executeQuery();
while(rs.next()) {
NN=NN+1;
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(mid3.getId()<mid4.getId())//3->4
{
sql="select * from station where Id between ? and ? order by Id ";
conn=ut.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1,mid3.getId()+1);
ps.setInt(2, mid4.getId());
rs=ps.executeQuery();
while(rs.next()) {
NN=NN+1;
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()) {
NN=NN+1;
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()) {
NN=NN+1;
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();
}
}
System.out.println("aadfasdfsdaf"+NN+"adsfads"+Num1(a,b));
if(NN>Num1(a,b))
{
return Num3(a,b);
}
else
{
return users;
}
}
}
package com.demo;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class main {
public static void main(String[] args) {
LoginerDaolmpl loginerDaoImpl=new LoginerDaolmpl();
List<User> users = new ArrayList<User>();
System.out.println("查詢");
// users=loginerDaoImpl.load();
//
// for(User u:users) {
// System.out.println(u.getName());
// }
Scanner scan=new Scanner(System.in);
String a=scan.next();
String b=scan.next();
// users=loginerDaoImpl.Num2(a, b);
// users=loginerDaoImpl.Num3(a, b);
// users=loginerDaoImpl.Num4(a, b);
users=loginerDaoImpl.Num5(a, b);
for(User u:users) {
System.out.println(u.getId());
System.out.println(u.getName());
}
System.out.println(loginerDaoImpl.Num1(a, b));
}
}
package com.demo;
import java.sql.Connection;
public class User {
private int Id;
private int Line;
private String Name;
private int Num;
private int Exchange;
public int getId() {
return Id;
}
public void setId(int id) {
Id = id;
}
public int getLine() {
return Line;
}
public void setLine(int line) {
Line = line;
}
public String getName() {
return Name;
}
public void setName(String name) {
Name = name;
}
public int getNum() {
return Num;
}
public void setNum(int num) {
Num = num;
}
public int getExchange() {
return Exchange;
}
public void setExchange(int exchange) {
Exchange = exchange;
}
public User(int id,int line ,String name,int num,int exchange) {
this.Name = name;
this.Id=id;
this.Line=line;
this.Num=num;
this.Exchange=exchange;
}
public User() {
// TODO Auto-generated constructor stub
}
}
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class util {
public static Connection getMysqlConn()//獲取連線，返回Connection型別，必須設定為static這樣才能在其他類中使用
{
Connection conn=null;
try
{
Class.forName("com.mysql.jdbc.Driver");//載入驅動
System.out.println("Success loading Mysql Driver!");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sjzsubway?useUnicode=true&characterEncoding=utf-8","root","980713");//連線資料庫
}
catch(Exception e)
{
System.out.println("Error");
e.printStackTrace();
}
return conn;
}
public static void close(PreparedStatement ps,Connection conn)//關閉函式
{
if(ps!=null)//只有狀態和連線時，先關閉狀態
{
try
{
ps.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
if(conn!=null)
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
public static void close(ResultSet rs,PreparedStatement ps,Connection conn)
{
if(rs!=null)//有結果集，狀態和連線時，先關閉結果集，在關閉狀態，在關閉連線
{
try
{
rs.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
if(ps!=null)
{
try
{
ps.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
if(conn!=null)
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
package DJSTL;
import util.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Scanner;
import java.util.Set;
public class DataBuilder {
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);
static String station_name;
public static List<Station> line1 = new ArrayList<Station>();//1號線
public static List<Station> line2 = new ArrayList<Station>();//2號線
public static List<Station> line3 = new ArrayList<Station>();//3號線
public static List<Station> line4 = new ArrayList<Station>();//10號線
public static List<Station> line5 = new ArrayList<Station>();//s1號線
public static List<Station> line6 = new ArrayList<Station>();//s8號線
public static Set<List<Station>> lineSet = new HashSet<List<Station>>();//所有線集合
public static int totalStaion = 0;//總的站點數量
static {
String[] line1Arr=new String[29];
String[] line2Arr=new String[37];
String[] line3Arr=new String[34];
String[] line4Arr=new String[18];
String[] line5Arr=new String[21];
String[] line6Arr=new String[19];
//1號線
try {
conn=ut.getConn();
ps=null;
ResultSet rs=null;
sql="select * from station where Line=?";
ps=conn.prepareStatement(sql);
ps.setInt(1, 1);
rs=ps.executeQuery();
int i=-1;
while(rs.next())
{
i++;
station_name=rs.getString("Name");
line1Arr[i]=station_name;
// System.out.println(station_name);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
try {
conn=ut.getConn();
ps=null;
ResultSet rs=null;
sql="select * from station where Line=?";
ps=conn.prepareStatement(sql);
ps.setInt(1, 2);
rs=ps.executeQuery();
int i=-1;
while(rs.next())
{
i++;
station_name=rs.getString("Name");
line2Arr[i]=station_name;
// System.out.println(station_name);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
try {
conn=ut.getConn();
ps=null;
ResultSet rs=null;
sql="select * from station where Line=?";
ps=conn.prepareStatement(sql);
ps.setInt(1,3);
rs=ps.executeQuery();
int i=-1;
while(rs.next())
{
i++;
station_name=rs.getString("Name");
line3Arr[i]=station_name;
// System.out.println(station_name);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
try {
conn=ut.getConn();
ps=null;
ResultSet rs=null;
sql="select * from station where Line=?";
ps=conn.prepareStatement(sql);
ps.setInt(1, 4);
rs=ps.executeQuery();
int i=-1;
while(rs.next())
{
i++;
station_name=rs.getString("Name");
line4Arr[i]=station_name;
// System.out.println(station_name);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
try {
conn=ut.getConn();
ps=null;
ResultSet rs=null;
sql="select * from station where Line=?";
ps=conn.prepareStatement(sql);
ps.setInt(1, 5);
rs=ps.executeQuery();
int i=-1;
while(rs.next())
{
i++;
station_name=rs.getString("Name");
line5Arr[i]=station_name;
// System.out.println(station_name);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
try {
conn=ut.getConn();
ps=null;
ResultSet rs=null;
sql="select * from station where Line=?";
ps=conn.prepareStatement(sql);
ps.setInt(1, 6);
rs=ps.executeQuery();
int i=-1;
while(rs.next())
{
i++;
station_name=rs.getString("Name");
line6Arr[i]=station_name;
// System.out.println(station_name);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
for(String s : line1Arr){
line1.add(new Station(s));
}
for(int i =0;i<line1.size();i++){
if(i<line1.size()-1){
line1.get(i).next = line1.get(i+1);
line1.get(i+1).prev = line1.get(i);
}
}
/*******************************************************************************/
//2號線
for(String s : line2Arr){
line2.add(new Station(s));
}
for(int i =0;i<line2.size();i++){
if(i<line2.size()-1){
line2.get(i).next = line2.get(i+1);
line2.get(i+1).prev = line2.get(i);
}
}
/*******************************************************************************/
//3號線
for(String s : line3Arr){
line3.add(new Station(s));
}
for(int i =0;i<line3.size();i++){
if(i<line3.size()-1){
line3.get(i).next = line3.get(i+1);
line3.get(i+1).prev = line3.get(i);
}
}
/*******************************************************************************/
//4號線
for(String s : line4Arr){
line4.add(new Station(s));
}
for(int i =0;i<line4.size();i++){
if(i<line4.size()-1){
line4.get(i).next = line4.get(i+1);
line4.get(i+1).prev = line4.get(i);
}
}
/*******************************************************************************/
//5號線
for(String s : line5Arr){
line5.add(new Station(s));
}
for(int i =0;i<line5.size();i++){
if(i<line5.size()-1){
line5.get(i).next = line5.get(i+1);
line5.get(i+1).prev = line5.get(i);
}
}
/*******************************************************************************/
//6號線
for(String s : line6Arr){
line6.add(new Station(s));
}
for(int i =0;i<line6.size();i++){
if(i<line6.size()-1){
line6.get(i).next = line6.get(i+1);
line6.get(i+1).prev = line6.get(i);
}
}
lineSet.add(line1);
lineSet.add(line2);
lineSet.add(line3);
lineSet.add(line4);
lineSet.add(line5);
lineSet.add(line6);
totalStaion = line1.size() + line2.size() + line3.size() + line4.size() + line5.size() + line6.size();
System.out.println("總的站點數量："+totalStaion);
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="com.demo.LoginerDaolmpl"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<form action="Shu_result.jsp" method="post" id="form">
<h4> 始發站：<input type="text" name="start"title="課程不能為空"></input><br></h4>
<h4> 終點站：<input type="text" name="end"title="課程不能為空"></input><br></h4>
<input type="submit" value="完成"/>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="com.demo.LoginerDaolmpl"%>
<%@page import="com.demo.User" %>
<%@page import="java.util.List" %>
<%@page import="java.util.ArrayList" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<%
request.setCharacterEncoding("utf-8");
LoginerDaolmpl loginerDaoImpl=new LoginerDaolmpl();
List<User> users = new ArrayList<User>();
String a=request.getParameter("start");
String b=request.getParameter("end");
User user=new User();
users=loginerDaoImpl.Num5(a,b);
%>
<body>
<table>
<tr>
<td>Id</td>
<td>站名</td>
</tr>
<%for(User u:users) { %>
<tr>
<td><%=u.getId() %></td>
<td><%=u.getName() %></td>
</tr>
<%} %>
</table>
**********************<br>
<input type="button" value="返回功能介面" οnclick="window.location.href='success.jsp';"/><br>
**********************<br>
</body>
</html>
時間記錄日誌：
| 任務 | 程式設計 | 閱讀量 | |||
| 日期 | |||||
| 星期一 | 完成前端設計 | 3h | |||
| 二 | |||||
| 三 | 連線資料庫，將所有線路資料錄入，能進行線路站點的查詢 | 3h | |||
| 四 | |||||
| 五 | |||||
| 六 | 運用演算法求出最短路徑 | 3h | |||
| 七 | 我們之後還套用了百度地圖的框架 | 3h |