Spring Data JPA结合PostgreSQL如何获取PUT接口及更新语句的更新列名？

Absolutely! There are several practical ways to track which columns actually had value changes in a PUT REST API when working with Spring Data JPA and PostgreSQL. Let’s walk through the most reliable approaches:

1. Entity-Level Change Tracking Using JPA Callbacks #

You can leverage JPA's lifecycle callbacks (like @PreUpdate) to capture the original state of an entity before updates, then compare it with the new state to identify changed columns.

Implementation Steps: #

• Add a transient field to your entity to cache the original values.
• Use @PrePersist and @PreUpdate to populate this cache with the entity's current state from the database.
• Create a method to compare the cached original values with the current entity values and return the list of changed columns.

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String email;
    private String password;

    // Transient field to store original state (won't be persisted)
    @Transient
    private Map<String, Object> originalValues = new HashMap<>();

    @Autowired
    @Transient
    private EntityManager entityManager;

    @PreUpdate
    public void captureOriginalState() {
        // Fetch the current persisted state from the database
        User persistedUser = entityManager.find(User.class, this.id);
        if (persistedUser != null) {
            originalValues.put("username", persistedUser.getUsername());
            originalValues.put("email", persistedUser.getEmail());
            originalValues.put("password", persistedUser.getPassword());
        }
    }

    // Method to get all columns with changed values
    public List<String> getChangedColumns() {
        List<String> changedColumns = new ArrayList<>();
        if (!Objects.equals(this.username, originalValues.get("username"))) {
            changedColumns.add("username");
        }
        if (!Objects.equals(this.email, originalValues.get("email"))) {
            changedColumns.add("email");
        }
        if (!Objects.equals(this.password, originalValues.get("password"))) {
            changedColumns.add("password");
        }
        return changedColumns;
    }

    // Getters and Setters omitted for brevity
}

Usage in Your REST Controller: #

@PutMapping("/users/{id}")
public ResponseEntity<User> updateUser(@PathVariable Long id, @RequestBody User updatedUser) {
    User existingUser = userRepository.findById(id)
            .orElseThrow(() -> new RuntimeException("User not found with id: " + id));

    // Merge updated values into existing entity
    existingUser.setUsername(updatedUser.getUsername());
    existingUser.setEmail(updatedUser.getEmail());
    existingUser.setPassword(updatedUser.getPassword());

    // Get changed columns before saving
    List<String> changedColumns = existingUser.getChangedColumns();
    System.out.println("Columns updated with value changes: " + changedColumns);

    userRepository.save(existingUser);
    return ResponseEntity.ok(existingUser);
}

Pros: Encapsulates change tracking within the entity, reusable across services.
Cons: Requires manual comparison for each field; not ideal for entities with many attributes.

2. DTO vs Entity Comparison (API Layer) #

A straightforward approach is to compare the incoming PUT DTO with the existing entity fetched from the database. This gives you full control over which fields to check.

Example Implementation: #

// DTO for PUT requests
public record UserUpdateDTO(String username, String email, String password) {}

@RestController
@RequestMapping("/users")
public class UserController {
    @Autowired
    private UserRepository userRepository;

    @PutMapping("/{id}")
    public ResponseEntity<User> updateUser(@PathVariable Long id, @RequestBody UserUpdateDTO dto) {
        User existingUser = userRepository.findById(id)
                .orElseThrow(() -> new RuntimeException("User not found"));

        List<String> changedFields = new ArrayList<>();

        // Compare each field and track changes
        if (!Objects.equals(dto.username(), existingUser.getUsername())) {
            existingUser.setUsername(dto.username());
            changedFields.add("username");
        }
        if (!Objects.equals(dto.email(), existingUser.getEmail())) {
            existingUser.setEmail(dto.email());
            changedFields.add("email");
        }
        if (!Objects.equals(dto.password(), existingUser.getPassword())) {
            existingUser.setPassword(dto.password());
            changedFields.add("password");
        }

        if (!changedFields.isEmpty()) {
            userRepository.save(existingUser);
            System.out.println("Updated columns: " + String.join(", ", changedFields));
        }

        return ResponseEntity.ok(existingUser);
    }
}

Pros: Simple to implement, no entity modifications needed, allows skipping unnecessary saves if no changes are detected.
Cons: Manual field comparison can become tedious for large DTOs/entities.

3. Database-Level Tracking with PostgreSQL Triggers #

If you need persistent audit logs or want to track changes across all applications using the database, you can use PostgreSQL triggers to capture updated columns.

Steps: #

1. Create an audit log table to store change details:

CREATE TABLE user_audit_log (
    id SERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    changed_columns TEXT[],
    change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. Create a trigger function that captures changed columns:

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Get all columns where new value != old value
    INSERT INTO user_audit_log(user_id, changed_columns)
    VALUES (NEW.id, ARRAY(
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'users'
        AND column_name NOT IN ('id', 'created_at') -- Exclude non-updatable columns
        AND (OLD.* IS DISTINCT FROM NEW.*)
    ));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. Attach the trigger to your users table:

CREATE TRIGGER track_user_updates
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

Usage in Your API: #

After updating the entity, you can query the user_audit_log table to retrieve the changed columns for the specific user ID.

Pros: Captures all changes at the database level, works for any application accessing the DB.
Cons: Adds database overhead, requires SQL knowledge, and changes are stored separately from the entity.

4. Using Hibernate Envers (Advanced Auditing) #

Hibernate Envers is a built-in auditing tool that tracks all entity changes. You can use it to fetch the revision history and identify which columns were modified in a specific update.

Setup: #

1. Add the Envers dependency to your pom.xml (Maven):

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-envers</artifactId>
    <version>${hibernate.version}</version>
</dependency>

2. Annotate your entity with @Audited:

@Entity
@Audited
public class User {
    // Entity fields and methods
}

3. Fetch the revision details in your service:

@Autowired
private AuditReader auditReader;

public List<String> getChangedColumnsForUser(Long userId) {
    List<Number> revisions = auditReader.getRevisions(User.class, userId);
    if (revisions.size() < 2) {
        return Collections.emptyList();
    }
    Number latestRevision = revisions.get(revisions.size() - 1);
    Number previousRevision = revisions.get(revisions.size() - 2);

    User currentUser = auditReader.find(User.class, userId, latestRevision);
    User previousUser = auditReader.find(User.class, userId, previousRevision);

    // Compare the two versions to find changed columns
    List<String> changedColumns = new ArrayList<>();
    if (!Objects.equals(currentUser.getUsername(), previousUser.getUsername())) {
        changedColumns.add("username");
    }
    // Repeat for other fields
    return changedColumns;
}

Pros: Comprehensive auditing out of the box, tracks full revision history.
Cons: Adds complexity, requires learning Envers API, and stores additional audit tables.

内容的提问来源于stack exchange，提问作者Bushra Saifi