According to the Spring data JPA reference documentation, stored procedures can be called from a JPA repository using the @Procedure annotation.
<pre class="wp-block-syntaxhighlighter-code">public interface OrderRepository extends Repository<Order, Long> {
Order save(Order fr);
Optional findById(Long id);
@Procedure("spGetByComplicatedQuery")
Order findByComplicatedQuery();<br />}
</pre>
In the above code, when the repository method findByComplicatedQuery() is called, the stored procedure spGetByComplicatedQuery is called instead. This allows for complex query optimisation using direct SQL. However, with MS SQL Server, a IllegalArgumentException is thrown during runtime
2021-05-28 10:25:12,460 ERROR o.a.juli.logging.DirectJDKLog http-nio-94-exec-3 Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null] with root cause connection id=10.6.12.118:49537 java.lang.IllegalArgumentException: Type cannot be null
at org.hibernate.query.procedure.internal.ProcedureParameterImpl.setHibernateType(ProcedureParameterImpl.java:130)
The problem is that many SQL Server stored procedures return results as a result set, and not output parameters. The @Procedure annotation cannot handle this. The work around is to use the @Query annotation with the native query flag.
<pre class="wp-block-syntaxhighlighter-code">public interface OrderRepository extends Repository<Order, Long> {
Order save(Order fr);
Optional findById(Long id);
@Query(nativeQuery=true, value="exec spGetByComplicatedQuery")
Order findByComplicatedQuery();<br />}
</pre>