使用JDBC通过网页去完成数据库表的增删改查

最新推荐文章于 2024-10-06 20:16:20 发布
最新推荐文章于 2024-10-06 20:16:20 发布
阅读量1.2k 收藏 13
点赞数 37
文章标签： java idea 课程设计 笔记 tomcat servlet jsp
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lingqi070/article/details/139024345
版权

使用JDBC通过网页去完成数据库表的增删改查

对于刚入门JavaWeb的人来说，想要一上来就开发一个管理系统可能会有些费力，而一个管理系统的主要功能及实现逻辑其实就是通过网页对你的数据库进行增删改查。所以搞懂这里面的逻辑之后再去开发系统会更加得心应手。下面是我做的一个简单的工程项目，使用的是JDBC，非常适合初学者。话不多说，直接上素材吧。

运行效果图

在这里插入图片描述

数据表

create database studentsdb;
use studentsdb;
create table student(id int primary key auto_increment,name varchar(20),
teacher varchar(20),
age int,
score int
)
insert into student(name,teacher,age,score)values('xiaoming','Jack',20,80),('xiaohong','Mike',21,90),
('xiaodong','Bob',19,78),
('lihua','kite',21,99);

工具类JDBCUtils.java

package cn.twle.demo.Utils;
import java.sql.*;
public class JDBCUtils {
    public static Connection getConnect() throws SQLException,ClassNotFoundException{
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/studentsdb?serverTimezone=GMT%2B8";
        String username="root";
        String password="root";
        Connection conn= DriverManager.getConnection(url,username,password);
        return conn;
    }
    public static void release(Statement stmt,Connection conn){
        if(stmt!=null){
            try{
                stmt.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            stmt=null;
        }
        if (conn!=null){
            try{
                conn.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            conn=null;
        }
    }
    public static void release(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){
            try{
                rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            rs=null;
        }
        release(stmt,conn);
    }
}

StudentDao.java

package cn.twle.demo.Dao;

import cn.twle.demo.pojo.Student;
import cn.twle.demo.Utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

public class StudentDao {
    public void insert(Student student) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnect();
            String sql = "insert into student" + "(name,teacher,age,score) values" +
                    "(?,?,?,?)";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getTeacher());
            preparedStatement.setInt(3, student.getAge());
            preparedStatement.setInt(4, student.getScore());
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, preparedStatement, conn);
        }
    }
    public ArrayList<Student> findAll(){
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        ArrayList<Student> student=new ArrayList<>();
        try{
            conn= JDBCUtils.getConnect();
            stmt=conn.createStatement();
            String sql="select * from student";
            rs=stmt.executeQuery(sql);
            while (rs.next()) {
                int id=rs.getInt("id");
                String name=rs.getString("name");
                String teacher=rs.getString("teacher");
                int age=rs.getInt("age");
                int score=rs.getInt("score");
                student.add(new Student(id,name,teacher,age,score));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return student;
    }
    public Student find(int id){
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        Student student=null;
        try{
            conn= JDBCUtils.getConnect();
            stmt=conn.createStatement();
            String sql="select * from student where id=?";
            rs=stmt.executeQuery(sql);
            while (rs.next()) {
                String name=rs.getString("name");
                String teacher=rs.getString("teacher");
                int age=rs.getInt("age");
                int score=rs.getInt("score");
                student=new Student(id,name,teacher,age,score);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return student;
    }
    public boolean delete(int id) {
        boolean flag=false;
        Connection conn=null;
        try {
            conn = JDBCUtils.getConnect();
            String sql="delete from student where id=?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            flag=statement.executeUpdate()>0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    public boolean update(Student student){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        boolean flag=false;
        try {
            conn = JDBCUtils.getConnect();
            String sql = "update student set name=?,teacher=?,age=?,score=? where id =?";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getTeacher());
            preparedStatement.setInt(3, student.getAge());
            preparedStatement.setInt(4, student.getScore());
            flag=preparedStatement.executeUpdate()>0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, preparedStatement, conn);
        }
        return flag;
    }
}

实体类

package cn.twle.demo.pojo;

public class Student {
    protected int id;
    protected String name;
    protected String teacher;
    protected int age;
    protected int score;
    public Student(){};
    public Student(String name,String teacher,int age,int score){
        super();
        this.name=name;
        this.teacher=teacher;
        this.age=age;
        this.score=score;
    }
    public Student(int id,String name,String teacher,int age,int score){
        super();
        this.id=id;
        this.name=name;
        this.teacher=teacher;
        this.age=age;
        this.score=score;
    }
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTeacher() {
        return teacher;
    }

    public void setTeacher(String teacher) {
        this.teacher = teacher;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }
}

StudentServlet.java

package cn.twle.demo.Servlet;

import javax.servlet.RequestDispatcher;
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 cn.twle.demo.Dao.StudentDao;
import cn.twle.demo.pojo.Student;

import java.io.IOException;
import java.util.List;
@WebServlet("/")
public class StudentServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private StudentDao studentDao;
    public void init(){studentDao=new StudentDao();}
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        req.setCharacterEncoding("utf-8");
        String action = req.getServletPath();
        try{
            switch (action) {
                case "/new":
                    showNewForm(req, resp);
                    break;
                case "/insert":
                    add(req, resp);
                    break;
                case "/delete":
                    delete(req, resp);
                    break;
                // 显示编辑表单的逻辑
                case "/find":
                    find(req, resp);
                    break;
                case "/update":
                    update(req, resp);
                    break;
                default:
                    findAll(req,resp);
                    break;
            }
        }catch (Exception e){
            throw new ServletException(e);
        }

    }
        private void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            String name = request.getParameter("name");
            String teacher = request.getParameter("teacher");
            int age = Integer.parseInt(request.getParameter("age"));
            int score = Integer.parseInt(request.getParameter("score"));

            Student newstudent = new Student(name, teacher, age, score); // 假设ID由数据库自动生成
            studentDao.insert(newstudent);
            response.sendRedirect("list");
        }
        private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            int id = Integer.parseInt(request.getParameter("id"));
            String name = request.getParameter("name");
            String teacher = request.getParameter("teacher");
            int age = Integer.parseInt(request.getParameter("age"));
            int score = Integer.parseInt(request.getParameter("score"));
            Student updatedStudent = new Student(id, name, teacher, age, score);
            studentDao.update(updatedStudent);
            response.sendRedirect("user-list.jsp");
        }

        private void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            int id = Integer.parseInt(request.getParameter("id"));
            studentDao.delete(id);
            response.sendRedirect("list");
        }

        // 新增方法以处理编辑页面的请求
        private void find(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            int id = Integer.parseInt(request.getParameter("id"));
            Student existingStudent=studentDao.find(id);
            RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
            request.setAttribute("student", existingStudent);
            dispatcher.forward(request, response);
        }
        private void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

            List<Student> listStudent  = studentDao.findAll();
            request.setAttribute("listStudent", listStudent);
            RequestDispatcher dispatcher = request.getRequestDispatcher("user-list.jsp");
            dispatcher.forward(request, response);
        }
        private void showNewForm(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
            dispatcher.forward(request, response);
        }
}

