2023.3.24——课后作业
地铁查询系统
1.设计思想：
1）线路查询：这个比较好实现，利用sql语句，根据输入的线路号，查询站点名称并输出到web上；
2）站点查询：这个比较好实现，利用sql语句，根据输入的站点名称，查询线路号并输出到web上；
3）起点—终点查询：利用Dijkstra算法找到最短路径，输入起始站和终点站后，计数最短路径经过的站点数，若中间有换线路，记录线路并输出。
2.源程序代码：
1）UserDao.java
package dao;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.sql.*;
import util.DBUtil;
import java.sql.*;
public class UserDao {
public static Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1");
return con;
}
/**时间复杂度比较复杂，因为换乘结点的关系导致的
最坏情况下(每个站之间都有连线，但是地铁线路图实际上是不存在次情况的):O(2^n)
相反
最优情况下(之间只有唯一的连接点,次情况下也不是很现实的，有的地铁换乘是多个换乘点都在同一条线上的)
此时用hashtable所以是:O(1)
*/
/*private java.util.HashSet<String> GetF(java.util.HashSet<String> beginlist)
{
if (mainht == null || mainht.isEmpty())
{
return null;
}
returnlist = new java.util.HashSet<String>();
if (beginlist.isEmpty())
{
isend = 1;
}
else
{
*//**O(n)
*//*
for (String strbegin : beginlist)
{
if (strbegin.indexOf("-") == -1 && mainht.containsKey(strbegin) == true) //have this key and first load data
{
bxy = (double[])DCht.get(strbegin);
earry = mainht.get(strbegin).toString().split("[,]", -1);
for (String ar : earry)
{
exy = (double[])DCht.get(ar);
isadd = CK(isadd, bxy, exy);
if (isadd == true)
{
returnlist.add(strbegin + "-" + ar);
isend = 0;
}
}
}
else if (strbegin.indexOf("-") > -1 && mainht.containsKey(strbegin.substring(strbegin.lastIndexOf("-") + 1)) == true)
{
temgstr = strbegin.substring(strbegin.lastIndexOf("-") + 1);
bxy = (double[])DCht.get(temgstr);
earry = mainht.get(temgstr).toString().split("[,]", -1); //exchange node
for (String ar : earry)
{
exy = (double[])DCht.get(ar);
isadd = CK(isadd, bxy, exy);
if (isadd == true)
{
if (!strbegin.contains(ar))
{
returnlist.add(strbegin + "-" + ar);
}
isend = 0;
}
}
}
}
}
earry = null;
if (isend == 0)
{
return GetF(returnlist);
}
else
{
return null;
}
}*/
}
2）DBUtil.java
package util;
import java.sql.*;
public class DBUtil {
public static Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=true");
return con;
}
public void close(ResultSet rs) {
}
public void close(PreparedStatement preparedStatement) {
}
public void close(Connection connection) {
}
}
3）index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>线路查询</title>
</head>
<body style="text-align:center" vertical-align="middle">
<h1>地铁查询系统</h1>
<p style="color: black; font-family: 宋体; font-size: 40px" align="center">
<h2>
<input type="button" value="线路查询" onclick="location.href='check1.jsp'"/>
</h2>
<h2>
<input type="button" value="站点查询" onclick="location.href='check2.jsp'"/>
</h2>
<h2>
<input type="button" value="起点—终点查询" onclick="location.href='check3.jsp'"/>
</h2>
</p>
</body>
</html>
4）check1.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>线路查询</title>
</head>
<body>
<h2 align="center">
线路查询
</h2>
<form action="check1_pd.jsp" method="get">
<p style="text-align:center;color: black; font-family: 宋体; font-size: 20px">
输入要查询线路的数字：
<br> <input type="text" name="number"/> <br>
<br><input type="submit" value="提交"/>
<input type="reset" value="重置"/> <br>
<br> <input type="button" value="返回菜单" onclick="location.href='index.jsp'"/> <br>
</p>
</form>
</body>
</html>
5）check1_pd.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<title>线路查询</title>
</head>
<body style="text-align:center" vertical-align="middle">
查询结果
<p>
<jsp:useBean id="util" class="util.DBUtil" scope="page"/>
<%
String number=(String)request.getParameter("number");
if(number==""){
out.print("<script language='javaScript'> alert('输入为空'); window.history.back(-1); </script>");
}
else
{
%>
<table border="1" align="center" vertical-align="middle">
<tr style="text-align:center;color: black; font-family: 宋体; font-size: 20px">
<td align="center" width=20%>线路号</td>
<td align="center" width=20%>车站名称</td>
</tr>
<%
int i = 0;
Connection connection = util.getConnection();
PreparedStatement preparedStatement = null;
ResultSet rs = null;
try {
String sql = " select * from test1 where number like ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "%" + number + "%");
rs = preparedStatement.executeQuery();
while (rs.next()) {
i++;
%>
<tr>
<td align="center"><%=rs.getObject(2) %>
</td>
<td align="center"><%=rs.getObject(4) %>
</td>
</tr>
<%
}
if (i == 0) {
out.print("<script language='javaScript'> alert('没有查询到有关信息'); window.history.back(-1); </script>");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.close(rs);
util.close(preparedStatement);
util.close(connection);
}
}
%>
</table>
<p style="text-align:center;color: black; font-family: 宋体; font-size: 20px">
<br> <input type="button" value="返回菜单" onclick="location.href='index.jsp'"/> <br>
</p>
</body>
</html>
6）check2.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>站点查询</title>
</head>
<body>
<h2 align="center">
站点查询
</h2>
<form id="1" action="check2_pd.jsp" method="get">
<p style="text-align: center; color: black; font-family: 宋体; font-size: 20px">
站点:
<label>
<input type="text" name="data" size="10"/>
</label>
<br><br>
<input type="submit" value="提交"/>
<input type="reset" value="重置"/>
<br><br> <input type="button" value="返回菜单" onclick="location.href='index.jsp'"/> <br>
</p>
</form>
</body>
</html>
7）check2_pd.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="util.DBUtil" %>
<%@ page import="java.util.ArrayList" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>站点查询</title>
</head>
<body style="text-align:center" vertical-align="middle">
查询结果
<p style="text-align: center; color: black; font-family: 宋体; font-size: 20px">
<table border="1" align="center">
<tr>
<td align="center" width=10%>线路</td>
<td align="center" width=10%>站点</td>
</tr>
<%
String data = request.getParameter("data");
String str = "";
Connection connection = DBUtil.getConnection();
PreparedStatement preparedStatement = null;
ResultSet rs = null;
ArrayList<String> list = new ArrayList<String>();
int i = 0;
try {
String sql = "select * from test1 ";
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
while (rs.next()) {
if (data.equals(rs.getObject("data"))) {
if (!list.contains((String) rs.getObject(2))) {
list.add((String) rs.getObject(2));
i++;
%>
<tr>
<td align="center"><%=rs.getObject("number") %>号线</td>
<td align="center"><%=rs.getObject("data") %></td>
</tr>
<%
}
}
}
if (i == 0) {
out.print("<script language='javaScript'> alert('没有查询到有关信息'); window.history.back(-1); </script>");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
%>
</table>
<script>
function del() {
var r = confirm("确定要删除吗？")
if (r == true) {
return true;
} else {
return false;
}
}
</script>
<p style="text-align:center;color: black; font-family: 宋体; font-size: 20px">
<br> <input type="button" value="返回菜单" onclick="location.href='index.jsp'"/> <br>
</p>
</body>
</html>
8）check3.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>起点——终点查询</title>
</head>
<body>
<h2 align="center">
起点——终点查询
</h2>
<form id="1" action="check3_pd.jsp" method="get">
<p style="text-align: center; color: black; font-family: 宋体; font-size: 20px">
起始站:
<label>
<input type="text" name="one" size="10"/>
</label>
<br><br>
终点站:
<label>
<input type="text" name="two" size="10"/>
</label>
<br><br>
<input type="submit" value="提交"/>
<input type="reset" value="重置"/>
<br><br> <input type="button" value="返回菜单" onclick="location.href='index.jsp'"/> <br>
</p>
</form>
</body>
</html>
9）check3_pd.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="util.DBUtil" %>
<%@ page import="java.util.ArrayList" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>起点-终点查询</title>
</head>
<body style="text-align:center" vertical-align="middle">
查询结果
<p style="text-align: center; color: black; font-family: 宋体; font-size: 20px">
<table border="1" style="align:center;">
<tr>
<td align="center" width=20%>路径</td>
<td align="center" width=2%>经历站点个数</td>
</tr>
<%
String one = request.getParameter("one");
String two = request.getParameter("two");
String str = "";
int number = 1;
Connection connection = DBUtil.getConnection();
PreparedStatement preparedStatement = null;
ResultSet rs = null;
ArrayList<String> list = new ArrayList<String>();
int i = 0;
try {
String sql = "select * from number";
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
while (rs.next()) {
if (one.equals(rs.getObject("one")) && two.equals(rs.getObject("two"))) {
if (!list.contains((String) rs.getObject(4))) {
list.add((String) rs.getObject(4));
i++;
%>
<tr>
<td align="center"><%=rs.getObject("four") %>
</td>
<%
}
}
}
String judgelist = list.get(0);
for (int j = 0; j < judgelist.length() - 1; j++) {
if (judgelist.charAt(j) == '-') {
number++;
}
}
%>
<td align="center"><%=number%>
</td>
</tr>
<%
if (i == 0) {
out.print("<script language='javaScript'> alert('没有查询到有关信息'); window.history.back(-1); </script>");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
%>
</table>
<script>
function del() {
var r = confirm("确定要删除吗？")
if (r == true) {
return true;
} else {
return false;
}
}
</script>
<p style="text-align:center;color: black; font-family: 宋体; font-size: 20px">
<br> <input type="button" value="返回菜单" onclick="location.href='index.jsp'"/> <br>
</p>
</body>
</html>
3.运行结果截图
4.编程总结分析
这次的程序对于我们来说还是比较难的，尤其是起点——终点查询的功能，另外那些附加的功能对于我们来说就更难了。
通过此次编程，我对于数据库的使用，Java的认识，HTML的认识更加深入。
5.时间记录日志
