Java+MyEclipse+Tomcat (六)详解Servlet和DAO数据库增删改查操作

转载来源:https://blog.csdn.net/Eastmount/article/details/45936121

版权声明:本文为博主原创文章,转载请注明CSDN博客源地址!共同学习,一起进步~ https://blog.csdn.net/Eastmount/article/details/45936121

        此篇文章主要讲述DAO、Java Bean和Servlet实现操作数据库,把链接数据库、数据库操作、前端界面显示分模块化实现。其中包括数据的CRUD增删改查操作,并通过一个常用的JSP网站前端模板界面进行描述。参考前文:
        Java+MyEclipse+Tomcat
(一)配置过程及jsp网站开发入门

        Java+MyEclipse+Tomcat
(二)配置Servlet及简单实现表单提交

        Java+MyEclipse+Tomcat
(三)配置MySQL及查询数据显示在JSP网页中

        Java+MyEclipse+Tomcat
(四)Servlet提交表单和数据库操作

        Java+MyEclipse+Tomcat (五)DAO和Java Bean实现数据库和界面分开操作
        免费资源下载地址:

        http://download.csdn.net/detail/eastmount/8733385


        PS:这篇文章可以认为是对前面五篇文章的一系列总结和应用,同时我认为理解该篇文章基本就能简单实现一个基于数据库操作的JSP网站,对你的课程项目或毕设有所帮助!但同时没有涉及事务、触发器、存储过程、并发处理等数据库知识,也没有Struts、Hibernate、Spring框架知识,它还是属于基础性文章吧!希望对你有所帮助~

一. 项目结构

        该项目的结构如下图所示:

        这是典型的DAO模式,其中bean文件夹中TrainManage.java类封装了数据库表TrainManage中的属性和get/set操作;DAO文件夹中TrainManageDAO.java是对类TrainManage(或火车表)的数据库增删改查操作;util中JDBCConnect.java主要是连接数据库MySQL的操作;servlet主要是POST方法请求表单。

二. 数据库初始化操作

        打开MySQL,输入默认超级root用户的密码,然后数据库的操作如下代码:

  1. --创建数据库
  2. create database ManageTrain;
  3. --使用数据库
  4. use ManageTrain;
  5. --创建表 车次信息管理表 主键:车次
  6. --属性:车次 出发地 目的地 行车时间 硬座票价 软座票价 硬卧票价 软卧票价 车辆路线 
  7. create table TrainManage(
  8. trainid varchar(20) primary key,
  9. start varchar(20),
  10. end varchar(20),
  11. time varchar(20),
  12. yzprice decimal(10,1),
  13. rzprice decimal(10,1),
  14. ywprice decimal(10,1),
  15. rwprice decimal(10,1),
  16. root varchar(200)
  17. );
  18. --插入数据
  19. insert TrainManage (trainid,start,end,time,yzprice,rzprice,ywprice,rwprice,root)
  20. values ("T87","Beijing","Guiyang","28小时","278","320","464.5","550",
  21. "Beijing Shijiazhuang Guiyang");
  22. insert TrainManage (trainid,start,end,time,yzprice,rzprice,ywprice,rwprice,root)
  23. values ("T87","Guiyang","Beijing","28小时","278","320","464.5","550",
  24. "Guiyang Shijiazhuang Beijing");
  25. --查询数据
  26. select * from TrainManage;
        注意:上面操作在MySQL黑框中输出增删改查的SQL语言就可以,不要把中文注释也执行。同时设置所有编码方式都统一为utf-8防止乱码,数据库表结构如下图所示:


