基于Servlet的CRUD

一、实验目的：

熟悉并掌握运用Servlet开发模式 (HTML+JSP+JavaBean+Servlet)即MVC模式实现数据库CRUD基本编程。

二、实验内容：

在MyEclipse环境下运用HTML+JSP+JavaBean+Servlet以及JDBC技术完成相应的数据库CRUD功能，调试运行程序。

三、实验要求：

1. 熟悉并掌握运用MVC技术开发功能模块的基本步骤；

2. 运用相关技术（HTML+JSP+JavaBean+Servlet以及JDBC）完成规定功能；

3. 写出实验报告。

四、实验步骤：

1．进入MyEclipse环境，新建一个Web Project。

 

  1. 设计数据库表结构。

 

  1. 采用MVC技术完成数据库的CRUD

总目录结构：

 

  1. 程序运行截图。

 

 

 

 

 

 

 

 

 

 

  1. 程序源码：

add.java

package com.xujiaxing.CRUD.servlet;

 

import java.io.IOException;

import java.io.PrintWriter;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.sql.*;

 

public class add extends HttpServlet {

 

/**

 * The doPost method of the servlet. <br>

 *

 * This method is called when a form has its tag value method equals to post.

 *

 * @param request the request send by the client to the server

 * @param response the response send by the server to the client

 * @throws ServletException if an error occurred

 * @throws IOException if an error occurred

 */

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

 

request.setCharacterEncoding("utf-8");

String strId="";

String strName="";

String strJiage="";

strId=request.getParameter("id");

strName=request.getParameter("name");

strJiage=request.getParameter("jiage");

Connection con=null;

Statement stmt=null;

 

String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=caidan;user=sa;password=123";

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

con=DriverManager.getConnection(url);

String strSql="insert into caidan values('"+strId+"','"+strName+"','"+strJiage+"')";

stmt=con.createStatement();

stmt.execute(strSql);

}

catch(Exception e)

{

response.setContentType("text/html");

response.setCharacterEncoding("utf-8");

PrintWriter out =response.getWriter();

out.print("<center><br><br>添加失败，序号："+strId);

out.print("<br><br><a href='../show.jsp>返回</a></center>");

out.close();

return;

}

response.sendRedirect("../show.jsp");

 

}

 

}

Delete.java

package com.xujiaxing.CRUD.servlet;

 

import java.io.IOException;

import java.sql.*;

import java.io.PrintWriter;

 

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

public class delete extends HttpServlet {

 

/**

 * The doGet method of the servlet. <br>

 *

 * This method is called when a form has its tag value method equals to get.

 *

 * @param request the request send by the client to the server

 * @param response the response send by the server to the client

 * @throws ServletException if an error occurred

 * @throws IOException if an error occurred

 */

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

 

request.setCharacterEncoding("utf-8");

String strId="";

strId=request.getParameter("id");

Connection con=null;

Statement stmt=null;

ResultSet rs=null;

 

String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=caidan;user=sa;password=123";

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

con=DriverManager.getConnection(url);

String strSql="delete from caidan where id='"+strId+"'";

stmt=con.createStatement();

stmt.execute(strSql);

}

catch(Exception e)

{

response.setContentType("text/html");

response.setCharacterEncoding("utf-8");

PrintWriter out =response.getWriter();

out.print("<center><br><br>删除失败，序号："+strId);

out.print("<br><br><a href='../show.jsp>返回</a></center>");

out.close();

return;

}

response.sendRedirect("../show.jsp");

}

 

}

Update.java

package com.xujiaxing.CRUD.servlet;

 

import java.io.IOException;

import java.sql.*;

import java.io.PrintWriter;

 

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

public class update extends HttpServlet {

 

/**

 * The doPost method of the servlet. <br>

 *

 * This method is called when a form has its tag value method equals to post.

 *

 * @param request the request send by the client to the server

 * @param response the response send by the server to the client

 * @throws ServletException if an error occurred

 * @throws IOException if an error occurred

 */

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

request.setCharacterEncoding("utf-8");

String strId="";

String strName="";

String strJiage="";

strId=request.getParameter("id");

strName=request.getParameter("name");

strJiage=request.getParameter("jiage");

Connection con=null;

Statement stmt=null;

ResultSet rs=null;

 

String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=caidan;user=sa;password=123";

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

con=DriverManager.getConnection(url);

String strSql="update caidan set name='"+strName+"',jiage='"+strJiage+"'where id="+strId+"";

stmt=con.createStatement();

stmt.execute(strSql);

}

catch(Exception e)

{

response.setContentType("text/html");

response.setCharacterEncoding("utf-8");

PrintWriter out =response.getWriter();

out.print("<center><br><br>更新失败，序号："+strId);

out.print("<br><br><a href='../show.jsp>返回</a></center>");

out.close();

return;

}

