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

原创一时失言乱红尘 2024-12-07 05:24 22阅读 0赞

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

1. User.java（实体类）

```java
import java.sql.Date;

public class User {
    private int id;
    private String name;
    private Date dateOfBirth;

// Getters and Setters
    // ...
}
```

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

```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();
        }
    }
}
```

这个Java文件中，`CRUDOperations`类包含了创建、读取、更新和删除用户的所有操作。这些操作都是通过JDBC来实现的。