三月二十日结对

合作对象:李佳岳

 

 

 

 

 

三月十九日和三月二十日,基本完成了北京市的地铁查询功能,可以查看路线,输入起始点和终点可以计算出最优路径,表结构未两个,一个用于存放站点另一个用于存放转成点。王建民老师评价,太素了,功能不太完善。

 

 

Line.java
package com.bean;

import java.util.ArrayList;

/*
 * 
 */
public class Line 
{
    private String number;
    private String startstopname;//起始站站名
    private String endstopname;//结束站站名
    private int startstopID;//    起始站站编号
    private int endstopID;//结束站站编号
    private String startline1=null;//起始点的第一条线
    private String startline2=null;//起始点第二条线
    private String endline1=null;//结束点的第一条线
    private String endline2=null;//结束点的第二条线
    private ArrayList<String> array = new ArrayList<String>();//存放过程站点
    private int originline;//起始站所在线路
    private int finishline;//结束点所在线路
    private String middlestop;//中转站站名
    private String temporarystartstopname;//临时起始站点名称
    private String temporaryendstopname;//临时终点站点名称
    private int temporaryline=0;//临时线路
    public Line() {
        
    }
    public int getTemporaryline() {
        return temporaryline;
    }
    public void setTemporaryline(int temporaryline) {
        this.temporaryline = temporaryline;
    }
    public String getTemporarystartstopname() {
        return temporarystartstopname;
    }
    public void setTemporarystartstopname(String temporarystartstopname) {
        this.temporarystartstopname = temporarystartstopname;
    }
    public String getTemporaryendstopname() {
        return temporaryendstopname;
    }
    public void setTemporaryendstopname(String temporaryendstopname) {
        this.temporaryendstopname = temporaryendstopname;
    }
    public String getMiddlestop() {
        return middlestop;
    }
    public void setMiddlestop(String middlestop) {
        this.middlestop = middlestop;
    }
    public int getOriginline() {
        return originline;
    }
    public void setOriginline(int originline) {
        this.originline = originline;
    }
    public int getFinishline() {
        return finishline;
    }
    public void setFinishline(int finishline) {
        this.finishline = finishline;
    }
    public ArrayList<String> getArray() {
        return array;
    }
    public void setArray(ArrayList<String> array) {
        this.array = array;
    }
    public String getStartline1() {
        return startline1;
    }
    public void setStartline1(String startline1) {
        this.startline1 = startline1;
    }
    public String getStartline2() {
        return startline2;
    }
    public void setStartline2(String startline2) {
        this.startline2 = startline2;
    }
    public String getEndline1() {
        return endline1;
    }
    public void setEndline1(String endline1) {
        this.endline1 = endline1;
    }
    public String getEndline2() {
        return endline2;
    }
    public void setEndline2(String endline2) {
        this.endline2 = endline2;
    }
    public String getStartstopname() {
        return startstopname;
    }
    public void setStartstopname(String startstopname) {
        this.startstopname = startstopname;
    }
    public String getEndstopname() {
        return endstopname;
    }
    public void setEndstopname(String endstopname) {
        this.endstopname = endstopname;
    }
    public int getStartstopID() {
        return startstopID;
    }
    public void setStartstopID(int startstopID) {
        this.startstopID = startstopID;
    }
    public int getEndstopID() {
        return endstopID;
    }
    public void setEndstopID(int endstopID) {
        this.endstopID = endstopID;
    }
    

}

package com.dao;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/*
 * 加载连接数据库
 */