response.sendRedirect("../show.jsp");

 

 

}

 

}

Add.html

<!DOCTYPE html>

<html>

  <head>

    <title>add.html</title>

 

    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

    <meta http-equiv="description" content="this is my page">

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">

    

    <!--<link rel="stylesheet" type="text/css" href="./styles.css">-->

 

  </head>

  

  <body>

    <center>

    <br><br>添加菜谱<br>

    <form name="f1" id="f1" action="servlet/add" method="post">

    <table border="0">

    <tr>

    <td>序号：</td>

    <td><input type="text" name="id"></td>

    </tr>

    <tr>

    <td>菜名：</td>

    <td><input type="text" name="name"></td>

    </tr>

    <tr>

    <td>价格：</td>

    <td><input type="text" name="jiage"></td>

    </tr>

    <tr>

    <td colspan="2" align="center"><input type="submit" value="确定"></td>

    </tr>

    </table>

    </form>

    </center>

  </body>

</html>

Select.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

<%@ page import="java.sql.*" %>

<%

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

  <head>

    <base href="<%=basePath%>">

    

    <title>My JSP 'select.jsp' starting page</title>

    

<meta http-equiv="pragma" content="no-cache">

<meta http-equiv="cache-control" content="no-cache">

<meta http-equiv="expires" content="0">    

<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

<meta http-equiv="description" content="This is my page">

<!--

<link rel="stylesheet" type="text/css" href="styles.css">

-->

 

  </head>

  <style> 

  .jvzhong{text-align:center}

  </style> 

  <body>

   <%

    request.setCharacterEncoding("utf-8");

   String name=request.getParameter("name");

   Connection conn=null;

   Statement stat=null;

   ResultSet rs=null;

   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

   String url ="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=caidan";     

   String user = "sa";     

   String password = "123";     

   conn = DriverManager.getConnection(url,user,password);

   stat = conn.createStatement();     

    rs = stat.executeQuery("select * from caidan where name='"+name+"'");  

    %>

    <br>

    <h3 class="jvzhong">菜品信息</h3>

    <hr>

    <br>

    <table align="center" width="450" border="100" cellSpacing=1 style="font-size: 15pt; border: dashed 1pt">

<tr>

    <td>

    序号

    </td>

    <td>

    菜名

    </td>

    <td>

    价格

    </td>

</tr>

<%

  if(rs.next())

  {

  out.print("<tr>");    

  out.print("<td>" + rs.getInt("id") + "</td>");    

  out.print("<td>" + rs.getString("name") + "</td>");    

  out.print("<td>" + rs.getString("jiage") + "</td>");

  %>    

  <td>

  <a href="servlet/delete?id=<%=rs.getInt("id") %>">删除</a>

  </td>    

  <td>

  <a href="update.jsp?id=<%=rs.getInt("id") %>">修改</a>

  </td>    

  <%    

  out.print("</tr>");   

   }     

   else {

out.print("<h4>不存在此条件的信息！</h4>");

}

   %>

  </table>

  <br>

  <br>

  <h4 class="jvzhong"> <a href=show.jsp>返回查询页面</a> </h4>

   <%     

    if(rs != null)    

    {       

    rs.close();        

    rs = null;    }        

    if(stat != null)    

    {        

    stat.close();        

    stat = null;    

    }        

    if(conn != null)    

    {        

    conn.close();        

    conn = null;    

    }    

    %>    

  

  </body>

</html>

Show.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>

<%

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

  <head>

    <base href="<%=basePath%>">

    

    <title>My JSP 'show.jsp' starting page</title>

    

<meta http-equiv="pragma" content="no-cache">

<meta http-equiv="cache-control" content="no-cache">

<meta http-equiv="expires" content="0">    

<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

<meta http-equiv="description" content="This is my page">

<!--

<link rel="stylesheet" type="text/css" href="styles.css">

