如何用单一方法优雅处理多种数据库操作？

Nice question! I’ve been in this exact situation before—having repetitive database connection/cleanup code across DAO methods is a total pain, and consolidating it into a single, reusable method is a great way to keep your code DRY (Don’t Repeat Yourself) and maintainable. Here’s a clean approach using Java’s functional interfaces and strategy pattern that works perfectly for this scenario:

Step 1: Define a Functional Interface for Database Operations #

First, create a small functional interface that encapsulates any database operation. This interface will take a Connection and return a generic result (or null for void operations):

@FunctionalInterface
private interface DbOperation<T> {
    T execute(Connection conn) throws SQLException;
}

This lets us pass any database logic as a lambda or method reference later.

Step 2: Build a Generic Execution Method #

In your ServiceDAO implementation class, write a single method that handles all the boilerplate: getting the connection, managing exceptions, closing resources, and executing the specific operation. This is where all the repetitive code lives:

private <T> T executeDbOperation(DbOperation<T> operation) {
    Connection conn = null;
    try {
        // Replace with your actual database connection logic
        conn = getDatabaseConnection();
        
        // Delegate the actual operation to the passed-in strategy
        return operation.execute(conn);
    } catch (SQLException e) {
        // Handle exceptions consistently—wrap in a custom exception, log, etc.
        throw new DatabaseOperationException("Failed to run database operation", e);
    } finally {
        // Ensure connection is closed properly
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // Log this error instead of throwing (avoid masking original exception)
                LoggerFactory.getLogger(getClass()).error("Failed to close database connection", e);
            }
        }
    }
}

Step 3: Refactor Your DAO Methods to Use the Generic Method #

Now you can rewrite all your original ServiceDAO methods to use this single execution method. Each method just focuses on its specific SQL logic—no more repeating connection/cleanup code:

// Your ServiceDAO implementation class
public class ServiceDAOImpl implements ServiceDAO {

    // ... existing fields, constructor, getDatabaseConnection() method ...

    @Override
    public void addRecord(UserRecord userRecord) {
        executeDbOperation(conn -> {
            String sql = "INSERT INTO user_records (id, name, email) VALUES (?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                // Set parameters based on your UserRecord
                stmt.setString(1, userRecord.getId());
                stmt.setString(2, userRecord.getName());
                stmt.setString(3, userRecord.getEmail());
                stmt.executeUpdate();
                return null; // Void operation, return null
            }
        });
    }

    @Override
    public void delRecord(UserRecord userRecord) {
        executeDbOperation(conn -> {
            String sql = "DELETE FROM user_records WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, userRecord.getId());
                stmt.executeUpdate();
                return null;
            }
        });
    }

    @Override
    public void updateRecord(UserRecord oldRecord, UserRecord newRecord) {
        executeDbOperation(conn -> {
            String sql = "UPDATE user_records SET name = ?, email = ? WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, newRecord.getName());
                stmt.setString(2, newRecord.getEmail());
                stmt.setString(3, oldRecord.getId());
                stmt.executeUpdate();
                return null;
            }
        });
    }

    @Override
    public List<UserRecord> getAllRecords() {
        return executeDbOperation(conn -> {
            String sql = "SELECT id, name, email FROM user_records";
            try (PreparedStatement stmt = conn.prepareStatement(sql);
                 ResultSet rs = stmt.executeQuery()) {
                
                List<UserRecord> records = new ArrayList<>();
                while (rs.next()) {
                    UserRecord record = new UserRecord();
                    record.setId(rs.getString("id"));
                    record.setName(rs.getString("name"));
                    record.setEmail(rs.getString("email"));
                    records.add(record);
                }
                return records;
            }
        });
    }

    // ... the executeDbOperation and DbOperation code from above ...
}

Why This Works #

• Eliminates Redundancy: All connection management, exception handling, and resource cleanup lives in one place—no more copying that code across every DAO method.
• Single Responsibility: Each DAO method only handles its specific business logic (SQL, parameter mapping, result mapping) instead of worrying about database infrastructure.
• Flexible & Extensible: Adding a new database operation is as simple as writing a new lambda that calls executeDbOperation, no changes to the core boilerplate needed.
• Readable: Each method’s intent is clear at a glance—you don’t have to wade through boilerplate to see what the actual database action is.

If you’re using a framework like Spring, this is exactly how JdbcTemplate works under the hood! But if you’re rolling your own DAO layer, this approach is lightweight and elegant.

内容的提问来源于stack exchange，提问作者the.Legend