idea配合Tomcat实现对Mysql数据库的增删改查

最新推荐文章于 2024-05-29 16:20:51 发布

Ctrl精

最新推荐文章于 2024-05-29 16:20:51 发布

阅读量2k

点赞数 2

分类专栏： Java javaWeb 文章标签： mysql java intellij idea tomcat 增删改查

本文链接：https://blog.csdn.net/qq_43468607/article/details/109988464

版权

Java 同时被 2 个专栏收录

54 篇文章 5 订阅

订阅专栏

javaWeb

9 篇文章 0 订阅

订阅专栏

首先在数据库中创建我们需要使用的数据库newdb3和角色列表

careate databases;
create table if not exists hero(
    id int primary key auto_increment,
    name varchar(20),
    type varchar(20),
    money int
);

连接数据库的配置文件jdbc.properties放在resources文件夹中

文件中的配置信息：

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
db.username=root
db.password=root
db.maxActive=10
db.initialSize=2

在pox.xml文件中的如下标签中

<dependencies></dependencies>

添加druid数据库连接池和mysql数据库驱动包

<!-- 连接MySQL数据库的依赖 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.15</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.21</version>
</dependency>

这时候需要刷新一下Maven项目
在这里插入图片描述
等待下载jar文件，直到下载完成

编写DBUtils文件，读取数据库配置文件，同时调用数据库连接池

import com.alibaba.druid.pool.DruidDataSource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

/*
* 数据库工具类中做的事儿:
* 1. 读取配置文件
* 2. 初始化连接池对象
* 3. 从连接池对象中获取连接
* */
public class DBUtils {

    private static DruidDataSource dds;
    static {
        Properties p = new Properties();
        InputStream ips =
                DBUtils.class.getClassLoader()
                        .getResourceAsStream("jdbc.properties");
        try {
            p.load(ips);
        } catch (IOException e) {
            e.printStackTrace();
        }
        String driver = p.getProperty("db.driver");
        String url = p.getProperty("db.url");
        String username = p.getProperty("db.username");
        String password = p.getProperty("db.password");

        dds = new DruidDataSource();
        dds.setDriverClassName(driver);
        dds.setUrl(url);
        dds.setUsername(username);
        dds.setPassword(password);
        //从配置文件中读取最大连接数量和初始连接数量
        String maxActive = p.getProperty("db.maxActive");
        String initialSize = p.getProperty("db.initialSize");
        dds.setInitialSize(Integer.parseInt(initialSize));
        dds.setMaxActive(Integer.parseInt(maxActive));
        dds.close();
    }

    public static Connection getConn() throws Exception {
        return dds.getConnection();
    }
}

接下来实现的步骤是：

添加英雄步骤

创建add.html页面 , 页面中添加form表单提交地址为add 准备三个文本框和一个提交按钮

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加英雄</title>
</head>
<body>
    <h3>添加英雄</h3>
<form method="get" action="/add">
    <input type="text" placeholder="英雄名称" name="name">
    <input type="text" placeholder="英雄类型" name="type">
    <input type="text" placeholder="价格" name="money">
    <input type="submit" value="添加英雄">
</form>
</body>
</html>

创建AddServlet,处理路径为/add, 在doGet方法中获取传递过来的参数, 获取数据库连接把接收到的参数保存到hero表中最后重定向到列表页面

import cn.util.DBUtils;

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.sql.Connection;
import java.sql.PreparedStatement;

@WebServlet(name = "AddServlet" ,urlPatterns = "/add")
public class AddServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            response.setContentType("text/html;charset = utf-8");
            request.setCharacterEncoding("UTF-8");
            String name = request.getParameter("name");
            String type = request.getParameter("type");
            String money = request.getParameter("money");
            try (Connection conn = DBUtils.getConn()) {
                String sql = "insert into hero values(null,?,?,?)";
                PreparedStatement pr = conn.prepareStatement(sql);
                pr.setString(1, name);
                pr.setString(2, type);
                pr.setInt(3, Integer.parseInt(money));
                pr.executeUpdate();
                response.getWriter().println("添加成功！");
                response.getWriter().println("<button><a href = '/select'>查询英雄</a></button>");
                response.sendRedirect("/select");
                pr.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
    }
}

查询英雄步骤

创建FindAllServlet 处理路径为/findall , 在doGet方法中获取数据库连接查询到hero表中所有的数据通过PrintWriter 把数据返回给浏览器显示

import cn.util.DBUtils;

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.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

@WebServlet(name = "FindAllServlet", urlPatterns = "/select")
public class FindAllServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        request.setCharacterEncoding("UTF-8");
        try (Connection conn = DBUtils.getConn()) {
            String sql = "select * from newdb3.hero;";
            PreparedStatement pr = conn.prepareStatement(sql);
            ResultSet rs = pr.executeQuery(sql);
            PrintWriter pk = response.getWriter();
            pk.println("<table border = 1>");
            pk.println("<tr><th>id</th><th>名字</th><th>类型</th><th>价格</th><th>操作</th></tr>");
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String type = rs.getString(3);
                int money = rs.getInt(4);
                PrintWriter pw = response.getWriter();
                pk.println("<tr>");
                pk.println("<td>" + id + "</td>");
                pk.println("<td>" + name + "</td>");
                pk.println("<td>" + type + "</td>");
                pk.println("<td>" + money + "</td>");
                pk.println("<td><a href = 'delete?id="+ id +"'>删除</a></td>");
                pk.println("</tr>");
            }
            pk.println("</table>");
            pk.println("<button><a href = 'add.html'>添加英雄</a></button>");
            pr.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

删除英雄步骤

在FindAllServlet中返回表格时多添加一列删除的超链接, 连接地址为 delete?id=xxx
创建DeleteServlet 处理路径为 /delete 在doGet方法中获取传递过来的id , 获取数据库连接通过SQL语句去hero表中删除掉对应的数据重定向到列表页面

import cn.util.DBUtils;

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.sql.Connection;
import java.sql.PreparedStatement;

@WebServlet(name = "DeleteServlet", urlPatterns = "/delete")
public class DeleteServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String id = request.getParameter("id");
        System.out.println("已经删除！" + id);
        try(Connection conn = DBUtils.getConn()) {
             String sql = "delete from hero where id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, Integer.parseInt(id));
            ps.executeUpdate();
            System.out.println("删除完成！");
            response.sendRedirect("/select");
        } catch (Exception e) {
             e.printStackTrace();
        }

    }
}