Implementing Logical Delete with DEL_YN Column in Spring Boot + JPA + Oracle

on


Implementing Logical Delete with DEL_YN Column in Spring Boot + JPA + Oracle

When working with databases, physically deleting data often isn’t the best approach—especially in enterprise applications where audit trails, data recovery, and debugging are critical. Instead, a logical delete can be a great alternative. This method involves marking records as "deleted" without physically removing them from the database.

In this post, I’ll guide you through implementing logical delete functionality in a Spring Boot application that uses JPA and Oracle. Specifically, we’ll use a DEL_YN column to track the delete status of records.

Why Use Logical Delete?

Logical delete is particularly useful in scenarios where:

  • Audit trails are required for regulatory or business purposes.
  • Soft recovery of deleted data is needed.
  • Historical data needs to be preserved for analysis or reporting.

Instead of deleting rows, we update a specific column (e.g., DEL_YN) to 'Y' to indicate a record is "deleted." This keeps the data accessible while marking it as inactive.

Database Schema

Let’s assume we have a table tb_abmk001c in Oracle with the following columns:

CREATE TABLE tb_abmk001c (
    data_reg_dtti VARCHAR2(20) PRIMARY KEY,
    del_yn CHAR(1) DEFAULT 'N',
    other_column1 VARCHAR2(255),
    other_column2 VARCHAR2(255)
);

The DEL_YN column is the key here:

  • 'N': Active record.
  • 'Y': Deleted record.

Requirements

We’ll implement a REST API to handle logical deletion. This will:

  1. Take dataRegDtti (primary key) as input.
  2. Update the DEL_YN column to 'Y' instead of physically deleting the row.

Implementation: Logical Delete Service

Here’s how you can implement logical deletion in your Spring Boot project.

Step 1: Define the Entity

First, define the entity that maps to your database table:

@Entity
@Table(name = "tb_abmk001c")
public class TbAbmk001C {
    @Id
    private String dataRegDtti;

    @Column(name = "del_yn", nullable = false, length = 1)
    private String delYn = "N"; // Default to 'N'

    @Column(name = "other_column1")
    private String otherColumn1;

    @Column(name = "other_column2")
    private String otherColumn2;

    @PrePersist
    public void prePersist() {
        if (this.dataRegDtti == null) {
            this.dataRegDtti = generateCurrentTimestampWithMicros();
        }
    }

    private String generateCurrentTimestampWithMicros() {
        return LocalDateTime.now()
                .format(DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSSSSS"));
    }

    // Getters and setters omitted for brevity
}

Highlights:

  • @PrePersist: Automatically sets dataRegDtti with a 20-character timestamp (yyyyMMddHHmmssSSSSSS) before the entity is persisted.
  • DEL_YN defaults to 'N'.

Step 2: Create the Repository

Create a JPA repository interface for the entity:

@Repository
public interface TbAbmk001CRepository extends JpaRepository<TbAbmk001C, String> {
    @Modifying
    @Transactional
    @Query("UPDATE TbAbmk001C e SET e.delYn = 'Y' WHERE e.dataRegDtti = :dataRegDtti")
    int updateDelYnByDataRegDtti(@Param("dataRegDtti") String dataRegDtti);
}

Step 3: Write the Service

Next, implement the service logic to call the repository method:

@Service
public class TbAbmk001CService {
    private final TbAbmk001CRepository repository;

    public TbAbmk001CService(TbAbmk001CRepository repository) {
        this.repository = repository;
    }

    public boolean logicalDelete(String dataRegDtti) {
        int rowsUpdated = repository.updateDelYnByDataRegDtti(dataRegDtti);
        return rowsUpdated > 0;
    }
}

Step 4: Expose the API

Finally, expose this functionality through a REST API:

@RestController
@RequestMapping("/api/tbAbmk001c")
public class TbAbmk001CController {
    private final TbAbmk001CService service;

    public TbAbmk001CController(TbAbmk001CService service) {
        this.service = service;
    }

    @DeleteMapping("/{dataRegDtti}")
    public ResponseEntity<String> logicalDelete(@PathVariable String dataRegDtti) {
        boolean success = service.logicalDelete(dataRegDtti);
        if (success) {
            return ResponseEntity.ok("Record marked as deleted.");
        } else {
            return ResponseEntity.status(HttpStatus.NOT_FOUND).body("Record not found.");
        }
    }
}

Testing the API

Test the API using tools like Postman or cURL.

  • Request:
DELETE /api/tbAbmk001c/20250220123045123456
  • Response (Success):
{
    "message": "Record marked as deleted."
}
  • Response (Failure):
{
    "message": "Record not found."
}

Additional Features

  1. Filter Active Records: Modify your queries to exclude logically deleted records:

    @Query("SELECT e FROM TbAbmk001C e WHERE e.delYn = 'N'")
List<TbAbmk001C> findAllActiveRecords();

  2. Undo Logical Delete: Add an endpoint to set DEL_YN back to 'N'.

  3. Audit Tracking: Use columns like deleted_at or deleted_by to capture additional metadata about deletions.


Conclusion

By using logical delete, you can ensure data integrity and compliance with business requirements without physically removing data. With Spring Boot, JPA, and Oracle, implementing this feature is straightforward. Whether you’re building audit systems or preserving historical data, this approach strikes the right balance between flexibility and performance.

Comments

Post a Comment