Custom SQL with SpringBot

This article will explore how you can create more complex queries adding custom SQL.


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
  1. Find the protected region [Add any additional class methods here] and turn it on
  2. 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.


Ready to start building?

Sign up to Codebots today to see how much faster you can build apps with us.