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

程序员文章站 2022-06-18 12:55:13

...

Servlet是一个特殊的Java程序，它运行于服务器的JVM中，能够依靠服务器的支持向浏览器提供显示内容。

JSP本质上是Servlet的一种简易形式， JSP会被服务器处理成一个类似于Servlet的Java程序，可以简化页面内容的生成。

Servlet和JSP最主要的不同点在于，Servlet 的应用逻辑是在Java 文件中，并且完全从表示层中的HTML分离开来。而JSP的情况是Java和HTML可以组合成一个扩展名为.jsp 的文件（有人说，Servlet就是在Java中写HTML，而JSP就是在HTML中写Java代码，当然，这个说法还是很片面的）。

JSP侧重于视图，Servlet更侧重于控制逻辑，在MVC架构模式中，JSP适合充当视图（view）而Servlet适合充当控制器（controller）。

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

1、项目目录结构

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

2、项目环境：

Eclipse Neon.3 Release (4.6.3)

jdk1.8.0_121

apache-tomcat-8.5.24

MYSQL5.7

Navicat for MySQL 11.1.13

所需jar包：

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

数据库连接jar：mysql-connector-java-5.1.39-bin.jar

jstl标签jar：jstl-1.2.jar

3、数据库相关配置

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

4、源代码

4.1 dao层（直接与数据库进行交互的层）

AdminDao.java

package com.web.tom.dao;

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

import com.web.tom.entity.Admin;

/**
 * 
 * @author tom
 *
 */
public class AdminDao {
	public List<Admin> getAllAdmin() { // 查询所有信息
		List<Admin> list = new ArrayList<Admin>(); // 创建集合
		Connection conn = DbHelper.getConnection();
		String sql = "select * from admin"; // SQL查询语句
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Admin admin = new Admin();
				admin.setId(rst.getInt("id")); // 得到ID
				admin.setUsername(rst.getString("username"));
				admin.setUserpwd(rst.getString("userpwd"));
				list.add(admin);
			}
			rst.close(); // 关闭
			pst.close(); // 关闭
		} catch (SQLException e) {
			e.printStackTrace(); // 抛出异常
		}
		return list; // 返回一个集合
	}

	public boolean addAdmin(Admin admin) { // 添加信息
		String sql = "INSERT INTO `admin`(`id`,`username`,`userpwd`) VALUES (?,?,?)"; // 添加的SQL语句
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, admin.getId());
			pst.setString(2, admin.getUsername());
			pst.setString(3, admin.getUserpwd());
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false; // 是否添加的判断
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean updateAdmin(Admin admin) { // 修改
		String sql = "UPDATE `admin` SET `username`=?,`userpwd`=? WHERE `id` = ?"; // 修改的SQL语句，根据ID修改
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, admin.getUsername());
			pst.setString(2, admin.getUserpwd());
			pst.setInt(3, admin.getId()); // 根据的ID
			int count = pst.executeUpdate();
			pst.close(); // 关闭
			return count > 0 ? true : false; // 是否修改的判断
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean deleteAdmin(int id) { // 删除
		String sql = "delete from admin where id = ?"; // 删除的SQL语句，根据ID删除
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false; // 是否删除的判断
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Admin selectAdminById(int id) { // 根据ID进行查询
		Connection conn = DbHelper.getConnection();
		String sql = "select * from admin where id = " + id;
		Admin admin = null;
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				admin = new Admin();
				admin.setId(rst.getInt("id"));
				admin.setUsername(rst.getString("username"));
				admin.setUserpwd(rst.getString("userpwd"));
			}
			rst.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return admin; // 返回
	}
}

DbHelper.java

package com.web.tom.dao;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * 连接数据库
 * 
 * @author tom
 *
 */
public class DbHelper {
	private static String url = "jdbc:mysql://localhost:3306/JspServlet"; // 数据库地址
	private static String userName = "root"; // 数据库用户名
	private static String passWord = "tanghh"; // 数据库密码
	private static Connection conn = null;

