基于角色案例简单的增删改查

最新推荐文章于 2023-12-19 15:45:40 发布

徐州蔡徐坤

最新推荐文章于 2023-12-19 15:45:40 发布

阅读量785

点赞数

分类专栏：开发常用知识点文章标签： CURD JDBC c3p0

本文链接：https://blog.youkuaiyun.com/qq_29864051/article/details/88283807

版权

开发常用知识点专栏收录该内容

28 篇文章

订阅专栏

系统的主要架构如下：数据库工具类是整合过的mysql数据库类，同时将事务的代码封装到DBUtil.java里面。数据库连接使用了C3P0数据库连接池，减少当频繁访问数据库时候的数据库打开关闭耗费的时间。因此，当连接对象从数据库连接池中获取到一个连接使用完毕后，执行close()方法并不是关闭数据库，而是将数据库连接放入到数据库连接池中。
在这里插入图片描述
数据库内容

初始页面

查询所有的角色信息
ListRoleServlet.java

package com.servlet;

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

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

import com.role.Role;
import com.util.DBUtil;

@WebServlet("/ListRoleServlet")
public class ListRoleServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
/*
	思路：1.创建数据库连接
				2.查询sql
				3.执行查询，放入到结果集中
				4.将结果集中的结果遍历赋值给对象
				5.将对象集合放置到request域中，用于前台界面拿值
*/
		DBUtil db=new DBUtil();
		String sql="select id,role_code,role_name from role";
		
		ResultSet rs=null;
		List<Role> roles=new ArrayList<Role>();//用来存放角色对象
		try {
			rs=db.executeQuery(sql, null);//结果集接收从数据库查询的记录
			while(rs!=null&&rs.next()) {
				Role role=new Role();//要在循环里面创建对象，这样每循环一次就会创建一个对象
				role.setId(rs.getString("id"));//根据rs.getString("参数1”），给role角色对应参数赋值
				role.setRoleCode(rs.getString("role_code"));
				role.setRoleName(rs.getString("role_name"));
				roles.add(role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			db.close();//使用完毕后将数据库连接放回到数据库连接池中
		}
		request.setAttribute("roles", roles);//将roles放入request域中，用于前台拿值
		request.getRequestDispatcher("listRole.jsp").forward(request, response);
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

listRole.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Role List</title>
</head>
<body>
	<table border="2" width="800" height="500" align="center" cellpadding="3" cellspacing="3">
		<tr >
			<td>ID</td>
			<td>角色编号</td>
			<td>角色名称</td>
			<td>操作</td>
		</tr>
		<!-- roles要从request域中取值，要使用el表达式
		这里需要使用forEach语句，要先引入jstl标签库
		语法；<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core">
	 -->
		<c:forEach var="r" items="${roles }">
			<tr>
				<td>${r.id }</td>
				<td>${r.roleCode }</td>
				<td>${r.roleName }</td>
				<td>
					<a href="${pageContext.request.contextPath}/GetRoleServlet?id=${r.id}">修改</a>
					<a href="${pageContext.request.contextPath}/DeleteRoleServlet?id=${r.id}">删除</a>
				</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

运行结果:
在这里插入图片描述
接下来点击删除，超链接到删除的DeleteRoleServlet.java

package com.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import com.util.DBUtil;

@WebServlet("/DeleteRoleServlet")
public class DeleteRoleServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String roleId=request.getParameter("id");
		DBUtil db=new DBUtil();
		String sql="delete from role where id=?";
		List<Object> param=new ArrayList<Object>();
		param.add(roleId);
		
		db.starTransaction();//开启事务
		boolean flag=true;
		try {
			db.executeUpdate(sql, param);
			db.commit();//事务的提交
		} catch (SQLException e) {
			e.printStackTrace();
			db.rollback();//数据操作异常时的事务回滚
			flag=false;
		}finally {
			db.close();
		}
		if(flag) {
			//如果删除成功，重定向到角色查询的servlet重新查询一遍数据库中的role表
			response.sendRedirect("ListRoleServlet");
			System.out.println("删除成功");
		}else {
			System.out.println("删除失败");
		}
	}

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

}

