Developer Docs

Custom SQL with SpringBot

Developers can add custom queries into SpringBot using two different libraries: QueryDSL or Spring Data JPA’s repositories.

File Name: [EntityName]Repository.java

Relative Path: serverside/src/main/java/[ProjectName]/repositories/[EntityName]Repository.java

Purpose: The repository is the DAO (Data Access Object) SpringBots uses to interfaces between server side application and database.

For this activity we will using the Learning Management System (LMS) - Example project and we will be adding a custom query to filter by lesson difficulty and duration.

Implementation

User Story : As a Content Creator I want to flag when a lesson a given difficulty is longer than a certain duration.

Note: LessonRepository already contains methods findByDuration and findByDifficulty which could be combined for the purpose of this task however for learning purposes, we will be creating a separate method.

Method 1: QueryDSL (Codebots Preferred Method)

QueryDSL is a library used in SpringBot to more easily query the database using Java classes.

  1. Open the LessonRepository.java file found at serverside/src/main/java/lmsspring/repositories/ (Note: lmsspring is the name of the project)
  2. Find the [Import any additional imports here] protected region and turn it on.
  3. Place the following code inside of it:

     import lmsspring.entities.QLessonEntity;
      import com.google.common.collect.Lists;
      import com.querydsl.core.types.Predicate;
    
  4. Find the [Add any additional class methods here] protected region and turn it on.
  5. Place the following code in the protected region:

     /**
       * Return a lesson or a list of lessons with a specific difficulty, longer than a given
       * duration
       *
       * @param difficultyEnum the desired difficulty level
       * @param duration the maximum duration to compare against
       * @return a list of entities that are longer than the duration, with the set difficulty
       */
      default List findByDifficultyAndDuration(DifficultyEnum difficultyEnum, int duration) {
          QLessonEntity lessonEntity = QLessonEntity.lessonEntity;
          Predicate predicate = lessonEntity.duration.gt(duration).andlessonEntity.difficulty.eq(difficultyEnum);
          return Lists.newArrayList(this.findAll(predicate));
      }
    

Now we will have a repository method findByDifficultyAndDuration() to query our database later.

As we have done, remember to add comments to your functions using Javadoc convention. This will help contribute information to your bot-written docs in Reference.

Method 2: Spring Data JPA’s repositories (Alternative)

In this method we will be using JPA repositories which contains a handful of generic queries, which you can learn at JPA Repository Doc. We will be following the same task as the previous method.

Let’s create a custom query using JPQL for our requirement.

  1. Locate the file LessonRepository.java (serverside/src/main/java/lmsspring/repositories/LessonRepository.java)
  2. Find the protected region [Import any additional imports here]
  3. Turn on the protected region and add the following import: import org.springframework.data.jpa.repository.Query;

     // % protected region % [Import any additional imports here] on begin
     import org.springframework.data.jpa.repository.Query;
     // % protected region % [Import any additional imports here] end
    
  4. Find the protected region [Add any additional class methods here] and turn it on
  5. Add the following code:

     /**
      * Return a lesson or a list of lessons with a specific difficulty, longer than a given
      * duration
      *
      * @param difficultyEnum the desired difficulty level
      * @param duration the maximum duration to compare against
      * @return a list of entities that are longer than the duration, with the set difficulty
      */
     List<LessonEntity> findByDifficultyAndDurationGreaterThan(DifficultyEnum difficultyEnum, int duration);
    
     // % protected region % [Add any additional class methods here] end
    

Solution

Have a look at the custom-sql-querydsl and custom-sql-jpa branches to see the code solution.

On this page