javaweb使用idea连接MySQL数据库和servlet进行增删改查操作

首先，需要下载并安装MySQL数据库，然后创建一个数据库，并创建一个表格存储数据。

接下来，打开IntelliJ IDEA，创建一个Web应用程序工程。

在工程的src目录下，创建一个Java类，用于连接和操作数据库。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBHelper {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
    private static String user = "root";
    private static String password = "123456";

    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    public static void closeConn(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static int executeUpdate(String sql) {
        Connection conn = null;
        Statement stmt = null;
        int result = 0;

        try {
            conn = getConn();
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }

        return result;
    }

    public static ResultSet executeQuery(String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConn();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;
    }
}

这个类中包含了获取数据库连接、关闭数据库连接、执行增删改查语句的方法。

接下来，创建一个Servlet的Java类，用于处理Http请求，并调用DBHelper中的方法操作数据库。

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;

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

public class StudentServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
    * @see HttpServlet#HttpServlet()
    */
    public StudentServlet() {
        super();
    }

    /**
    * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();

        String action = request.getParameter("action");

        if ("add".equals(action)) {
            String name = request.getParameter("name");
            int age = Integer.parseInt(request.getParameter("age"));
            String gender = request.getParameter("gender");

            String sql = "insert into student(name,age,gender) values('" + name + "'," + age + ",'" + gender + "')";
            int result = DBHelper.executeUpdate(sql);

            if (result > 0) {
                out.print("<script>alert('添加成功');window.location.href='index.jsp';</script>");
            } else {
                out.print("<script>alert('添加失败');history.go(-1);</script>");
            }

        } else if ("delete".equals(action)) {
            int id = Integer.parseInt(request.getParameter("id"));

            String sql = "delete from student where id=" + id;
            int result = DBHelper.executeUpdate(sql);

            if (result > 0) {
                out.print("<script>alert('删除成功');window.location.href='index.jsp';</script>");
            } else {
                out.print("<script>alert('删除失败');history.go(-1);</script>");
            }

        } else if ("update".equals(action)) {
            int id = Integer.parseInt(request.getParameter("id"));
            String name = request.getParameter("name");
            int age = Integer.parseInt(request.getParameter("age"));
            String gender = request.getParameter("gender");

            String sql = "update student set name='" + name + "',age=" + age + ",gender='" + gender + "' where id=" + id;
            int result = DBHelper.executeUpdate(sql);

            if (result > 0) {
                out.print("<script>alert('修改成功');window.location.href='index.jsp';</script>");
            } else {
                out.print("<script>alert('修改失败');history.go(-1);</script>");
            }

        } else if ("query".equals(action)) {
            String name = request.getParameter("name");

            String sql = "select id,name,age,gender from student where name like '%" + name + "%'";
            ResultSet rs = DBHelper.executeQuery(sql);

            StringBuilder sb = new StringBuilder("<table border='1'>");
            sb.append("<tr><th>编号</th><th>姓名</th><th>年龄</th><th>性别</th><th>操作</th></tr>");

            try {
                while (rs.next()) {
                    sb.append("<tr>");
                    sb.append("<td>" + rs.getInt("id") + "</td>");
                    sb.append("<td>" + rs.getString("name") + "</td>");
                    sb.append("<td>" + rs.getInt("age") + "</td>");
                    sb.append("<td>" + rs.getString("gender") + "</td>");
                    sb.append("<td><a href='update.jsp?id=" + rs.getInt("id") + "'>修改</a>  <a href='StudentServlet?action=delete&id=" + rs.getInt("id") + "'>删除</a></td>");
                    sb.append("</tr>");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBHelper.closeConn(DBHelper.getConn());
            }

            sb.append("</table>");
            out.print(sb.toString());

        } else {
            response.sendRedirect("index.jsp");
        }

        out.flush();
        out.close();
    }

    /**
    * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

这个Servlet中包含了添加学生记录、删除学生记录、修改学生记录、查询学生记录的方法。

最后，在Web应用程序中创建JSP页面，用于添加、修改、查询和展示学生信息的操作。

以查询学生信息页面query.jsp为例：

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生信息查询</title>
</head>
<body>
    <h2>学生信息查询</h2>
    <form action="StudentServlet" method="get">
        姓名：<input type="text" name="name">
        <input type="hidden" name="action" value="query">
        <input type="submit" value="查询">
    </form>
    <hr>
    <h3>查询结果</h3>
    <%= request.getAttribute("resultStr") %>
</body>
</html>

在这个页面中，用户可以输入学生姓名，然后点击查询按钮，调用Servlet中的查询学生记录的方法，返回查询结果并显示在页面上。

这样就完成了使用Idea连接MySQL数据库和Servlet进行增删改查操作的示例。