Over a million developers have joined DZone.

Adding Hibernate SQL Features into Your Spring Data Repository

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.

· Database Zone

To stay on top of the changing nature of the data connectivity world and to help enterprises navigate these changes, download this whitepaper from Progress Data Direct that explores the results of the 2016 Data Connectivity Outlook survey.

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 your custom 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;
 }
}

Turn Data Into a Powerful Asset, Not an Obstacle with Democratize Your Data, a Progress Data Direct whitepaper that explains how to provide data access for your users anywhere, anytime and from any source.

Topics:
java,sql,frameworks,hibernate,persistence,spring

Published at DZone with permission of Boris Lam, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}