JAVA数据库增删改查

故事与他645

已于 2025-04-02 02:08:46 修改

阅读量666

点赞数 15

文章标签： java 数据库开发语言服务器网络

于 2025-04-01 22:58:38 首次发布

本文链接：https://blog.csdn.net/2301_78554096/article/details/146925176

版权

格式

Main.java(测试类)

package com.example;

import com.example.dao.UserDao;
import com.example.model.User;

public class Main {
    public static void main(String[] args) {
        UserDao userDao = new UserDao();
        
        // 测试添加用户
        System.out.println("=== 添加用户 ===");
        User user1 = new User();
        user1.setName("张三");
        user1.setEmail("zhangsan@example.com");
        int id1 = userDao.addUser(user1);
        System.out.println("添加用户成功，ID: " + id1);
        
        User user2 = new User();
        user2.setName("李四");
        user2.setEmail("lisi@example.com");
        int id2 = userDao.addUser(user2);
        System.out.println("添加用户成功，ID: " + id2);
        
        // 测试查询所有用户
        System.out.println("\n=== 所有用户 ===");
        userDao.getAllUsers().forEach(System.out::println);
        
        // 测试根据ID查询用户
        System.out.println("\n=== ID为1的用户 ===");
        User foundUser = userDao.getUserById(1);
        System.out.println(foundUser != null ? foundUser : "用户不存在");
        
        // 测试更新用户
        System.out.println("\n=== 更新用户 ===");
        if (foundUser != null) {
            foundUser.setEmail("zhangsan_new@example.com");
            boolean updated = userDao.updateUser(foundUser);
            System.out.println(updated ? "更新成功" : "更新失败");
            System.out.println("更新后的用户: " + userDao.getUserById(1));
        }
        
        // 测试删除用户
        System.out.println("\n=== 删除ID为2的用户 ===");
        boolean deleted = userDao.deleteUser(2);
        System.out.println(deleted ? "删除成功" : "删除失败");
        
        // 再次查询所有用户
        System.out.println("\n=== 当前所有用户 ===");
        userDao.getAllUsers().forEach(System.out::println);
    }
}

UserDao.java(数据访问对象)

package com.example.dao;

import com.example.model.User;
import com.example.util.DataSource;
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 UserDao {
    
    // 添加用户
    public int addUser(User user) {
        String sql = "INSERT INTO users(name, email) VALUES(?, ?)";
        int generatedId = -1;
        
        try (Connection conn = DataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());
            pstmt.executeUpdate();
            
            // 获取自增ID
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    generatedId = rs.getInt(1);
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return generatedId;
    }
    
    // 获取所有用户
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users";
        
        try (Connection conn = DataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                users.add(user);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return users;
    }
    
    // 根据ID获取用户
    public User getUserById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        User user = null;
        
        try (Connection conn = DataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setEmail(rs.getString("email"));
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return user;
    }
    
    // 更新用户
    public boolean updateUser(User user) {
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        boolean rowUpdated = false;
        
        try (Connection conn = DataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());
            pstmt.setInt(3, user.getId());
            rowUpdated = pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rowUpdated;
    }
    
    // 删除用户
    public boolean deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        boolean rowDeleted = false;
        
        try (Connection conn = DataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            rowDeleted = pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rowDeleted;
    }
}

User.java(实体类)

package com.example.model;

public class User {
    private int id;
    private String name;
    private String email;
    
    // 构造方法
    public User() {}
    
    public User(int id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }
    
    // Getter和Setter方法
    public int getId() {
        return id;
    }
    
    public void setId(int id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public String getEmail() {
        return email;
    }
    
    public void setEmail(String email) {
        this.email = email;
    }
    
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", email=" + email + "]";
    }
}

DataSource.java(连接池工具类)

package com.example.util;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataSource {
    private static final Logger logger = Logger.getLogger(DataSource.class.getName());
    private static final HikariConfig config = new HikariConfig();
    private static final HikariDataSource ds;

    static {
        try {
            // 配置数据库连接 (修改以下参数为你的实际值)
            config.setJdbcUrl("jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true");
            config.setUsername("aaa");  // 改为你的实际用户名
            config.setPassword("123456"); // 改为你的实际密码
            
            // 优化连接池配置
            config.addDataSourceProperty("cachePrepStmts", "true");
            config.addDataSourceProperty("prepStmtCacheSize", "250");
            config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
            config.setMaximumPoolSize(10);
            config.setMinimumIdle(5);
            config.setConnectionTimeout(30000);
            config.setIdleTimeout(600000);
            config.setMaxLifetime(1800000);
            config.setLeakDetectionThreshold(15000); // 添加泄漏检测
            
            // MySQL 8.0+ 特定配置
            config.addDataSourceProperty("useSSL", "false");
            config.addDataSourceProperty("allowPublicKeyRetrieval", "true");
            
            ds = new HikariDataSource(config);
            
            // 测试连接是否成功
            try (Connection conn = ds.getConnection()) {
                logger.info("数据库连接测试成功!");
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据库连接初始化失败", e);
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    private DataSource() {}
}

连接池

下载

https://repo1.maven.org/maven2/com/zaxxer/HikariCP/5.0.1/HikariCP-5.0.1.jar

https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar

https://repo1.maven.org/maven2/org/slf4j/slf4j-simple/2.0.9/slf4j-simple-2.0.9.jar

https://repo1.maven.org/maven2/org/slf4j/slf4j-api/2.0.9/slf4j-api-2.0.9.jar

配置

将这些JAR文件放入lib文件夹：

右键项目 > "Build Path" > "Configure Build Path"

选择"Libraries"标签 > "Add JARs"

选择lib文件夹下的这四个JAR文件，点击"OK"

MySQL8.0.12

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);