修改操作
这个稍微复杂一点，因此先理清业务逻辑：
点击修改->根据传过来的id，去数据库中拿到这个角色的信息->把信息放到域中，前台页面拿到servlet传过来的值->编辑页面的servlet再根据name去拿值，执行update操作->最后还是要重定向到角色信息显示页面的servlet，重新查一遍。

根据id查角色信息的servlet
GetRoleServlet.java

package com.servlet;

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

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

import com.role.Role;
import com.util.DBUtil;


@WebServlet("/GetRoleServlet")
public class GetRoleServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String roleId=request.getParameter("id");
		DBUtil db=new DBUtil();
		String sql="select id,role_code,role_name from role where id=?";
		List<Object> param=new ArrayList<Object>();
		param.add(roleId);
		ResultSet rs=null;
		Role role=new Role();//因为修改只涉及到一个角色，故只需要new一个对象
		try {
			rs=db.executeQuery(sql, param);
			if(rs!=null&&rs.next()) {
				role.setId(rs.getString("id"));
				role.setRoleCode(rs.getString("role_code"));
				role.setRoleName(rs.getString("role_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			db.close();
		}
		request.setAttribute("role", role);//存放到request域中，前台从中拿值
		request.getRequestDispatcher("editRole.jsp").forward(request,response);
		//转发到editRole.jsp,同时将参数传递过去
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

编辑页面
在这里插入图片描述
editRole.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>角色编辑</title>
</head>
<body>
	<form action="${pageContext.request.contextPath}/EditRoleServlet" method="post" align="center">
	<!-- 不希望在页面中显示 ，同时希望能够将参数传递出去，可以使用隐藏域-->
		<input type="hidden" name="id" value="${role.id}">
		角色ID:${role.id }<br>
		角色编号:<input type="text" name="rolecode" value="${role.roleCode}"/>	<br>
		角色名称:<input type="text" name="rolename" value="${role.roleName}"/><br>
		<input type="submit" value="修改">
	</form>
</body>
</html>

编辑角色信息的servlet
EditRoleServlet.java

package com.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import com.util.DBUtil;

/**
 * Servlet implementation class EditRoleServlet
 */
@WebServlet("/EditRoleServlet")
public class EditRoleServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		
		//这是从request域中拿值！！！，参数不要写错
		String roleId=request.getParameter("id");
		String roleCode=request.getParameter("rolecode");
		String roleName=request.getParameter("rolename");
		
		DBUtil db=new DBUtil();
		String sql="update role set role_code=?,role_name=? where id=?";
		List<Object> param=new ArrayList<Object>();
		param.add(roleCode);
		param.add(roleName);
		param.add(roleId);
		
		db.starTransaction();
		boolean flag=true;
		try {
			db.executeUpdate(sql, param);
			db.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			db.rollback();
			flag=false;
		}finally {
			db.close();
		}
		if(flag) {
			//老规矩，还是回到角色信息页面
			response.sendRedirect("ListRoleServlet");
		}else {
			System.out.println("修改失败");
		}
	}

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

}

添加信息页面
在这里插入图片描述
add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加角色</title>
</head>
<body>
	<form action="${pageContext.request.contextPath}/addRoleServlet" method="post" align="center">
	<!-- 不希望在页面中显示 ，同时希望能够将参数传递出去，可以使用隐藏域-->
		<p>请输入要添加的角色信息:</p>
		角色编号:<input type="text" name="rolecode"/>	<br>
		角色名称:<input type="text" name="rolename"/><br>
		<input type="submit" value="添加">
	</form>
</body>
</html>

添加角色的servlet
addRoleServlet.java

package com.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import com.util.DBUtil;

@WebServlet("/addRoleServlet")
public class addRoleServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		String roleCode=request.getParameter("rolecode");
		String roleName=request.getParameter("rolename");
		
		DBUtil db=new DBUtil();
		String sql="insert into role (role_code,role_name) values (?,?)";
		List<Object> param=new ArrayList<Object>();
		param.add(roleCode);
		param.add(roleName);
		
		db.starTransaction();
		boolean flag=true;
		try {
			db.executeUpdate(sql, param);
			db.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			db.rollback();
			flag=false;
		}finally {
			db.close();
		}
		if(true) {
			response.sendRedirect("ListRoleServlet");
		}else{
			System.out.println("添加失败");
		}
	}

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

}

以上。