学习Dao类中人员相关的数据库操作方法

1. 数据库表结构
   假设存在一个名为 persons 的表，用于存储人员信息，表结构如下：
   CREATE TABLE persons ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT, gender VARCHAR(10), email VARCHAR(100) UNIQUE );
2. 基本的 Dao 类实现及操作方法
   `import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.ArrayList;
   import java.util.List;

public class PersonDao {
private static final String URL = "jdbc:mysql://localhost:3306/your_database";
private static final String USER = "your_username";
private static final String PASSWORD = "your_password";

// 获取数据库连接
private Connection getConnection() throws SQLException {
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    return DriverManager.getConnection(URL, USER, PASSWORD);
}

// 添加人员信息
public boolean addPerson(String name, int age, String gender, String email) {
    String sql = "INSERT INTO persons (name, age, gender, email) VALUES (?,?,?,?)";
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, name);
        pstmt.setInt(2, age);
        pstmt.setString(3, gender);
        pstmt.setString(4, email);
        int rows = pstmt.executeUpdate();
        return rows > 0;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// 根据 ID 删除人员信息
public boolean deletePerson(int id) {
    String sql = "DELETE FROM persons WHERE id = ?";
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setInt(1, id);
        int rows = pstmt.executeUpdate();
        return rows > 0;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// 根据 ID 更新人员信息
public boolean updatePerson(int id, String name, int age, String gender, String email) {
    String sql = "UPDATE persons SET name = ?, age = ?, gender = ?, email = ? WHERE id = ?";
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, name);
        pstmt.setInt(2, age);
        pstmt.setString(3, gender);
        pstmt.setString(4, email);
        pstmt.setInt(5, id);
        int rows = pstmt.executeUpdate();
        return rows > 0;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// 根据 ID 查询人员信息
public Person getPersonById(int id) {
    String sql = "SELECT * FROM persons WHERE id = ?";
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setInt(1, id);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            String name = rs.getString("name");
            int age = rs.getInt("age");
            String gender = rs.getString("gender");
            String email = rs.getString("email");
            return new Person(id, name, age, gender, email);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

// 查询所有人员信息
public List<Person> getAllPersons() {
    List<Person> persons = new ArrayList<>();
    String sql = "SELECT * FROM persons";
    try (Connection conn = getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            String gender = rs.getString("gender");
            String email = rs.getString("email");
            persons.add(new Person(id, name, age, gender, email));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return persons;
}

}

class Person {
private int id;
private String name;
private int age;
private String gender;
private String email;

public Person(int id, String name, int age, String gender, String email) {
    this.id = id;
    this.name = name;
    this.age = age;
    this.gender = gender;
    this.email = email;
}

// Getters and Setters
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 int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

public String getGender() {
    return gender;
}

public void setGender(String gender) {
    this.gender = gender;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

}`