用SQL进行地铁线路换乘查询
发布时间
阅读量：
阅读量
这代码是当初做课程作业时写出来的，过了几个月了现在看了看发现还挺不错的，说不定现在还写不出来了呢··。语法较为基础，都是简单的关键字，但是逻辑嵌套比较复杂，也用了大量的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访问地址：之前用的腾讯云服务器到期了没续费~~看不了demo了T-T
全部评论 (0)
还没有任何评论哟~