	private DbHelper() {

	}

	public static Connection getConnection() {
		if (null == conn) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection(url, userName, passWord);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return conn;
	}

	public static void main(String[] args) { // 测试数据库是否连通
		System.err.println(getConnection());
	}
}

4.2 entity层（实体类映射层）

Admin.java

package com.web.tom.entity;

import java.io.Serializable;

public class Admin implements Serializable { // 数据封装类

	private static final long serialVersionUID = 1L;

	private int id;
	private String username;
	private String userpwd;

	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 getUserpwd() {
		return userpwd;
	}

	public void setUserpwd(String userpwd) {
		this.userpwd = userpwd;
	}

}

4.3 servlet层（请求控制层）

AddServlet.java

package com.web.tom.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.web.tom.dao.AdminDao;
import com.web.tom.entity.Admin;

public class AddServlet extends HttpServlet { // 添加数据
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doPost(req, resp);
	}

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String username = req.getParameter("username");
		String userpwd = req.getParameter("userpwd");
		Admin admin = new Admin();
		admin.setUsername(new String(username.getBytes("ISO-8859-1"), "UTF-8")); // 转值，中文需要转换为utf-8
		admin.setUserpwd(new String(userpwd.getBytes("ISO-8859-1"), "UTF-8"));
		AdminDao dao = new AdminDao();
		dao.addAdmin(admin);
		req.getRequestDispatcher("ShowServlet").forward(req, resp);
	}
}

DeleteServlet.java

package com.web.tom.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.web.tom.dao.AdminDao;

public class DeleteServlet extends HttpServlet { // 删除数据

	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doPost(req, resp);
	}

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String idStr = req.getParameter("id"); // 删除数据的ID，根据ID删除
		if (idStr != null && !idStr.equals("")) {
			int id = Integer.valueOf(idStr);
			AdminDao dao = new AdminDao();
			dao.deleteAdmin(id);
		}
		req.getRequestDispatcher("ShowServlet").forward(req, resp);
	}

}

ShowServlet.java

package com.web.tom.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.web.tom.dao.AdminDao;
import com.web.tom.entity.Admin;

public class ShowServlet extends HttpServlet { // 显示全部数据

	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doPost(req, resp);
	}

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		AdminDao dao = new AdminDao();
		List<Admin> list = dao.getAllAdmin();
		req.setAttribute("list", list);
		req.getRequestDispatcher("index.jsp").forward(req, resp);
	}
}

UpdateServlet.java

package com.web.tom.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.web.tom.dao.AdminDao;
import com.web.tom.entity.Admin;

public class UpdateServlet extends HttpServlet{  //修改

    private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // 查询到选中ID的值所对应的数据
		String idStr = req.getParameter("id");
		if (idStr != null && !idStr.equals("")) {
			int id = Integer.valueOf(idStr);
			AdminDao dao = new AdminDao();
			Admin admin = dao.selectAdminById(id);
			req.setAttribute("admin", admin);
		}
		req.getRequestDispatcher("update.jsp").forward(req, resp);

	}

    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {  //根据此ID对数据的值进行修改
        String username = req.getParameter("username");
        String userpwd = req.getParameter("userpwd");
        String idStr = req.getParameter("id");
        Admin admin = new Admin();
        admin.setId(Integer.valueOf(idStr));
        admin.setUsername(new String(username.getBytes("ISO-8859-1"),"UTF-8"));
        admin.setUserpwd(new String(userpwd.getBytes("ISO-8859-1"),"UTF-8"));
        AdminDao dao = new AdminDao();
        dao.updateAdmin(admin);
        req.getRequestDispatcher("ShowServlet").forward(req, resp);
    }
    
    

}

