JAVA数据库增删改查

格式

 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("[e-mail]");int id1 = userDao.addUser(user1);System.out.println("添加用户成功，ID: " + id1);User user2 = new User();user2.setName("李四");user2.setEmail("[e-mail]");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("[e-mail]");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();// 获取自增IDtry (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;}@Overridepublic 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
);

结果