simulations v1.1

Project overview

Add @Query and query methods to Repositories
JUnit tests

References

Defining Query Methods
JPA Query Methods
Java and SQL: three different approaches allong the time

Project structure

to-do

JPA Queries Types

In Java Persistence API (JPA), there are mainly four types of queries:

JPQL (Java Persistence Query Language) Queries
Named Queries: @NamedQuery JPQL
Native Queries: database-specific @Query SQL queries
- Named Native Query: combines both and allows to define and execute native SQL
JPA Query Methods: as methods signature

JPQL (Java Persistence Query Language) Queries

JPQL (Java Persistence Query Language) Queries: JPQL is a high-level query language that is used to query data from the database.

It is similar to SQL but operates on entity objects and their properties instead of database tables and columns. JPQL queries are created using the EntityManager.createQuery() method and executed using the Query.getResultList() or Query.getSingleResult() methods.

String jpql = "SELECT e FROM Employee e WHERE e.salary > 50000";
Query query = entityManager.createQuery(jpql);
List<Employee> employees = query.getResultList();

Named Queries

Named Queries: Named queries are predefined JPQL queries that are annotated in the entity class using the @NamedQuery annotation.

Named queries can be executed using the EntityManager.createNamedQuery() method. Named queries are useful when you have frequently used queries and want to avoid writing the same query multiple times.

@Entity
@NamedQuery(name="Employee.findBySalary", query="SELECT e FROM Employee e WHERE e.salary > :salary")
public class Employee {
    // entity properties and methods
}

String namedQueryName = "Employee.findBySalary";
Query query = entityManager.createNamedQuery(namedQueryName);
query.setParameter("salary", 50000);
List<Employee> employees = query.getResultList();

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
  User findByLastnameOrFirstname(@Param("lastname") String lastname,
                                 @Param("firstname") String firstname);
}

Native Queries

Native queries are database-specific SQL queries.

Native Queries: Native queries are database-specific SQL queries that can be executed using the EntityManager.createNativeQuery() method.

Native queries are useful when you need to use database-specific features that are not supported by JPQL. The results of native queries can be mapped to entity objects using the Query.setResultTransformer() method.

String sql = "SELECT * FROM employees WHERE salary > 50000";
Query query = entityManager.createNativeQuery(sql, Employee.class);
List<Employee> employees = query.getResultList();

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "SELECT * FROM Employee WHERE salary > ?1", nativeQuery = true)
    List<Employee> findEmployeesWithHighSalary(Double salary);
}

JPA Query Methods

JPA Query Methods: JPA provides a feature called query methods, which allows you to define queries as methods in your entity repository interfaces.

Query methods are based on method names and use metadata annotations to define the query. Query methods are executed using the Spring Data JPA JpaRepository or CrudRepository interfaces.

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    List<Employee> findBySalaryGreaterThan(double salary);
    List<Employee> deleteBySalaryLowerThan (double salary);
}

// usage
List<Employee> employees = employeeRepository.findBySalaryGreaterThan(50000);

Query Methods

Standard CRUD functionality repositories usually have queries on the underlying datastore.

With Spring Data, declaring those queries becomes a four-step process:

1. Declare an interface extending Repository

Declare an interface extending Repository or one of its subinterfaces and type it to the domain class and ID type that it should handle, as shown in the following example:

interface PersonRepository extends Repository<Person, Long> { … }

2.Declare query methods on the interface

interface PersonRepository extends Repository<Person, Long> {
  List<Person> findByLastname(String lastname);
}

3. Set up Spring

Set up Spring to create proxy instances for those interfaces, either with JavaConfig or with XML configuration.

Java
XML

import org.springframework.data.….repository.config.EnableJpaRepositories;

@EnableJpaRepositories
class Config { … }

The JPA namespace is used in this example. If you use the repository abstraction for any other store, you need to change this to the appropriate namespace declaration of your store module. In other words, you should exchange jpa in favor of, for example, mongodb.
Note that the JavaConfig variant does not configure a package explicitly, because the package of the annotated class is used by default.
To customize the package to scan, use one of the basePackage… attributes of the data-store-specific repository’s @EnableJpaRepositories-annotation.

4. Inject the repository instance

Inject the repository instance and use it, as shown in the following example:

class SomeClient {

  private final PersonRepository repository;

  SomeClient(PersonRepository repository) {
    this.repository = repository;
  }

  void doSomething() {
    List<Person> persons = repository.findByLastname("Matthews");
  }
}

PagingAndSortingRepository

It is a sub-interface of CrudRepository. It has two additional methods for Pagination and Sorting and inherited super-interface methods.

public interface PagingAndSortingRepository<T, ID> extends Repository<T, ID> {

  Iterable<T> findAll(Sort sort);
  Page<T> findAll(Pageable pageable);
}

Review the PagingAndSortingRepository interface, the two methods, findAll(Sort sort) and findAll(Pageable pageable), provide the core functionalities for sorting and pagination.

In simple, make the repository bean extend the PagingAndSortingRepository interface, and Spring Data will automatically provide the paging and sorting features.

Examples

package com.example.demo.repository;

import com.example.demo.model.Player;
import org.springframework.data.jpa.repository.CrudRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;

@Repository
public interface PlayerRepository extends CrudRepository<Player, String> {

    // 1. Find by player name
    Optional<Player> findByPlayer(String playerName);

    // 2. Find by active status
    List<Player> findByActive(boolean active);

    // 3. Find by age
    List<Player> findByAge(int age);

    // 4. Find by id and active status
    Optional<Player> findByIdAndActive(String id, boolean active);

