Friday, July 20, 2012

Adding Hibernate native SQL features into your Spring Data Repository

JPA provides @NamedNativeQuery for you to use native SQL. However, the usage is not so convenient, especially when you need to map multiple entities in your native SQL. You have to define a set of SqlResultSetMapping mapping which is quite error prone.

For those who have used Hibernate native SQL features before, you will find that it is much more easier to use than JPA's @NamedNativeQuery. In recent projects, I am using Spring Data JPA. I have added hibernate native query features to my Spring Data base repostory. You can now perform native query in JPA without SqlResultSetMapping.

If you do not know how to add custom behaviour to your Spring data JPA base repository, please see my previous post for how to customize your Spring data JPA base repository for detail. You can see in previous post that I intentionally expose the repository interface (i.e. the springDataRepositoryInterface property) in the GenericRepositoryImpl. This small tricks enable me to access the annotation in the repository interface easily.

In your repositry interface, define which native SQL queries you want to use through your @NativeQueries and @NativeQuery annotation. The usage is just similar to calling hibernate native query features. You could just see the hibernate alias and property references.

Actually Spring data JPA start support native query for the @Query annotation by setting the nativeQuery flag to true. However, they have some limitation on the support execution of pagination or dynamic sorting for native queries.

Hi, Thank you for your thorough example. I've implemented the repository customization and the @NativeQueries annotations to the repository per your example, but I'm still getting a compiler error that queryNatively is undefined for my repository. The repository derives from GenericRepository and I'm injecting the Impl using a factory per your code.

Any idea where I should look to find out why queryNatively is not being found?

Profile

My expertise is in Java EE technology, object-oriented application development, and use of open source frameworks (e.g. Spring , Apache MyFaces). In recent years, I mainly worked in development projects of Java EE applications in Weblogic platform with Oracle database. I primarily involved in framework development and architectural design in these projects.