当前位置：Gxlcms > 数据库问题 > 使用JDBC实现对数据的增删改查

使用JDBC实现对数据的增删改查

时间：2021-07-01 10:21:17 帮助过：31人阅读

import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class ListEmpServlet extends HttpServlet{ protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException{ //保证正确读取Post提交来的中文 request.setCharacterEncoding("utf-8"); //保证正确输出中文 response.setContentType("text/html;charset=utf-8"); //获取输出流对象，并输出信息 PrintWriter out=response.getWriter(); //获取表单提交的数据 //String name= request.getParameter("name"); //double salary = Double.valueOf(request.getParameter("salary")); //int age = Integer.valueOf(request.getParameter("age")); //将数据插入到数据库t_emp表中 Connection conn = null; PreparedStatement stat=null; try { //1、加载MySQL的JDBC的驱动 Class.forName("com.mysql.jdbc.Driver"); //2、取得连接的URL，能访问MySQL数据库的用户名和密码 String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8"; String username = "root"; String password = "123456"; //3、创建与MySQL数据库的连接类的实例 conn=DriverManager.getConnection(url,username,password); stat=conn.prepareStatement("select * from t_emp"); ResultSet rs = stat.executeQuery(); //4、输出表格之前的页面标记 out.println("<HTML>"); out.println("<HEAD></HEAD>"); out.println("<BODY style=‘font-size:30px‘>"); out.println("<table border=‘1‘ cellpadding=‘0‘"+ "cellspacing=‘0‘ width=‘600px‘"); out.println("<tr><caption>员工信息列表</caption></tr>"); //定义表格标题 out.println("<tr><td>编号</td><td>姓名</td><td>薪水</td>"+ "<td>年龄</td><td> 操作</td></tr>"); //3、遍历结果集，以表格形式输出数据 while(rs.next()){ int id=rs.getInt("id"); String name=rs.getString("name"); double salary=rs.getDouble("salary"); int age=rs.getInt("age"); out.println("<tr>"); out.println("<td>"+id+"</td>"); out.println("<td>"+name+"</td>"); out.println("<td>"+salary+"</td>"); out.println("<td>"+age+"</td>"); out.println("<td><a href=‘delete?id="+id+"‘ "+ "onclick=\"return confirm(‘是否确定删除"+name+"‘);\">删除</a>"); out.println("<a href=‘load?id="+id+"‘>修改</a></td>"); out.println("</tr>"); } out.println("</table>"); out.println("<a href=‘addEmp.jsp‘ style=‘font-size:20px‘>>>添加员工信息</a></td>"); out.println("</body></HTML>"); } catch (Exception e) { e.printStackTrace(); out.print("系统繁忙，稍后重试"); }finally{ if(stat!=null){ try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }

Step2： 新建DeleteEmpServlet类，处理删除动作
获取请求参数值id，构建删除语句并执行
代码如下：

package web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class DeleteEmpServlet extends HttpServlet{
    private static final long serialVersionUID = 1L;

    protected void service(HttpServletRequest request,
            HttpServletResponse response)
            throws ServletException,IOException{
        //保证正确读取Post提交来的中文
        request.setCharacterEncoding("utf-8");
        //保证正确输出中文
        response.setContentType("text/html;charset=utf-8");
        //获取输出流对象，并输出信息
        PrintWriter out=response.getWriter();
        //获取删除的编号
        int id =Integer.parseInt(request.getParameter("id"));
        //连接数据库，执行删除操作
        Connection conn = null;
        PreparedStatement stmt=null;
        try {
            //1、加载MySQL的JDBC的驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、取得连接的URL，能访问MySQL数据库的用户名和密码
            String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
            String username = "root";
            String password = "123456"; 
            //3、创建与MySQL数据库的连接类的实例
            conn=DriverManager.getConnection(url,username,password);
            stmt=conn.prepareStatement("delete from t_emp where id=?");//删除的sql语句
            stmt.setInt(1, id);
            stmt.executeUpdate();// 用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE
            response.sendRedirect("list");  //重定向到查询页面
        } catch (Exception e) {
            e.printStackTrace();
            out.print("系统繁忙，稍后重试");
        }finally{
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }

    }
}

Step3 : 新建LoadEmpServlet.java文件
获取请求参数值id，查询数据并构建表单显示数据
代码如下：

package web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class LoadEmpServlet extends HttpServlet{
    protected void service(HttpServletRequest request,
            HttpServletResponse response)
            throws ServletException,IOException{
        //保证正确读取Post提交来的中文
        request.setCharacterEncoding("utf-8");
        //保证正确输出中文
        response.setContentType("text/html;charset=utf-8");
        //获取输出流对象，并输出信息
        PrintWriter out=response.getWriter();
        //获取请求参数值id
        int id = Integer.parseInt(request.getParameter("id"));
        //根据ID查询数据库获取员工信息
        Connection conn = null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            //1、加载MySQL的JDBC的驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、取得连接的URL，能访问MySQL数据库的用户名和密码
            String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
            String username = "root";
            String password = "123456"; 
            //3、创建与MySQL数据库的连接类的实例
            conn=DriverManager.getConnection(url,username,password);
            stmt=conn.prepareStatement("select * from t_emp where id=?");
            stmt.setInt(1, id);
            rs=stmt.executeQuery();
            out.println("<html><head></head><body style=‘font-size:30px‘>");
            if(rs.next()){
                String name=rs.getString("name");
                double salary=rs.getDouble("salary");
                int age=rs.getInt("age");
                out.println("<form action=‘modify‘ method=‘post‘>");//输出表单
                out.println("编号："+ id + "<br>");
                out.println("<input type=‘hidden‘ name=‘id‘ value=‘"+id+"‘/><br>");
                out.println("姓名：<input name=‘name‘ value=‘"+name+"‘/><br>");
                out.println("薪水：<input name=‘salary‘ value=‘"+salary+"‘/><br>");
                out.println("年龄：<input name=‘age‘ value=‘"+age+"‘/><br>");
                out.println("<input type=‘submit‘ value=‘修改‘/>");
                out.println("</form>");
                out.println("</body></html>");
                }
        } catch (Exception e) {
            e.printStackTrace();
            out.print("系统繁忙，稍后重试");
        }finally{
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }

    }
}

Step4: 新建ModifyEmpServlet.java文件
修改数据表中的数据
代码如下：

package web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class ModifyEmpServlet extends HttpServlet{
    protected void service(HttpServletRequest request,
            HttpServletResponse response)
            throws ServletException,IOException{
        //保证正确读取Post提交来的中文
        request.setCharacterEncoding("utf-8");
        //保证正确输出中文
        response.setContentType("text/html;charset=utf-8");
        //获取输出流对象，并输出信息
        PrintWriter out=response.getWriter();
        //1、获取请求参数值
        int id=Integer.parseInt(request.getParameter("id"));
        String name= request.getParameter("name");
        double salary = Double.valueOf(request.getParameter("salary"));
        int age = Integer.valueOf(request.getParameter("age"));
        //2、数据库数据更新
        Connection conn = null;
        PreparedStatement stmt=null;
        try {
            //1、加载MySQL的JDBC的驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、取得连接的URL，能访问MySQL数据库的用户名和密码
            String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
            String username = "root";
            String password = "123456"; 
            //3、创建与MySQL数据库的连接类的实例
            conn=DriverManager.getConnection(url,username,password);
            stmt=conn.prepareStatement("update t_emp set name=?,"+
                    "salary=?,age=? where id=?"); //数据更新SQL
            stmt.setString(1, name);
            stmt.setDouble(2, salary);
            stmt.setInt(3, age);
            stmt.setInt(4, id);
            stmt.executeUpdate();
            response.sendRedirect("list");//从定向到查询界面
        } catch (Exception e) {
            e.printStackTrace();
            out.print("系统繁忙，稍后重试");
        }finally{
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }

    }
}

Step5： 新建一个addEmp.jsp文件，实现添加员工信息操作
代码如下：

<%@ page language="java" import="java.util.*" 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>Servlet program</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">
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->
  </head>

  <body>
    <form action="add" method="post"> 
        <fieldset>
            <legend>添加员工</legend>
            姓名：<input name="name"/><br>
            薪水：<input name="salary"/><br>
            年龄：<input name="age"/><br>
            <input type="submit" value="添加">
        </fieldset>
    </form>
  </body>
</html>

Step6： 修改web.xml文件实现配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" 
    xmlns="http://java.sun.com/xml/ns/javaee" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
  <display-name></display-name> 
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
 <!-- 添加员工信息 -->
  <servlet>
    <servlet-name>addEmpServlet</servlet-name>
    <servlet-class>web.AddEmpServlet</servlet-class>
  </servlet>
  <!-- 删除员工信息 -->
  <servlet>
    <servlet-name>deleteEmpServlet</servlet-name>
    <servlet-class>web.DeleteEmpServlet</servlet-class>
  </servlet>
  <!-- 加载员工信息 -->
  <servlet>
    <servlet-name>loadEmpServlet</servlet-name>
    <servlet-class>web.LoadEmpServlet</servlet-class>
  </servlet>
   <!-- 更新员工信息 -->
  <servlet>
    <servlet-name>modifyEmpServlet</servlet-name>
    <servlet-class>web.ModifyEmpServlet</servlet-class>
  </servlet>
  <!-- 查询员工信息 -->
  <servlet>
    <servlet-name>listEmpServlet</servlet-name>
    <servlet-class>web.ListEmpServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>addEmpServlet</servlet-name>
    <url-pattern>/add</url-pattern>  <!-- 地址栏必须是addEmp.jsp，提交表单后出现在地址栏的地址 -->
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>deleteEmpServlet</servlet-name>
    <url-pattern>/delete</url-pattern>  
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>loadEmpServlet</servlet-name>
    <url-pattern>/load</url-pattern>  
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>modifyEmpServlet</servlet-name>
    <url-pattern>/modify</url-pattern>  
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>listEmpServlet</servlet-name>
    <url-pattern>/list</url-pattern>  
  </servlet-mapping>
</web-app>

Step7 :加载到Tomcat服务器中，在浏览器运行
页面见第一个图。

这样，一个简单的增删改查操作就完成了。
如果发现不妥或疑问之处，麻烦提出。这段时间正在学习这方面的知识，欢迎交流。

版权声明：本文为博主原创文章，未经博主允许不得转载。

使用JDBC实现对数据的增删改查

标签：myeclipse   jdbc   servlet   员工信息管理   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！