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

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!

CodePudding user response：

Since the return value of this methods indicates the number of modified objects (in your can it can only be 0 or 1) I would just use it to translate it into a more understandable response.

The simplest case is a REST Api:

When the Method returns 1 the POST/PUT/PATCH call was successful (if there was no other error) and you would return 201 Created and maybe a little more like a Location header.

And when it returns 0 it means no object got modified and because it's an insert this shouldn't happen and therefore you would return 500 InternalServerError

But I would say in your case this is redundant because as I said it's an insert , you only have those two return options and I guess if something doesn't work during the insert you already get an exception by spring boot and therefore when you called the method without getting an error I would say it was successful.

But ofcourse you can double check to be sure and maybe even use it for tests to enforce the expected behavior or something else.

CodePudding user response：

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.");
        }
    }
}