spring data spa and oracle package

Question

I'm trying to build a spring boot rest endpoint that calls an oracle stored procedure that has 2 in parameters and 45 restrain parameters. I have created an entity:

package com.me.data.userservice.models;

import javax.persistence.*;
import java.util.Date;

/**
 * Created by me on 5/27/17.
 */
@Entity
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "getUserDetailsSp",
                procedureName = "user_pkg.get_user_details",
                parameters = {
                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_in_user_id", type = Long.class),
                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_in_region", type = String.class)
                },
                resultClasses = UserDetails.class)
})
public class UserDetails {
    //region Private members
    @Id
    private Long user_id;
    private String first_name;
    private String last_name;
    private String email;
    @Column(name = "p_in_user_id", nullable = false)
    private String userId;
    private String user_id;
    @Column(name = "p_in_region", nullable = false)
    private String region;
    private Long scnd_user_id;
    ...
}

And a repository:

package com.me.data.userservice.repositories;

import com.me.data.userservice.models.UserDetails;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

/**
 * Created by me on 5/27/17.
 */
public interface UserDetailsRepository extends CrudRepository<UserDetails, Long> {
    @Procedure(name="getUserDetailsSp")
    public UserDetails findByUserIdAndRegion(@Param("p_in_user_id") String userId, @Param("p_in_region") String region);
}

All of this code compiles fine but when I call the repository I get the following error:

2017-05-27 18:41:27 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Object of class [org.springframework.data.jpa.repository.query.PartTreeJpaQuery] must be an instance of class org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery; nested exception is java.lang.IllegalArgumentException: Object of class [org.springframework.data.jpa.repository.query.PartTreeJpaQuery] must be an instance of class org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery] with root cause java.lang.IllegalArgumentException: Object of class [org.springframework.data.jpa.repository.query.PartTreeJpaQuery] must be an instance of class org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery at org.springframework.util.Assert.instanceCheckFailed(Assert.java:389) at org.springframework.util.Assert.isInstanceOf(Assert.java:327) at org.springframework.util.Assert.isInstanceOf(Assert.java:339) at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:300) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82) at...

For some reason I am failing at decoding this error and can't find any help on google.

This must be possible but I am not finding any examples that have more than one return value nor input-only parameters.

ADDED DETAIL

I actually just realized that I user_pkg.get_user_details is actually a query function. How does this change my app?

Answer 1

My solution or I should say my workaround was a little bit different because after trying a lot of things I gave up on the @NamedStoredProcedureQueries. So, I did it via a @Query annotation

@Repository 
public interface MyObjectRepository extends CrudRepository<MyObject, String>  {
    @Query(value = "EXECUTE [dbo].[myProc] :fieldName, :pages", nativeQuery = true)
    Set<MyObject> findAllByFieldName(@Param("fieldName") String fieldName, @Param("pages") int pages);
}

This link might be helpful DATA REPOSITORIES IN SPRING DATA JPA

Answer 2

Ok, so I found a solution. I'm not sure that it is the best solution but it is a solution.

First I changed my repository to be a standard component and chose to use a NamedParameterJdbcTemplate and a custom row mapper.

@Component
public class UserDetailsRepository {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public UserDetails findByUserIdAndDomain(@Param("p_in_user_id") String userId, @Param("p_in_region") String region) {

        String sql = "select * from TABLE(user_pkg.get_user_details(:p_in_user_id,:p_in_region))";
        MapSqlParameterSource namedParameters = new MapSqlParameterSource("p_in_user_id", userId)
                .addValue("p_in_region", region);
        UserDetails result = (UserDetails)jdbcTemplate.queryForObject(
                sql, namedParameters, new UserDetailsMapper());

        return result;

    }
}

Rowmapper:

import com.me.data.userservice.models.UserDetails;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by me on 5/27/17.
 */
public class UserDetailsMapper implements RowMapper {
    @Override
    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
        UserDetails userDetails = new UserDetails();
               userDetails.setAccount_number(resultSet.getString("account_number"));
        userDetails.setCompany_name(resultSet.getString("company_name"));
        ...
        return userDetails;
    }
}