学习进度第4周
| 时间 | 30小时 |
| 代码量 | 2000行左右 |
| 博客园 | 2 |
| 知识点 |
知识点：从最基本的jsp连接数据库学习。 List<类名> list=new ArrayList<>()：这就像是一个容器。能把数据储存在 list里。 ResultSet rs：创建rs结果集可以用来接受储存数据。 rs = state.executeQuery(sql)：就上面的代码而言，excuteQuery就是对数据库进行操作，然后再把值传给rs结果集中。 rs.getString（“id”）：单独提取id这个值。 基本熟悉select语句。select site from all_line where id between 1 and 6； 搜索出id为1-6的站台。 req.getParameter("line")：getParameter就是从网页中获取输入的数据。 --------------------------------------------------------------------------------------------------------------------------------------------------------------------- String line=dao.line(site); <td><%=request.getAttribute("lines") %></td> 网页接受lines的数据。 |
package com.sub.dao; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.sub.been.SubwayBeen; import com.sub.util.DBUtil; public class SubwayDao { /* * 搜索一号线的所有路线 */ public List<SubwayBeen> one_line(String line, String intro, String route,String site) { String sql = "select * from one_line order by id ASC"; List<SubwayBeen> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); SubwayBeen bean = null; while (rs.next()) { String id = rs.getString("id"); String line2 = rs.getString("line"); String intro2 = rs.getString("intro"); String route2 = rs.getString("route"); String site2 = rs.getString("site"); bean = new SubwayBeen(id, line2, intro2, route2,site2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } /* * 搜索二号线的所有路线 */ public List<SubwayBeen> two_line(String line, String intro, String route,String site) { String sql = "select * from two_line order by id ASC"; List<SubwayBeen> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); SubwayBeen bean = null; while (rs.next()) { String id = rs.getString("id"); String line2 = rs.getString("line"); String intro2 = rs.getString("intro"); String route2 = rs.getString("route"); String site2 = rs.getString("site"); bean = new SubwayBeen(id, line2, intro2, route2,site2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } /* * 搜索四号线的所有路线 */ public List<SubwayBeen> four_line(String line, String intro, String route,String site) { String sql = "select * from four_line order by id ASC"; List<SubwayBeen> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); SubwayBeen bean = null; while (rs.next()) { String id = rs.getString("id"); String line2 = rs.getString("line"); String intro2 = rs.getString("intro"); String route2 = rs.getString("route"); String site2 = rs.getString("site"); bean = new SubwayBeen(id, line2, intro2, route2,site2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } /* * 搜索四号线支线的所有路线 */ public List<SubwayBeen> four_feederline(String line, String intro, String route,String site) { String sql = "select * from four_feederline order by id ASC"; List<SubwayBeen> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); SubwayBeen bean = null; while (rs.next()) { String id = rs.getString("id"); String line2 = rs.getString("line"); String intro2 = rs.getString("intro"); String route2 = rs.getString("route"); String site2 = rs.getString("site"); bean = new SubwayBeen(id, line2, intro2, route2,site2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } /* * 根据站台名获取该站台的id */ public String id(String site) { String sql="select id from all_line where site = '"+site+"'"; Connection conn = DBUtil.getConn(); Statement state = null; String id=null; ResultSet rs = null; try { state=conn.createStatement(); rs = state.executeQuery(sql); while(rs.next()) { id=rs.getString("id"); } }catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return id; } /* * 根据站台名获取该站台属于几号线路 */ public String line(String site) { String sql="select line from all_line where site = '"+site+"'"; Connection conn = DBUtil.getConn(); Statement state = null; String line=null; ResultSet rs = null; try { state=conn.createStatement(); rs = state.executeQuery(sql); while(rs.next()) { line=rs.getString("line"); } }catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return line; } /* * 根据获取的id值搜索出其中间的站台升序 */ public String station1(int id1,int id2) { String sql="select site from all_line where id between '"+id1+"' and '"+id2+"' order by id ASC" ; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; String route = ""; try { state=conn.createStatement(); rs = state.executeQuery(sql); if(rs.next()) route=rs.getString("site"); while(rs.next()) { String site=rs.getString("site"); route+="->"+site; } }catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return route; } /* * 根据获取的id值搜索出其中间的站台降序 */ public String station2(int id1,int id2) { String sql="select site from all_line where id between '"+id1+"' and '"+id2+"' order by id DESC" ; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; String route = ""; try { state=conn.createStatement(); rs = state.executeQuery(sql); if(rs.next()) route=rs.getString("site"); while(rs.next()) { String site=rs.getString("site"); route+="->"+site; } }catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return route; } }
package com.sub.servlet; 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; import com.sub.been.SubwayBeen; import com.sub.dao.SubwayDao; @WebServlet("/SubwayServlet") public class SubwayServlet extends HttpServlet { private static final long serialVersionUID = 1L; SubwayDao dao=new SubwayDao(); protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("one_line".equals(method)) { one_line(req, resp); }else if("two_line".equals(method)) { two_line(req, resp); }else if("four_line".equals(method)) { four_line(req, resp); }else if("four_feederline".equals(method)) { four_feederline(req, resp); }else if("no_transfer1".equals(method)) { no_transfer1(req, resp); }else if("transfer".equals(method)) { transfer(req, resp); } } /* * 搜索一号线的所有路线 */ private void one_line(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String line = req.getParameter("line"); String intro = req.getParameter("intro"); String route = req.getParameter("route"); String site = req.getParameter("site"); List<SubwayBeen> ones = dao.one_line(line, intro, route,site); req.setAttribute("ones", ones); req.getRequestDispatcher("one_line_searchlist.jsp").forward(req,resp); } /* * 搜索二号线的所有路线 */ private void two_line(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String line = req.getParameter("line"); String intro = req.getParameter("intro"); String route = req.getParameter("route"); String site = req.getParameter("site"); List<SubwayBeen> twos = dao.two_line(line, intro, route,site); req.setAttribute("twos", twos); req.getRequestDispatcher("two_line_searchlist.jsp").forward(req,resp); } /* * 搜索四号线的所有路线 */ private void four_line(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String line = req.getParameter("line"); String intro = req.getParameter("intro"); String route = req.getParameter("route"); String site = req.getParameter("site"); List<SubwayBeen> fours = dao.four_line(line, intro, route,site); req.setAttribute("fours", fours); req.getRequestDispatcher("four_line_searchlist.jsp").forward(req,resp); } /* * 搜索四号线支线的所有路线 */ private void four_feederline(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String line = req.getParameter("line"); String intro = req.getParameter("intro"); String route = req.getParameter("route"); String site = req.getParameter("site"); List<SubwayBeen> four_feeders = dao.four_feederline(line, intro, route,site); req.setAttribute("four_feeders", four_feeders); req.getRequestDispatcher("four_feederline_searchlist.jsp").forward(req,resp); } /* * 根据站台名查询出其中间站台 */ private void no_transfer1(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String site = req.getParameter("site"); //从网页获取输入的起始站台 int id1 = Integer.parseInt(dao.id(site)); //获取其站台id 将其转换为int类型 String site2 = req.getParameter("site2"); //从网页获取输入的终止站台 int id2 = Integer.parseInt(dao.id(site2)); //获取其站台id 将其转换为int类型 if(id1<id2) { String station=dao.station1(id1, id2); //dao层升序车站 req.setAttribute("stations", station); String line=dao.line(site); //通过站名知道哪条线 System.out.print(line); req.setAttribute("lines", line); req.getRequestDispatcher("no_transfer_searchlist.jsp").forward(req,resp);//升序车站 req.getRequestDispatcher("no_transfer_searchlist.jsp").forward(req,resp);//升序车站 }else { String station=dao.station2(id2, id1); req.setAttribute("stations", station); String line=dao.line(site); req.setAttribute("lines", line); req.getRequestDispatcher("no_transfer_searchlist.jsp").forward(req,resp);//降序车站 req.getRequestDispatcher("no_transfer_searchlist.jsp").forward(req,resp);//升序车站 } } private void transfer(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String site1 = req.getParameter("site1"); //获取车站名 int line1 = Integer.parseInt(dao.line(site1)); //将车站名获取的线路转换为整型 int id1=Integer.parseInt(dao.id(site1)); //将车站名获取的id转换为整型 String site2 = req.getParameter("site2"); int line2 = Integer.parseInt(dao.line(site2)); int id2=Integer.parseInt(dao.id(site2)); //--------------------------------------1到2----------------------------------- if(line1==1&&line2==2) { if(id1<=10) { String station1=dao.station1(id1, 10); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=115) { String station2=dao.station2(id2,115); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(115,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }if(id1>10) { String station1=dao.station2(10,id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=115) { String station2=dao.station2(id2,115); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(115,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //--------------------------------------2到1--------------------------------------- if(line1==2&&line2==1) { if(id1<=115) { String station1=dao.station1(id1, 115); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=10) { String station2=dao.station2(id2,10); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(10,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }if(id1>115) { String station1=dao.station2(115,id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=10) { String station2=dao.station2(id2,10); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(10,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //--------------------------------------1到4--------------------------------------- if(line1==1&&line2==4) { if(id1<=12) { String station1=dao.station1(id1, 12); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=1011) { String station2=dao.station2(id2,1011); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(1011,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }else { String station1=dao.station2(12, id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=1011) { String station2=dao.station2(id2,1011); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(1011,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //--------------------------------------4到1--------------------------------------- if(line1==4&&line2==1) { if(id1<=1011) { String station1=dao.station1(id1, 1011); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=12) { String station2=dao.station2(id2,12); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(12,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }else { String station1=dao.station2(1011, id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=12) { String station2=dao.station2(id2,12); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(12,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //-----------------------------------------------2到4---------------------------------- if(line1==2&&line2==4) { if(id1<=112) { String station1=dao.station1(id1, 112); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=1011) { String station2=dao.station2(id2,1008); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(1008,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }else { String station1=dao.station2(112, id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=1011) { String station2=dao.station2(id2,1008); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(1008,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //-----------------------------------------------4到2---------------------------------- if(line1==4&&line2==2) { if(id1<=1008) { String station1=dao.station1(id1, 1008); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=112) { String station2=dao.station2(id2,112); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(112,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }else { String station1=dao.station2(1008, id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=112) { String station2=dao.station2(id2,112); //降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); }else { String station2=dao.station1(112,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); } req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //--------------------------------------------4到4支线---------------------------------------------------- if(line1==4&&line2==41) { if(id1<=1018) { String station1=dao.station1(id1, 1018); //升序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); String station2=dao.station1(10001,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }else { String station1=dao.station2(1018, id1); //降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); String station2=dao.station1(10001,id2); //升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } //--------------------------------------------4支线到4-------------------------------------------- if(line1==41&&line2==4) { String station1=dao.station2(10001, id1);//降序 req.setAttribute("stations1", station1); req.setAttribute("line1", line1); if(id2<=1018) { String station2=dao.station2(id2, 1018);//降序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); }else { String station2=dao.station1(1018, id2);//升序 req.setAttribute("stations2", station2); req.setAttribute("line2", line2); req.getRequestDispatcher("transfer_searchlist.jsp").forward(req,resp); } } } }
知识点：从最基本的jsp连接数据库学习。
List<类名> list=new ArrayList<>()：这就像是一个容器。能把数据储存在 list里。
ResultSet rs：创建rs结果集可以用来接受储存数据。
rs = state.executeQuery(sql)：就上面的代码而言，excuteQuery就是对数据库进行操作，然后再把值传给rs结果集中。
rs.getString（“id”）：单独提取id这个值。
基本熟悉select语句。select site from all_line where id between 1 and 6； 搜索出id为1-6的站台。
req.getParameter("line")：getParameter就是从网页中获取输入的数据。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
String line=dao.line(site);
req.setAttribute("lines", line); 将字符串line的数据导入到网页lines中，设置lines提供为网页的接收
<td><%=request.getAttribute("lines") %></td> 网页接受lines的数据。
