InvalidDataAccessApiUsageException occurs when using @Procedure annotation [DATAJPA-1555]

[spring-projects-issues]

Pavan Kumar Jadda opened DATAJPA-1555 and commented

I have an Entity class named 'Category' and trying use stored procedure based on the[ official docs |https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.stored-procedures]. 

 

Category.Java

@Entity
@Data
@EqualsAndHashCode(callSuper = true)
@Table(name = "category")
@NamedStoredProcedureQuery(name = "getAllCategoriesThroughStoredProcedure",procedureName = "get_all_categories",resultClasses = Category.class)
public class Category extends AbstractAuditingEntity implements Serializable
{
 private static final long serialVersionUID = -6699422774799518217L;

 @Id
 @Column(name = "id")
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private Long id;

 @NotEmpty
 @Column(name = "name", nullable = false)
 private String name;

 @Column(name = "description")
 private String description;

}

 

CategoryRepository.Java

public interface CategoryRepository extends JpaRepository<Category,Long>
{
@Procedure(procedureName="get_all_categories")
List<Category> getAllCategoriesThroughStoredProcedure();
}

When I invoke getAllCategoriesThroughStoredProcedure() method I get an exception

org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null]
 

 

I tried different syntaxes specified on official docs and everything throws the same error. I am using spring-boot-starter-data-jpa:2.1.5.RELEASE with Spring Boot 2.1.5

 

---

Affects: 2.1.8 (Lovelace SR8)

Reference URL: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.stored-procedures

Issue Links:

• DATAJPA-1092 Support JPA 2.1 stored procedures returning result sets ("duplicates")

[spring-projects-issues]

Jens Schauder commented

You need to reference the @NamedStoredProcedureQuery configuration via its name getAllCategoriesThroughStoredProcedure

The following method will call the stored procedure.

@Procedure(procedureName="getAllCategoriesThroughStoredProcedure")
List<Category> getAllCategoriesThroughStoredProcedure();

[spring-projects-issues]

Jens Schauder commented

Can you please confirm that this works?

[spring-projects-issues]

Pavan Kumar Jadda commented

Jens Schauder It did not work. I got the same error. One more thing, Spring Data JPA docs say, it has to be procedure name on Database, is this right syntax?

/**
 * The name of the procedure in the database, defaults to {@code ""}.
 */
String procedureName() default "";

[spring-projects-issues]

Jens Schauder commented

Sorry, it should be:

@Procedure(name="getAllCategoriesThroughStoredProcedure")
List<Category> getAllCategoriesThroughStoredProcedure();

I rewrote the documentation about stored procedures just last week. Maybe take a look at the new version if it makes things a little clearer: https://github.com/spring-projects/spring-data-jpa/commit/5ee04edaf9117884a241031ff4c52fde995ac0b7

You can use either value or procedureName with the name used in the database. Or you can use name and the name used in the JPA Stored Procedure declaration. If you use the database name Spring Data will ignore the JPA Stored Procedure declaration and therefore doesn't have type information from that declaration available.

In this case the missing information then becomes where to get the result from: a return value of the stored procedure or an OUT parameter.

At least that is my assumption so far. If this also turns out to be wrong I need some actual code reproducing the issue

[spring-projects-issues]

Pavan Kumar Jadda commented

Jens Schauder I created https://github.com/pavankjadda/SpringDataDemo repository with code and instructions on how to run it. I also noted errors in Errors.md file for reference. Please have a look. By the way, I did look at your latest documentation, but the issue remains same

[spring-projects-issues]

Jens Schauder commented

If you use the name used in @NamedStoredProcedureQuery looking up the query works, but it then hits the problem that returning a result set / cursor isn't supported, which makes this a duplicate of DATAJPA-1092

[spring-projects-issues]

Pavan Kumar Jadda commented

Jens Schauder When you say lookup query, you mean this or something else

@Query(value = "CALL get_all_categories;",nativeQuery = true)
List<Category> getAllCategoriesThroughStoredProcedure();

[spring-projects-issues]

Pavan Kumar Jadda commented

And for the being, I am using a new interface with Entity Manager execute my stored procedure

CategoryRepositoryCustom.java

public interface CategoryRepositoryCustom
{
 List<Category> getAllCategoriesThroughStoredProcedure();
}

CategoryRepositoryCustomImpl.java

 

@Repository
public class CategoryRepositoryCustomImpl implements CategoryRepositoryCustom
{
 private final EntityManager entityManager;
 private final ModelMapper modelMapper;

 public CategoryRepositoryCustomImpl(EntityManager entityManager, ModelMapper modelMapper)
 {
 this.entityManager = entityManager;
 this.modelMapper = modelMapper;
 }

 @Override
 public List<Category> getAllCategoriesThroughStoredProcedure()
 {
 List<Category> categories=new ArrayList<>();
 StoredProcedureQuery storedProcedureQuery=entityManager.createNamedStoredProcedureQuery("getAllCategoriesThroughStoredProcedure");
 List<?> categoriesResult=storedProcedureQuery.getResultList();

 categoriesResult.forEach(category -> categories.add(modelMapper.map(category, Category.class)));
 return categories;
 }
}
 

 

CategoryRepository.java

public interface CategoryRepository extends JpaRepository<Category,Long>,CategoryRepositoryCustom
{
}

[spring-projects-issues]

Jens Schauder commented

Pavan Kumar Jadda When I wrote "lookup" I meant Spring Data JPA finds the declaration of the named stored procedure and is able to determine the return type, so the original error does go away. Just to be replaced by the one caused by DATAJPA-1092