Spring Data JPA check if record exist and update else insert

Question

How can I check the DB if a record already exists for a given case using Spring JPA query using one params. If it exists it does an update or else it inserts as a new record. I have tried a simple implementation, but I end up with a 500 server error no other error is logged.

Resolved [java.lang.NullPointerException] Completed 500 INTERNAL_SERVER_ERROR

This is what I have tried so far My Controller

 @RequestMapping(path="/updatedm",method = RequestMethod.POST)
    public Boolean updateDMStatus(@RequestParam("country") String country,
                                 @RequestParam("Id") String pId,
                                 @RequestParam("case") String case,
                                 @RequestParam("status") String status,
                                  @RequestParam("updatedBy") String updatedBy){

        Boolean createDm = eaDataStoreService.updateDM(country,Id,case,status,updatedBy);

        return createDm;

    }

My repository

public interface DMValidatedRepository extends CrudRepository<DMValidated, String> {

    DMValidated findByCase(@Param("case") String case);
}

My Service

 public boolean updateDM(String country, String Id, String case, String status,String updatedBy) {
        DMValidated document = dmValidated.findByCase(case);

        if(document != null){
            document.setStatus(status);
            document.setUpdatedBy(updatedBy);
            dmValidated.save(document);
        }else{
            document.getId();
            document.getCase();
            document.getCountry();
            document.getStatus();
            document.getUpdatedBy();
            dmValidated.save(document);
        }


        return true;

    }

My Model

@Data
@ToString
@Entity
@Table(name = "DMStatus")
public class DMValidated{
    @Id
    @GeneratedValue
    private String id;

    @Column(name = "country")
    private String country;
    @Column(name = "Id")
    private String Id;
    @Column(name = "Case")
    private String case;
    @Column(name = "status")
    private String status;
    @Column(name = "updatedBy")
    private String updatedBy;

    public  DMValidated( String country, String Id,
                                String case, String status, String updatedBy){
        this.country = country;
        this.Id=Id;
        this.case = case;
        this.status =status;
        this.updatedBy = updatedBy;
    }

Am not sure if this is the right way of doing this, have tried to research but I have not found something concreate. How can I achieve this?

score 3 · Accepted Answer · answered Jul 02 '21 at 07:36

It's not difficult you have just forgotten the code to create properly the object when it is new and needs to be inserted

    if(document != null){
        document.setStatus(status);
        document.setUpdatedBy(updatedBy);
    }else{
        document = new DMValidated();
        document.setId(Id);
        document.setCase(case);
        document.setCountry(country);
        document.setStatus(status);
        document.setUpdatedBy(updatedBy);
    }

    dmValidated.save(document);

The error that occurred previously in your code is the following

      }else{
            document.getId();
            ...
           }

In this else you get only when document == null, so when you invoke document.getId() a null pointer exception is thrown, and then 500 error occurs.

Hello! Wouldn't be possible to have concurrency issues if you have 2 instances of your application running at the same time? Let's say the record doesn't exist and then, at the same time, the `updateDM` method is called. It would try to create a new record in both applications instances but in fact one of the "inserts" will be overwritten at `dmValidated.save(document);`. In that scenario how should we proceed? — RCaetano, Feb 07 '23 at 11:47

score 0 · Answer 2 · answered Jul 02 '21 at 08:18

else{
            document.getId();
            document.getCase();
            document.getCountry();
            document.getStatus();
            document.getUpdatedBy();
            dmValidated.save(document);
        }

above case document object initialize but property datatype string is always null Thats why each time document.getId() is null or other property to get occurred Nullpointer.

Correct code

 else{
        document = new DMValidated();
        document.setId(Id);
        document.setCase(case);
        document.setCountry(country);
        document.setStatus(status);
        document.setUpdatedBy(updatedBy);
    }

score -1 · Answer 3 · answered Jul 02 '21 at 04:52

This operation you are attempting to do is colloquially called UPSERT, and happens to be a bit of challenge to achieve purely with JPA and Hibernate. The way I've done it in the past is with jOOQ.

That said, there are good resources here in StackOverflow that will help you find an answer faster if you search for those keywords.

Anyhow, here are some readings you may want to go over first:

Pretty much any reading from Vlad Mihalcea will give you insights of this topic, plus JPA and Hibernate in general.

thank you @ne3rmore, I will read, no working example you have? — arriff, Jul 02 '21 at 05:14

Spring Data JPA check if record exist and update else insert

3 Answers3