用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
阅读全文
0 0
- 用SQL进行地铁线路换乘查询
- 地铁换乘的线路问题
- 华为上机考试样题三----地铁线路换乘问题
- 苏州地铁线路查询接口 地铁线路实时详细信息
- 地铁换乘
- 地铁换乘
- 地铁换乘
- 地铁换乘
- 地铁换乘
- 地铁采用一次换乘到达sql
- 最佳飞机换乘线路查询系统的实现
- 地铁路线查询sql
- 蓝桥杯-地铁换乘
- 蓝桥杯地铁换乘问题
- 蓝桥杯-地铁换乘
- 华为地铁换乘问题
- 地铁换乘C++
- 中国地铁换乘网——免费提供地铁查询代码下载
- 怎样选择合适的ADC芯片
- axios 请求并发
- 通联Toppay消费报文解读
- codecept API Test
- H5新特性(常见)
- 用SQL进行地铁线路换乘查询
- Spring配置---<aop:aspectj-autoproxy />
- 数据库表导出成Excel工具类
- 谈谈熟悉在GUI界面下编译代码的同学怎么快速入门Android Studio
- 服务端口问题
- MTK sensor 框架
- 最大公约数。
- linux lnmp环境安装redis和redis拓展
- AOP面向切向编程