使用Java与MySQL数据库进行CRUD操作实例-蒲公英云

使用Java与MySQL数据库进行CRUD操作实例

以下是一个简单的Java和MySQL CRUD操作的例子。我们将创建一个User类，然后在Java中使用JDBC来执行CRUD操作。

User.java（实体类）

import java.sql.Date;
public class User {
    private int id;
    private String name;
    private Date dateOfBirth;
    // Getters and Setters
    // ...
}

CRUDOperations.java（进行CRUD操作的Java文件）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
public class CRUDOperations {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/userdb";
    private static final String USER_TABLE = "users";
    public void createUser(User user) throws SQLException {
        Connection conn = DriverManager.getConnection(DB_URL);
        String sql = "INSERT INTO " + USER_TABLE + " (id, name, dateOfBirth) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, user.getId()));
            pstmt.setString(2, user.getName());
            pstmt.setDate(3, LocalDate.of(user.getDateOfBirth().getYear(), user.dateOfBirth()..getMonthValue(), user.dateOfBirth().getDate())));
            pstmt.executeUpdate();
        } finally {
            conn.close();
        }
    }
    // Read operations
    public User getUserById(int id) throws SQLException {
        Connection conn = DriverManager.getConnection(DB_URL);
        String sql = "SELECT * FROM " + USER_TABLE + " WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id));
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id")));
                    user.setName(rs.getString("name"));
                    user.setDateOfBirth(rs.getDate("dateOfBirth")));
                    return user;
                }
            }
        } finally {
            conn.close();
        }
        // If no user found with the given id
        throw new SQLException("User not found with id: " + id);
    }
    // Update operations
    public void updateUser(User user) throws SQLException {
        Connection conn = DriverManager.getConnection(DB_URL);
        String sql = "UPDATE " + USER_TABLE + " SET name = ?, dateOfBirth = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, user.getName()));
            pstmt.setDate(2, LocalDate.of(user.getDateOfBirth().getYear()), user.dateOfBirth().MONTHValue(), user.dateOfBirth().DATE()));
            pstmt.setInt(3, user.getId()));
            pstmt.executeUpdate();
        } finally {
            conn.close();
        }
    }
    // Delete operations
    public void deleteUser(int id) throws SQLException {
        Connection conn = DriverManager.getConnection(DB_URL);
        String sql = "DELETE FROM " + USER_TABLE + " WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id));
            pstmt.executeUpdate();
        } finally {
            conn.close();
        }
    }
}