import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 数据库连接工具 * @author Hu * */ public class DBUtil { public static String dbURL="jdbc:sqlserver://localhost:1433;DatabaseName=subway";//数据源 ！！！！注意若出现加载或者连接数据库失败一般是这里出现问题 public static String Name="sa"; public static String Pwd="YXB99299"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动 conn = DriverManager.getConnection(dbURL, Name, Pwd); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
public class Subway { private int id; private String line; private int stopid; private String stopname; public void setID(int id) { this.id=id; } public int getID() { return id; } public void setLine(String line) { this.line=line; } public String getLine() { return line; } public void setStopID(int stopid) { this.stopid=stopid; } public int getStopID() { return stopid; } public void setStopName(String stopname) { this.stopname=stopname; } public String getStopName() { return stopname; } public Subway(){} public Subway(String station) { this.station=station; } public Subway(int id,String station) { this.id=id; this.station=station; } public Subway(int id,String station,int line) { this.id=id; this.station=station; this.line=line; } }
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class SubwayDao { public List<Subway> search(String station) { String sql = "select * from course where station ='" + station + "'"; List<Subway> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); Subway bean = null; while (rs.next()) { int id = rs.getInt("id"); int line = rs.getInt("line"); bean = new Subway(id,station,line); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } /** * 全部数据 * @param name * @param teacher * @param classroom * @return */ public List<Subway> list() { String sql = "select * from course"; List<Subway> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); Subway bean = null; while (rs.next()) { int id = rs.getInt("id"); String station = rs.getString("station"); int line = rs.getInt("line"); bean = new Subway(id,station,line); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } public Subway getIdByStation(String station) { String sql = "select * from course where station ='" + station + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; Subway Subway = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id"); Subway = new Subway(id, station); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return Subway; } }
import java.util.List; public class SubwayService { SubwayDao cDao = new SubwayDao(); public Subway getIdByStation(String station) { return cDao.getIdByStation(station); } /** * 查找 * @return */ public List<Subway> search(String station) { return cDao.search(station); } /** * 全部数据 * @return */ public List<Subway> list() { return cDao.list(); } }
import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class SubwayServlet extends HttpServlet { private static final long serialVersionUID = 1L; SubwayService service = new SubwayService(); /** * 方法选择 */ protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("search".equals(method)) { search(req, resp); } else if ("list".equals(method)) { list(req, resp); }else if ("getidbyname".equals(method)) { getIdByStation(req, resp); } } private void list(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); List<Subway> Subway = service.list(); req.setAttribute("Subway", Subway); req.getRequestDispatcher("list.jsp").forward(req,resp); } private void getIdByStation(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String station = req.getParameter("start"); Subway Subway = service.getIdByStation(station); if(Subway == null) { req.setAttribute("message", "无此站点！"); req.getRequestDispatcher("index.jsp").forward(req,resp); } else { req.setAttribute("Subway", Subway); req.getRequestDispatcher("index2.jsp").forward(req,resp); } } private void search(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); int id = Integer.parseInt(req.getParameter("start")); String station = req.getParameter("end"); Subway S = service.getIdByStation(station); List<Subway> Subway = service.search(station); req.setAttribute("Subway", Subway); req.getRequestDispatcher("searchlist.jsp").forward(req,resp); } }
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>石家庄地铁</title> </head> <body> <div align="center"> <form method="post" action="SubwayServlet?method=getidbyname" onsubmit="return check()"> <table> <tr> <td> <img alt="" src="index.jpg"> </td> </tr> <tr> <td> 起始站：<input type="text" id="start"> </td> </tr> </table> </form> </div> <div> <button type="submit">查 询</button> </div> <script type="text/javascript"> function check() { var start = document.getElementById("start");; //非空 if(start.value == '') { alert('请填写'); return false; } } </script> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>石家庄地铁</title> </head> <body> <div align="center"> <form method="post" action="SubwayServlet?method=search" onsubmit="return check()"> <table> <tr> <td> <input type="hidden" id="start" value=""> 终点站：<input type="text" id="end"> </td> </tr> </table> </form> </div> <div> <button type="submit">查 询</button> </div> <script type="text/javascript"> function check() { var start = document.getElementById("start"); var end = document.getElementById("end"); //非空 if(end.value == '') { alert('请填写'); return false; } } </script> </body> </html>
题目：
1.开发一套石家庄地铁线路查询系统。
2.功能设计
（1）数据库设计：将石家庄地铁线路图的各个线路，各个站点，换乘信息等用数据库的形式保存起来，应该保存的信息有 {线路号，线路的各个站名，车站的换乘信息}。
（2）站点查询：用户可以输入任一一条线路或输入出发地和目的地信息，可以查询到相关内容。