Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
spring-projects-issues opened this issue Jun 10, 2019 · 9 comments
Assignees
Labels
in: core status: duplicate type: bug

Comments

@spring-projects-issues
Copy link

@spring-projects-issues spring-projects-issues commented Jun 10, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 10, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 10, 2019

Jens Schauder commented

Can you please confirm that this works?

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 10, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 11, 2019

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: 5ee04ed

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 11, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 14, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 14, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 14, 2019

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 17, 2019

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

@spring-projects-issues spring-projects-issues added type: bug in: core status: duplicate labels Dec 30, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: core status: duplicate type: bug
Projects
None yet
Development

No branches or pull requests

2 participants