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）站点查询：用户可以输入任一一条线路或输入出发地和目的地信息，可以查询到相关内容。
