使用Mysql数据库完成增删改查综合案例（JSP页面）

时间：2024-03-07 08:24:49
本案例页面如下：

 


这是index.jsp页面（包含模糊查询）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import=" java.sql.* "%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>首页</title>
</head>
<body>
<% 
    //取查询的名字
    String selectName = request.getParameter("txtSelect");
%>

<form action="index.jsp">
<h3 align=\'center\'>
请输入要查询的的名字：<input type="text" name="txtSelect" value="<%=selectName==null?"":selectName%>">
                    <input type="submit" name="btnSelect" value="开始查询">
</h3>
</form>

<table border="1" width="80%" align=\'center\'>
    <tr>
        <td></td>
        <td>序号</td>
        <td>姓名</td>
        <td>性别</td>
        <td>出生日期</td>
        <td>操作</td>
    </tr>
    
<%    
    //连接数据库的五大参数
    String driverClass = "com.mysql.cj.jdbc.Driver";
    String serverIp = "localhost";
    String databaseName = "test3";
    String userName = "root";
    String pwd = "123456";
    String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";
    String sql = "select * from test3";
    
    //读取JDBC
    Class.forName(driverClass);
    //链接数据库
    Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);
    
    //如果为空，代表当前的状态不是查询，而是查询所有的内容
    PreparedStatement ps ;
    if(selectName == null){
        //判断查询文本框里面是否有内容，如果没有则执行查询全部信息
         ps = con.prepareStatement(sql);
    }else{
        //如果有，则根据文本框的里面的内容在数据库根据姓名查询
         ps = con.prepareStatement("select * from test3 where user_name like ?");
        ps.setString(1,\'%\'+selectName+\'%\');
    }

    //ResultSet是一个指向数据库的变量，本质上是不保存任何数据的，执行查询
    ResultSet rs = ps.executeQuery(); 
    //boolean flag = rs.next();   //判断返回指针是否还能继续往下移动
    //显示序号
    int i = 1;  
    while( rs.next()){
%>

    <tr>
        <td><%out.print(i++);%></td>
        <td><%=rs.getString("id") %></td>
        <td><%=rs.getString("user_name")%></td>
        <td><%=rs.getString("sex")%></td>
        <td><%=rs.getString("birt") %></td>
        <td>
        <a href="insert_Jsp.jsp">添加</a>
        <a href="editView.jsp?id=<%=rs.getString("id")%> ">编辑</a> 
             <a href="delete_action.jsp?id=<%=rs.getString("id")%>">删除</a>
        </td>
    </tr>
<%
    }
%>    
</table>

<h1 align="center">共计<%=i-1%>条记录</h1>
<% 
    ps.close();
    con.close();
%>

</body>
</html>
这是删除逻辑页面：delete_action.jsp（后台运行，不显示）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除的业务逻辑执行页面</title>
</head>
<body>
<%
    //获取参数
    String id = request.getParameter("id");

    //连接数据库的五大参数
    String driverClass = "com.mysql.cj.jdbc.Driver";
    String serverIp = "localhost";
    String databaseName ="test3";
    String user = "root";
    String pwd = "123456";
    
    //拼凑成一个完整的Url地址
    String jdbcUrl ="jdbc.mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai%useSSL=true";
    
    //创建对象
    Connection con = DriverManager.getConnection(jdbcUrl,user,pwd);
    String sql = "delete from test3 where id = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    
    //对应sql语句的问号跟上面从页面获取的参数相对应
    ps.setObject(1,id);
    //执行删除
    ps.executeUpdate();
    //关闭连接
    ps.close();
    con.close();
    
    //完成删除后跳转回index页面
    response.sendRedirect("index.jsp");
%>
</body>
</html>
这是用于添加的业务逻辑的运算：index_action.jsp（不显示，后台运算）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import = "java.sql.*" %>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加</title>
</head>
<body>
<%
    //获取参数
    String name = request.getParameter("txtName");
    String sex = request.getParameter("txtSex");
    String birt = request.getParameter("txtBirthday");
    //连接数据库的五大参数
    String driverClass = "com.mysql.cj.jdbc.Driver";
    String serverIp = "localhost";
    String databaseName = "test3";
    String userName = "root";
    String pwd = "123456";
    String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";
    
    Class.forName(driverClass);
    Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);
    String sql ="insert into test3(user_name,sex,birt) values(?,?,?)";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1,name);
    ps.setString(2,sex);
    ps.setString(3,birt);
    ps.executeUpdate();
    con.close();
    response.sendRedirect("index.jsp");
