JSP + Servlet + JDBC + Mysql 简单实现增删改查

程序员文章站 2022-06-18 11:26:52

...

一，准备

1，工具

IDE：IntelliJ IDEA

数据库：mysql

数据库操作工具：Navicat for MySQL

2，数据表

JSP + Servlet + JDBC + Mysql 简单实现增删改查

3，项目结构

JSP + Servlet + JDBC + Mysql 简单实现增删改查

二，后端代码

1，数据库工具 DBUtil.java

package com.util;

import java.sql.*;

/**
 * 数据库工具
 */
public class DBUtil {
    private static String dbUrl = "jdbc:mysql://localhost:3306/javawebdemo"; //数据库地址
    private static String dbUser = "root"; //数据库用户
    private static String dbPwd = "123456"; //数据库密码
    private static String dbDriver = "com.mysql.jdbc.Driver"; //数据库驱动

    private static Connection conn = null;

    //获取连接
    public static Connection getConn(){
        if(null == conn){
            try {
                Class.forName(dbDriver);
                conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }

    /*//测试
    @Test
    public void test(){
        if(getConn() != null){
            System.out.println("数据库连接成功");
        }else{
            System.out.println("数据库连接失败");
        }
    }*/
}

2，实体类 User.java

package com.domain;

/**
 * 用户实体类
 */
public class User {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

3，数据库交互 dao层 UserDao.java

package com.dao;

import com.domain.User;
import com.util.DBUtil;
import org.junit.Test;

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

/**
 * 数据库交互 dao层
 */
public class UserDao {
    public List<User> getAllUser(){
        List<User> list = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        String sql = "select * from user";

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                list.add(user);
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public boolean addUser(User user){
        String sql = "insert into user(username, password) values(?,?)";
        Connection conn = DBUtil.getConn();

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUsername());
            pst.setString(2, user.getPassword());
            int count = pst.executeUpdate();
            pst.close();
            return count>0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteUser(int id){
        String sql = "delete from user where id = ?";
        Connection conn = DBUtil.getConn();

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            int count = 0;
            count = pst.executeUpdate();
            pst.close();
            return count>0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateUser(User user){
        String sql = "update user set username=?,password=? where id=?";
        Connection conn = DBUtil.getConn();

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,user.getUsername());
            pst.setString(2, user.getPassword());
            pst.setInt(3, user.getId());
            int count = pst.executeUpdate();
            pst.close();
            return count>0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public User findUserById(int id){
        String sql = "select * from user where id = " + id;
        Connection conn = DBUtil.getConn();
        User user = null;

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Test
    public void test(){
        User user = new User();
        user.setUsername("admin1");
        user.setPassword("123456");
        UserDao ud = new UserDao();
        boolean b = ud.addUser(user);
        if(b){
            System.out.println("添加数据成功");
        }
    }

}

4，web控制添加数据 AddServlet.java

package com.servlet;

import com.dao.UserDao;
import com.domain.User;

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 java.io.IOException;

@WebServlet("/addServlet")
public class AddServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("utf-8");

        String username = request.getParameter("username");
        String password = request.getParameter("password");

        User u = new User();
        u.setUsername(username);
        u.setPassword(password);
        /*u.setUsername(new String(username.getBytes("ISO-8859-1"),"UTF-8"));
        u.setPassword(new String(password.getBytes("ISO-8859-1"),"UTF-8"));*/
        UserDao ud = new UserDao();
        ud.addUser(u);
        request.getRequestDispatcher("showServlet").forward(request,response);
    }

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

5，web控制删除数据 DeleteServlet.java

package com.servlet;

import com.dao.UserDao;

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 java.io.IOException;

@WebServlet("/deleteServlet")
public class DeleteServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("utf-8");

        String idStr = request.getParameter("id");
        int id = Integer.valueOf(idStr);
        UserDao ud = new UserDao();
        if(ud.deleteUser(id)){
            request.setAttribute("deleted", "删除成功！");
            request.getRequestDispatcher("showServlet").forward(request,response);
        }else{
            response.sendRedirect("showServlet");
        }
    }

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

6，web控制修改数据 UpdateServlet.java

package com.servlet;

import com.dao.UserDao;
import com.domain.User;

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 java.io.IOException;

@WebServlet("/updateServlet")
public class UpdateServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("utf-8");

        String idStr = request.getParameter("id");
        if (idStr != null && !idStr.equals("")) {
            int id = Integer.valueOf(idStr);
            UserDao dao = new UserDao();
            User user = dao.findUserById(id);
            request.setAttribute("user", user);
        }
        request.getRequestDispatcher("update.jsp").forward(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String idStr = request.getParameter("id");
        User u = new User();
        u.setId(Integer.valueOf(idStr));
        u.setUsername(username);
        u.setPassword(password);
        UserDao ud = new UserDao();
        ud.updateUser(u);
        request.getRequestDispatcher("showServlet").forward(request, response);
    }
}

6，web控制显示数据 ShowServlet.java

package com.servlet;

import com.dao.UserDao;
import com.domain.User;

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 java.io.IOException;
import java.util.List;

@WebServlet("/showServlet")
public class ShowServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("utf-8");

        UserDao ud = new UserDao();
        List<User> list = ud.getAllUser();

        request.setAttribute("list", list);
        request.getRequestDispatcher("index.jsp").forward(request,response);
    }

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

三，jsp前端页面

1，添加数据 add.jsp

<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2/28/2020
  Time: 9:13 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>用户添加</title>
</head>
<body>
<form action="${pageContext.request.contextPath}/addServlet" method="post">
    <table border="1" >
        <tr>
            <td colspan="2"><h1>用户添加</h1></td>
        </tr>
        <tr>
            <td>用户名：</td>
            <td><input type="text" name="username"/></td>
        </tr>
        <tr>
            <td>密码：</td>
            <td><input type="password" name="password"/></td>

        </tr>
        <tr>
            <td colspan="2">
                <input type="submit" value="提交">
                <input type="reset" value="重置">
            </td>
        </tr>
    </table>
</form>
</body>
</html>

2，显示数据 index.jsp

<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2/28/2020
  Time: 9:13 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
    <title>用户展示</title>
</head>
<body>
<h1>用户展示</h1>
<h2>${deleted}</h2>
<table>
    <tr>
        <td>编号</td>
        <td>用户名</td>
        <td>密码</td>
        <td>操作</td>
    </tr>
    <c:forEach items="${list}" var="item">
        <tr>
            <td>${item.id}</td>
            <td>${item.username}</td>
            <td>${item.password}</td>
            <td><a href="${pageContext.request.contextPath}/deleteServlet?id=${item.id}">删除</a>-----<a href="${pageContext.request.contextPath}/updateServlet?id=${item.id}">编辑</a> </td>
        </tr>
    </c:forEach>
    <tr>
        <td colspan="2" style="text-align: left"><a href="add.jsp">用户添加</a> </td>
    </tr>
</table>

</body>
</html>

3，修改数据 update.jsp

<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2/28/2020
  Time: 9:14 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>用户编辑</title>
</head>
<body>
<form action="${pageContext.request.contextPath}/updateServlet" method="post" style="align-items: center">
    <table border="1" >
        <tr>
            <td colspan="2"><h1>用户编辑</h1></td>
        </tr>
        <tr>
            <td>id</td>
            <td><input type="text" name="id" value="${user.id}" /></td>
        </tr>
        <tr>
            <td>姓名</td>
            <td><input type="text" name="username" value="${user.username}" /></td>
        </tr>
        <tr>
            <td>密码</td>
            <td><input type="text" name="password" value="${user.password}" /></td>
        </tr>

        <tr>
            <td colspan="2">
                <input type="submit" value="提交"/>
                <input type="button" value="Back" onclick="history.go(-1)"/>
            </td>

        </tr>
    </table>

</form>
</body>
</html>

四，页面效果

JSP + Servlet + JDBC + Mysql 简单实现增删改查