三. 简单查询操作DAO方法

        新建Web Project,项目名为“TrainDatabase”,对火车车次数据库的增删改查。
        运行效果如下图所示:

 
        1.在src下新建文件夹util,然后添加类JDBCConnect.java。代码如下:
        主要是调用getConnection(url, userName, password)方法进行连接数据库操作,数据库的名称为TrainManage,默认的连接对象为root,密码为123456。同时定义两个函数executeUpdate()执行无参数的SQL语句操作和有参数的SQL语句操作。
  1. package util;
  2. import java.sql.*;
  3. import com.mysql.jdbc.Driver;
  4. public class JDBCConnect {
  5. //获取默认数据库连接
  6. public static Connection getConnection() throws SQLException {
  7. return getConnection("ManageTrain", "root", "123456"); //数据库名 默认用户 密码
  8. }
  9. //连接数据库 参数:数据库名 root登录名 密码
  10. public static Connection getConnection(String dbName, String userName,
  11. String password) throws SQLException {
  12. String url = "jdbc:mysql://localhost:3306/" + dbName
  13. + "?characterEncoding=utf-8";
  14. //连接MySQL"com.mysql.jdbc.Driver"
  15. DriverManager.registerDriver(new Driver());
  16. return DriverManager.getConnection(url, userName, password);
  17. }
  18. //设置 PreparedStatement 参数
  19. public static void setParams(PreparedStatement preStmt, Object... params)
  20. throws SQLException {
  21. if (params == null || params.length == 0)
  22. return;
  23. for (int i = 1; i <= params.length; i++) {
  24. Object param = params[i - 1];
  25. if (param == null) {
  26. preStmt.setNull(i, Types.NULL);
  27. } else if (param instanceof Integer) {
  28. preStmt.setInt(i, (Integer) param);
  29. } else if (param instanceof String) {
  30. preStmt.setString(i, (String) param);
  31. } else if (param instanceof Double) {
  32. preStmt.setDouble(i, (Double) param);
  33. } else if (param instanceof Long) {
  34. preStmt.setDouble(i, (Long) param);
  35. } else if (param instanceof Timestamp) {
  36. preStmt.setTimestamp(i, (Timestamp) param);
  37. } else if (param instanceof Boolean) {
  38. preStmt.setBoolean(i, (Boolean) param);
  39. } else if (param instanceof Date) {
  40. preStmt.setDate(i, (Date) param);
  41. }
  42. }
  43. }
  44. //执行 SQL,返回影响的行数 异常处理
  45. public static int executeUpdate(String sql) throws SQLException {
  46. return executeUpdate(sql, new Object[] {});
  47. }
  48. //带参数执行SQL,返回影响的行数 异常处理
  49. public static int executeUpdate(String sql, Object... params)
  50. throws SQLException {
  51. Connection conn = null;
  52. PreparedStatement preStmt = null;
  53. try {
  54. conn = getConnection();
  55. preStmt = conn.prepareStatement(sql);
  56. setParams(preStmt, params);
  57. return preStmt.executeUpdate(); //执行SQL操作
  58. } finally {
  59. if (preStmt != null)
  60. preStmt.close();
  61. if (conn != null)
  62. conn.close();
  63. }
  64. }
  65. }

       2.在src下新建文件夹bean,然后添加类TrainManage.java。代码如下:

  1. package bean;
  2. public class TrainManage {
  3. private String trainid; //车次
  4. private String start; //出发地
  5. private String end; //目的地
  6. private String time; //行车时间
  7. private float yzprice; //硬座票价
  8. private float rzprice; //软座票价
  9. private float ywprice; //硬卧票价
  10. private float rwprice; //软卧票价
  11. private String root; //车辆路线
  12. public String getTrainid() { return trainid; }
  13. public String getStart() { return start; }
  14. public String getEnd() { return end; }
  15. public String getTime() { return time; }
  16. public Float getYzprice() { return yzprice; }
  17. public Float getRzprice() { return rzprice; }
  18. public Float getYwprice() { return ywprice; }
  19. public Float getRwprice() { return rwprice; }
  20. public String getRoot() { return root; }
  21. public void setTrainid(String str) { this.trainid = str; }
  22. public void setStart(String str) { this.start = str; }
  23. public void setEnd(String str) { this.end = str; }
  24. public void setTime(String str) { this.time = str; }
  25. public void setYzprice(Float price) { this.yzprice = price; }
  26. public void setRzprice(Float price) { this.rzprice = price; }
  27. public void setYwprice(Float price) { this.ywprice = price; }
  28. public void setRwprice(Float price) { this.rwprice = price; }
  29. public void setRoot(String str) { this.root = str; }
  30. }
        3.在src下新建文件夹DAO,然后添加类StudentDAO.java。代码如下:

        通常DAO(Data Access Object)数据访问对象是负责与数据库连接,主要功能执行对数据表的CUDR操作(创建、更新、删除、查询)。每个数据表都定义一个DAO接口或类实现,实现对此表的读写操作。换句话说,就是在域名.项目.模块.dao文件夹下创建个DAO类即可。

  1. package DAO;
  2. import java.sql.Connection;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. import java.util.ArrayList;
  7. import java.util.List;
  8. import bean.TrainManage;
  9. import util.JDBCConnect;
  10. public class TrainManageDAO {
  11. //插入车次
  12. public static int insert(TrainManage train) throws Exception {
  13. String sql = "INSERT INTO TrainManage (trainid,start,end,time,yzprice,rzprice,ywprice,rwprice,root) " +
  14. "VALUES ('" + train.getTrainid() +"','"+ train.getStart() +"','"+ train.getEnd() +
  15. "','"+ train.getTime() +"','"+ train.getYzprice() +"','"+ train.getRzprice() +
  16. "','"+ train.getYwprice() +"','"+ train.getRwprice() +"','"+ train.getRoot() +"');";
  17. System.out.println(sql);
  18. return JDBCConnect.executeUpdate(sql);
  19. /**
  20. * 总是报错 No value specified for parameter 5
  21. * 很多原因是 insert into train (?,?,?) values (?,?,?) 前面不应该是问号
  22. * 但我的怀疑是参数过多使用executeUpdate(sql,?,?,?...)方法是错误
  23. *
  24. * String sql = "INSERT INTO TrainManage (trainid,start,end,time,yzprice,rzprice,ywprice,rwprice,root) VALUES (?,?,?,?,?,?,?,?,?);";
  25. * System.out.println(sql);
  26. * return JDBCConnect.executeUpdate(sql, train.getTrainid(), train.getStart(), train.getEnd(),
  27. * train.getTime(), train.getYzprice(), train.getRzprice(), train.getYwprice(),
  28. * train.getRwprice(), train.getRoot());
  29. */
  30. }
  31. //更新车次
  32. public static int update(TrainManage train) throws Exception {
  33. String sql = "UPDATE TrainManage SET start = ?, end = ? WHERE trainid = ? ";
  34. return JDBCConnect.executeUpdate(sql, train.getStart(), train.getEnd(), train.getTrainid());
  35. }
  36. //删除操作
  37. public static int delete(String id) throws Exception {
  38. String sql = "DELETE FROM TrainManage WHERE trainid = ? ";
  39. return JDBCConnect.executeUpdate(sql, id);
  40. }
  41. //查找记录 某车次
  42. public static TrainManage find(String id) throws Exception {
  43. String sql = "SELECT * FROM TrainManage WHERE trainid = ? ";
  44. Connection conn = null;
  45. PreparedStatement preStmt = null;
  46. ResultSet rs = null;
  47. try {
  48. //链接数据库执行SQL语句
  49. conn = JDBCConnect.getConnection(); //连接默认数据库
  50. preStmt = conn.prepareStatement(sql);
  51. preStmt.setString(1, id);
  52. rs = preStmt.executeQuery();
  53. //获取查询结果
  54. if (rs.next()) {
  55. TrainManage train = new TrainManage();
  56. train.setTrainid(rs.getString("trainid"));
  57. train.setStart(rs.getString("start"));
  58. train.setEnd(rs.getString("end"));
  59. train.setTime(rs.getString("time"));
  60. train.setYzprice(rs.getFloat("yzprice"));
  61. train.setYwprice(rs.getFloat("ywprice"));
  62. train.setRzprice(rs.getFloat("rzprice"));
  63. train.setRwprice(rs.getFloat("rwprice"));
  64. train.setRoot(rs.getString("root"));
  65. return train;
  66. } else {
  67. return null;
  68. }
  69. } finally { //依次关闭 记录集 声明 连接对象
  70. if (rs != null)
  71. rs.close();
  72. if (preStmt != null)
  73. preStmt.close();
  74. if (conn != null)
  75. conn.close();
  76. }
  77. }
  78. //查找记录 起始站 达到站
  79. public static List<TrainManage> findStartEnd(String start,String end) throws Exception {
  80. List<TrainManage> list = new ArrayList<TrainManage>();
  81. String sql = null;
  82. Connection conn = null;
  83. Statement statement = null;
  84. ResultSet rs = null;
  85. //判断SQL语句
  86. if(start==""&&end=="") {
  87. sql = "SELECT * FROM TrainManage;";
  88. } else if(end=="") {
  89. sql = "SELECT * FROM TrainManage WHERE start = '"+ start + "';";
  90. } else if(start=="") {
  91. sql = "SELECT * FROM TrainManage WHERE end = '"+ end + "';";
  92. } else {
  93. sql = "SELECT * FROM TrainManage WHERE start = '"
  94. + start + "' and end = '"+ end +"';";
  95. }
  96. //执行
  97. try {
  98. //链接数据库执行SQL语句
  99. conn = JDBCConnect.getConnection(); //连接默认数据库
  100. statement = conn.createStatement();
  101. System.out.println(start+" "+end);
  102. System.out.println(sql);
  103. rs = statement.executeQuery(sql);
  104. //获取查询结果
  105. while(rs.next()) {
  106. TrainManage train = new TrainManage();
  107. train.setTrainid(rs.getString("trainid"));
  108. train.setStart(rs.getString("start"));
  109. train.setEnd(rs.getString("end"));
  110. train.setTime(rs.getString("time"));
  111. train.setYzprice(rs.getFloat("yzprice"));
  112. train.setYwprice(rs.getFloat("ywprice"));
  113. train.setRzprice(rs.getFloat("rzprice"));
  114. train.setRwprice(rs.getFloat("rwprice"));
  115. train.setRoot(rs.getString("root"));
  116. list.add(train);
  117. }
  118. } catch (Exception e) {
  119. System.out.println("错误:"+e.getMessage());
  120. }
  121. finally { //依次关闭 记录集 声明 连接对象
  122. if (rs != null)
  123. rs.close();
  124. if (statement != null)
  125. statement.close();
  126. if (conn != null)
  127. conn.close();
  128. }
  129. return list;
  130. }
  131. //查询所有车次信息
  132. public static List<TrainManage> listStudents() throws Exception {
  133. List<TrainManage> list = new ArrayList<TrainManage>();
  134. String sql = "SELECT * FROM TrainManage";
  135. Connection conn = null;
  136. PreparedStatement preStmt = null;
  137. ResultSet rs = null;
  138. try {
  139. conn = JDBCConnect.getConnection();
  140. preStmt = conn.prepareStatement(sql);
  141. rs = preStmt.executeQuery();
  142. while (rs.next()) {
  143. //设置数据库中表参数 否则报错java.sql.SQLException: Column 'id' not found.
  144. TrainManage train = new TrainManage();
  145. train.setTrainid(rs.getString("trainid"));
  146. train.setStart(rs.getString("start"));
  147. train.setEnd(rs.getString("end"));
  148. train.setTime(rs.getString("time"));
  149. train.setYzprice(rs.getFloat("yzprice"));
  150. train.setYwprice(rs.getFloat("ywprice"));
  151. train.setRzprice(rs.getFloat("rzprice"));
  152. train.setRwprice(rs.getFloat("rwprice"));
  153. train.setRoot(rs.getString("root"));
  154. list.add(train);
  155. }
  156. } finally {
  157. if (rs != null)
  158. rs.close();
  159. if (preStmt != null)
  160. preStmt.close();
  161. if (conn != null)
  162. conn.close();
  163. }
  164. return list;
  165. }
  166. }

        4.在WebRoot文件夹下创建trainManage.jsp文件,页面布局代码如下:

  1. <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
  2. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
  3. <jsp:directive.page import="DAO.TrainManageDAO"/>
  4. <jsp:directive.page import="java.util.List"/>
  5. <%
  6. List trainList = TrainManageDAO.listStudents();
  7. request.setAttribute("trainList", trainList);
  8. %>
  9. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  10. <html>
  11. <head>
  12. <title>火车卡订票系统管理系统</title>
  13. </head>
  14. <body>
  15. <div align="center">
  16. <div class="trainCSS">
  17. <table width="1024" height="150" cellpadding="0" cellspacing="0">
  18. <tr><td colspan="2">
  19. <img src="./image/logo.jpg" alt="logo" width="1024" height="149">
  20. </td></tr>
  21. <tr><td width="205" bgcolor="#b7d7ec">
  22. <p align="center">
  23. <script language=JavaScript>
  24. today=new Date();
  25. function initArray(){
  26. this.length=initArray.arguments.length;
  27. for(var i=0;i<this.length;i++)
  28. this[i+1]=initArray.arguments[i]; }
  29. var d=new initArray(
  30. "星期日","星期一","星期二","星期三","星期四", "星期五","星期六");
  31. document.write("<font color=##ff0000 style='font-size:12pt;font-family: 宋体'> ",
  32. today.getYear()+1900,"年",today.getMonth()+1,"月",today.getDate(),"日 ",d[today.getDay()+1],
  33. "</font>" );
  34. </script>
  35. </p>
  36. </td>
  37. <td width="819" bgcolor="#b7d7ec">
  38. <marquee direction="left" onmouseover=this.stop() onmouseout=this.start() scrollAmount=3 scrollDelay=100>
  39. <FONT style="FONT-SIZE: 18px"color=blue>欢迎使用火车票订票系统管理系统 ,如有不足,敬请指导!</FONT>
  40. </marquee>
  41. </td>
  42. </tr>
  43. <tr><td height="12"></td></tr>
  44. </table>
  45. </div>
  46. <div class="trainCSS">
  47. <table border="0" width="1024" cellpadding="0" cellspacing="0">
  48. <tr><td width="130" bgcolor="#dfeaf1" valign="top">
  49. <table width="100%" cellpadding="0" cellspacing="0" border="0">
  50. <tr><td height="10"></td></tr>
  51. <tr><td align="center"> <font size="3" color="blue">管理员:xxx,欢迎您!</font></td></tr>
  52. <tr><td height="10"></td></tr>
  53. <tr>
  54. <td align="center"><p><a href="trainManage.jsp">
  55. <img src="image/ccxxgl-xz.jpg" width="194" height="37" border="0"></a><td> </td>
  56. </tr>
  57. <tr><td height="10"></td></tr>
  58. <tr><td align="center"><a href="buyManage.jsp">
  59. <img src="image/dpgl.jpg" width="194" height="37" border="0"></a>
  60. </td></tr>
  61. <tr><td height="10"></td></tr>
  62. <tr><td align="center"><a href="peopleManage.jsp">
  63. <img src="image/hygl.jpg" width="194" height="37" border="0"></a>
  64. </td></tr>
  65. <tr><td height="10"></td></tr>
  66. <tr><td align="center"><a href="serverManage.jsp">
  67. <img src="image/spqktj.jpg" width="194" height="37" border="0"></a>
  68. </td></tr>
  69. <tr><td height="10"></td></tr>
  70. <tr><td align="center"><a href="infoManage.jsp">
  71. <img src="image/lygl.jpg" width="194" height="37" border="0"></a>
  72. </td></tr>
  73. <tr><td height="10"></td></tr>
  74. <tr><td align="center"><a href="exitManage.jsp">
  75. <img src="image/exit.jpg" width="194" height="37" border="0"></a>
  76. </td></tr>
  77. </table>
  78. </td>
  79. <td>
  80. <table width="100%" height="350" border="1" cellpadding="0" cellspacing="0" bgcolor="#dfeaf1">
  81. <tr><TD align="center" valign="top" style="padding-left:20px;padding-top:10px;">
  82. <div align="left">当前位置: <a href="homepage.jsp">首页</a> > 车次信息管理</div>
  83. <br /><br />
  84. <form action="trainManageSelect.jsp" method="post">
  85. <!-- html:form为struts -->
  86. 出发地: <input type="text" id="start" name="start" style='font-size:18px' width=200/>
  87. 到达地: <input type="text" id="end" name="end" style='font-size:18px'/><br /><br />
  88. <input type="submit" name="Select" value="查询车次" style='font-size:18px'/>
  89.   <a href="trainManageInsert.jsp" style='font-size:20px'>插入车次</a>
  90. </form>
  91. <table border="2" bordercolor="#12A0F5">
  92. <tr align=center>
  93. <td>火车列次</td><td>出发地</td><td>目的地</td><td>行车时间</td>
  94. <td>硬座票价</td><td>软座票价</td><td>硬卧票价</td><td>软卧票价</td>
  95. <td>车辆路线</td><td>信息操作</td>
  96. </tr>
  97. <c:forEach items="${trainList}" var="train">
  98. <tr align=center>
  99. <td>${train.trainid}</td>
  100. <td>${train.start}</td>
  101. <td>${train.end}</td>
  102. <td>${train.time}</td>
  103. <td>${train.yzprice}</td>
  104. <td>${train.rzprice}</td>
  105. <td>${train.ywprice}</td>
  106. <td>${train.rwprice}</td>
  107. <td>
  108. <A href="trainManageDetail.jsp?action=see&id=${train.trainid}">查看</A></td>
  109. <td>
  110. <a href="trainManageUpdate.jsp?action=edit&id=${train.trainid}">修改</a>
  111. <a href="trainManageDelete.jsp?action=del&id=${train.trainid}"
  112. onclick="return confirm('确定删除?')">删除</a>
  113. </td>
  114. </tr>
  115. </c:forEach>
  116. </table>
  117. <br /><br />
  118. </TD></tr>
  119. </table>
  120. </td>
  121. </tr>
  122. <tr><td height="20"></td></tr>
  123. </table>
  124. </div>
  125. <div class="div">
  126. <!-- 底部版权所有界面 -->
  127. <TABLE class=wrapper border=0 cellSpacing=0 cellPadding=0 width="100%" align=center>
  128. <TBODY>
  129. <TR><TD style="LINE-HEIGHT: 100%" align="center">
  130. <HR style="WIDTH: 96%; HEIGHT: 4px; COLOR: #02457c">
  131. <font size="2"> <SCRIPT language=javascript src=""></SCRIPT>
  132. <!-- target=_blank开启新的网页 -->
  133. <BR><A href="aboutme.jsp">关于我们</A> | <A href="wzsm.jsp"
  134. >网站声明</A> <BR>版权所有&copy;2014-2015 北京邮电大学 Eastmount <BR>
  135. 京ICP备10009636号 </font>
  136. </TD></TR>
  137. </TBODY>
  138. </TABLE>
  139. </div>
  140. </div>
  141. </body>
  142. </html>
        5在WebRoot路径下创建image文件夹,并添加jsp中需要使用的图片资源。
        6.在WebRoot/WEB-INF/lib文件夹中添加mysql-connector-java-5.1.15-bin.jar文件,访问MySQL数据库需要用到。
        7.右键项目,Run As在Tomcat 7.x下即可运行,效果如前图所示。
        其中JSP文件与DAO的交互核心代码如下:
  1. <jsp:directive.page import="DAO.TrainManageDAO"/>
  2. <jsp:directive.page import="java.util.List"/>
  3. <%
  4. List trainList = TrainManageDAO.listStudents();
  5. request.setAttribute("trainList", trainList);
  6. %>
  7. ...
  8. <table border="2" bordercolor="#12A0F5"> 
  9. <tr align=center>
  10. <td>火车列次</td><td>出发地</td><td>目的地</td><td>行车时间</td>
  11. <td>硬座票价</td><td>软座票价</td><td>硬卧票价</td><td>软卧票价</td>
  12. <td>车辆路线</td><td>信息操作</td>
  13. </tr>
  14. <c:forEach items="${trainList}" var="train">  
  15. <tr align=center>  
  16. <td>${train.trainid}</td>  
  17. <td>${train.start}</td>  
  18. <td>${train.end}</td>  
  19. <td>${train.time}</td>  
  20. <td>${train.yzprice}</td>  
  21. <td>${train.rzprice}</td>  
  22. <td>${train.ywprice}</td>  
  23. <td>${train.rwprice}</td> 
  24. <td><A href="trainManageDetail.jsp?action=see&id=${train.trainid}">查看</A></td> 
  25. <td>  
  26. <a href="trainManageUpdate.jsp?action=edit&id=${train.trainid}">修改</a>  
  27. <a href="trainManageDelete.jsp?action=del&id=${train.trainid}"   
  28. onclick="return confirm('确定删除?')">删除</a>  
  29. </td>  
  30. </tr>  
  31. </c:forEach>
  32. </table>



