冲刺作业：课后作业补充---地铁系统

设计思想：

（1）线路查询：数据库设计：将北京地铁线路图的各个线路，各个站点，换乘信息等用数据库的形式保存起来，应该保存的信息有线路号，线路的各个站名，车站的换乘信息。

（2）站点查询：用户可以输入任一一条线路或输入出发地和目的地信息，可以查询到相关内容。

（3）换乘查询：弗洛伊德（floyd）算法求图的最短路径

数据库设计：

源程序代码：

dao

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

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);
    }
 
}

前端：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

<%@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>