user-list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
    <title>User Management Application</title>
</head>
<body>
<br>

<div >
    <!-- <div class="alert alert-success" *ngIf='message'>{{message}}</div> -->

    <div>
        <h3 >学生列表</h3>
        <hr>
        <div>

            <a href="<%=request.getContextPath()%>/new">添加学生</a>
        </div>
        <br>
        <table>
            <tr>
                <th>ID</th>
                <th>姓名</th>
                <th>指导老师</th>
                <th>学生年龄</th>
                <th>分数</th>
            </tr>

            <c:forEach var="student" items="${listStudent}">

                <tr>
                    <td><c:out value="${student.id}" /></td>
                    <td><c:out value="${student.name}" /></td>
                    <td><c:out value="${student.teacher}" /></td>
                    <td><c:out value="${student.age}" /></td>
                    <td><c:out value="${student.score}"/></td>
                    <td><a href="find?id=<c:out value='${student.id}' />">查看</a>
                        &nbsp;&nbsp;&nbsp;<a href="delete?id=<c:out value='${student.id}' />">删除</a>
                        <a href="editStudent.jsp?id=<c:out value='${student.id}' />">修改</a>
                    </td>
                </tr>
            </c:forEach>
        </table>
    </div>
</div>
</body>
</html>

user-form.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<body>
<br>
<div>
    <div>
        <div>
            <c:choose>
            <c:when test="${student != null}">
            <form action="update" method="post">
                <h2>查看学生</h2>
                <input type="hidden" name="id" value="${student.id}" />
                </c:when>
                <c:otherwise>
                <form action="insert" method="post">
                    <h2>添加学生</h2>
                    </c:otherwise>
                    </c:choose>

                    <fieldset>
                        <label>学生姓名</label>
                        <input type="text" name="name" value="${student.name}" required="required" />
                    </fieldset>
                    <fieldset>
                        <label>学生指导老师</label>
                        <input type="text" name="teacher" value="${student.teacher}" />
                    </fieldset>
                    <fieldset>
                        <label>年龄</label>
                        <input type="text" name="age" value="${student.age}" />
                    </fieldset>
                    <fieldset>
                        <label>分数</label>
                        <input type="text" name="score" value="${student.score}" />
                    </fieldset>

                    <button type="submit" class="btn btn-success">保存</button>
                </form> <!-- 注意这里表单的结束标签 -->
        </div>
    </div>
</div>
</body>
</html>

editStudent.jsp