-->

 

  </head>

  <style> 

  .jvzhong{text-align:center}

  </style> 

  <body>

  <%

  response.setCharacterEncoding("utf-8");

  request.setCharacterEncoding("utf-8");

  String id=request.getParameter("id");

  String name=request.getParameter("name");

  String jiage=request.getParameter("jiage");

  Connection conn=null;

  Statement stat=null;

  ResultSet rs=null;

  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

  String url ="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=caidan";     

  String user = "sa";     

  String password = "123";     

  conn = DriverManager.getConnection(url,user,password);   

  stat = conn.createStatement();     

  rs = stat.executeQuery("select * from caidan");

 

  

   %>

   <br>

    <h2 class="jvzhong">菜单信息</h2>  <hr>    

    <br> 

  <table align="center" width="450" border="100" cellSpacing=3 style="font-size:15pt;border:dashed 1pt">

  <tr>

  <td>序号</td>

  <td>菜品</td>

  <td>价格</td>

  </tr>

  <%

  while(rs.next())

  {

  out.print("<tr>");    

  out.print("<td>" + rs.getInt("id") + "</td>");    

  out.print("<td>" + rs.getString("name") + "</td>");    

  out.print("<td>" + rs.getString("jiage") + "</td>");

  %>    

  <td>

  <a href="servlet/delete?id=<%=rs.getInt("id") %>">删除</a>

  </td>    

  <td>

  <a href="update.jsp?id=<%=rs.getInt("id") %>">修改</a>

  </td>    

  <%    

  out.print("</tr>");   

   }     

   %>

  </table>

  <br>

   <form class="jvzhong" action="select.jsp" method="post">   

   <h3>按菜名查询:<input type="text" name="name" value="" title="菜名不能为空"></input>    

   <input type="submit" value="查询" /></h3>    

   <br>    

   </form>

   <br><h3 class="jvzhong"><a href=add.html>返回添加菜单信息页面</a></h3> <br>      

    <%     

    if(rs != null)    

    {       

    rs.close();        

    rs = null;    }        

    if(stat != null)    

    {        

    stat.close();        

    stat = null;    

    }        

    if(conn != null)    

    {        

    conn.close();        

    conn = null;    

    }    

    %>    

 

  </body>

</html>

Update.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

<%@page import="java.sql.*"%> 

<%

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

  <head>

    <base href="<%=basePath%>">

    

    <title>My JSP 'update.jsp' starting page</title>

    

<meta http-equiv="pragma" content="no-cache">

<meta http-equiv="cache-control" content="no-cache">

<meta http-equiv="expires" content="0">    

<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

<meta http-equiv="description" content="This is my page">

<!--

<link rel="stylesheet" type="text/css" href="styles.css">

-->

 

  </head>

  <style> 

  .jvzhong{text-align:center}

  </style> 

  <body>

    <%

    response.setCharacterEncoding("UTF-8");

    request.setCharacterEncoding("utf-8");

   String id=request.getParameter("id");

   Connection conn=null;

   Statement stat=null;

   ResultSet rs=null;

   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

   String url ="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=caidan";     

   String user = "sa";     

   String password = "123";     

   conn = DriverManager.getConnection(url,user,password);

   stat = conn.createStatement();     

    rs = stat.executeQuery("select * from caidan where id="+id+"");  

    %>

    <br>

    <h2 class="jvzhong">菜谱信息</h2>

    <hr>

    <br>

    <h3 class="jvzhong">要修改的菜谱如下</h3>

    <table align="center" width="450" border="100" cellSpacing=1 style="font-size: 15pt; border: dashed 1pt">

    <tr>

       <td>序号</td>

       <td>菜名</td>

       <td>价格</td>

    </tr>

    <%

       while(rs.next())

       {

        out.print("<tr>");

        out.print("<td>"+rs.getInt("id")+"</td>");

        out.print("<td>"+rs.getString("name")+"</td>");

        out.print("<td>"+rs.getString("jiage")+"</td>");

        out.print("</tr>");

       

     %>

    </table>

    <br>

    <br>

    <h3 class="jvzhong">将菜单信息更改为：</h3>

    <form class="jvzhong" action="servlet/update" method="post">

     <h4 class="jvzhong"> 

          序号：

     <input type="text" name="id" value="<%=rs.getInt("id")%>" title="学号不能改变" readonly="readonly"></input>

     <br>

     </h4>

     <h4 class="jvzhong"> 

          菜名：

     <input type="text" name="name"  title="菜名不能为空"></input>

     <br>

     </h4>

      <h4 class="jvzhong"> 

          价格：

     <input type="text" name="jiage"  title="价格不能为空"></input>

     <br>

     </h4>

     <input type="submit" value="修改" />

    </form>

    <h3 class="jvzhong">

    <a href=add.jsp>返回添加信息页面</a>

    </h3>

    <h3 class="jvzhong">

    <a href=show.jsp>返回信息查询页面</a>

    </h3>

    <%

    }

    %>

    <%

    if (rs != null)

    {

    rs.close();

    rs = null;

    }

    if (stat != null)

     {

     stat.close();

     stat = null;

     }

     if (conn != null) {

     conn.close();

     conn = null;

     }

     %>

 

  </body>

</html>

五、心得体会：

一开始做实验的时候，还不是非常熟悉servlet，但是慢慢的通过自己的研究，终于明白了servlet的运行原理，原来是把原来的jsp文件进行拆分，将数据库相关操作放入到servlet中，顿时醍醐灌顶，会当凌绝顶，一览众山小。