用SQL进行地铁线路换乘查询

你的名字 2022-06-06 06:14 274阅读 0赞

这代码是当初做课程作业时写出来的，过了几个月了现在看了看发现还挺不错的，说不定现在还写不出来了呢·~·。语法较为基础，都是简单的关键字，但是逻辑嵌套比较复杂，也用了大量的in，所以效率上可能不那么给列，不知道用来查公交速度如何~~~  
SQL我写在了Java里，方便传参和二次换乘衔接，代码如下

package xiaolengzi;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    
    
    public class Path {
        long startTime = System.currentTimeMillis();
        Connection con;// 创建一个数据库连接
        Connection con2;// 创建一个数据库连接，只创建一个会自动断开
        Statement sta0;// 创建预编译语句对象，最好是换成PreparedStatement吧
        Statement sta1;
        Statement sta2;
        String sql0;
        String sql1;
        String sql2;
        ResultSet rs0;
        ResultSet rs1;
        ResultSet rs2;
        List<String> mid = new ArrayList<String>();
    
        public static void main(String[] args) {
            new Path("古田一路", "街道口");
        }
    
        public Path(String start, String end) {
            try {
                Class.forName("com.mysql.jdbc.Driver");// 加载MySQL驱动程序
                String url = "jdbc:mysql://127.0.0.1:3306/Metro";
                String user = "xiaoleng";// 用户名,系统默认的账户名
                String password = "xiaoleng123";// 你安装时选设置的密码
                con = DriverManager.getConnection(url, user, password);// 获取连接
                con2 = DriverManager.getConnection(url, user, password);// 获取连接
                sta0 = con.createStatement();
                sta1 = con2.createStatement();
                sta2 = con2.createStatement();
                sql0 = "SELECT NAME FROM `METROSITE` WHERE BELONG in"
                        + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '"+ start + "')" 
                        + "and `NAME` in(SELECT `NAME` FROM METROSITE WHERE BELONG in ("
                        + "SELECT BELONG FROM METROSITE WHERE `NAME` = '" + end + "'))";
                rs0 = sta0.executeQuery(sql0);
                while (rs0.next()) {
                    mid.add(rs0.getString(1));
                }
    
                if (mid.size() == 0) {
                    System.out.println("您选择的两个站之间无法通过一次换乘到达，请问需要换乘第二次吗？(默认YES)");
                    sql1 = "SELECT `NAME` FROM `METROSITE` WHERE BELONG in"
                            + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '" + start + "') "
                            + "and `NAME` in(SELECT `NAME` FROM METROSITE WHERE BELONG in("
                            + "SELECT BELONG FROM METROSITE WHERE BELONG not in"
                            + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '" + start + "')));";
                    rs1 = sta1.executeQuery(sql1);
                    while (rs1.next()) {
                        sql2 = "SELECT `NAME` FROM `METROSITE` WHERE BELONG in"
                                + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '" + end + "')"
                                + "and `NAME` in(SELECT `NAME` FROM METROSITE WHERE BELONG in("
                                + "SELECT BELONG FROM METROSITE WHERE BELONG not in"
                                + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '" + end + "')))"
                                + "and `NAME` in(SELECT `NAME` FROM METROSITE WHERE BELONG in"
                                + "(SELECT BELONG FROM METROSITE WHERE `NAME` in"
                                + "(SELECT `NAME` FROM `METROSITE` WHERE BELONG in"
                                + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '" + start + "')"
                                + "and `NAME` in(SELECT `NAME` FROM METROSITE WHERE BELONG in("
                                + "SELECT BELONG FROM METROSITE WHERE BELONG not in"
                                + "(SELECT BELONG FROM METROSITE WHERE `NAME` = '" + start + "'))))))"
                                + "and `NAME` in(SELECT `NAME` FROM METROSITE WHERE BELONG in"
                                + "(SELECT BELONG FROM METROSITE WHERE `NAME`='" + rs1.getString(1) + "'));";
                        rs2 = sta2.executeQuery(sql2);
                        while (rs2.next()) {
                            System.out.println("可换乘线路为：" + start + "————>" + rs1.getString(1) + "————>" + rs2.getString(1)
                                    + "————>" + end);
                        }
                    }
                } else {
                    for (int i = 0; i < mid.size(); i++) { 
                        System.out.println("可换乘线路为：" + start + "————>" + mid.get(i) + "————>" + end);
                    }
                }
                long endTime = System.currentTimeMillis();
                System.out.println("计算完了!!!，耗时：" + (endTime - startTime) + "ms");
                con.close();con2.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }
    
    }

关键的部分呢，就是那几个SQL语句了。  
数据库的构造是这个样子的，是不是很简单啊

private Integer id;  //当主键的
    
        private String name;  //地铁站名
    
        private Integer belong;  //属于几号线

效果演示：  
![这里写图片描述][SouthEast]![这里写图片描述][SouthEast 1]  
票价这个功能Java后台搞定了，但是这个小项目里没弄，就没管它了  
demo访问地址：之前用的腾讯云服务器到期了没续费~~看不了demo了T-T

[SouthEast]: https://img-blog.csdn.net/20171101114839451?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcXFfMzkzMDAzMzI=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 1]: /images/20220606/2e14f5e9aec9427baa4b5de63358f38a.png