使用servlet,jdbc将mysql中数据显示在jsp页面中,且实现直接删除数据库数据

 用servlet,jdbc将mysql中数据显示在jsp页面中:

效果如下:

     

 

//包bean.Student

package bean;

public class Student {
	private String sno;
	private String sname;
	private String sex;
	private int age;
	private String sdept;
	public String getSno() {
		return sno;
	}
	public void setSno(String sno) {
		this.sno = sno;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getSdept() {
		return sdept;
	}
	public void setSdept(String sdept) {
		this.sdept = sdept;
	}
	
}

 

 //包dao.DBConnection

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import bean.Student;

public class DBConnection {
	
	/**
	 * 驱动类名称
	 */
	private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
	
	/**
	 * 数据库连接字符串
	 */
	private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/db3";
	
	/**
	 * 数据库用户名
	 */
	private static final String USER_NAME = "root";
	
	/**
	 * 数据库密码
	 */
	private static final String PASSWORD = "123456";
	
	/**
	 * 数据库连接类
	 */
	private static Connection conn;
	
	/**
	 * 数据库操作类
	 */
	private static Statement stmt;
	
	
	
	// 加载驱动
	static{
		try {
			Class.forName(DRIVER_CLASS);
		} catch (Exception e) {
			System.out.println("加载驱动错误");
			System.out.println(e.getMessage());
		}
	}
	
	// 取得连接
	private static Connection getConnection(){
		
		try {
			conn = DriverManager.getConnection(DATABASE_URL, USER_NAME, PASSWORD);
		} catch (Exception e) {
			System.out.println("取得连接错误");
			System.out.println(e.getMessage());
		}
		return conn;
	}
	
	
	public void ExecuteDel(String sql){
		
		try {
			stmt = getConnection().createStatement();
		} catch (Exception e) {
			System.out.println("statement取得错误");
			System.out.println(e.getMessage());
		}
		
		try {
			int rows = stmt.executeUpdate(sql);
			if(rows >= 1){
				System.out.println("成功删除.....");
			} else {
				System.out.println("删除失败.....");
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		
	}
	
	public ArrayList<Student> getStudentList(String sql){
		
		ArrayList<Student> list = new ArrayList<Student>();
		
		// 取得数据库操作对象
		try {
			stmt = getConnection().createStatement();
		} catch (Exception e) {
			System.out.println("statement取得错误");
			System.out.println(e.getMessage());
			return null;
		}
		
		try {
			
			// 查询数据库对象,返回记录集(结果集)
			ResultSet rs = stmt.executeQuery(sql);
			
			// 循环记录集,查看每一行每一列的记录
			while (rs.next()) {
				// 第一列 sno
				String sno = rs.getString(1);
				
				// 第2列 sname
				String sname = rs.getString(2);
				
				// 性别
				String ssex = rs.getString(3);
				
				// 年龄
				int sage = rs.getInt(4);
				
				// 系
				String sdept = rs.getString(5);
				
				Student stu = new Student();
				stu.setSno(sno);
				stu.setSname(sname);
				stu.setAge(sage);
				stu.setSex(ssex);
				stu.setSdept(sdept);
				
				
				list.add(stu);
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return list;
	}
}

 //包servlet.StuServlet

package servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;

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

import bean.Student;

import dao.DBConnection;

public class StuServlet extends HttpServlet {


	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		this.doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		DBConnection db = new DBConnection();
		
		String sql = "select * from Student";
		
		ArrayList<Student> list = db.getStudentList(sql);
		
		request.setAttribute("list", list);
		request.getRequestDispatcher("index.jsp").forward(request, response);
	}

	

}

 

 // DelServlet

package 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 dao.DBConnection;

public class DelServlet extends HttpServlet {

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

	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		
		String sno = request.getParameter("sno");
		
		//在mysql中语句:delete from student where Sno = '3';
		String sql = "delete from student where Sno = ' " + sno + " ' ";
		
		DBConnection db = new DBConnection();
		db.ExecuteDel(sql);
		
		request.getRequestDispatcher("stuservlet").forward(request, response);
		
	}

	
}

 

 // web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 
	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_2_5.xsd">
	
  <servlet>
    <servlet-name>StuServlet</servlet-name>
    <servlet-class>servlet.StuServlet</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>DelServlet</servlet-name>
    <servlet-class>servlet.DelServlet</servlet-class>
  </servlet>


  <servlet-mapping>
    <servlet-name>StuServlet</servlet-name>
    <url-pattern>/stuservlet</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>DelServlet</servlet-name>
    <url-pattern>/delservlet</url-pattern>
  </servlet-mapping>
  
  
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

 

 / / index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>list</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>
  			<td>所在系</td>
  			<td>&nbsp;</td>
  		</tr>
  		
  		<c:forEach items="${list}" var="stu">
  			<tr>
  				<td>${stu.sno }</td>
  				<td>${stu.sname }</td>
  				<td>${stu.sex }</td>
  				<td>${stu.age }</td>
  				<td>${stu.sdept }</td>
  				<td><a href="delservlet?sno=${stu.sno }">删除</a></td>
  			</tr>
  		</c:forEach>
  	</table>
  	
  	
  </body>
</html>


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页