Java Web JDBC实现CRUD操作总结(二)

来源：互联网发布：qq群加人软件编辑：程序博客网时间：2024/06/05 04:06

(一)项目结构

这里写图片描述

(二)数据库表结构

CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(12) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `sex` varchar(12) DEFAULT NULL,  `birthday` date DEFAULT NULL,  `description` text,  `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2015419063 DEFAULT CHARSET=utf8;

(三)JSP源码

1.addPerson.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><title>添加人员信息</title><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <script type="text/javascript" language="javascript"          src="${pageContext.request.contextPath}/My97DatePicker/WdatePicker.js" ></script>  <script type="text/javascript" language="javascript"          src="${pageContext.request.contextPath}/js/jquery-1.8.1js" ></script></head><body>     <!--将表单 数据提交给Operate.jsp页面操作  -->     <form action="${pageContext.request.contextPath}/Operate.jsp" method="post">             <table>                 <tr>                     <td>姓名</td>                     <td><input name="name"/></td>                 </tr>                 <tr>                      <td>性别</td>                      <td><select name="sex">                             <option value="男">男</option>                             <option value="女">女</option>                      </select></td>                 </tr>                 <tr>                     <td>年龄</td>                     <td><input name="age"/></td>                 </tr>                 <!-- 1.调用日期空间 -->               <tr>                    <td>生日</td>                    <td><input id="d11" name="birthday" type="text"                    onclick="WdatePicker()"></td>                </tr>                 <!-- 2.描述 -->                 <tr>                     <td>描述</td>                     <td><textarea name="description"></textarea></td>                 </tr>                 <tr>                     <td colspan="2"><input type="submit"  value="提交"/></td>                 </tr>             </table>     </form></body></html>

2.delete.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"    pageEncoding="ISO-8859-1"%><%@ page import="java.sql.*" %>    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Insert title here</title></head><body>              <% Connection conn = null;                 Statement stmt = null;              %>              <%               try{                  Class.forName("com.mysql.jdbc.Driver");                  conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testWeb", "root", "root");                  stmt = conn.createStatement();                  String id=request.getParameter("id");                  String sql= "delete from person where id='"+id+"'";                 // out.println(sql);                  int i=stmt.executeUpdate(sql);              }catch(Exception e){                  e.printStackTrace();              }finally{                  if(stmt!=null&conn!=null){                      stmt.close();                      conn.close();                  }              }                response.sendRedirect("listPerson.jsp");               %>       </body></html>

3.listPerson.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@ page import="java.sql.*" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>人员信息列表</title><script type="text/javascript" language="javascript"          src="${pageContext.request.contextPath}/js/jquery-1.8.1js" ></script></head><body>   <center>         <h4>人员信息列表</h4>   </center>   <a href="addPerson.jsp">添加新人员</a><br/><br/>    <table border="1" cellpadding="2"  cellspacing="1" width="100%">            <tr align="center">                <th>编号</th>                <th>姓名</th>                <th>年龄</th>                <th>性别</th>                <th>生日</th>                <th>备注</th>                <th>操作</th>            </tr>            <%                Class.forName("com.mysql.jdbc.Driver");                Connection conn = null;                ResultSet rs = null;                Statement stmt = null;                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testWeb", "root", "root");                stmt = conn.createStatement();                String query = "select * from person";                rs = stmt.executeQuery(query);                while(rs.next()){            %>            <tr >                <%                    int id = rs.getInt("id");                    String name = rs.getString("name");                    int age = rs.getInt("age");                    String sex = rs.getString("sex");                    Date birthday = rs.getDate("birthday");                    String description=rs.getString("description");                %>                <td align="center"><%=id%></td>                <td align="center"><%=name%></td>                <td align="center"><%=age%></td>                <td align="center"><%=sex%></td>                <td align="left"><%=birthday%></td>                <td align="left"><%=description%></td>                <!-- 修改、删除通过request对象获取地址栏中的id数据 -->                <td align="center"><a href="modify.jsp?id=<%=id%>">修改 &nbsp;&nbsp;</a><a href="delete.jsp?id=<%=id%>" >删除</a></td>            </tr>                           <%                      }            %>   </table></body></html>

4.modify.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@ page import="java.sql.*" %>    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body>    <%    Class.forName("com.mysql.jdbc.Driver");    Connection conn = null;    ResultSet rs = null;    Statement stmt = null;    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testWeb", "root", "root");    stmt = conn.createStatement();    String id=request.getParameter("id");    String sql = "select * from person where id='"+id+"'";    rs=stmt.executeQuery(sql);    response.setContentType("text/html;UTF-8");     while(rs.next()){         request.setAttribute("id", rs.getInt("id"));         request.setAttribute("name",rs.getString("name"));         request.setAttribute("sex",rs.getString("sex"));         request.setAttribute("age",rs.getInt("age"));         request.setAttribute("birthday",rs.getString("birthday"));         request.setAttribute("description",rs.getString("description"));         request.getRequestDispatcher("update.jsp").forward(request, response);    }    %></body></html>

5.modifyAction.jsp

<%@page import="java.text.SimpleDateFormat"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@ page import="java.sql.*" %>    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>修改页面数据</title></head><body>      <%       //在表单跳转页面，也就是此页面设置其你去字符编码为UFT-8解决乱码问题       request.setCharacterEncoding("utf-8");        String id=request.getParameter("id");       String name=request.getParameter("name");       String sex=request.getParameter("sex");       String birthday=request.getParameter("birthday");       String description=request.getParameter("description");       //1.编写SQL语句       String sql="update person set name=? ,sex=?,birthday=?,description=? where id=?";       //2.加载驱动器       Class.forName("com.mysql.jdbc.Driver");       Connection conn = null;       PreparedStatement ps= null;       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testWeb", "root", "root");       ps=conn.prepareStatement(sql);       ps.setString(1,name);       ps.setString(2, sex);       SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");       Date date=new Date(sf.parse(birthday).getTime());       ps.setDate(3, date);       ps.setString(4, description);       ps.setInt(5,Integer.parseInt(id));       ps.executeUpdate();       if(ps!=null&conn!=null){           ps.close();           conn.close();       }       //执行完sql语句跳转到listPerson.jsp       response.sendRedirect("listPerson.jsp");       %></body></html>

6./Project/WebContent/Operate.jsp

 <%@page import="java.text.SimpleDateFormat"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@ page import="java.sql.*" %>  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body>    <%       request.setCharacterEncoding("UTF-8");       String name=request.getParameter("name");       String age=request.getParameter("age");       String sex=request.getParameter("sex");       String birthday=request.getParameter("birthday");       String description=request.getParameter("description");       //1.编写SQL语句       String sql="insert into person(name,age,sex,birthday,description) values(?,?,?,?,?)";       //2.加载驱动器       Class.forName("com.mysql.jdbc.Driver");       Connection conn = null;       PreparedStatement ps= null;       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testWeb", "root", "root");       ps=conn.prepareStatement(sql);       ps.setString(1, name);       ps.setString(2, age);       ps.setString(3, sex);       SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");       Date date=new Date(sf.parse(birthday).getTime());       ps.setDate(4, date);       ps.setString(5, description);       ps.executeUpdate();       if(ps!=null&conn!=null){           ps.close();           conn.close();       }       //执行完sql语句跳转到listPerson.jsp       response.sendRedirect("listPerson.jsp");       %></body></html>

7./Project/WebContent/update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>将数据跳到此页面</title> <script type="text/javascript" language="javascript"          src="${pageContext.request.contextPath}/My97DatePicker/WdatePicker.js" ></script>  <script type="text/javascript" language="javascript"          src="${pageContext.request.contextPath}/js/jquery-1.8.1js" ></script></head>   <%      /*获取modify.jsp保存的数据 */      Integer id=(Integer)request.getAttribute("id");      Integer age=(Integer)request.getAttribute("age");      String name=(String)request.getAttribute("name");      String sex=(String)request.getAttribute("sex");      String birthday=(String)request.getAttribute("birthday");      String description=(String)request.getAttribute("description");    %><body>    <!-- 在form标签内添加表单 -->    <form action="${pageContext.request.contextPath}/modifyAction.jsp?id=<%=id%>" method="post">           <table>                <tr>                    <td>姓名</td>                    <!--  request获得的数据添加到表单上-->                    <td><input name="name" value=<%=name%>></td>                </tr>                    <tr>                     <td>性别</td>                     <td><select name="sex">                      <%                       if(sex.equals("男")){                     %>                        <option value="男" selected>男</option>                        <%                                 }else{                        %>                          <option value="男">男</option>                        <%                              }                       %>                      <%                       if(sex.equals("女")){                     %>                        <option value="女" selected>女</option>                        <%                                 }else{                        %>                          <option value="女">女</option>                        <%                              }                       %>                      </select></td>                  </tr>                   <tr>                    <td>生日</td>                    <td><input id="d11" name="birthday" type="text"                    onclick="WdatePicker()" value="<%=birthday%>"></td>                </tr>              <tr>                  <td>描述</td>                  <td><textarea  name="description"  ><%=description %></textarea></td>              </tr>                 <tr>                   <td colspan="2"><input type="submit" value="提交"> </td>              </tr>           </table>    </form></body></html>

总结：

1.JSP设计界面,我们不需要其处理业务逻辑，因此我们需要将数据处理交给Servlet去处理。

2.以上业务逻辑均在JSP页面处理，需要修改。

阅读全文

0 0