public class JdbcUtils
{
    private static Properties pros=null;
    static 
    {//初始化,将dbconfig.properties加载到pros中
        try 
        {
            //加载配置文件
            InputStream in=JdbcUtils.class.getResourceAsStream("dbconfig.properties");
            pros=new Properties();
            pros.load(in);
        }
        catch(IOException e)
        {
            throw new RuntimeException(e);
        }
        try
        {
            //加载驱动类
            Class.forName(pros.getProperty("driver"));
        }catch(ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() throws SQLException
    {
        //得到Connection
        return DriverManager.getConnection(pros.getProperty("url"), pros.getProperty("user"), pros.getProperty("password"));
    }



}
Jdbcutils.java
package com.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;


/*
 * 测试JdbcUtils
 */
public class TextJdbcUtils {
    
    public void text() throws SQLException{
        Connection con=null;
        try {
            con = JdbcUtils.getConnection();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println(con);
    }

}
TextJdbcUtils
package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.concurrent.RunnableScheduledFuture;

import com.bean.Line;

public class UserDao {
    int demostartid=0,demoendid=0;
    ArrayList<String> array=new ArrayList<String>();
    /*
     *     起始点与终点在一条线上且非换乘站
     */
    public ArrayList<String> Connectonlyoneline(Line line) throws ClassNotFoundException
    {
        ArrayList<String> array1=new ArrayList<String>();
        int c=0;
        try {
            //连接数据库
            Connection con = JdbcUtils.getConnection();
            Statement statement = con.createStatement();
            //是否成功连接
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!"); 
            String sql="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName='"+line.getStartstopname()+"' AND f2.StopName='"+line.getEndstopname()+"'";
            //保证起始站(同时有可能是中转站)与终点站(同时有可能是中转站)在同一条线路上
            ResultSet rs=statement.executeQuery(sql);
            while(rs.next()) {
                line.setTemporaryline(rs.getInt("Line"));
            }
            //准备数据库查询:将起始站和终点站的中间站
            System.out.println(line.getTemporaryline());
            array.add("乘坐"+line.getTemporaryline()+"号线");
            c=select(line);    //得到起始站与终点站的顺序
            System.out.println(c);
            if(c==1) 
            {//起始站编号小于终点站,即起始站在前,终点站在后
                String sql1="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"')";
                /*
                 * 找到这样的一些站:它们的ID号大于起始站的ID号,
                 * 它们的ID号小于结束站的ID号,并且它们都是同一条线上的站点
                 */
                ResultSet rs1 = statement.executeQuery(sql1);
                while(rs1.next())
                {//遍历结果集
                    array.add(rs1.getString("StopName"));
                }
                rs1.close();
            }
            else if(c==2) 
            {//起始站编号大于终点站,即起始站在后,终点站在前
                String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"')";
                /*
                 * 同上
                 */
                ResultSet rs2 = statement.executeQuery(sql2);
                while(rs2.next()) 
                {
                    array1.add(rs2.getString("StopName"));
                }
                for(int i=0;i<array1.size();i++)
                {//测试是否有值
                    System.out.print(array1.get(i)+" ");
                }
                for(int j=array1.size()-1;j>=0;j--) 
                {//将站名倒序传入
                    array.add(array1.get(j));
                }
                rs2.close();
            }
            con.close();
            statement.close();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            line.setArray(array);//似乎无意义,在下面已经传回array
            for(int i=0;i<array.size();i++) {
                System.out.println(array.get(i)+" ");
            }
        }
        return array;
    }
    
    /*
     *     先不着急,起始站/终点站是换乘站的情况最后考虑
     *     首先判断起始站或结束站是否为中转站
     */
    public void JudgeChangeStop(Line line) throws ClassNotFoundException {
        try {
            Connection con=JdbcUtils.getConnection();
            Statement statement=con.createStatement();
            String sql="select * from changeline";
            ResultSet rs = statement.executeQuery(sql);
            while(rs.next()) {
                if(line.getStartstopname().equals(rs.getString("ChangeStopName")))
                {
                    line.setStartline1(rs.getString("ID1"));
                    line.setStartline2(rs.getString("ID2"));
                }
                if(line.getEndstopname().equals(rs.getString("ChangeStopName")))
                {
                    line.setEndline1(rs.getString("ID1"));
                    line.setEndline2(rs.getString("ID2"));
                }
            }
            con.close();
            statement.close();
            rs.close();
        }catch(SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if(line.getStartline1()==null)
            {
                
            }
        }
    }
    /*
     *     看站点顺序
     */
    public int select(Line line) throws ClassNotFoundException {
        int i=0;//返回值,1为顺序(数据库中),2为逆序(数据库中)
        try {
            Connection con = JdbcUtils.getConnection();
            Statement statement=con.createStatement();
            String sql="select * from firstline where StopName='"+line.getStartstopname()+"'and Line='"+line.getTemporaryline()+"' or StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"'";
            /*
             * 找到与起始站名称相同,结束站名称相同,且同时属于同一条线的两个站
             */
            ResultSet rs = statement.executeQuery(sql);
            while(rs.next()) {
                if(line.getStartstopname().equals(rs.getString("StopName")))
                {
                    line.setStartstopID(rs.getInt("StopID"));
                }
                if(line.getEndstopname().equals(rs.getString("StopName"))) {
                    line.setEndstopID(rs.getInt("StopID"));
                }
            }
            con.close();
            statement.close();
            rs.close();
        }catch(SQLException e) {
            throw new RuntimeException(e);
        }
        System.out.println(line.getStartstopID()+" "+line.getEndstopID());
        if(line.getStartstopID()<line.getEndstopID()) {
            i=1;
            return i;
        }
        else {
            i=2;
            return i;
        }
    }
    /*
     *     判断是否为同一条线
     */
    public int same(Line line) throws ClassNotFoundException {
        int checksameline=0;//同一条线
        try {
            Connection con = JdbcUtils.getConnection();
            Statement statement = con.createStatement();
            String sql="select * from firstline where StopName='"+line.getStartstopname()+"' or StopName='"+line.getEndstopname()+"'";
            /*
             * 同上,但用不着判断是否属于同一线路(本判断只存在于起始点与结束点是普通点)
             */
            ResultSet rs = statement.executeQuery(sql);
            while(rs.next()) {
                if(line.getStartstopname().equals(rs.getString("StopName")))
                {
                    line.setOriginline(rs.getInt("Line"));
                }
                if(line.getEndstopname().equals(rs.getString("StopName"))) {
                    line.setFinishline(rs.getInt("Line"));
                }
            }
        }catch(SQLException e) {
            throw new RuntimeException(e);
        }
        if(line.getFinishline()==line.getOriginline()) {//是同一条线
            checksameline=1;
            return checksameline;
        }
        else {//不是同一条线
            checksameline=2;
            return checksameline;
        }
    }
    /*
     *     两条不同的、有交点的线路
     */
    public ArrayList<String> Connecttwoline(Line line) throws SQLException, ClassNotFoundException{
        int c=0;
        
        Connection con = JdbcUtils.getConnection();
        Statement statement = con.createStatement();
        same(line);
        String sql="select * from changeline where ID1='"+line.getOriginline()+"' and ID2='"+line.getFinishline()+"'";
        //根据交点线路查询中转站
        ResultSet rs = statement.executeQuery(sql);
        while(rs.next()) {
            line.setMiddlestop(rs.getString("ChangeStopName"));
        }
        rs.close();
        line.setTemporarystartstopname(line.getStartstopname());
        //记录当前起始点与结束点
        line.setTemporaryendstopname(line.getEndstopname());
        //同上
        String sql1="select * from firstline where Line='"+line.getOriginline()+"' and StopName='"+line.getMiddlestop()+"'";
        //根据起始线路和中转站名称标出中转站位置
        ResultSet rs1 = statement.executeQuery(sql1);
        while(rs1.next()) {
            line.setEndstopname(rs1.getString("StopName"));
            //起始点不变,结束点设置为中转点
        }
        rs1.close();
        line.setStartstopname(line.getTemporarystartstopname());
        line.setTemporaryline(line.getOriginline());
        //将起始点和中转点所在线路一致的放入临时站线路
        /*
         * 
         * 
         * 
         * 
         */
        array.add("乘坐"+line.getOriginline()+"号线");
        c=select(line);    //得到起始站与终点站的顺序
        if(c==1) 
        {//起始站编号小于终点站,即起始站在前,终点站在后
            String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"')";
            /*
             * 找到这样的一些站:它们的ID号大于起始站的ID号,
             * 它们的ID号小于结束站的ID号,并且它们都是同一条线上的站点
             */
            ResultSet rs2 = statement.executeQuery(sql2);
            while(rs2.next())
            {//遍历结果集
                array.add(rs2.getString("StopName"));
            }
            rs2.close();
        }
        else if(c==2) 
        {//起始站编号大于终点站,即起始站在后,终点站在前
            ArrayList<String> array1=new ArrayList<String>();
            String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"')";
            /*
             * 同上
             */
            ResultSet rs2 = statement.executeQuery(sql2);
            while(rs2.next()) 
            {
                array1.add(rs2.getString("StopName"));
            }
            for(int i=0;i<array1.size();i++)
            {//测试是否有值
                System.out.print(array1.get(i)+" ");
            }
            for(int j=array1.size()-1;j>=0;j--) 
            {//将站名倒序传入
                array.add(array1.get(j));
            }

            rs2.close();
        }
        array.add("转乘"+line.getFinishline()+"号线");
        /*
         * 
         * 
         * 
         * 
         */
        line.setStartstopname(line.getMiddlestop());
        //将起始点设为中转点
        line.setEndstopname(line.getTemporaryendstopname());
        //将结束点回归
        line.setTemporaryline(line.getFinishline());
        //将中转点和结束点所在线路一致的放入临时站线路
        /*
         * 
         * 
         * 
         */
        c=select(line);    //得到起始站与终点站的顺序
        if(c==1) 
        {//起始站编号小于终点站,即起始站在前,终点站在后
            String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"')";
            /*
             * 找到这样的一些站:它们的ID号大于起始站的ID号,
             * 它们的ID号小于结束站的ID号,并且它们都是同一条线上的站点
             */
            ResultSet rs2 = statement.executeQuery(sql2);
            while(rs2.next())
            {//遍历结果集
                array.add(rs2.getString("StopName"));
            }
            rs2.close();
        }
        else if(c==2) 
        {//起始站编号大于终点站,即起始站在后,终点站在前
            ArrayList<String> array1=new ArrayList<String>();
            String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"')";
            /*
             * 同上
             */
            ResultSet rs2 = statement.executeQuery(sql2);
            while(rs2.next()) 
            {
                array1.add(rs2.getString("StopName"));
            }
            for(int i=0;i<array1.size();i++)
            {//测试是否有值
                System.out.print(array1.get(i)+" ");
            }
            for(int j=array1.size()-1;j>=0;j--) 
            {//将站名倒序传入
                array.add(array1.get(j));
            }

            rs2.close();
        }
        /*
         * 
         * 
         * 
         * 
         */
        //由于array是全局变量,所以继续直接接受数据即可
        return array;
        
    }
    /*
     * 判断有无交点
     */
    public int checklink(Line line) throws ClassNotFoundException {
        int c=0;
        Connection con;
        try {
            same(line);
            con = JdbcUtils.getConnection();
            Statement statement = con.createStatement();
            String sql="select * from changeline where ID1='"+line.getOriginline()+"' and ID2='"+line.getFinishline()+"'";
            ResultSet rs=statement.executeQuery(sql);
            while(rs.next()) {
                if(rs.getString("ChangeStopName").equals("")) {
                    
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
        return c;
    }
    /*
     * 解决两条线之间没有换乘站,只能通过第三条线换乘
     * 即A(普通站)->B(中转站)->C(中转站)->D(普通站)
     * 
     */
    public ArrayList<String> Connectthreeline(Line line) throws ClassNotFoundException {
        ArrayList<Integer> array1=new ArrayList<Integer>();
        int c=0;
        int min=0;
        try
        {
            same(line);
            Connection con = JdbcUtils.getConnection();
            Statement statement = con.createStatement();
            
            String sql="SELECT    * FROM    changeline c1 JOIN changeline c2 ON c1.ID2 = c2.ID1 WHERE trim(c1.ChangeStopName) != '' AND trim(c2.ChangeStopName) != '' AND c1.ID1 = '"+line.getOriginline()+"' AND c2.ID2 = '"+line.getFinishline()+"'";
            ResultSet rs = statement.executeQuery(sql);
            while(rs.next())
            {
                if(line.getOriginline()==rs.getInt("ID1"))
                {
                    array1.add(rs.getInt("ID2"));
                }
            }
            rs.close();
            line.setTemporarystartstopname(line.getStartstopname());
            line.setTemporaryendstopname(line.getEndstopname());
            /*
             * 
             * 第一次转乘
             * A(普通站)->B(中转站)
             * 
             */
            for(int j=0;j<array1.size();j++)
            {
                ArrayList<String> array3=new ArrayList<String>();
                
                System.out.println("也许这是我想要的起始站"+line.getTemporarystartstopname());
                String sql1="select * from changeline where ID1='"+line.getOriginline()+"' and ID2='"+array1.get(j)+"'";
                ResultSet rs1 = statement.executeQuery(sql1);
                while(rs1.next())
                {
                    line.setEndstopname(rs1.getString("ChangeStopName"));
                }
                rs1.close();
                array3.add("乘坐"+line.getOriginline()+"号线");
                array3.add(line.getTemporarystartstopname());
                String sql4="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName='"+line.getStartstopname()+"' AND f2.StopName='"+line.getEndstopname()+"'";
                //保证两个中转站在同一条线路上
                ResultSet rs4=statement.executeQuery(sql4);
                while(rs4.next()) {
                    line.setTemporaryline(rs4.getInt("Line"));
                }
                rs.close();
                c=select(line);
                if(c==1) 
                {//起始站编号小于终点站,即起始站在前,终点站在后
                    String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"')";
                    /*
                     * 找到这样的一些站:它们的ID号大于起始站的ID号,
                     * 它们的ID号小于结束站的ID号,并且它们都是同一条线上的站点
                     */
                    ResultSet rs2 = statement.executeQuery(sql2);
                    while(rs2.next())
                    {//遍历结果集
                        array3.add(rs2.getString("StopName"));
                    }
                    rs2.close();
                }
                else if(c==2) 
                {//起始站编号大于终点站,即起始站在后,终点站在前
                    ArrayList<String> array2=new ArrayList<String>();
                    String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"')";
                    /*
                     * 同上
                     */
                    ResultSet rs2 = statement.executeQuery(sql2);
                    while(rs2.next()) 
                    {
                        array2.add(rs2.getString("StopName"));
                    }
                    for(int i=0;i<array2.size();i++)
                    {//测试是否有值
                        System.out.print(array2.get(i)+" ");
                    }
                    for(int i=array2.size()-1;i>=0;i--) 
                    {//将站名倒序传入
                        array3.add(array2.get(i));
                    }
                    rs2.close();
                }
                /*
                 * 第二次换乘
                 * B(中转站)->C(中转站)
                 * 
                 */
                line.setStartstopname(line.getEndstopname());
                String sql3="select * from changeline where ID1='"+array1.get(j)+"' and ID2='"+line.getFinishline()+"'";
                ResultSet rs3 = statement.executeQuery(sql3);
                while(rs3.next())
                {
                    line.setEndstopname(rs3.getString("ChangeStopName"));
                }
                String sql5="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName='"+line.getStartstopname()+"' AND f2.StopName='"+line.getEndstopname()+"'";
                //保证两个中转站在同一条线路上
                ResultSet rs5=statement.executeQuery(sql5);
                while(rs5.next()) {
                    line.setTemporaryline(rs5.getInt("Line"));
                }
                rs5.close();
                array3.add("转乘"+line.getTemporaryline()+"号线");
                c=select(line);
                if(c==1) 
                {//起始站编号小于终点站,即起始站在前,终点站在后
                    String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"')";
                    /*
                     * 找到这样的一些站:它们的ID号大于起始站的ID号,
                     * 它们的ID号小于结束站的ID号,并且它们都是同一条线上的站点
                     */
                    ResultSet rs2 = statement.executeQuery(sql2);
                    while(rs2.next())
                    {//遍历结果集
                        array3.add(rs2.getString("StopName"));
                    }
                    rs2.close();
                }
                else if(c==2) 
                {//起始站编号大于终点站,即起始站在后,终点站在前
                    ArrayList<String> array2=new ArrayList<String>();
                    String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"')";
                    /*
                     * 同上
                     */
                    ResultSet rs2 = statement.executeQuery(sql2);
                    while(rs2.next()) 
                    {
                        array2.add(rs2.getString("StopName"));
                    }
                    for(int i=0;i<array2.size();i++)
                    {//测试是否有值
                        System.out.print(array2.get(i)+" ");
                    }
                    for(int i=array2.size()-1;i>=0;i--) 
                    {//将站名倒序传入
                        array3.add(array2.get(i));
                    }
                    rs2.close();
                }
                /*
                 * 
                 * 最后一次换乘
                 * 即C(中转站)->D(普通站)
                 */
                line.setStartstopname(line.getEndstopname());
                line.setEndstopname(line.getTemporaryendstopname());
                String sql6="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName='"+line.getStartstopname()+"' AND f2.StopName='"+line.getEndstopname()+"'";
                //保证两个中转站在同一条线路上
                ResultSet rs6=statement.executeQuery(sql6);
                while(rs6.next()) {
                    line.setTemporaryline(rs6.getInt("Line"));
                }
                rs.close();
                array3.add("转乘"+line.getTemporaryline()+"号线");
                c=select(line);
                if(c==1) 
                {//起始站编号小于终点站,即起始站在前,终点站在后
                    String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"')";
                    /*
                     * 找到这样的一些站:它们的ID号大于起始站的ID号,
                     * 它们的ID号小于结束站的ID号,并且它们都是同一条线上的站点
                     */
                    ResultSet rs2 = statement.executeQuery(sql2);
                    while(rs2.next())
                    {//遍历结果集
                        array3.add(rs2.getString("StopName"));
                    }
                    rs2.close();
                }
                else if(c==2) 
                {//起始站编号大于终点站,即起始站在后,终点站在前
                    ArrayList<String> array2=new ArrayList<String>();
                    String sql2="select * from firstline where StopID>=(select StopID from firstline where StopName='"+line.getEndstopname()+"' and Line='"+line.getTemporaryline()+"') and StopID<=(select StopID from firstline where StopName='"+line.getStartstopname()+"' and Line='"+line.getTemporaryline()+"')";
                    /*
                     * 同上
                     */
                    ResultSet rs2 = statement.executeQuery(sql2);
                    while(rs2.next()) 
                    {
                        array2.add(rs2.getString("StopName"));
                    }
                    for(int i=0;i<array2.size();i++)
                    {//测试是否有值
                        System.out.print(array2.get(i)+" ");
                    }
                    for(int i=array2.size()-1;i>=0;i--) 
                    {//将站名倒序传入
                        array3.add(array2.get(i));
                    }
                    rs2.close();
                }
                if(j==0) {
                    min=array3.size();
                }
                /*
                 * 第一次:5 min=5 array.size=5
                 * 第二次:3 array.size=3 min=5
                 * 第三次:7 array.size=7 min=3
                 */
                //输出array3测试
                System.out.println();
                System.out.println();
                for(int x=0;x<array3.size();x++) {
                    System.out.print(array3.get(x)+" ");
                    
                }
                System.out.println();
                System.out.println(min);
                if(min>=array3.size()) {
                    min=array3.size();
                    for(int x=0;x<array3.size();x++) {
                        System.out.print(array3.get(x)+" ");
                        
                    }
                    array=array3;
                }
            }
            statement.close();
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return array;
        
    }
    public int CheckAll(Line line) throws SQLException {
        int checkall=0;
        Connection con=JdbcUtils.getConnection();
        Statement statement = con.createStatement();
        String sql="SELECT * FROM firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName='"+line.getStartstopname()+"' AND f2.StopName='"+line.getEndstopname()+"'";
        ResultSet rs = statement.executeQuery(sql);
        while(rs.next()){
            line.setTemporaryline(rs.getInt("Line"));
        }
        rs.close();
        if(line.getTemporaryline()!=0) {
            checkall=1;
            return checkall;
        }
        else if(line.getTemporaryline()==0){
            String sql1="select * from firstline where StopName='"+line.getStartstopname()+"' or StopName='"+line.getEndstopname()+"'";
            /*
             * 同上,但用不着判断是否属于同一线路(本判断只存在于起始点与结束点是普通点)
             */
            ResultSet rs1 = statement.executeQuery(sql1);
            while(rs1.next()) {
                if(line.getStartstopname().equals(rs1.getString("StopName")))
                   {
                       line.setOriginline(rs1.getInt("Line"));
                   }
                   if(line.getEndstopname().equals(rs1.getString("StopName"))) {
                       line.setFinishline(rs1.getInt("Line"));
                   }
            }
            rs1.close();
            if(line.getOriginline()==0||line.getFinishline()==0) {
                checkall=4;
                return checkall;
            }
            System.out.println("这不合理!所以线一为"+line.getOriginline()+",线二为"+line.getFinishline());
            String sql2="select * from changeline where ID1='"+line.getOriginline()+"' and ID2='"+line.getFinishline()+"'";
            ResultSet rs2 = statement.executeQuery(sql2);
            while(rs2.next()) {
                line.setMiddlestop(rs2.getString("ChangeStopName"));
            }
            System.out.println(line.getMiddlestop()+"也许不是我想要的");
            if(line.getMiddlestop().equals("")) {
                checkall=3;
                return checkall;
            }
            else {
                checkall=2;
                return checkall;
            }
        }
        return checkall;
        
    }
}
UserDao
package com.service;

import com.dao.UserDao;

public class UserService {
    
    private UserDao userdao=new UserDao();
}
package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

import com.bean.Line;
import com.dao.UserDao;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class servlet
 */
@WebServlet("/servlet")
public class servlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8"); //1
        response.setContentType("text/html;charset=utf-8"); //2
        response.setCharacterEncoding("utf-8"); //3
        UserDao dao = new UserDao();
        Line line = new Line();
        ArrayList<String> array = new ArrayList<String>();
        String startstopname=request.getParameter("startstopname");
        String endstopname=request.getParameter("endstopname");
        System.out.println(endstopname);
        System.out.println(startstopname);
        line.setStartstopname(startstopname);
        line.setEndstopname(endstopname);
        
        try {
            int x=dao.CheckAll(line);
            System.out.println("实际上传过来的值是"+x);
            if(x==1) {
                array=dao.Connectonlyoneline(line);
            }
            else if(x==2) {
                array=dao.Connecttwoline(line);
            }
            else if(x==3){
                array=dao.Connectthreeline(line);
            }
            else {
                request.setAttribute("message", "请输入正确的站名!");
                request.getRequestDispatcher("/Query.jsp").forward(request, response);
                return;
            }
            //array=dao.Connectthreeline(line);
            request.setAttribute("stop", array);
        /*    if(array.equals(null)) {
                
            }*/
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            
            request.getRequestDispatcher("/result.jsp").forward(request, response);
        }
        
    }

}
servlet.java
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="com.dao.JdbcUtils"%>
    <%@ page import="java.sql.Connection"%> 
    <%@ page import="java.sql.PreparedStatement"%> 
<%@ page import="java.sql.SQLException"%> 
<%@ page import="java.sql.Statement"%> 
<%@ page import="java.sql.ResultSet"%> 
<%@page import="com.bean.Line" %>
<html>
<head>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0"> 
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>在此处插入标题</title>
</head>
<body>
<jsp:useBean id="util" class="com.dao.JdbcUtils" scope="page" />
<%
String nn = request.getParameter("number");; //通过 id 获取;
System.out.println(nn);
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = null;
try {
    String sql = "SELECT * FROM firstline where Line = '"+nn+"'";
    ps = connection.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();//要用statement类的executeQuery()方法来下达select指令以查询数据库,executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用。即语句:ResultSet rs=s.executeQuery(sql);
    while(rs.next()){
%>
             <tr>
            <td><%=rs.getString("StopName")%></td>-->
            </tr>    
<%
            }
        } catch (Exception e) {
            out.println(e);
        }

    %>
    <input type="button" value="返回" onclick="location.href='welcome.jsp'" /></br>
</body>
</html>
1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>石家庄地铁查询</title>
<style type="text/css">
 .a {
     margin-top: 1em;   
  margin-right: 23em;   
   margin-bottom: 1em;   
  margin-left: 23em;  

}
.bg{
width: 800px;
height: 748px;
background-image:url(subway.png);
background-repeat:repeat;
}
</style>
</head>
<body class=.bg>
<% String msg="";
String message=(String)request.getAttribute("message");
    if(message!=null){
        msg=message;
    }
%>
    <h1 style="color:red" align="center">北京市地铁</h1>
    
<form action="${pageContext.request.contextPath}/servlet" method="post" onsubmit="return check();">
    <div class=a>
        <%=msg %>
    </div>
    <div class=a>
    起始站:<input type="text" name="startstopname" id="startstopname"/>
    <br/>
    终点站:<input type="text" name="endstopname" id="endstopname"/>
    </div>
    <div class=a>
    <input type="submit" value="提交" onclick="check();"/>
    </div>
</form>
    <div class=a>

<body>
<img src="images/subway.png"width="400" height="356"/>

</body>
</div>
<form action="1.jsp" method="get">
<h1 style="text-align: center;"></h1>
<br>
<div class=a>

    <tr><td>
        线路查询:<input type="text" name="number"><br></tr>
    <tr>
            <input type="submit" value="查询"></td> </tr>
    </form>
</div>
<script type="text/javascript">
        function check() {
            var startstopname = document.getElementById("startstopname");
            var endstopname = document.getElementById("endstopname");
             if(startstopname.value=='') {
                alert('请填写起点站');
                return false;
            }else if(endstopname.value==''){
                alert('请填写终点站');
                return false;
                }
            else{
                return true;
            }
        }
    </script>
</body>
</html>
Query.jsp
<%@ page import="java.util.ArrayList" %>
<%@ page import="com.bean.Line"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%ArrayList array=(ArrayList)request.getAttribute("stop"); %>
<%if(array==null){
    request.setAttribute("message", "请输入数据!");
    request.getRequestDispatcher("/Query.jsp").forward(request, response);
    }else{%>
<%for(int i=0;i<array.size();i++){%>
    <th>
    <%=array.get(i) %>
    </th>
    <%} 
    }%>
<form action="Query.jsp">
    <button type="submit">回到首页</button>
</form>
</body>
</html>
result.jsp

 

 

posted @ 2023-03-20 21:48  布吉岛???  阅读(9)  评论(0编辑  收藏  举报