How to return useful information in JPA custom query whose return value is not 1?

Issue

I have several custom queries in an interface that extends JpaRepository. The interface is analogous to the below (note: the below is just for illustration, and may have errors, but don't concern yourself with that).

public interface MyRepo extends JpaRepository<SMark, String> {

@Transactional
@Modifying
@Query(value = "INSERT INTO my_table(id, col_1, col_2) " +
        "values(:col_1, :col_2))", nativeQuery = true)
int insertRecord(@Param("col_1") String col_1, @Param("col_2") String col_2);

So, my issue is that I am finding it difficult to do anything useful with the int return type for anything other than a successful query (which will return a 1). Is there a way to do anything useful with the return other than sending the value as part of the response? In other words, if the response is not a 1, and an exception is not thrown, can the non-1 response be translated into something more informative to the user?

For example, I am currently doing the following, which I would like to improve upon if I was a confident about the not-1 status:

    if(status == 1) {
        StatusResponse statusResponse = new StatusResponse("Successful Delete ", null);
        return new ResponseEntity<>(statusResponse, HttpStatus.OK);
    }
    else {
        StatusResponse statusResponse = new StatusResponse("Delete not successful (lacking details from response)  ", null);
        return new ResponseEntity<>(statusResponse, HttpStatus.NOT_ACCEPTABLE);
    }

Grateful for any response. Thanks!

Solution

I would not recommend the approach of using return type as valid operation or not. I would prefer having database level constraint like unique constraint, check constraint or trigger check for insert/update/delete.

Nevertheless, we can use default method inside interface and throw database exception and in Spring you can configure ExceptionHandler to wrap the exception and return with some valid error code ?

    public interface MyRepo extends JpaRepository<SMark, String> {

    @Transactional
    @Modifying
    @Query(value = "INSERT INTO my_table(id, col_1, col_2) " +
            "values(:col_1, :col_2))", nativeQuery = true)
    int insertRecord(@Param("col_1") String col_1, @Param("col_2") String col_2);

    default void insert(String col1, String col2) {
        if (insertRecord(col1, col2) != 1) {
            throw new DataIntegrityViolationException("Unable to perform DML operation.");
        }
    }
}

Answered By - Sagar Rout