如何仅用Java和JDBC（无ORM）实现带泛型的通用CRUD DAO？

Got it, let's build a framework-agnostic generic DAO setup that works with your Employee and Job models. This approach keeps your code DRY by centralizing common CRUD logic, while letting each model handle its own entity-specific mapping.

Step 1: Define the Generic DAO Interface #

First, create an interface that outlines all core CRUD operations. We'll use a generic type <T> to make it reusable across any model.

import java.util.List;
import java.util.Optional;

public interface GenericDao<T> {
    // Create
    void save(T entity);

    // Read
    Optional<T> findById(Long id);
    List<T> findAll();

    // Update
    void update(T entity);

    // Delete
    void deleteById(Long id);
}

Step 2: Create Abstract Base DAO #

This abstract class implements the generic interface and handles all boilerplate code (like database connections, statement execution, and result set handling). Entity-specific logic (like mapping a ResultSet to your model) is left as abstract methods for concrete DAOs to implement.

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public abstract class AbstractGenericDao<T> implements GenericDao<T> {
    // Replace with your actual DB credentials
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_db";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "your_password";

    // Abstract methods for entity-specific logic
    protected abstract String getTableName();
    protected abstract String getInsertQuery();
    protected abstract String getUpdateQuery();
    protected abstract void setInsertParameters(PreparedStatement stmt, T entity) throws SQLException;
    protected abstract void setUpdateParameters(PreparedStatement stmt, T entity) throws SQLException;
    protected abstract T mapResultSetToEntity(ResultSet rs) throws SQLException;

    // Helper method to get DB connection
    protected Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    // Implement generic save
    @Override
    public void save(T entity) {
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(getInsertQuery())) {

            setInsertParameters(stmt, entity);
            stmt.executeUpdate();
        } catch (SQLException e) {
            handleException(e);
        }
    }

    // Implement generic find by ID
    @Override
    public Optional<T> findById(Long id) {
        String query = String.format("SELECT * FROM %s WHERE id = ?", getTableName());
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setLong(1, id);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return Optional.of(mapResultSetToEntity(rs));
            }
        } catch (SQLException e) {
            handleException(e);
        }
        return Optional.empty();
    }

    // Implement generic find all
    @Override
    public List<T> findAll() {
        List<T> entities = new ArrayList<>();
        String query = String.format("SELECT * FROM %s", getTableName());
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                entities.add(mapResultSetToEntity(rs));
            }
        } catch (SQLException e) {
            handleException(e);
        }
        return entities;
    }

    // Implement generic update
    @Override
    public void update(T entity) {
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(getUpdateQuery())) {

            setUpdateParameters(stmt, entity);
            stmt.executeUpdate();
        } catch (SQLException e) {
            handleException(e);
        }
    }

    // Implement generic delete by ID
    @Override
    public void deleteById(Long id) {
        String query = String.format("DELETE FROM %s WHERE id = ?", getTableName());
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setLong(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            handleException(e);
        }
    }

    // Common exception handling (customize this as needed)
    protected void handleException(SQLException e) {
        System.err.println("Database operation failed: " + e.getMessage());
        e.printStackTrace();
        // You could also throw a custom runtime exception here
    }
}

Step 3: Define Your Model Classes #

Let's assume your Employee and Job models look like this (adjust fields to match your actual schema):

// Employee.java
public class Employee {
    private Long id;
    private String firstName;
    private String lastName;
    private Double salary;
    private Long jobId;

    // Constructors, getters, setters
    public Employee() {}

    public Employee(String firstName, String lastName, Double salary, Long jobId) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.salary = salary;
        this.jobId = jobId;
    }

    // Getters and setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }
    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }
    public Double getSalary() { return salary; }
    public void setSalary(Double salary) { this.salary = salary; }
    public Long getJobId() { return jobId; }
    public void setJobId(Long jobId) { this.jobId = jobId; }
}

// Job.java
public class Job {
    private Long id;
    private String title;
    private Double minSalary;
    private Double maxSalary;

    // Constructors, getters, setters
    public Job() {}

    public Job(String title, Double minSalary, Double maxSalary) {
        this.title = title;
        this.minSalary = minSalary;
        this.maxSalary = maxSalary;
    }

    // Getters and setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public Double getMinSalary() { return minSalary; }
    public void setMinSalary(Double minSalary) { this.minSalary = minSalary; }
    public Double getMaxSalary() { return maxSalary; }
    public void setMaxSalary(Double maxSalary) { this.maxSalary = maxSalary; }
}

