×
Back to book

Custom SQL with SpringBot

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

Developers can add custom SQL 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.

Implementation

Task : Create a query to find all the fish that are alive and were purchased.

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 FishRepository.java file found at serverside/src/main/java/fishnatics/repositories/
  2. Find the [Import any additional imports here] protected region and turn it on.
  3. Place the following code inside of it:
     import fishnatics.entities.QFishEntity;
     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 an fish or a list of fish that have are alive and have been bought
      *
      * @return a list of fish with that have are alive and have been bought
      */
     default List findByAliveAndPurchased() {
         QFishEntity fishEntity = QFishEntity.fishEntity;
         Predicate predicate = fishEntity.alive.eq(true).and(fishEntity.born.eq(BornEnum.PURCHASED));
         return Lists.newArrayList(this.findAll(predicate));
     }

Now we will have a repository method findByAliveAndPurchased() 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 (Out of Date)

In this method we will be using JPA repositories contains a handful of generic queries, which you can learn at JPA Repository Doc. This time around we will be doing the same task, but instead applying it to Cage enclosures.

User Story : As a Zoo Keeper I want to flag when a Cage enclosure is at capacity so that I can properly manage the locations of the animals.

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

  1. Locate the file EnclosureRepository.java (serverside/src/main/java/zoo/repositories/EnclosureRepository.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 an entity or a list of entities that have the Enclosure Type set to Cage and  at Capacity.
*
* @return a list of enclosures with Cage that have the Capacity attribute set to true
*/
@Query("select s from EnclosureEntity s where s.full = true and s.enclosure = zoo.entities.enums.EnclosureTypeEnum.CAGE")
List<EnclosureEntity> findByCapacityAndCage();
// % protected region % [Add any additional class methods here] end

Since enclosure type is an enum we will need to reference the enum with package.class.enum for comparison.