利用数据库实现对显示全部学生的信息和依照sid进行删除

本文链接：https://blog.csdn.net/weixin_45807622/article/details/109075672

记得要导入MySQL的jar
创建index.jsp文件利用超链接

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


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    
    
    <title>My JSP 'index.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>
  
  <body>
    <a href="/Lesson9/ListStudentServlet"> 查询全部学生</a>
  </body>
</html>

展示全部学生的信息
创建list.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib  uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    
    
    <title>My JSP 'list.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>
  
  <body>
     <table border="1">
        <tr>
        <td>学号</td>
         <td>姓名</td>
          <td>年龄</td>
          <td>删除</td>
        </tr>
        <c:forEach items="${students}" var="student">
           <tr>
               <td>${student.sid}</td>
               <td>${student.sname}</td>
               <td>${student.sage}</td>
               <td><a href="javascript:delStu(${student.sid })">删除</a></td>
           </tr>
        </c:forEach>
        
     </table>
     <script type="text/javascript">
        function delStu(sid){
        	if(confirm("确定删除这个学生吗？")){
        		location.href="/Lesson9/RemoveStudentBySidServlet?sid="+sid;
        	}
        	
        }
     </script>
     
  </body>
</html>

创建实体student

package com.gg.entity;

public class Student {
       private String sid;
       private String sname;
       private Integer sage;
       
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Student(String sid, String sname, Integer sage) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.sage = sage;
	}
	
	@Override
	public String toString() {
		return "Student [sid=" + sid + ", sname=" + sname + ", sage=" + sage + "]";
	}
	public String getSid() {
		return sid;
	}
	public void setSid(String sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public Integer getSage() {
		return sage;
	}
	public void setSage(Integer sage) {
		this.sage = sage;
	}
       
}

工具包DBHelper.java

package com.gg.helper;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBHelper {
	private static Connection con=null;
	private static String user="root";
	private static String password="123456";
	private static String url="jdbc:mysql:///class1";
	private static String className="com.mysql.jdbc.Driver";
	
	
	public static Connection getConnection(String dbName) {
		url="dbc:mysql:///dbName";
		try {
			//加载驱动
			Class.forName(className);
			con=DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
	public static Connection getConnection() {
		try {
			Class.forName(className);
			con=DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
		
	}
}

创建 ListStudentServlet的servlet文件
查询全部学生的信息

package com.gg.controller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import com.gg.entity.Student;
import com.gg.helper.DBHelper;

public class ListStudentServlet extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		List<Student>  students=new ArrayList<Student>();
		String sql="select * from student";
		//
		Connection con=DBHelper.getConnection();
		//
		try {
			PreparedStatement pstmt=con.prepareStatement(sql);
		    ResultSet rs=	pstmt.executeQuery();
		    while(rs.next()) {
		    	Student stu=new Student();
		    	stu.setSid(rs.getString("sid"));
		    	stu.setSname(rs.getString("sname"));
		    	stu.setSage(rs.getInt("sage"));
		    	students.add(stu);
		    	
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		request.getSession().setAttribute("students", students);
		response.sendRedirect("list.jsp");
	}


}

删除数据的servlet

package com.gg.controller;

import java.io.IOException;
import java.sql.Connection;
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;

import com.gg.helper.DBHelper;

public class RemoveStudentBySidServlet extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String  sid=request.getParameter("sid");
		String sql="delete from student where sid=?";
		Connection con=DBHelper.getConnection();
		try {
			PreparedStatement pstmt=con.prepareStatement(sql);
			pstmt.setString(1, sid);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		response.sendRedirect("ListStudentServlet");
		
	}

	

}