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.