%>
</body>
</html>
这是添加的显示页面：index.jsp（用户客户端）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加信息</title>
</head>
<body>
<%

%>

<form action="insert.jsp">
    学号：<input type="text" name="txtId" ><br>
    姓名：<input type="text" name="txtName"><br>
    性别：<input type="text" name="txtSex"><br>
    出生年月日：<input type="text" name="txtBirthday"><br>
    <input type="submit" value="确认添加">
</form>
</body>
</html>
修改的业务逻辑运算界面：update_action.jsp（不显示，后台执行）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>编辑业务逻辑页面</title>
</head>
<body>
<%
    //获取参数
    String id = request.getParameter("txtId");
    String name = request.getParameter("txtUser");
    String sex = request.getParameter("selSex");
    String birt = request.getParameter("txtBirthday");
    
    //连接数据库的五大参数
    String driverClass = "com.mysql.cj.jdbc.Driver";
    String serverIp = "localhost";
    String databaseName = "test3";
    String userName = "root";
    String pwd = "123456";
    //整合连接地址
    String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";
    
    Class.forName(driverClass);
    Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);
    //要执行的SQL语句
    String sql ="update test3 set user_name=?,sex=?,birt=? where id=?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1,name);
    ps.setString(2,sex);
    ps.setString(3,birt);
    ps.setObject(4,id);
    //执行修改
    ps.execute();
    
    ps.close();
    con.close();
    response.sendRedirect("index.jsp");
%>    
</body>
</html>
修改的显示界面：editView（由客户端进行操作的显示页面）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>老师讲解的修改</title>

</head>
<body>
<%
//获取参数
    String id = request.getParameter("id");
    //连接数据库的五大参数
    String driverClass = "com.mysql.jdbc.Driver";
    String databaseName = "test3";
    String serverIP = "localhost";
    String user = "root";
    String pwd = "123456";
    
    //拼凑成一个完整的Url地址
    String jdbcUrl = "jdbc:mysql://"+serverIP+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";
    
    Class.forName(driverClass);
    Connection con = DriverManager.getConnection(jdbcUrl,user,pwd);
    String sql = "select * from test3 where id = ?";
    //创建对象
      PreparedStatement ps = con.prepareStatement(sql);
      //将字符串的id转换成int型的id
      ps.setObject(1, id);
      ResultSet rs= ps.executeQuery();
      if(rs.next()){
%>

<form action="update_action.jsp" >
    <input type="hidden" name="txtId" value="<%=rs.getString("id") %>">
    姓名：<input type ="text" name="txtUser" value="<%=rs.getString("user_name")%>"><br>
    性别：
    <select name="selSex">
        <option value="男" <%if(rs.getString("sex").equals("男")){out.print("selected");} %> >男</option>
        <option value="女" <%if(rs.getString("sex").equals("女")){out.print("selected");} %>>女</option>
        <option value="其他" <%if(rs.getString("sex").equals("其他")){out.print("selected");} %>>其他</option>
    </select><br>
    出生日期：<input type ="text" name="txtBirthday" value="<%=rs.getString("birt") %>"><br>
    <input type="submit" value="确认更新" name="btnsub">
</form>

<%
      }else{
          response.sendRedirect("error.jsp");
      }
      rs.close();
      ps.close();
      con.close();
%>
</body>
</html>

最后选择整个项目，右键选择Run As --> Run on Server 运行整个项目，然后将地址栏复制，在浏览器的地址栏粘贴，加上/index.jsp运行

 

 

 

相关文章