四. 关键字查询操作

        关键字查询运行截图如下图所示:




        该步骤只需要在WebRoot文件夹下创建trainManageSelect.jsp即可,代码如下:
  1. <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
  2. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
  3. <jsp:directive.page import="DAO.TrainManageDAO"/>
  4. <jsp:directive.page import="bean.TrainManage"/>
  5. <jsp:directive.page import="java.util.List"/>
  6. <%
  7. request.setCharacterEncoding("UTF-8");
  8. response.setCharacterEncoding("UTF-8");
  9. String mstart = request.getParameter("start");
  10. String mend = request.getParameter("end");
  11. List<TrainManage> trainList = TrainManageDAO.findStartEnd(mstart,mend);
  12. //List trainList = TrainManageDAO.listStudents();
  13. request.setAttribute("trainList", trainList);
  14. %>
  15. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  16. <html>
  17. <head>
  18. <title>火车卡订票系统管理系统</title>
  19. </head>
  20. <body>
  21. <div align="center">
  22. <div class="trainCSS">
  23. <table width="1024" height="150" cellpadding="0" cellspacing="0">
  24. <tr><td colspan="2">
  25. <img src="./image/logo.jpg" alt="logo" width="1024" height="149">
  26. </td></tr>
  27. <tr><td width="205" bgcolor="#b7d7ec">
  28. <p align="center">
  29. <script language=JavaScript>
  30. today=new Date();
  31. function initArray(){
  32. this.length=initArray.arguments.length;
  33. for(var i=0;i<this.length;i++)
  34. this[i+1]=initArray.arguments[i]; }
  35. var d=new initArray(
  36. "星期日","星期一","星期二","星期三","星期四", "星期五","星期六");
  37. document.write("<font color=##ff0000 style='font-size:12pt;font-family: 宋体'> ",
  38. today.getYear()+1900,"年",today.getMonth()+1,"月",today.getDate(),"日 ",d[today.getDay()+1],
  39. "</font>" );
  40. </script>
  41. </p>
  42. </td>
  43. <td width="819" bgcolor="#b7d7ec">
  44. <marquee direction="left" onmouseover=this.stop() onmouseout=this.start() scrollAmount=3 scrollDelay=100>
  45. <FONT style="FONT-SIZE: 18px"color=blue>欢迎使用火车票订票系统管理系统 ,如有不足,敬请指导!</FONT>
  46. </marquee>
  47. </td>
  48. </tr>
  49. <tr><td height="12"></td></tr>
  50. </table>
  51. </div>
  52. <div class="trainCSS">
  53. <table border="0" width="1024" cellpadding="0" cellspacing="0">
  54. <tr><td width="130" bgcolor="#dfeaf1" valign="top">
  55. <table width="100%" cellpadding="0" cellspacing="0" border="0">
  56. <tr><td height="10"></td></tr>
  57. <tr><td align="center"> <font size="3" color="blue">管理员:xxx,欢迎您!</font></td></tr>
  58. <tr><td height="10"></td></tr>
  59. <tr>
  60. <td align="center"><p><a href="trainManage.jsp">
  61. <img src="image/ccxxgl-xz.jpg" width="194" height="37" border="0"></a><td> </td>
  62. </tr>
  63. <tr><td height="10"></td></tr>
  64. <tr><td align="center"><a href="buyManage.jsp">
  65. <img src="image/dpgl.jpg" width="194" height="37" border="0"></a>
  66. </td></tr>
  67. <tr><td height="10"></td></tr>
  68. <tr><td align="center"><a href="peopleManage.jsp">
  69. <img src="image/hygl.jpg" width="194" height="37" border="0"></a>
  70. </td></tr>
  71. <tr><td height="10"></td></tr>
  72. <tr><td align="center"><a href="serverManage.jsp">
  73. <img src="image/spqktj.jpg" width="194" height="37" border="0"></a>
  74. </td></tr>
  75. <tr><td height="10"></td></tr>
  76. <tr><td align="center"><a href="infoManage.jsp">
  77. <img src="image/lygl.jpg" width="194" height="37" border="0"></a>
  78. </td></tr>
  79. <tr><td height="10"></td></tr>
  80. <tr><td align="center"><a href="exitManage.jsp">
  81. <img src="image/exit.jpg" width="194" height="37" border="0"></a>
  82. </td></tr>
  83. </table>
  84. </td>
  85. <td>
  86. <table width="100%" height="350" border="1" cellpadding="0" cellspacing="0" bgcolor="#dfeaf1">
  87. <tr><TD align="center" valign="top" style="padding-left:20px;padding-top:10px;">
  88. <div align="left">当前位置: <a href="homepage.jsp">首页</a> > 车次信息管理</div>
  89. <br /><br />
  90. <form action="trainManageSelect.jsp" method="post">
  91. <!-- html:form为struts -->
  92. 出发地: <input type="text" id="start" name="start" style='font-size:18px' width=200/>
  93. 到达地: <input type="text" id="end" name="end" style='font-size:18px'/><br /><br />
  94. <input type="submit" name="Select" value="查询车次" style='font-size:18px'/>
  95.   <a href="trainManageInsert.jsp" style='font-size:20px'>插入车次</a>
  96. </form>
  97. <table border="2" bordercolor="#12A0F5">
  98. <tr align=center>
  99. <td>火车列次</td><td>出发地</td><td>目的地</td><td>行车时间</td>
  100. <td>硬座票价</td><td>软座票价</td><td>硬卧票价</td><td>软卧票价</td>
  101. <td>车辆路线</td><td>信息操作</td>
  102. </tr>
  103. <c:forEach items="${trainList}" var="train">
  104. <tr align=center>
  105. <td>${train.trainid}</td>
  106. <td>${train.start}</td>
  107. <td>${train.end}</td>
  108. <td>${train.time}</td>
  109. <td>${train.yzprice}</td>
  110. <td>${train.rzprice}</td>
  111. <td>${train.ywprice}</td>
  112. <td>${train.rwprice}</td>
  113. <td>
  114. <A href="trainManageDetail.jsp?action=see&id=${train.trainid}">查看</A></td>
  115. <td>
  116. <a href="trainManageUpdate.jsp?action=edit&id=${train.trainid}">修改</a>
  117. <a href="trainManageDelete.jsp?action=del&id=${train.trainid}"
  118. onclick="return confirm('确定删除?')">删除</a>
  119. </td>
  120. </tr>
  121. </c:forEach>
  122. </table>
  123. <br /><br />
  124. </TD></tr>
  125. </table>
  126. </td>
  127. </tr>
  128. <tr><td height="20"></td></tr>
  129. </table>
  130. </div>
  131. <div class="div">
  132. <!-- 底部版权所有界面 -->
  133. <TABLE class=wrapper border=0 cellSpacing=0 cellPadding=0 width="100%" align=center>
  134. <TBODY>
  135. <TR><TD style="LINE-HEIGHT: 100%" align="center">
  136. <HR style="WIDTH: 96%; HEIGHT: 4px; COLOR: #02457c">
  137. <font size="2"> <SCRIPT language=javascript src=""></SCRIPT>
  138. <!-- target=_blank开启新的网页 -->
  139. <BR><A href="aboutme.jsp">关于我们</A> | <A href="wzsm.jsp"
  140. >网站声明</A> <BR>版权所有&copy;2014-2015 北京邮电大学 Eastmount <BR>
  141. 京ICP备10009636号 </font>
  142. </TD></TR>
  143. </TBODY>
  144. </TABLE>
  145. </div>
  146. </div>
  147. </body>
  148. </html>
        调用的trainManageDAO.java中的函数findStartEnd(String start,String end)如下,它如果不输入则进行所有查找,也支持单方面出发或到达查找:
  1. //查找记录 某车次
  2. public static List<TrainManage> findStartEnd(String start,String end) throws Exception {
  3. List<TrainManage> list = new ArrayList<TrainManage>();
  4. String sql = null;
  5. Connection conn = null;
  6. Statement statement = null;
  7. ResultSet rs = null;
  8. //判断SQL语句
  9. if(start==""&&end=="") {
  10. sql = "SELECT * FROM TrainManage;";
  11. } else if(end=="") {
  12. sql = "SELECT * FROM TrainManage WHERE start = '"+ start + "';";
  13. } else if(start=="") {
  14. sql = "SELECT * FROM TrainManage WHERE end = '"+ end + "';";
  15. } else {
  16. sql = "SELECT * FROM TrainManage WHERE start = '"
  17. + start + "' and end = '"+ end +"';";
  18. }
  19. //执行
  20. try {
  21. //链接数据库执行SQL语句
  22. conn = JDBCConnect.getConnection(); //连接默认数据库
  23. statement = conn.createStatement();
  24. System.out.println(start+" "+end);
  25. System.out.println(sql);
  26. rs = statement.executeQuery(sql);
  27. //获取查询结果
  28. while(rs.next()) {
  29. TrainManage train = new TrainManage();
  30. train.setTrainid(rs.getString("trainid"));
  31. train.setStart(rs.getString("start"));
  32. train.setEnd(rs.getString("end"));
  33. train.setTime(rs.getString("time"));
  34. train.setYzprice(rs.getFloat("yzprice"));
  35. train.setYwprice(rs.getFloat("ywprice"));
  36. train.setRzprice(rs.getFloat("rzprice"));
  37. train.setRwprice(rs.getFloat("rwprice"));
  38. train.setRoot(rs.getString("root"));
  39. list.add(train);
  40. }
  41. } catch (Exception e) {
  42. System.out.println("错误:"+e.getMessage());
  43. }
  44. finally { //依次关闭 记录集 声明 连接对象
  45. if (rs != null)
  46. rs.close();
  47. if (statement != null)
  48. statement.close();
  49. if (conn != null)
  50. conn.close();
  51. }
  52. return list;
  53. }
        其中核心步骤如下:
        1.在trainManage.jsp提交POST表单出发地(<input id="start")和到达地(<input id="end"),提交给trainManageSelect.jsp;
        2.在trainManageSelect.jsp中通过获取出发地和到达地,并执行DAO中的TrainManageDAO.java中关键字查找函数:
        String mstart = request.getParameter("start");
        String mend = request.getParameter("end");
        List<TrainManage> trainList = TrainManageDAO.findStartEnd(mstart,mend); 
        request.setAttribute("trainList", trainList);

        3.在JSP中通过EL循环显示结果如下:
        <c:forEach items="${trainList}" var="train">  
            <tr align=center>  
                <td>${train.trainid}</td> 
                <td>${train.start}</td>
                 ...
            </tr>
        </c:forEach>
        PS:同时在这过程中你会遇到执行关键字中文查询时显示乱码。
        在trainManageSelect.java文件中通过函数findStartEnd(start,end)打桩输出提交表单"北京 长沙"如下所示:


       而且我的服务器、MySQL、JSP、URL3306地址中都设置了UTF-8编码方式,最终解决有两个方面:
       第一个方面是在进行SQL查找时,我最初使用的第一种方法核心代码:
  1. String sql = "SELECT * FROM TrainManage WHERE start = ? and end = ? ;";
  2. Connection conn = JDBCConnect.getConnection(); //连接默认数据库
  3. PreparedStatement preStmt = conn.prepareStatement(sql);
  4. preStmt.setString(1, start);
  5. preStmt.setString(2, end);
  6. ResultSet rs = preStmt.executeQuery();
        但是在查询英文Beijing => Guizhou 时能正确显示,而中文就没有结果,后改为带引号("'"+string+"'")的参数方式,核心代码如下:
  1. String sql = "SELECT * FROM TrainManage WHERE start = '"
  2. + start + "' and end = '"+ end +"';";
  3. Connection conn = JDBCConnect.getConnection(); //连接默认数据库
  4. Statement statement = conn.createStatement();
  5. ResultSet rs = statement.executeQuery(sql);
  6. while(rs.next()){...}
        后来使用该方法仍然存在乱码,但是这次我发现了原因,在form提交表单时采用Get方法就会出现中文乱码,虽然URL中显示的是:
        http://localhost:8080/TrainDatabase/trainManageSelect.jsp?start=北京&end=长沙&sumbit=提交
        但是可能URL转String出现乱码的,改为POST即可实现正常查询,显示正常中文。
        注意:前提是我的所有编码字符集都统一为UTF-8。