    // 5. Count players by active status
    long countByActive(boolean active);

    // 6. Delete by id
    void deleteById(String id);

    // 7. Delete by player name
    void deleteByPlayer(String playerName);

    // 8. Custom query to find players with at least one simulation
    @Query("SELECT p FROM Player p JOIN FETCH p.simulations s WHERE SIZE(s) > 0")
    List<Player> findPlayersWithSimulations();

    // 9. Custom query to find players with at least one subscription
    @Query("SELECT p FROM Player p JOIN FETCH p.subscriptions s WHERE SIZE(s) > 0")
    List<Player> findPlayersWithSubscriptions();

    // 10. Custom query to find players with at least one payment
    @Query("SELECT p FROM Player p JOIN FETCH p.payments pmt WHERE SIZE(pmt) > 0")
    List<Player> findPlayersWithPayments();

    // 11. Custom query to find players with at least one card
    @Query("SELECT p FROM Player p JOIN FETCH p.cards c WHERE SIZE(c) > 0")
    List<Player> findPlayersWithCards();

    // 12. Custom query to find players with at least one social
    @Query("SELECT p FROM Player p JOIN FETCH p.socials s WHERE SIZE(s) > 0")
    List<Player> findPlayersWithSocials();

    // 13. Custom query to find players with at least one enrollment
    @Query("SELECT p FROM Player p JOIN FETCH p.enrollments e WHERE SIZE(e) > 0")
    List<Player> findPlayersWithEnrollments();

    // 14. Custom query to find players with at least one imagePlayerId
    @Query("SELECT p FROM Player p WHERE SIZE(p.imagePlayerIds) > 0")
    List<Player> findPlayersWithImagePlayerIds();

    // 15. Custom query to find players without any simulations
    @Query("SELECT p FROM Player p LEFT JOIN p.simulations s WHERE s IS EMPTY")
    List<Player> findPlayersWithoutSimulations();

    // 16. Custom query to find players without any subscriptions
    @Query("SELECT p FROM Player p LEFT JOIN p.subscriptions s WHERE s IS EMPTY")
    List<Player> findPlayersWithoutSubscriptions();

    // 17. Custom query to find players without any payments
    @Query("SELECT p FROM Player p LEFT JOIN p.payments pmt WHERE pmt IS EMPTY")
    List<Player> findPlayersWithoutPayments();

    // 18. Custom query to find players without any cards
    @Query("SELECT p FROM Player p LEFT JOIN p.cards c WHERE c IS EMPTY")
    List<Player> findPlayersWithoutCards();

    // 19. Custom query to find players without any socials
    @Query("SELECT p FROM Player p LEFT JOIN p.socials s WHERE s IS EMPTY")
    List<Player> findPlayersWithoutSocials();

    // 20. Custom query to find players without any enrollments
    @Query("SELECT p FROM Player p LEFT JOIN p.enrollments e WHERE e IS EMPTY")
    List<Player> findPlayersWithoutEnrollments();
}

Supported keywords inside method names

The following table describes the keywords supported for JPA and what a method containing that keyword translates to:

Keyword	Sample	JPQL snippet
`Distinct`	`findDistinctByLastnameAndFirstname`	`select distinct ... where x.lastname = ?1 and x.firstname = ?2`
`And`	`findByLastnameAndFirstname`	`… where x.lastname = ?1 and x.firstname = ?2`
`Or`	`findByLastnameOrFirstname`	`… where x.lastname = ?1 or x.firstname = ?2`
`Is`, `Equals`	`findByFirstname`,`findByFirstnameIs`,`findByFirstnameEquals`	`… where x.firstname = ?1`
`Between`	`findByStartDateBetween`	`… where x.startDate between ?1 and ?2`
`LessThan`	`findByAgeLessThan`	`… where x.age < ?1`
`LessThanEqual`	`findByAgeLessThanEqual`	`… where x.age <= ?1`
`GreaterThan`	`findByAgeGreaterThan`	`… where x.age > ?1`
`GreaterThanEqual`	`findByAgeGreaterThanEqual`	`… where x.age >= ?1`
`After`	`findByStartDateAfter`	`… where x.startDate > ?1`
`Before`	`findByStartDateBefore`	`… where x.startDate < ?1`
`IsNull`, `Null`	`findByAge(Is)Null`	`… where x.age is null`
`IsNotNull`, `NotNull`	`findByAge(Is)NotNull`	`… where x.age not null`
`Like`	`findByFirstnameLike`	`… where x.firstname like ?1`
`NotLike`	`findByFirstnameNotLike`	`… where x.firstname not like ?1`
`StartingWith`	`findByFirstnameStartingWith`	`… where x.firstname like ?1` (parameter bound with appended `%`)
`EndingWith`	`findByFirstnameEndingWith`	`… where x.firstname like ?1` (parameter bound with prepended `%`)
`Containing`	`findByFirstnameContaining`	`… where x.firstname like ?1` (parameter bound wrapped in `%`)
`OrderBy`	`findByAgeOrderByLastnameDesc`	`… where x.age = ?1 order by x.lastname desc`
`Not`	`findByLastnameNot`	`… where x.lastname <> ?1`
`In`	`findByAgeIn(Collection<Age> ages)`	`… where x.age in ?1`
`NotIn`	`findByAgeNotIn(Collection<Age> ages)`	`… where x.age not in ?1`
`True`	`findByActiveTrue()`	`… where x.active = true`
`False`	`findByActiveFalse()`	`… where x.active = false`
`IgnoreCase`	`findByFirstnameIgnoreCase`	`… where UPPER(x.firstname) = UPPER(?1)`