Creating Spring Boot MVC application with AWS DynamoDB in 10 mins

Image
AWS DynamoDB DB is a serverless NOSQL database. You can understand how to build a spring boot Java web MVC application (Game Leaderboard) reading a AWS DynamoDB in 10 mins. Source of the demo code: https://github.com/babysteps-topro/spring-mvc-dynamodb Command to run the project: mvn spring-boot:run Video explain the table design: https://youtu.be/V0GtrBfY7XM Prerequisite: Install the AWS CLI: https://youtu.be/pE-Q_4YXlR0 Video explain the how to create the table: https://youtu.be/sBZIVLlmpxY

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.

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

Comments

Hi Boris,

Great blog! Is there an email address I can contact youin private?
Boris Lam said…
Thanks Nikos, I have sent a email to you. :)
Unknown said…
hi,
this is cool,
but i am curious how to add functionality on method level annotation (element type Method).

how the annotation prcessor work, how to handle those annotated method invocation, like one on @Query annotation

thanks.
Unknown said…
hi,
this is cool,
but i am curious how to add functionality on method level annotation (element type Method).

how the annotation prcessor work, how to handle those annotated method invocation, like one on @Query annotation

thanks.
Boris Lam said…
Hi,

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.

You could take a look on the this for method level annotation.
andrea said…
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?

Thank you
Anonymous said…
Best Places To Bet On Boxing - Mapyro
Where To Bet On https://febcasino.com/review/merit-casino/ Boxing. It's a sports betting event in which you bet on the outcome of a game. https://jancasino.com/review/merit-casino/ In the 출장샵 boxing https://deccasino.com/review/merit-casino/ world, ventureberg.com/ each player must decide if or not to

Popular posts from this blog

Sample Apps: Spring data MongoDB and JSF Integration tutorial (PART 1)

Customizing Spring Data JPA Repository

Adding Hibernate Entity Level Filtering feature to Spring Data JPA Repository