使用JSP内置对象完成雇员表的增删改查等操作

最新推荐文章于 2022-11-06 17:10:11 发布

@一世情缘

最新推荐文章于 2022-11-06 17:10:11 发布

阅读量2.7k

点赞数 16

分类专栏： Javaweb 文章标签： JSP的增删改查 JSP连接数据库

本文链接：https://blog.csdn.net/qq_41443616/article/details/88736686

版权

Javaweb 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

学了jsp和数据库的相关内容，做了一个小练习，发现对自己学的知识还是有很好的巩固效果的，直接上代码

页面首页 – index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>首页</title>
<script type="text/javascript">
    function changeColor(obj,color){
     obj.bgcolor = color;
    }
</script>
</head>
<body>
<%!
    public static final String URL = "index.jsp";
    // 定义数据库驱动程序
    public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
    // 数据库连接地址
    public static final String DBURL = "jdbc:mysql://localhost/book?serverTimezone=UTC&useSSL=false";
    // 数据库连接用户名
    public static final String DBUSER = "root";
    // 数据库连接密码
    public static final String DBPASS = "root";
%>
<%
    // 声明数据库连接对象
    Connection conn = null;
    // 声明数据库操作
    PreparedStatement pstmt = null;
    // 声明数据库结果集
    ResultSet rs = null;
%>
<%
    // 为当前所在的页，默认为第一页
    int currentPage = 1;
    // 每次显示的记录数
    int lineSize = 5;
    // 记录总页数
    int pageCount = 0;
%>
<center>
 <h1>雇员列表</h1>
 <h3><a href="insert.jsp?cp=<%=currentPage%>&ls=<%=lineSize%>">增加新雇员</a></h3>
 <h3><a href="select.jsp">查询雇员</a></h3> 
</center>
<table border="1" cellpadding="5" cellspacing="0" bgcolor="F2F2F2" width="100%">
 <tr onMousemOve="changColor(this,'White')" onMouseOut="changeColor(this,'F2F2F2')">
  <td align="center"><span>雇员编号</span></td>
  <td align="center"><span>雇员姓名</span></td>
  <td align="center"><span>雇员工作</span></td>
  <td align="center"><span>雇佣日期</span></td>
  <td align="center"><span>基本工资</span></td>
  <td align="center"><span>奖金</span></td>
  <td align="center" colspan="2"><span>操作</span></td>
 </tr>
<%
 try{
  //数据库驱动程序加载
  Class.forName(DBDRIVER);
  // 取得数据库连接
  conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
  // 使用普通查询
  String sql = "SELECT * FROM emp_01 ";
  pstmt = conn.prepareStatement(sql);
  rs = pstmt.executeQuery();
  // 将光标移动到最后一行
  rs.last();
  // 返回当前光标指向的行数的值,也就是记录的总数
  int size = rs.getRow();
  // 计算总页数
  pageCount = (size / lineSize == 0) ? (size / lineSize) : (size / lineSize + 1);
  // 获取当前页的值
  String tmp = request.getParameter("currentPage");
  if (tmp == null){
   tmp = "1";
  }
  currentPage = Integer.parseInt(tmp);
  if (currentPage >= pageCount){
   currentPage = pageCount;
  }
  //   把结果集指针调整到当前页应该显示的记录的开始.
  boolean flag = rs.absolute((currentPage-1)*lineSize+1); 
  int count = 0;
     do{
      if (count > lineSize){
       break;
      }
      String empno = rs.getString(1);
      String ename = rs.getString(2);
      String job = rs.getString(3);
      Date hiredate = rs.getDate(4);
      Double sal = rs.getDouble(5);
      Double comm = rs.getDouble(6);
      count++;
%>
    <tR onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td align="center"><span><%=empno%></span></td>
  <td align="center"><span><%=ename%></span></td>
  <td align="center"><span><%=job%></span></td>
  <td align="center"><span><%=hiredate%></span></td>
  <td align="center"><span><%=sal%></span></td>
  <td align="center"><span><%=comm%></span></td>
  <td align="center"><span><a href="update.jsp?empno=<%=empno%>&cp=<%=currentPage%>&ls=<%=lineSize%>">修改</a></span></td>
  <td align="center"><span><a href="delete.jsp?empno=<%=empno%>&cp=<%=currentPage%>&ls=<%=lineSize%>">删除</span></td>
 </tR>
<%
 }while(rs.next());
 }catch(Exception e){
  System.out.println(e);// 向tomcat中打印
 }finally{// 程序同意出口
  // 关闭结果集
  rs.close();
     //关闭操作
     pstmt.close();
     //关闭连接
     conn.close();
 }
