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

来源:互联网 发布:白苹果修复保留数据 编辑:程序博客网 时间:2024/03/29 23:40

这代码是当初做课程作业时写出来的,过了几个月了现在看了看发现还挺不错的,说不定现在还写不出来了呢·~·。语法较为基础,都是简单的关键字,但是逻辑嵌套比较复杂,也用了大量的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;  //属于几号线

效果演示:
这里写图片描述这里写图片描述
票价这个功能Java后台搞定了,但是这个小项目里没弄,就没管它了
demo访问地址:武汉市地铁信息服务系统,usename:leng password:xianyang