Java连接mysql数据库，并在JSP页面实现对数据库表的增删改查

实现软件

Navicat Premium

IntelliJ IDEA 2019.2

1.创建test数据库和表 student2

表 student2结构如下

3.创建一个 java web 项目并导入 jar 包

mysql-connector-java-5.1.9-bin.jar
链接：https://pan.baidu.com/s/1p-e9U2WUr4cPglXpoILOxA
提取码：g829

4.创建Dao类，负责数据库的连接与关闭

package czh;//

import java.sql.*;

public class Dao {

    public static Connection getConnection() throws SQLException {
    	//不加useUnicode=true&characterEncoding=UTF-8"会造成中文乱码
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8"; 
        String username = "root";
        String password = "123456";
        Connection conn = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功");
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭连接
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException
    {
        try
        {
            rs.close();
            ps.close();
            conn.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
    }

}

5.创建实体类 Students

public class Students {
    int sid;
    String name;
    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
	//无参构造方法（必须有，没有系统会自己创建）
    public Students() {
    }
	//有参构造方法
    public Students(int sid, String name) {
        this.sid = sid;
        this.name = name;
    }
}

6.创建数据库 增删改查 方法类 sqlDao

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

public class sqlDao {
    //查
    public static String getNameById(int id) {
        Connection conn;
        String name = null;
        try {
            conn = Dao.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from student2 where id = ?");
            ps.setInt(1, id);   //第一个参数指的是上面执行的sql语句中的第几个参数，第二个是要设置的值
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                name = rs.getString("name");
            }
            Dao.close(rs, ps, conn);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return name;
    }
    //删
    public static String delById(int id) {
        Connection conn;
        String whether=null;
        try {
            conn = Dao.getConnection();
            PreparedStatement ps = conn.prepareStatement("delete from student2 where id = ?");
            ps.setInt(1, id);   
            ps.executeUpdate();
            whether="删除成功";
        } catch (SQLException e) {
            e.printStackTrace();
            whether="删除失败";
        }
        return whether;
    }
    //增
    public static String insert(int id,String name) {
        Connection conn;
        String whether=null;
        try {
            conn = Dao.getConnection();
            PreparedStatement ps = conn.prepareStatement("insert into student2 values (?,?)");
            ps.setInt(1,id);
            ps.setString(2,name);
            ps.executeUpdate();
            whether="插入成功";
        } catch (SQLException e) {
            e.printStackTrace();
            whether="插入失败";
        }
        return whether;
    }

    //改
    public static String update(int id,String name) {
        Connection conn;
        String whether=null;
        try {
            conn = Dao.getConnection();
            PreparedStatement ps = conn.prepareStatement("update student2 set name=? where id=?");
            ps.setString(1,name);
            ps.setInt(2,id);
            ps.executeUpdate();
            whether="修改成功";
        } catch (SQLException e) {
            e.printStackTrace();
            whether="修改失败";
        }
        return whether;
    }

    //查表中所有数据
    //被static关键字修饰的方法或者变量不需要依赖于对象来进行访问，方便在没有创建对象的情况下来进行调用（方法/变量）。
    public static List<Students> getAll() throws SQLException {
        Connection conn;
            conn = Dao.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from student2");
            ResultSet rs = ps.executeQuery();
            List<Students> studentsList =new ArrayList<Students>();
            while (rs.next()) {
                studentsList.add(new Students(rs.getInt("id"),rs.getString("name")));
            }
            //遍历studentsList并输出到控制台
//            for(Students student: studentsList){
//            System.out.println(student.getSid()+student.getName());
//        }
        Dao.close(rs, ps, conn);
            return studentsList;
    }

}

7.创建一个处理HTTP请求的Servlet类 sqlServlet （此处处理post请求，实现对客户端（浏览器）发送数据的读取，处理，发送）

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

public class sqlServlet extends javax.servlet.http.HttpServlet {
    public sqlServlet() {
        super();
    }
    protected void doPost(javax.servlet.http.HttpServletRequest request,
                          javax.servlet.http.HttpServletResponse response)
            throws javax.servlet.ServletException, IOException {
        int sid = 0;
        //若此处不加if判断，当表单输入的值为空时，会报错
        // 在使用Integer.parseInt()函数将该变量的值由字符串类型String转为整型int会报错，
        // 因空值" "是不能转为int类型的，只有包含数字之类的String类型才可以转
        if (request.getParameter("id")!=null && !request.getParameter("id").equals("")) {
            sid = Integer.parseInt(request.getParameter("id"));   //getParameter获取参数值
        }
        String username=request.getParameter("name");
        String submitButton = request.getParameter("submitButton");
        switch (submitButton){
            case "query":
                String name = sqlDao.getNameById(sid);
                request.setAttribute("name", name);		//setAttribute设置属性值，后面NameResult.jsp页面会获取该值

                request.getRequestDispatcher("/NameResult.jsp").forward(request, response);    // 请求转发
                break;
            case "delete":
                String whether1= sqlDao.delById(sid);
                request.setAttribute("whether1",whether1);
                request.getRequestDispatcher("/NameResult.jsp").forward(request, response);
                break;
            case "insert":
                String whether2= sqlDao.insert(sid,username);
                request.setAttribute("whether2",whether2);
                request.getRequestDispatcher("/NameResult.jsp").forward(request, response);

                break;
            case "update":
                String whether3= sqlDao.update(sid,username);
                request.setAttribute("whether3",whether3);
                request.getRequestDispatcher("/NameResult.jsp").forward(request, response);
                break;
            case "queryAll":
                try {
                    List<Students> students= sqlDao.getAll();
                    request.setAttribute("studentsList", students);
                    sqlDao.getAll();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                request.getRequestDispatcher("/NameResult.jsp").forward(request, response);
                break;
            default:
                break;
        }
    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        this.doPost(request, response);
    }
}

在web.xml中配置Servlet

	<servlet>
        <servlet-name>SqlS</servlet-name>
        <servlet-class>csh.sqlServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>SqlS</servlet-name>
        <url-pattern>/mySqls</url-pattern>
    </servlet-mapping>

8.创建welcome.jsp页面

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8">
    <title>welcome</title>
</head>
<body>
    <form action="mySqls" method="post">
        学号：<input type="text" name="id"/>
        <input type="submit" name="submitButton" value="query" />
        <input type="submit" name="submitButton" value="delete" />
        <br>姓名：<input type="text" name="name"/>
        <input type="submit" name="submitButton" value="insert" />
        <input type="submit" name="submitButton" value="update" />
        <input type="submit" name="submitButton" value="queryAll" />
    </form>

    <form name="form" method="post" action="NameResult.jsp">

        　　 <input type="submit" value="跳转1">

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

9.创建NameResult.jsp页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="csh.*" %>
<%@ page import="java.util.List" %>

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>NameResult</title>
</head>
<body>
    <%--查询--%>
    <%
        String submitButton=request.getParameter("submitButton");
        switch(submitButton){
            case "query":%>
                <h1>学号<%=request.getParameter("id")%>的同学名字为：<%=request.getAttribute("name")%></h1>
            <%break;
                case "delete":
            %>
            <%=request.getAttribute("whether1")%>
            <%break;
                case "update":
            %>
            <%=request.getAttribute("whether3")%>
            <%break;
                case "insert":
            %>
            <%=request.getAttribute("whether2")%>
            <%break;
                case "queryAll":
            %>
            <table>
                <tr>
                    <th>学号</th>
                    <th>姓名</th>
                </tr>
            <%
                List<Students> studentsList = (List<Students>) request.getAttribute("studentsList");
                for(Students student: studentsList){
            %>
                <tr>
                    <td><%=student.getSid()%></td>
                    <td><%=student.getName()%></td>
                </tr>
            <%};%>

            </table>

            <%break;
                default:
                    break;
            };
            %>
    <form action="welcome.jsp" method="post">
        <input type="submit" value="返回" />
    </form>
</body>
</html>

10.创建过滤器 CharacterFilter 解决中文乱码问题（可选）

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class CharacterFilter implements Filter {
    public void init(FilterConfig filterConfig) throws ServletException {
    }
    public void doFilter(ServletRequest req, ServletResponse resp,
                         FilterChain chain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse) resp;
        // 拦截所有的请求 解决全站中文乱码
        // 指定 request 和 response 的编码
        request.setCharacterEncoding("utf-8"); // 只对消息体有效
        response.setContentType("text/html;charset=utf-8");
        // 在放行时 应该给目标资源一个request对象 让目标资源调用
        // getParameter时调到我们写的getParameter
        // 对request进行包装
        CharacterRequest characterRequest = new CharacterRequest(request);
        //放行
        chain.doFilter(characterRequest, response);
    }
    public void destroy() {
    }
}
// 针对 request 对象进行包装
// 继承 默认包装类HttpServletRequestWrapper
class CharacterRequest extends HttpServletRequestWrapper {
    public CharacterRequest(HttpServletRequest request) {
        super(request);
    }
    // 子类继承父类一定会覆写一些方法，此处用于重写getParamter()方法
    public String getParameter(String name) {
        // 调用 被包装对象的getParameter()方法 获得请求参数
        String value = super.getParameter(name);
        if (value == null)
            return null;
        // 判断请求方式
        String method = super.getMethod();
        if ("get".equalsIgnoreCase(method)) {
            try {
                value = new String(value.getBytes("iso-8859-1"), "utf-8");
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException(e);
            }
        }
        // 解决乱码后返回结果
        return value;
    }
}

在web.xml中配置过滤器

<filter>
        <filter-name>CharacterFilter</filter-name>
        <filter-class>csh.CharacterFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>CharacterFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

11.运行截图

必须创建一个package将编写的java文件放入，否则jsp页面无法实现正常访问