%>
</table>
<center>
<a href="index.jsp?currentPage=1">首页</a>
 <%if (currentPage - 1 > 0){
%>
<a href="index.jsp?currentPage=<%=currentPage - 1%>">上一页</a>
<%
}else {
%>
<span>上一页</span>
<%
}
%>
<%if (currentPage != pageCount){
%>
 <a href="index.jsp?currentPage=<%=currentPage + 1%>">下一页</a>
<%
}else {
%>
<span>下一页</span>
<%
}
%>
<a href="index.jsp?currentPage=<%=pageCount %>">尾页</a>
<br>
<form action="" method="post">
 当前页：<input type="button" value="<%= currentPage%>">
 总页数：<input type="button" value="<%=pageCount%>">
   跳转到第<input type="text" name="currentPage" size="4">页
  <input type="submit" name="submit" value="跳转">
</form> 
</center>
</body>
</html>

添加雇员 – insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加</title>
</head>
<body>
<% // 乱码解决
 request.setCharacterEncoding("UTF-8") ;
%>
 <script language="javascript" type="text/javascript">
  function changeColor(obj,color){
   obj.bgColor = color ;
  }
 </script>
<form action="insert_dao.jsp" method="post">
<table border="1" width="100%" cellpadding="5" cellspacing="0" bgcolor="F2F2F2">
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td colspan="3">
   <h1>增加新雇员</h1>  </td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td><font size="2">雇员编号：</font></td>
  <td><input type="text" name="empno" size="4" maxlength="4"></td>
  <td><span id="empno_msg"><font color="RED">*</font></span></td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td><font size="2">雇员姓名：</font></td>
  <td><input type="text" name="ename"></td>
  <td><span id="ename_msg"><font color="RED">*</font></span></td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td><font size="2">雇员工作：</font></td>
  <td><input type="text" name="job"></td>
  <td><span id="job_msg"><font color="RED">*</font></span></td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td><font size="2">雇佣日期：</font></td>
  <td><input type="date" name="hiredate" size="15" maxlength="15"></td>
  <td><span id="hiredate_msg"><font color="RED">*</font></span></td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td><font size="2">基本工资：</font></td>
  <td><input type="text" name="sal"></td>
  <td><span id="sal_msg"><font color="RED">*</font></span></td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td><font size="2">雇佣奖金：</font></td>
  <td><input type="text" name="comm"></td>
  <td><span id="comm_msg"><font color="RED">*</font></span></td>
 </tr>
 <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
  <td colspan="3">
   <input type="hidden" name="cp" value="<%=request.getParameter("cp")%>">
   <input type="hidden" name="ls" value="<%=request.getParameter("ls")%>">
   <input type="submit" value="添加">
   <input type="reset" value="重置"> 
  </td>
 </tr>
</table>
<center><a href="index.jsp"><font size="5" color="red">返回首页</font></a></center>
</body>
</html>

添加雇员到数据库-- insert_dao.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.text.*,java.util.Date" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加操作-数据库</title>
</head>
<body>
<%
 request.setCharacterEncoding("UTF-8");
%>
<%
// 获取表单输入的雇员的相关信息
 int empno = Integer.parseInt(request.getParameter("empno"));
 String ename = request.getParameter("ename");
 String job = request.getParameter("job");
 Date hiredate = new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("hiredate"));
 double sal = Double.parseDouble(request.getParameter("sal"));
 double comm = Double.parseDouble(request.getParameter("comm"));
%>
<%!
 // 定义数据库驱动程序
 public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
 // 数据库连接地址
 public static final String DBURL = "jdbc:mysql://localhost/book?serverTimezone=UTC&useSSL=false";
 // 数据库连接用户名
 public static final String DBUSER = "root";
 // 数据库连接密码
 public static final String DBPASS = "root";
%>
<%
 Connection conn = null;
 PreparedStatement pstmt = null;
%>
<%
 String msg = "雇员增加失败！";
 // 加载驱动
 Class.forName(DBDRIVER);
 // 建立连接
 conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
 String sql = "insert into emp_01 values (?,?,?,?,?,?)";
 pstmt = conn.prepareStatement(sql);
 // 添加雇员信息
 pstmt.setInt(1,empno);
 pstmt.setString(2,ename);
 pstmt.setString(3,job) ;
 pstmt.setDate(4,new java.sql.Date(hiredate.getTime()));
 pstmt.setDouble(5,sal);
 pstmt.setDouble(6,comm);
 if(pstmt.executeUpdate()>0){
  msg = "雇员增加成功！" ;
 }
%>
<script language="javascript">
 alert("<%=msg%>") ;
 window.location = "index.jsp?cp=<%=request.getParameter("cp")%>&ls=<%=request.getParameter("ls")%>" ;
</script>
</body>
</html>

查询雇员 – select.jsp

