JDBC 删除信息

ListStu:

1）链接删除信息的Servlet：

"<a href='/JDBC01/DropStu?id="+id+"'>删除</a>&nbsp;"

package case01;

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 java.sql.Statement;
import java.util.Date;

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


@WebServlet("/ListStu")
public class ListStu extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    
    public ListStu() {
        super();
        
    }

    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();
        
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        
        // mysql 数据库驱动程序的加载
        try {
            // 加载，固定的语句
            Class.forName("com.mysql.jdbc.Driver");
            out.print("数据库驱动程序加载成功！");
            
            //数据库连接
            String url = "jdbc:mysql://127.0.0.1:3306/test";
            String user = "root";
            String password = "zzgzzg";
            conn = DriverManager.getConnection(url, user, password);
            
            out.print("<br>"+"数据库连接成功！");
            
            // 数据库访问并获取结果
            state = conn.createStatement();
            // sql 语句
            String sql = "select id,name,sex,birthday from student";
            rs = state.executeQuery(sql);
            
            // 画表
            out.print("<table border=1>");
            // 表头
            out.print("<tr>"
                    +"<td>id</td>"
                    +"<td>name</td>"
                    +"<td>sex</td>"
                    +"<td>birthday</td>"
                    +"<td>操作</td>"
                    + "</tr>");
            
            // 循环获取字段数据并输出
            while(rs.next()) {
                int id = rs.getInt(1); // 获取第一个字段,id
                String name = rs.getString("name"); // 获取名字
                String sex = rs.getString("sex");
                Date birthday = rs.getDate("birthday");
                
                // 表内容
                // 利用<a>标签连接删除、修改的Servlet
                out.print("<tr>"
                        +"<td>"+id+"</td>"
                        +"<td>"+name+"</td>"
                        +"<td>"+sex+"</td>"
                        +"<td>"+birthday+"</td>"
                        +"<td>"
                        + "<a href='/JDBC01/DropStu?id="+id+"'>删除</a>&nbsp;"
                        + "<a href='/JDBC01/ListOne?id="+id+"'>修改 </a></td>"
                        + "</tr>");
            }
            
            out.print("</table>");
            out.print("<br>"+"数据库访问成功！");
            
        } catch (ClassNotFoundException e) {
            
            e.printStackTrace();
            out.print("数据库驱动程序加载失败！");
            
        } catch (SQLException e) {
            
            e.printStackTrace();
            out.print("数据库连接失败！");
        }finally {
            // 回收资源
            // 回收 conn
            if(conn!=null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
            // 回收state
            if(state!=null) {
                try {
                    state.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                state = null;
            }
            // 回收 rs
            if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            
        }
            
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doGet(request, response);
    }

}

ListStu Run as （Run on server）效果图：

DropStu:

1）寻找并执行删除删除的 sql 语句：

// 依据id 进行寻找对应位置并删除
            int id = Integer.parseInt(request.getParameter("id"));
            String sql="delete from student where id=?";
            
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.executeUpdate();

2）重定向：

//重定向
            response.sendRedirect("ListStu"); // 相对位置再次访问ListStu

package case01;

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 java.sql.Statement;
import java.util.Date;

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


@WebServlet("/DropStu")
public class DropStu extends HttpServlet {
    private static final long serialVersionUID = 1L;
       

    public DropStu() {
        super();
        
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();
        
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        
        // mysql 数据库驱动程序的加载
        try {
            // 固定的语句
            Class.forName("com.mysql.jdbc.Driver");
            out.print("数据库驱动程序加载成功！");
            
            //数据库连接
            String url = "jdbc:mysql://127.0.0.1:3306/test";
            String user = "root";
            String password = "zzgzzg";
            
            conn = DriverManager.getConnection(url, user, password);
            // 依据id 进行寻找对应位置并删除
            int id = Integer.parseInt(request.getParameter("id"));
            String sql="delete from student where id=?";
            
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setInt(1, id);
            prep.executeUpdate();
        
            //重定向
            response.sendRedirect("ListStu"); // 相对位置再次访问ListStu
            

        }catch(Exception e) {
            e.printStackTrace();
            out.print("<br>"+"数据库访问失败！");
        }finally {
            // 回收资源
            // 回收 conn
            if(conn!=null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
            // 回收state
            if(state!=null) {
                try {
                    state.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                state = null;
            }
            // 回收 rs
            if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            
        }
    }


    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doGet(request, response);
    }

}