冲刺作业：课后作业补充---地铁系统
设计思想：
（1）线路查询：数据库设计：将北京地铁线路图的各个线路，各个站点，换乘信息等用数据库的形式保存起来，应该保存的信息有 线路号，线路的各个站名，车站的换乘信息。
（2）站点查询：用户可以输入任一一条线路或输入出发地和目的地信息，可以查询到相关内容。
（3）换乘查询：弗洛伊德（floyd）算法求图的最短路径
数据库设计：
源程序代码：
dao
package dao;
import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import javabean.*;
import dbutil.DBUtil;
public class Dao {
public List<LineSelect> lineselect(String line){
List<LineSelect> list = new ArrayList<LineSelect>();
Connection con = DBUtil.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql="select * from subwaynew where line=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,line);
rs = pstmt.executeQuery();
while(rs.next()) {
String name = rs.getString("name");
LineSelect result = new LineSelect(name);
list.add(result);
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(pstmt,con);
}
return list;
}
public List<PlaceSelect> placeselect(String name){
List<PlaceSelect> list = new ArrayList<PlaceSelect>();
Connection con = DBUtil.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql="select * from subwaynew where name=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,name);
rs = pstmt.executeQuery();
while(rs.next()) {
String line = rs.getString("line");
PlaceSelect result = new PlaceSelect(line);
list.add(result);
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(pstmt,con);
}
return list;
}
public static int start(String start) {
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
ResultSet rs=null;
int id=0;
try {
String sql="SELECT ID FROM subwaynew where name=? ";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, start);
rs=pstmt.executeQuery();
while(rs.next()) {
id=rs.getInt("ID");
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs,pstmt, con);
}
return id;
}
public static int end(String end) {
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
ResultSet rs=null;
int id=0;
try {
String sql="SELECT ID FROM subwaynew where name=? ";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, end);
rs=pstmt.executeQuery();
while(rs.next()) {
id=rs.getInt("ID");
if(String.valueOf(id)!=null) {
break;
}
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs,pstmt, con);
}
return id;
}
public static String startline(int id) {
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
ResultSet rs=null;
String line="0";
try {
String sql="SELECT line FROM subwaynew where id=? ";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, id);
rs=pstmt.executeQuery();
while(rs.next()) {
line=rs.getString("line");
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs,pstmt, con);
}
return line;
}
public static String endline(int id) {
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
ResultSet rs=null;
String line="0";
try {
String sql="SELECT line FROM subwaynew where id=? ";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, id);
rs=pstmt.executeQuery();
while(rs.next()) {
line=rs.getString("line");
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs,pstmt, con);
}
return line;
}
public List<Transferquery> transferquery(int start,int end,String stl,String enl) {
List<Transferquery> list = new ArrayList<>();
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
String sql="SELECT * FROM subwaynew WHERE ID BETWEEN ? AND ? ";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, start);
pstmt.setInt(2, end);
rs=pstmt.executeQuery();
String huanline="0";
String huanname="0";
String line=stl;
System.out.println(line);
System.out.println(enl);
while(rs.next()) {
String name=rs.getString("name");
String huancheng=rs.getString("istransfer");
Transferquery result=new Transferquery(name);
if(line.equals(stl)) {
list.add(result);
if(huancheng!=null&&huancheng.equals(enl)) {
huanline = huancheng;
huanname = name;
line = enl;
continue;
}
}
else if((rs.getString("line")).equals("2")&&huanline.equals(line)) {
list.add(result);
}
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs,pstmt, con);
}
return list;
}
}
javabean，数据库链接由于普遍性不再展示
servlet：
package servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.ArrayList;
import javabean.*;
import dao.Dao;
import dao.Ergodic;
@WebServlet("/Servlet")
public class Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Dao dao = new Dao();
request.setCharacterEncoding("utf-8");
String method=request.getParameter("method");
String line = request.getParameter("line");
String name = request.getParameter("name");
System.out.println(method);
if(method.equals("lineselect")) {
System.out.println(line);
List<LineSelect> result = dao.lineselect(line);
request.setAttribute("line", result);
request.getRequestDispatcher("NewFile1.jsp").forward(request, response);
}else if(method.equals("placeselect")){
System.out.println(name);
List<PlaceSelect> result = dao.placeselect(name);
request.setAttribute("name", result);
request.getRequestDispatcher("NewFile1.jsp").forward(request, response);
}else if(method.equals("transferquery")) {
String start = request.getParameter("start");
String end = request.getParameter("end");
int startid=dao.start(start);
int endid=dao.end(end);
String stl=dao.startline(startid);
String enl=dao.endline(endid);
//Ergodic.main(start,end,stl,enl, startid, endid);
List<Transferquery> result = dao.transferquery(startid, endid,stl,enl);
request.setAttribute("linename", result);
request.getRequestDispatcher("select2.jsp").forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
前端：
<%@page import="javabean.LineSelect" %>
<%@page import="java.util.List" %>
<%@page import="javabean.LineSelect" %>
<%@page import="javabean.PlaceSelect" %>
<%@page import="javabean.Transferquery" %>
<%@page import="java.util.List" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>线路查询</title>
</head>
<link rel="stylesheet" type="text/css" href="resource/layui/css/layui.css">
<style>
.curtab{
font-weight: bold;
border-right:#b2c9d3 1px solid;
background: #99ffff;
}
</style>
<script src="jquery-1.11.0.js"></script>
<script type="text/jscript">
//显示tab（tabHeadId：tab头中当前的超链接；tabContentId要显示的层ID）
function showTab(tabHeadId,tabContentId)
{
//tab层
var tabDiv = document.getElementById("tabDiv");
//将tab层中所有的内容层设为不可见
//遍历tab层下的所有子节点
var taContents = tabDiv.childNodes;
for(i=0; i<taContents.length; i++)
{
//将所有内容层都设为不可见
if(taContents[i].id!=null && taContents[i].id != 'tabsHead')
{
taContents[i].style.display = 'none';
}
}
//将要显示的层设为可见
document.getElementById(tabContentId).style.display = 'block';
//遍历tab头中所有的超链接
var tabHeads = document.getElementById('tabsHead').getElementsByTagName('a');
for(i=0; i<tabHeads.length; i++)
{
//将超链接的样式设为未选的tab头样式
tabHeads[i].className='tabs';
}
//将当前超链接的样式设为已选tab头样式
document.getElementById(tabHeadId).className='curtab';
document.getElementById(tabHeadId).blur();
}
</script>
<body>
<div id="tabDiv" style="width:1000px">
<div id="tabsHead" >
<ul class="layui-nav" lay-filter="">
<li class="layui-nav-item"><a id="tabs1" href="javascript:showTab('tabs1','tabContent1')">换乘查询</a></li>
<li class="layui-nav-item"><a id="tabs2" href="javascript:showTab('tabs2','tabContent2')">线路查询</a></li>
<li class="layui-nav-item"><a id="tabs3" href="javascript:showTab('tabs3','tabContent3')">站点查询</a></li>
</ul>
</div>
<div id="tabContent1" style="display:block">
<form class="layui-form" action="Servlet?method=transferquery" method="post">
出发地:
<input type="text" class="layui-input" name="start" id="1" placeholder="如苹果园" value="">
目的地:
<input type="text" class="layui-input" name="end" id="2" placeholder="如车公庄" value="">
<input type="submit" class="layui-btn layui-btn-radius" value="查询">
</form>
</div>
<div id="tabContent2" style="display:none">
<form class="layui-form" action="Servlet?method=lineselect" method="post" onsubmit="return check()">
<input type="text" class="layui-input" name="line" placeholder="请输入线路名称，如1">
<input type="submit" class="layui-btn layui-btn-radius" value="查询">
</form>
<hr>
<table width=800px align="center" border="1">
<tr height=50px><td>序号</td><td>站名</td></tr>
<c:if test="${line!=null}">
<c:forEach items="${line}" var="x" varStatus="xh">
<tr height=50px><td>${xh.count}</td> <td>${x.name}</td></tr>
</c:forEach>
</c:if>
</table>
</div>
<div id="tabContent3" style="display:none">
<form class="layui-form" action="Servlet?method=placeselect" method="post">
<input type="text" class="layui-input" name="name" placeholder="站点名称，如“八宝山”">
<input type="submit" class="layui-btn layui-btn-radius" value="查询">
</form>
<c:if test="${name!=null}">
<hr style:"height:10px">
<hr>
<hr>
<table>
<tr><td>线路名</td></tr>
<c:forEach items="${name}" var="x" >
<tr height=50px><td>${x.line}</td></tr>
</c:forEach>
</table>
</c:if>
</div>
</div>
</body>
</html>
截图：
总结：本次作业中，线路查询和站点查询很好解决，但是在换乘查询中，虽然知道是使用弗洛伊德算法，由于数据库的设计出现了矛盾，导致没有完成。
浙公网安备 33010602011771号