CODEWITHSUNDEEP
spring-bootprimary-keyjparepository
Arul
Arul

Reputation: 69

JPARepository.save method is not detecting Primary Key Violation trying to insert a record the second time

In a Spring Boot application I tried directly inserting in SQL Server.

CREATE TABLE T_FLATTEN_PROCESSING_INFO(
    [HOST_ID] [varchar](150),
    [BEGIN_VERSION] [bigint],
    [END_VERSION] [bigint],
    PROCESSING_DT_TM DATETIME2,
    PRIMARY KEY (BEGIN_VERSION, END_VERSION)
)

Repository

@Repository
public interface FlattenProcessingInfoRepo extends JpaRepository<TableFlattenProcessingInfo, TableFlattenProcessingInfoCPK> {
}

Entity Object

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "T_FLATTEN_PROCESSING_INFO")
public class TableFlattenProcessingInfo {

    @EmbeddedId
    private TableFlattenProcessingInfoCPK tableFlattenProcessingInfoCPK;

    @Column(name = "HOST_ID")
    private String hostId;

    @Column(name = "PROCESSING_DT_TM")
    @DateTimeFormat(pattern = "MM/DD/YYYY HH:mm:ss")
    private LocalDateTime processingDateTime;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Embeddable
public class TableFlattenProcessingInfoCPK {
    @Column(name = "BEGIN_VERSION")
    private long beginVersion;

    @Column(name = "END_VERSION")
    private long endVersion;
}

Service

@Transactional(transactionManager = "standardizedDataSourceTransactionManager", propagation = Propagation.REQUIRES_NEW)
    public boolean processSave(long beginVersion, long endVersion, String hostId) {

        boolean success = false;

        try {
            TableFlattenProcessingInfo tableFlattenProcessingInfo = new TableFlattenProcessingInfo();
            TableFlattenProcessingInfoCPK tableFlattenProcessingInfoCPK = new TableFlattenProcessingInfoCPK();

            tableFlattenProcessingInfoCPK.setBeginVersion(beginVersion);
            tableFlattenProcessingInfoCPK.setEndVersion(endVersion);

            tableFlattenProcessingInfo.setTableFlattenProcessingInfoCPK(tableFlattenProcessingInfoCPK);

            tableFlattenProcessingInfo.setHostId(hostId);
            tableFlattenProcessingInfo.setProcessingDateTime(DateUtil.convertUTCToEST(new Date(),
                    TimeZone.getTimeZone("UTC"),
                    TimeZone.getTimeZone("America/New_York")));

            flattenProcessingInfoRepo.save(tableFlattenProcessingInfo);
            success = true;
        } catch (Exception e) {
            log.info("Error Inserting Flatten Batch means batch already taken up for" +
                    " processing by another Pod -> {}", e.getMessage());

            success = false;
        }

        return success;
    }

Test

boolean retVal = flattenProcessingInfoService.processSave(1000, 2000, "localhost");
boolean retVal = flattenProcessingInfoService.processSave(1000, 2000, "localhost");

I try to insert the same record twice. It does not insert twice; I see only one record in the DB.

Why don't I get a Primary Key Violation exception the second time?

Upvotes: 0

Views: 57

Answers (1)

Idol
Idol

Reputation: 570

You don't have any errors because method flattenProcessingInfoRepo.save(entity) checks if an entity is new or not. If it is new, it issues an INSERT statement; if it is old, it issues an UPDATE statement. Take a look at SimpleJpaRepository<T, ID>.save():

@Transactional
@Override
public <S extends T> S save(S entity) {

    Assert.notNull(entity, "Entity must not be null.");

    if (entityInformation.isNew(entity)) {
        em.persist(entity);
        return entity;
    } else {
        return em.merge(entity);
    }
}

Method entityInformation.isNew(entity) is pretty simple: it checks if entity has an id or if id is empty. If id is empty (null) the entity is considered to be new. So in your case, since you manually set the id, the entity is always considered 'existing, not new' and em.merge(entity) is called. And this method, if you dig deeper in the spring code, leads to the following events:

  1. checking if the entity is loaded into the session and if not the SELECT query is issued to get the entity from the DB
  2. executing an INSERT or UPDATE statement based on step 1: if the entity exists, it executes UPDATE; if not, INSERT

Exception handling note. @M.Deinum in the comments is right: if an exception happens on the DB-level, you will get it only after transaction commits. And a transaction commits only after method @Transactional finishes, i.e. after processSave(..) returns.

So in your case it is required to handle exceptions in the service, which calls flattenProcessingInfoService.processSave(1000, 2000, "localhost");:

try {
   flattenProcessingInfoService.processSave(1000, 2000, "localhost");
} catch (Exception e) {
   log.info("Error Inserting Flatten Batch means batch already taken up for processing by another Pod -> {}", e.getMessage());
}

Regarding your question in the comments, how you can check yourself if entity already exists before saving, check How to put check if record already exist - spring boot.

Upvotes: 0

Related Questions