使用了模糊查询 – 雇员姓名和雇员工作

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.util.Date,java.text.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查询</title>
</head>
<body>
<% request.setCharacterEncoding("UTF-8");// 解决乱码 %>
<%
 try{
  String keyWord = request.getParameter("kw");// 接收查询关键字
  if (keyWord == null){
   keyWord = "";
  }
%>
<%!
  public static final String URL = "index.jsp";
  // 定义数据库驱动程序
  public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
  // 数据库连接地址
  public static final String DBURL = "jdbc:mysql://localhost/book?serverTimezone=UTC&useSSL=false";
  // 数据库连接用户名
  public static final String DBUSER = "root";
  // 数据库连接密码
  public static final String DBPASS = "root";
%>
<%
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
 
%>
<%  
  // 加载驱动
  Class.forName(DBDRIVER);
  // 建立连接
  conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
  // 模糊查询
  String sql = "select * from emp_01 where ename like ? or job like ? ";
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, "%" + keyWord + "%");
  pstmt.setString(2, "%" + keyWord + "%");
  rs = pstmt.executeQuery();// 执行查询操作
%>
<center>
 <form action="select.jsp" method="post">
  请输入查询关键字<input type="text" name="kw">
  <input type="submit" value="查询">
 </form>
 <table border="1" cellpadding="5" cellspacing="0" bgcolor="F2F2F2" width="100%">
  <tr onMousemOve="changColor(this,'White')" onMouseOut="changeColor(this,'F2F2F2')">
   <td align="center"><span>雇员编号</span></td>
   <td align="center"><span>雇员姓名</span></td>
   <td align="center"><span>雇员工作</span></td>
   <td align="center"><span>雇佣日期</span></td>
   <td align="center"><span>基本工资</span></td>
   <td align="center"><span>雇佣奖金</span></td>
  </tr>
<%
  while (rs.next()){
   int empno = rs.getInt(1);
   String ename = rs.getString(2);
   String job = rs.getString(3);
   Date hiredate = rs.getDate(4);
   Double sal = rs.getDouble(5);
   Double comm = rs.getDouble(6);
%>
  <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
   <td align="center"><span><%=empno%></span></td>
   <td align="center"><span><%=ename%></span></td>
   <td align="center"><span><%=job%></span></td>
   <td align="center"><span><%=hiredate%></span></td>
   <td align="center"><span><%=sal%></span></td>
   <td align="center"><span><%=comm%></span></td>
  </tr>
<%
  }
%>
 </table>
<a href="index.jsp"><font size="5" color="red">返回首页</font></a>
</center>
<%
 }catch(Exception e){
  e.printStackTrace();
 }
%>
</body>
</html>

修改雇员 – update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.util.Date" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改</title>
</head>
<body>
<script type="text/javascript">
 function changColor(obj,color){
  obj.bgColor = color;
 }
</script>
<%
 request.setCharacterEncoding("UTF-8");
%>
<%!
 //定义数据库驱动程序
 public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
 // 数据库连接地址
 public static final String DBURL = "jdbc:mysql://localhost/book?serverTimezone=UTC&useSSL=false";
 // 数据库连接用户名
 public static final String DBUSER = "root";
 // 数据库连接密码
 public static final String DBPASS = "root";
%>
<%
 // 声明数据库连接对象
 Connection conn = null;
 // 声明数据库操作
 PreparedStatement pstmt = null;
 // 声明数据库结果集
 ResultSet rs = null;
%>
<%
 Class.forName(DBDRIVER);
 conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
 String sql = "select * from emp_01 where empno=?";
 int empno = 0;
 try{
 // 通过雇员编号来找到相应的信息，然后修改
  empno = Integer.parseInt(request.getParameter("empno"));
 }catch(Exception e){}
 pstmt = conn.prepareStatement(sql);
 pstmt.setInt(1, empno);
 rs = pstmt.executeQuery();
 if (rs.next()){
  empno = rs.getInt(1);
  String ename = rs.getString(2);
     String job = rs.getString(3);
     Date hiredate = rs.getDate(4);
     Double sal = rs.getDouble(5);
     Double comm = rs.getDouble(6);
%>
  <form action="update_dao.jsp" method="post" >
   <table border="1" width="100%" cellpadding="5" cellspacing="0" bgcolor="F2F2F2">
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td colspan="3">
      <h1>增加新雇员</h1> 
     </td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td><font size="2">雇员编号：</font></td>
     <td><input type="text" name="empno" size="4" maxlength="4" value="<%=empno%>"></td>
     <td><span id="empno_msg"><font color="RED">*</font></span></td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td><font size="2">雇员姓名：</font></td>
     <td><input type="text" name="ename" value="<%=ename%>"></td>
     <td><span id="ename_msg"><font color="RED">*</font></span></td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td><font size="2">雇员工作：</font></td>
     <td><input type="text" name="job" value="<%=job%>"></td>
     <td><span id="job_msg"><font color="RED">*</font></span></td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td><font size="2">雇佣日期：</font></td>
     <td><input type="date" name="hiredate" size="15" maxlength="15" value="<%=hiredate%>"></td>
     <td><span id="hiredate_msg"><font color="RED">*</font></span></td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td><font size="2">基本工资：</font></td>
     <td><input type="text" name="sal" value="<%=sal%>"></td>
     <td><span id="sal_msg"><font color="RED">*</font></span></td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td><font size="2">雇佣奖金：</font></td>
     <td><input type="text" name="comm" value="<%=comm%>"></td>
     <td><span id="comm_msg"><font color="RED">*</font></span></td>
    </tr>
    <tr onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
     <td colspan="3">
      <input type="hidden" name="cp" value="<%=request.getParameter("cp")%>">
      <input type="hidden" name="ls" value="<%=request.getParameter("ls")%>">
      <input type="submit" value="修改">
      <input type="reset" value="重置">  
     </td>
    </tr>
   </table>
  </form>
<%
 }else{
%>
  <script type="text/javascript">
   alert("没有此雇员的信息！")
   window.location = "index.jsp?cp=<%=request.getParameter("cp")%>&ls=<%=request.getParameter("ls")%>";
  </script>
<%
 }
 conn.close();
