[javaweb作业]读取数据库数据删除修改

0x3fffff

已于 2024-06-12 17:11:39 修改

阅读量147

点赞数 3

文章标签：数据库 oracle

于 2023-11-08 09:56:53 首次发布

本文链接：https://blog.csdn.net/qq_19655605/article/details/134281895

版权

课堂练习，没有写service和servlet

数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stuid` int NOT NULL,
  `stuname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `stusex` int NULL DEFAULT NULL,
  PRIMARY KEY (`stuid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (222, 'aaa', 0);
INSERT INTO `student` VALUES (211164301, '张念祖', 0);
INSERT INTO `student` VALUES (211164302, '杨天宇', 1);
INSERT INTO `student` VALUES (211164303, '张帅', 1);
INSERT INTO `student` VALUES (211164304, '李文豪', 1);

SET FOREIGN_KEY_CHECKS = 1;

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="com.dao.UserDao" %>
<%@ page import="com.dao.impl.UserDaoImpl" %>
<%@ page import="java.util.List" %>
<%@ page import="com.entity.User" %>

<html>
<head>
  <title>通过JSP打开数据表</title>
</head>
<body>
<center>
<table border="1px" style="background: #7fd6ff">
  <tr>
    <td>id</td>
    <td>姓名</td>
    <td>性别</td>
    <td>删除</td>
    <td>修改</td>
  </tr>
  <%
    UserDao ud = new UserDaoImpl();
    List<User> userList = ud.findAllUserList();
    for (User user : userList) {
      out.println("<tr>"+
              "<td>"+user.getStuid()+"</td>"+
              "<td>"+user.getStuname()+"</td>"+
              "<td>"+(user.getStusex()==1?"男":"女")+"</td>"+
              "<td><a href='deleteUser.jsp?stuid="+user.getStuid()+"'>删除</a></td>"+
              "<td><a href='updateUser.jsp?stuid="+user.getStuid()+"&stuname="+user.getStuname()+"&stusex="+user.getStusex()+"'>修改</a></td>"+
              "</tr>");
    }
  %>

</table>
</center>
</body>
</html>

update.jsp

<%@ page import="com.dao.impl.UserDaoImpl" %>
<%@ page import="com.dao.UserDao" %>
<%@ page import="com.entity.User" %><%--
  Created by IntelliJ IDEA.
  User: 53172
  Date: 2023/11/8
  Time: 9:05
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<%
String stuid = request.getParameter("stuid");
String stuname = request.getParameter("stuname");
String stusex = request.getParameter("stusex");
stusex=(stusex.equals("男")?"1":"0");
UserDao ud = new UserDaoImpl();
ud.updateUser(new User(Integer.parseInt(stuid),stuname,Integer.parseInt(stusex)));
response.sendRedirect("index.jsp");
%>
</body>
</html>

updateUser.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>更新用户</title>
</head>
<body>
<%
  String stuid = request.getParameter("stuid");
  String stuname = request.getParameter("stuname");
  String stusex = request.getParameter("stusex");
  String sex = stusex.equals("1")?"男":"女";
%>
<form action="update.jsp" method="post">
<input value="<%=stuid%>" name="stuid" type="hidden">
姓名:<input value="<%=stuname%>" name="stuname"><br/>
性别:<input value="<%=sex%>" name="stusex"><br/>
<input type="submit">
</form>
</body>
</html>

deleteUser.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>删除用户</title>
</head>
<body>
<%
    String stuid = request.getParameter("stuid");
    UserDao ud = new UserDaoImpl();
    ud.deleteUser(Integer.parseInt(stuid));
    response.sendRedirect("index.jsp");
%>
</body>
</html>

jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
uname=root
upwd=123456

BaseDao.java

package com.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class BaseDao {
    public Connection conn=null;
    public PreparedStatement ps=null;
    private static  String driver;
    private static String url;
    private static String uname;
    private static String upwd;
    static{
        InputStream is=BaseDao.class.getClassLoader().getResourceAsStream("com/config/jdbc.properties");
        Properties properties=new Properties();
        try {
            properties.load(is);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            uname = properties.getProperty("uname");
            upwd = properties.getProperty("upwd");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void getConnection() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,uname,upwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void closeConnection(){
        try {
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public ResultSet selectSql(String sql,Object[] objs){
        ResultSet rs = null;
        if (conn!=null){
            try {
                ps = conn.prepareStatement(sql);
                if (objs!=null){
                    for (int i=0;i<objs.length;i++){
                        ps.setObject(i+1,objs[i]);
                    }
                }
                rs=ps.executeQuery();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return rs;
    }

    public int editSql(String sql,Object[] objs){
        int rs=0;
        if (conn!=null){
            try{
                ps = conn.prepareStatement(sql);
                if (objs!=null){
                    for (int i=0;i<objs.length;i++){
                        ps.setObject(i+1,objs[i]);
                    }
                }
                rs=ps.executeUpdate();
            }catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return rs;
    }
}

UserDao.java

package com.dao;

import com.entity.User;

import java.util.List;

public interface UserDao {
    public List<User> findAllUserList();
    public int deleteUser(int stuid);
    public int updateUser(User user);
}

User.java

package com.entity;

public class User {
    int stuid,stusex;
    String stuname;
    public User(){};
    public User(int stuid, String stuname, int stusex) {
        this.stuid = stuid;
        this.stuname = stuname;
        this.stusex = stusex;
    }

    public int getStuid() {
        return stuid;
    }

    public void setStuid(int stuid) {
        this.stuid = stuid;
    }

    public String getStuname() {
        return stuname;
    }

    public void setStuname(String stuname) {
        this.stuname = stuname;
    }

    public int getStusex() {
        return stusex;
    }

    public void setStusex(int stusex) {
        this.stusex = stusex;
    }

    @Override
    public String toString() {
        return "User{" +
                "stuid='" + stuid + '\'' +
                ", stuname='" + stuname + '\'' +
                ", stusex='" + stusex + '\'' +
                '}';
    }
}

UserDaoImpl.java

package com.dao.impl;

import com.dao.BaseDao;
import com.dao.UserDao;
import com.entity.User;

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

public class UserDaoImpl extends BaseDao implements UserDao {
    public List<User> findAllUserList() {
        getConnection();
        List<User> userList=new ArrayList<>();
        try {
            String sql = "select * from student";
            ResultSet rs = selectSql(sql,null);
            while (rs.next()){
                User user = new User();
                user.setStuid(rs.getInt("stuid"));
                user.setStuname(rs.getString("stuname"));
                user.setStusex(rs.getInt("stusex"));
                userList.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        closeConnection();
        return userList;
    }

    @Override
    public int deleteUser(int stuid) {
        getConnection();
        String sql = "delete from student where stuid = ?";
        int i = editSql(sql, new Object[]{stuid});
        closeConnection();
        return i;
    }

    @Override
    public int updateUser(User user) {
        //修改用户信息
        getConnection();
        String sql = "update student set stuname = ?,stusex = ? where stuid = ?";
        int i = editSql(sql, new Object[]{user.getStuname(),user.getStusex(),user.getStuid()});
        closeConnection();
        return i;
    }
}