五. 插入操作调用Servlet

        该部分运行截图如下图所示:

        在src中创建文件夹servlet,同时创建Servlet文件InsertTrainAction.java。
  1. package servlet;
  2. import java.io.IOException;
  3. import java.io.PrintWriter;
  4. import javax.servlet.ServletException;
  5. import javax.servlet.http.HttpServlet;
  6. import javax.servlet.http.HttpServletRequest;
  7. import javax.servlet.http.HttpServletResponse;
  8. import bean.TrainManage;
  9. import util.JDBCConnect;
  10. import DAO.TrainManageDAO;
  11. public class InsertTrainAction extends HttpServlet {
  12. public InsertTrainAction() {
  13. super();
  14. }
  15. public void destroy() {
  16. super.destroy(); // Just puts "destroy" string in log
  17. // Put your code here
  18. }
  19. //The doGet method of the servlet
  20. public void doGet(HttpServletRequest request, HttpServletResponse response)
  21. throws ServletException, IOException {
  22. response.setContentType("text/html");
  23. PrintWriter out = response.getWriter();
  24. out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
  25. out.println("<HTML>");
  26. out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
  27. out.println(" <BODY>");
  28. out.print(" This is ");
  29. out.print(this.getClass());
  30. out.println(", using the GET method");
  31. out.println(" </BODY>");
  32. out.println("</HTML>");
  33. out.flush();
  34. out.close();
  35. }
  36. //The doPost method of the servlet
  37. public void doPost(HttpServletRequest request, HttpServletResponse response)
  38. throws ServletException, IOException {
  39. response.setCharacterEncoding("UTF-8"); //设置输出编码
  40. request.setCharacterEncoding("UTF-8");
  41. try {
  42. //插入数据库
  43. String m_trainid = request.getParameter("trainid");
  44. String m_start = request.getParameter("start");
  45. String m_end = request.getParameter("end");
  46. String m_time = request.getParameter("time");
  47. String m_yz = request.getParameter("yzprice");
  48. Float m_yzprice = Float.parseFloat(m_yz);
  49. String m_rz = request.getParameter("rzprice");
  50. Float m_rzprice = Float.parseFloat(m_rz);
  51. String m_yw = request.getParameter("ywprice");
  52. Float m_ywprice = Float.parseFloat(m_yw);
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 书香水墨 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读