Boris Lam

About Boris Lam

Boris is an experienced Java developer with in Hong Kong. His expertise is in Java EE technology, object-oriented application development, and the use of open source frameworks (e.g. Spring , Apache MyFaces). In recent years, he is primarily involved in framework development and architectural design in serveral Government related software development projects.

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.

1. Add you cusomt annotation @NativeQueries and @NativeQuery

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueries {
 NativeQuery[] queries() default  {};
}
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQuery {
 String name()  default "";
 String sql()  default "";
}

2. Add the method “queryNatively” in base Spring Data Repository.

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.

public List queryNatively(String nativeQueryName, LinkedHashMap<String,Class<?>> inEntityClasses, Map inParams ){
 SQLQuery query =  this.createHibernateNativeQuery( nativeQueryName,  inParams );
     //add entities
     if (inEntityClasses!=null) {      
      for (Object key: inEntityClasses.keySet()) {
    String entityClassAlias = key.toString();
    Class<?> entityClass = (Class<?>)inEntityClasses.get(key);  
    query.addEntity(entityClassAlias,entityClass);
         }    
     }
             
     //add parameter
     if (inParams != null){
      for (Object key: inParams.keySet()) {
    String queryParamName = key.toString();
    Object queryParamValue = inParams.get(key);
    query.setParameter(queryParamName, queryParamValue);
         }
     }  
     return (query!=null)? query.list() : null ;
  }
    

  private SQLQuery createHibernateNativeQuery (String nativeQueryName, Map inParams ){
     if (GenericRepository.class.isAssignableFrom(getSpringDataRepositoryInterface())) {
      
      Annotation nativeQueryAnn = getSpringDataRepositoryInterface().getAnnotation(NativeQueries.class);
      if(nativeQueryAnn != null){
       NativeQueries nativeQueries = (NativeQueries)nativeQueryAnn;
       NativeQuery[] queries  = nativeQueries.queries();
    for (NativeQuery sqlquery : queries) { 
          
     if (StringUtils.equals(nativeQueryName, sqlquery.name())) {
      String sql  = sqlquery.sql();
      
      Session hiernateSess = em.unwrap(Session.class);
         SQLQuery query = hiernateSess.createSQLQuery(sql);
         
         //add parameter
         if (inParams != null){
          for (Object key: inParams.keySet()) {
        String queryParamName = key.toString();
        Object queryParamValue = inParams.get(key);
        query.setParameter(queryParamName, queryParamValue);
             }
         }  
         
         return query;
     }    
    }
      }
     
     }
     return null;
  }

3. Example Usage

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.

@NativeQueries (
 queries = {
   @NativeQuery(name="query1", 
         sql="SELECT {cat.*}, {mother.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID and c.name =  :catName "),
   @NativeQuery(name="query2", 
         sql="SELECT {cat.*} FROM CATS where c.ID =  :catName")       
 }
)
public interface CatRepository extends GenericRepository<Cat, Long> {
}

In your service or business class that inject your repository, you could just simply call the queryNatively() method to perform native SQL query.

@Service
public class CatService {
 
 @Inject
 private CatRepository catRepository;
 
 public List<Cat> searchCat( String catName) {
    
  List<Cat> catList;
 
  // Add entity mapping for your query
  HashMap<String, Object> inParams = new HashMap<String, Object>();
  inParams.put("catName", "Felix");
  
  
  // Prepare parameters for your native sql
  LinkedHashMap<String, Object> entityMap = new LinkedHashMap<String, Object>();
  entityMap.put("cat", Cat.class);
  entityMap.put("mother",Mother.class);
 

 
  catList = catRepository.queryNatively(
    "query1", "",entityParam);
 
  return catList;
 }
}

Reference: Adding Hibernate native SQL features into your Spring Data Repository from our JCG partner Boris Lam at the Programming Peacefully blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

2 Responses to "Hibernate native SQL features into your Spring Data Repository"

  1. Tejo says:

    I try to extends @Query functionality, it will use Method Annotation Element type, I just don’t understand how to implement QueryMethod invocation listener? , where and when to register those QueryMethod.

  2. Tejo says:

    I am using version 1.0.2, and cannot find nativeQuery= … , the only available annotation parameter are value and countQuery

Leave a Reply


nine × = 18



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close