4.4 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
	id="WebApp_ID" version="3.1">
	<display-name>WebBasic</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
	
	<servlet>
		<servlet-name>AddServlet</servlet-name>
		<servlet-class>com.web.tom.servlet.AddServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>AddServlet</servlet-name>
		<url-pattern>/AddServlet</url-pattern>
	</servlet-mapping>
	
	<servlet>
		<servlet-name>DeleteServlet</servlet-name>
		<servlet-class>com.web.tom.servlet.DeleteServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>DeleteServlet</servlet-name>
		<url-pattern>/DeleteServlet</url-pattern>
	</servlet-mapping>
	
	<servlet>
		<servlet-name>UpdateServlet</servlet-name>
		<servlet-class>com.web.tom.servlet.UpdateServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>UpdateServlet</servlet-name>
		<url-pattern>/UpdateServlet</url-pattern>
	</servlet-mapping>
	
	<servlet>
		<servlet-name>ShowServlet</servlet-name>
		<servlet-class>com.web.tom.servlet.ShowServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>ShowServlet</servlet-name>
		<url-pattern>/ShowServlet</url-pattern>
	</servlet-mapping>
</web-app>

5、jsp页面

5.1 add.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>    
    <title>添加</title>
    <link rel="stylesheet" href="css/index.css" type="text/css" />
  </head>

  <body>
  <form action="AddServlet" method="post">
    <table border="1" class="t1">
        <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="userpwd"/></td>
        </tr>
        <tr>
            <td colspan="2">
                <input  type="submit" value="提交"/>
                <input  type="reset" value="清空"/>
            </td>
        </tr>
    </table>
   </form>
  </body>
</html>

5.2 index.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!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>
    <style type="text/css">
            table {
                border: 1px solid pink;
                margin: 0 auto;
            }
            
            td{
                width: 150px;
                border: 1px solid pink;
                text-align: center;
            }
    </style>
</head>
<body>
    <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.userpwd }</td>
                <td><a href="DeleteServlet?id=${item.id }">删除</a>|<a href="UpdateServlet?id=${item.id }">修改</a></td>
            </tr>
        </c:forEach>
        <tr>
            <td colspan="6" style="text-align: left;"><a href="add.jsp">添加管理员</a></td>
        </tr>
    </table>
</body>
</html>

5.3 update.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>    
    <title>修改</title>
    <link rel="stylesheet" href="css/index.css" type="text/css" />
  </head>
  
  <body>
    <form action="UpdateServlet" method="post">
    <table border="1" class="t1">
        <tr>
            <td colspan="2"><h1>修改管理员信息</h1></td>
        </tr>
        <tr>
            <td>编号:</td>
            <td><input  type="text" name="id" value="${admin.id}" readonly="readonly"/></td>
        </tr>
        
        <tr>
            <td>管理员帐号:</td>
            <td><input  type="text" name="username" value="${admin.username}"/></td>
        </tr>
        <tr>
            <td>管理员密码:</td>
            <td><input  type="text" name="userpwd" value="${admin.userpwd}"/></td>
        </tr>
        <tr>
            <td colspan="2">
                <input  type="submit" value="提交"/>
                <input  type="button" value="返回" onclick="history.go(-1)"/>
            </td>
        </tr>
    </table>
   </form>
  </body>
</html>

上一篇： Java中有关数据方面的一些小知识

下一篇：抖音号推广都是什么（附抖音号盈利模式）

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

jsp+servlet+jdbc实现对数据库的增删改查

MySql+IDEA简单实现JDBC的增删改查

JDBC之Java连接mysql实现增删改查

运用Servlet+JSP+MySQL+JDBCTempleat+Duird+BeanUtilS+tomcat技术。实现用户信息的增删改查操作(添加功能及界面实现)。

动态网站项目（Dynamic Web Project）CRUD(增删改查)功能的实现(mvc(五层架构)+jdbc+servlet+tomcat7.0+jdk1.8)，前端使用JSP+JSTL+EL组合

Servlet+JSP实现简单的用户管理（无数据库）增删改查

使用Servlet+JSP+EL+JSTL来实现简易增删改查操作

jsp+servlet+jdbc实现对数据库的增删改查

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

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