<%@ page import="cn.twle.demo.pojo.Student" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>编辑学生信息</title>
</head>
<body>
<h1>编辑学生信息</h1>
<%-- 假设有一个Student对象在request范围内，包含要编辑的学生信息 --%>
<% Student student = (Student) request.getAttribute("student"); %>
<form action="update" method="post">
    <input type="hidden" name="id" value="<%= (student != null) ? student.getId() : "" %>">

    <label for="name">姓名:</label>
    <input type="text" id="name" name="name" value="<%= (student != null) ? student.getName() : "" %>">

    <label for="teacher">教师:</label>
    <input type="text" id="teacher" name="teacher" value="<%= (student != null) ? student.getTeacher() : "" %>">

    <label for="age">年龄:</label>
    <input type="text" id="age" name="age" value="<%= (student != null) ? student.getAge() : "" %>">

    <label for="score">分数:</label>
    <input type="text" id="score" name="score" value="<%= (student != null) ? student.getScore() : "" %>">

    <input type="submit" value="保存更改">
</form>
</body>
</html>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
<!--    <servlet>-->
<!--        <servlet-name>StudentServlet</servlet-name>-->
<!--        <servlet-class>cn.twle.demo.Servlet.StudentServlet</servlet-class>-->
<!--    </servlet>-->
<!--    <servlet-mapping>-->
<!--        <servlet-name>StudentServlet</servlet-name>-->
<!--        <url-pattern>/</url-pattern>-->
<!--    </servlet-mapping>-->
    <welcome-file-list>
        <welcome-file>user-list.jsp</welcome-file>
    </welcome-file-list>

</web-app>
柒墨轩
关注
html+写入数据库+JDBC更改数据库
learnalwaystodie的博客
10-10 363
暂停，先看规范 java servlet规范
合肥师范学院《javaweb》实验六：使用JDBC完成数据的增删改查
12-24
使用 JDBC 完成数据的增删改查 JDBC（Java Database Connectivity）是一种用于连接 Java 程序和数据库的技术，允许 Java 程序连接到关系数据库管理系统，执行 SQL 语句，进行数据的增删改查操作。在本实验报告中，...
JDBC操作数据库之增删查改
weixin_70960994的博客
07-12 674
JDBC操作数据库,实现增删查改。
Java通过JDBC对MySQL数据库表增删改查(初步总结)
Etui۹(･༥･´)و的博客
03-05 2267
Java通过JDBC对MySQL数据库表增删改查(初步总结) 作为初学者水平有限，如有错误还请批评指正！ 注： 1、案例中的引用大多来自java.sql.*，不能引用为mysql下的jdbc包 2、实验使用的数据库为MySQL，连接数据库的mysql-connection-java-5-7-1.jar包可自行到MySQL官网下载，也可至我主页下载。 3、源码中用于存放登录信息的jdbc.properties文件放于src/目录下 4、在源码中我对增删改(preparedStatementUpdate(Str
JavaWeb简单实例——使用JDBC完成MySQL数据库的增删改查
hssjsh的博客
11-23 1707
JavaWeb简单实例——使用JDBC完成对MySQL的基本的增删改查的操作
使用JDBC对数据库进行增删改查操作
m0_65068333的博客
10-30 2350
使用JDBC对数据库进行增删改查操作
使用JDBC实现数据库的增删改查
tzk
08-13 1805
1、实现步骤 1.1、导入jar包 在libs文件夹下复制jar包，没有libs文件夹新建一个，jar包下载 2、注意事项 3、Demo
JDBC连接数据库进行增删改查
weixin_67658096的博客
10-30 2837
JDBC操作数据库进行增删改查
使用简单的java项目实现jdbc连接数据库完成增删改查
m0_63374546的博客
08-05 1196
使用简单的java项目实现jdbc连接数据库完成增删改查
JDBC进行数据库的增删改查
weixin_68683361的博客
10-30 3297
JDBC进行数据库的增删改查
jsp+servlet+jdbc实现对数据库的增删改查
10-20
在本文中，我们将深入探讨如何使用JSP、Servlet和JDBC技术来实现对数据库的增删改查操作。首先，我们需要了解JSP和Servlet的基本概念及其在Web开发中的角色。 **1. JSP (JavaServer Pages) 和 Servlet 简介** JSP...
jsp连接Oracle数据库实现增删改查
01-09
"jsp连接Oracle数据库实现增删改查"这个项目旨在教你如何利用JSP（JavaServer Pages）技术来构建一个完整的用户管理系统，包括登录、注册以及对数据表（如学生表）的CRUD（Create, Read, Update, Delete）操作。...
JDK1.0主要特性
最新发布
FoolRabbit的专栏
10-06 241
JDK1.0主要特性。
JavaScript ArrayBuffer的读写与类型转换
白瑕 的博客
10-01 469
所有视图的基本单位是ArrayBuffer, ArrayBuffer中只存储二进制格式的数据.ArrayBuffer不可直接读写, 必须通过视图(比如DataView)暴露的API.
java将word转pdf
紫月7575的博客
09-30 567
java将word转pdf
spring-boot 整合 mybatis
m0_72168501的博客
09-29 566
spring boot 整合 mybatis
Java JDBC教程：MySQL数据库的增删改查操作详解
本文档详细介绍了如何使用Java JDBC连接MySQL数据库实现增删改查操作。JDBC是Java应用程序与关系型数据库交互的基础技术，对于Java开发者来说是入门必备的知识。虽然在实际项目中，许多开发者倾向于使用更高级的ORM...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值