%>
<center><a href="index.jsp"><font size="5" color="red">返回首页</font></a></center>
</body>
</html>

修改后的数据入数据库 – update_dao.jsp

<%@page import="java.text.SimpleDateFormat"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.util.Date" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改操作-数据库</title>
</head>
<body>
<%
 request.setCharacterEncoding("UTF-8");
%>
<%
 int empno = Integer.parseInt(request.getParameter("empno"));
 String ename = request.getParameter("ename");
 String job = request.getParameter("job");
 Date hiredate = new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("hiredate"));
 Double sal = Double.parseDouble(request.getParameter("sal"));
 Double comm = Double.parseDouble(request.getParameter("comm"));
%>
<%!
 //定义数据库驱动程序
 public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
 // 数据库连接地址
 public static final String DBURL = "jdbc:mysql://localhost/book?serverTimezone=UTC&useSSL=false";
 // 数据库连接用户名
 public static final String DBUSER = "root";
 // 数据库连接密码
 public static final String DBPASS = "root";
%>
<%
  // 声明数据库连接对象
  Connection conn = null;
  // 声明数据库操作
  PreparedStatement pstmt = null;
%>
<%
 String msg = "雇员修改失败！";
 Class.forName(DBDRIVER);
 conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
 String sql = "update emp_01 set ename=?,job=?,hiredate=?,sal=?,comm=? where empno=?";
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, ename);
 pstmt.setString(2, job);
 pstmt.setDate(3, new java.sql.Date(hiredate.getTime()));
 pstmt.setDouble(4, sal);
 pstmt.setDouble(5, comm);
 pstmt.setInt(6, empno);
 if (pstmt.executeUpdate()>0){
  msg = "雇员修改成功";
 }
%>
<script type="text/javascript">
 alert("<%=msg%>");
 window.location = "index.jsp?cp=<%=request.getParameter("cp")%>&ls=<%=request.getParameter("ls")%>"

</script>
</body>
</html>

删除雇员 – delete.jsp

直接从数据库删除

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.util.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除</title>
</head>
<body>
<%
 request.setCharacterEncoding("UTF-8");
%>
<%
 int empno = Integer.parseInt(request.getParameter("empno"));
%>
<%!
 //定义数据库驱动程序
 public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
 // 数据库连接地址
 public static final String DBURL = "jdbc:mysql://localhost/boot?serverTimezone=UTC&useSSL=false";
 // 数据库连接用户名
 public static final String DBUSER = "root";
 // 数据库连接密码
 public static final String DBPASS = "root";
%>
<%
    // 声明数据库连接对象
    Connection conn = null;
    // 声明数据库操作
    PreparedStatement pstmt = null;
%>
<%
 String msg = "雇员删除失败！！！";
 Class.forName(DBDRIVER);
 conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
 String sql = "delete from emp_01 where empno=?";
 pstmt = conn.prepareStatement(sql);
 pstmt.setInt(1, empno);
 if (pstmt.executeUpdate() > 0){
  msg = "雇员删除成功";
 }
%>
<script language="javascript">
 alert("<%=msg%>") ;
 window.location = "index.jsp?cp=<%=request.getParameter("cp")%>&ls=<%=request.getParameter("ls")%>" ;
</script>
</body>
</html>

总结：做完这个项目后，我发现单纯的使用JSP还是有局限性，有很多的代码是重复的，但是如果使用数据库的DAO模式，就可以省略很多的代码，便于查看，但是对于JSP的很多知识还是提升了理解，巩固了所学的知识。