Stored Procedure on Spring Boot

spring-boot-stored-proc.md

Stored Procedure on Spring Boot

Executing stored procedures in Spring Boot using the @Procedure annotation provides a convenient way to interact with database procedures. By annotating a method with @Procedure and specifying the procedure name, you can easily call stored procedures, pass parameters, and handle result sets, simplifying the integration of stored procedures in your Spring Boot application.

Demostrations

Convert JPA Specification to Stored Procedure

Selected Function to Convert

Under file: src/main/java/hk/org/ha/tutor/mts/service/UserService.java

    public List<User> findAll(Optional<String> searchTerm, Optional<Rank> rank) {
        // user listing
        Specification<User> specification = isRecordActive().and(orderByUserCode());
        if (searchTerm.isPresent()) {
            specification = specification.and(
                    nameLike(searchTerm.get())
                            .or(userCodeEqual(searchTerm.get())));
        }
        if (rank.isPresent() && rank.get() != Rank.All) {
            specification = specification.and(
                    rankEqual(rank.get()));
        }
        return userRepository.findAll(specification);
    }

Stored Procedure

The following are the converted stored procedures for both Postgres and Sybase.

Postgres:

create or replace procedure get_users(
    in p_search_term varchar,
    in p_rank varchar,
    out result refcursor)
language plpgsql
as $$
begin
    open result for select * from usr
      where (name ilike '%' || p_search_term || '%' or user_code = p_search_term)
        and (p_rank = '' or rank = p_rank);
end;
$$;

Sybase:

create or replace procedure get_users
    @p_search_term varchar(255),
    @p_rank varchar(255)
as
begin
    select * from usr
    where (name like '%' + @p_search_term + '%' or user_code = @p_search_term)
        and (@p_rank = '' or rank = @p_rank)
end
go

sp_procxmode get_users, chained
go

If you are using @Procedure, it is necessary to use the chained mode for the procedure. If there is any reason why you cannot switch from unchained to chained, please refer to the next demonstration.

Implementation of changes

Under file: src/main/java/hk/org/ha/tutor/mts/entity/User.java add the following above the public class User.

Postgres:

@NamedStoredProcedureQuery(name = "getUsers", procedureName = "get_users", resultClasses = User.class, parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_search_term", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_rank", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "result", type = void.class)
})

Sybase:

@NamedStoredProcedureQuery(name = "getUsers", procedureName = "get_users", resultClasses = User.class, parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_search_term", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_rank", type = String.class)
})

Under file: src/main/java/hk/org/ha/tutor/mts/repository/UserRepository.java, add the following function.

    @Procedure(name = "getUsers")
    List<User> getUsers(@Param("p_search_term") String searchTerm, @Param("p_rank") String rank);

Under file: src/main/java/hk/org/ha/tutor/mts/service/UserService.java, change the following function.

    public List<User> findAll(Optional<String> searchTerm, Optional<Rank> rank) {
        Rank pRank = rank.orElse(Rank.All);
        return userRepository.getUsers(
                searchTerm.orElse(""),
                pRank == Rank.All ? "" : pRank.getDataValue());
    }

Use @Query for Sybase

You can use @Query instead of @Procedure for the read-only stored procedure, this is necessary if you cannot change the procedure execute mode (sp_procxmode) from unchained to chained.

Implementation of changes

Under file: src/main/java/hk/org/ha/tutor/mts/entity/User.java add the following above the public class User.

@NamedNativeQuery(name = "getUsers", query = "exec get_users ?1, ?2 ", resultClasses = User.class)

Under file: src/main/java/hk/org/ha/tutor/mts/repository/UserRepository.java, add the following function.

    @Query(name = "getUsers")
    List<User> getUsers(String searchTerm, String rank);

Under file: src/main/java/hk/org/ha/tutor/mts/service/UserService.java, add the following above the function.

+   @Transactional(Transactional.TxType.NOT_SUPPORTED)
    public List<User> findAll(Optional<String> searchTerm, Optional<Rank> rank) {
        Rank pRank = rank.orElse(Rank.All);
        return userRepository.getUsers(
                searchTerm.orElse(""),
                pRank == Rank.All ? "" : pRank.getDataValue());
    }

Custom ResultSet mapping

For @NamedNativeQuery

@SqlResultSetMapping(name = "userResult", entities = { @EntityResult(entityClass = User.class, fields = {
        @FieldResult(name = "userCode", column = "user_code"),
        @FieldResult(name = "name", column = "name"),
        @FieldResult(name = "rank", column = "rank"),
}) })
@NamedNativeQuery(name = "getUsers", query = "exec get_users ?1, ?2 ", resultSetMapping = "userResult")

For @NamedStoredProcedureQuery

@SqlResultSetMapping(name = "userResult", entities = { @EntityResult(entityClass = User.class, fields = {
        @FieldResult(name = "userCode", column = "user_code"),
        @FieldResult(name = "name", column = "name"),
        @FieldResult(name = "rank", column = "rank"),
}) })
@NamedStoredProcedureQuery(name = "getUsersProc", procedureName = "get_users", resultSetMapping = "userResult", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_search_term", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_rank", type = String.class)
})

You can use this technique to map extra fields onto different entities.

References

• https://www.baeldung.com/spring-data-jpa-stored-procedures