Step 4: Create Concrete DAOs for Each Model #

Implement DAOs for Employee and Job by extending the abstract class. Each will provide entity-specific queries and mapping logic.

EmployeeDao.java #

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeDao extends AbstractGenericDao<Employee> {
    @Override
    protected String getTableName() {
        return "employees";
    }

    @Override
    protected String getInsertQuery() {
        return "INSERT INTO employees (first_name, last_name, salary, job_id) VALUES (?, ?, ?, ?)";
    }

    @Override
    protected String getUpdateQuery() {
        return "UPDATE employees SET first_name = ?, last_name = ?, salary = ?, job_id = ? WHERE id = ?";
    }

    @Override
    protected void setInsertParameters(PreparedStatement stmt, Employee employee) throws SQLException {
        stmt.setString(1, employee.getFirstName());
        stmt.setString(2, employee.getLastName());
        stmt.setDouble(3, employee.getSalary());
        stmt.setLong(4, employee.getJobId());
    }

    @Override
    protected void setUpdateParameters(PreparedStatement stmt, Employee employee) throws SQLException {
        stmt.setString(1, employee.getFirstName());
        stmt.setString(2, employee.getLastName());
        stmt.setDouble(3, employee.getSalary());
        stmt.setLong(4, employee.getJobId());
        stmt.setLong(5, employee.getId());
    }

    @Override
    protected Employee mapResultSetToEntity(ResultSet rs) throws SQLException {
        Employee employee = new Employee();
        employee.setId(rs.getLong("id"));
        employee.setFirstName(rs.getString("first_name"));
        employee.setLastName(rs.getString("last_name"));
        employee.setSalary(rs.getDouble("salary"));
        employee.setJobId(rs.getLong("job_id"));
        return employee;
    }
}

JobDao.java #

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JobDao extends AbstractGenericDao<Job> {
    @Override
    protected String getTableName() {
        return "jobs";
    }

    @Override
    protected String getInsertQuery() {
        return "INSERT INTO jobs (title, min_salary, max_salary) VALUES (?, ?, ?)";
    }

    @Override
    protected String getUpdateQuery() {
        return "UPDATE jobs SET title = ?, min_salary = ?, max_salary = ? WHERE id = ?";
    }

    @Override
    protected void setInsertParameters(PreparedStatement stmt, Job job) throws SQLException {
        stmt.setString(1, job.getTitle());
        stmt.setDouble(2, job.getMinSalary());
        stmt.setDouble(3, job.getMaxSalary());
    }

    @Override
    protected void setUpdateParameters(PreparedStatement stmt, Job job) throws SQLException {
        stmt.setString(1, job.getTitle());
        stmt.setDouble(2, job.getMinSalary());
        stmt.setDouble(3, job.getMaxSalary());
        stmt.setLong(4, job.getId());
    }

    @Override
    protected Job mapResultSetToEntity(ResultSet rs) throws SQLException {
        Job job = new Job();
        job.setId(rs.getLong("id"));
        job.setTitle(rs.getString("title"));
        job.setMinSalary(rs.getDouble("min_salary"));
        job.setMaxSalary(rs.getDouble("max_salary"));
        return job;
    }
}

Step 5: Test the Implementation #

Here's how you'd use these DAOs in your application:

public class Main {
    public static void main(String[] args) {
        // Test JobDao
        JobDao jobDao = new JobDao();
        Job newJob = new Job("Software Engineer", 60000.0, 120000.0);
        jobDao.save(newJob);

        // Find all jobs
        System.out.println("All Jobs:");
        jobDao.findAll().forEach(job -> System.out.println(job.getTitle()));

        // Test EmployeeDao
        EmployeeDao employeeDao = new EmployeeDao();
        Employee newEmployee = new Employee("John", "Doe", 85000.0, 1L);
        employeeDao.save(newEmployee);

        // Find employee by ID
        employeeDao.findById(1L).ifPresent(emp -> 
            System.out.println("\nFound Employee: " + emp.getFirstName() + " " + emp.getLastName())
        );
    }
}

Key Notes: #

• Flexibility: Swap out the JDBC connection logic with a connection pool or other data source without changing the DAO structure.
• Extensibility: Add more generic methods (like findByCriteria) to the GenericDao interface and implement them in AbstractGenericDao if needed.
• Error Handling: The handleException method is a placeholder—replace it with proper logging and custom exception handling for production code.

内容的提问来源于stack exchange，提问作者A.Gashi