Spring Boot: JPA Queries

Spring Boot JPA Queries

Spring Boot JPA

📘 JPA Queries

Spring Boot provides an implementation of the Java Persistence API (JPA) to simplify database access: ORM (Object-Relational Mapping)

In JPA, entity classes represent tables in the database, and relationships between entities are mapped using annotations.

JPA (Java Persistence API) enables efficient data retrieval through various querying methods.

It supports JPQL (Java Persistence Query Language) for object-oriented queries.

1 JPQL (Java Persistence Query Language)

JPQL (Java Persistence Query Language) is an object-oriented query language used to define queries for entities and their relationships in JPA (Java Persistence API). It’s similar to SQL but operates on JPA entity objects rather than database tables.

Criteria API for type-safe programmatic queries, and native SQL for database-specific operations. JPA also allows named queries, parameterized queries, and various operations like filtering, sorting, and joining entities.

1.1 Key Characteristics of JPQL

1. Object-Oriented: Works with Java objects and their properties, not database tables and columns.
2. Database-Independent: Queries can work across different database systems without modification.
3. Supports JPA Relationships: Can easily navigate entity relationships.
4. Powerful: Supports complex operations like joins, aggregations, and subqueries.

1.2 JPQL vs SQL

Here’s a comparison between JPQL and SQL:

Aspect	JPQL	SQL
Operates on	Entity objects	Database tables
Case sensitivity	Case-sensitive	Generally case-insensitive
Portability	Database-independent	May contain database-specific syntax
Join syntax	Simplified using relationships	Requires explicit join conditions

1.3 JPQL Syntax Examples

1. Basic Select Query:

SELECT u FROM User u WHERE u.age > 18

2. Join Query:

SELECT o FROM Order o JOIN o.customer c WHERE c.country = 'USA'

3. Aggregate Function:

SELECT AVG(e.salary) FROM Employee e WHERE e.department.name = 'IT'

4. Subquery:

SELECT e FROM Employee e WHERE e.salary > (SELECT AVG(emp.salary) FROM Employee emp)

1.4 Using JPQL in Spring Data JPA

1. With @Query annotation:

@Query("SELECT u FROM User u WHERE u.email = ?1 AND u.active = true")
User findActiveUserByEmail(String email);

1. With EntityManager:

String jpql = "SELECT p FROM Product p WHERE p.category.name = :categoryName";
List<Product> products = entityManager.createQuery(jpql, Product.class)
    .setParameter("categoryName", "Electronics")
    .getResultList();

1.5 Benefits of JPQL

1. Type-safety: Works with Java types, reducing runtime errors.
2. Abstraction: Shields developers from database-specific details.
3. Optimization: JPA providers can optimize JPQL queries for better performance.
4. Flexibility: Allows for complex queries while maintaining database independence.

2 Relevant ways to Query

Spring Boot offers several ways to create queries when working with JPA repositories.

Method	Description	Pros	Cons
Derived Query Methods	Create queries by method names in repository interfaces	- Easy to use for simple queries- No need to write SQL/JPQL- Automatic query generation	- Limited to simple queries- Can lead to long method names for complex queries
@Query Annotation	Write custom JPQL or native SQL queries in repository interfaces	- Allows complex queries- Supports both JPQL and native SQL- Can be more readable than derived queries	- Queries are strings, so no compile-time checking- Can mix persistence logic with repository interface
Specification API	Build dynamic queries programmatically	- Flexible for complex, dynamic queries- Type-safe- Reusable query parts	- More verbose for simple queries- Requires more setup
Query by Example (QBE)	Create queries using domain object instances as examples	- Intuitive for simple queries- No need for query language- Works well with dynamic queries	- Limited to equality and ‘like’ comparisons- Not suitable for complex conditions
@NamedQueries	Define queries at the entity level	- Separates query logic from repository- Reusable across multiple repositories- Potentially better performance	- Queries are strings, so no compile-time checking- Can clutter entity classes with query definitions

Here are the five most relevant methods, along with examples for the userBorrowBook entities:

2.1 1. Derived Query Methods

Derived query methods allow you to create queries by simply declaring method names in your repository interface. Spring Data JPA automatically generates the query based on the method name.

public interface BookRepository extends JpaRepository {
    List findByAuthorAndAvailableTrue(String author);
    Optional findByIsbn(String isbn);
    List findByPublicationDateAfter(LocalDate date);
}

public interface BorrowRepository extends JpaRepository {
    List findByUserAndIsReturnedFalse(UserApp user);
    List findByBookAndReturnDateBefore(Book book, LocalDate date);
}

public interface UserAppRepository extends JpaRepository {
    List findByAgeGreaterThanAndIsArchivedFalse(int age);
    Optional findByEmail(String email);
}

3 2. @Query Annotation

The @Query annotation allows you to write custom JPQL or native SQL queries directly in your repository interface.

public interface BookRepository extends JpaRepository {
    @Query("SELECT b FROM Book b WHERE b.pagesQty > :minPages AND b.available = true")
    List findAvailableBooksWithMoreThanPages(@Param("minPages") int minPages);
}

public interface BorrowRepository extends JpaRepository {
    @Query("SELECT b FROM Borrow b WHERE b.user.id = :userId AND b.isReturned = false")
    List findActiveUserBorrows(@Param("userId") String userId);
}

public interface UserAppRepository extends JpaRepository {
    @Query(value = "SELECT * FROM user_app WHERE EXTRACT(YEAR FROM AGE(dob)) > :age", nativeQuery = true)
    List findUsersOlderThan(@Param("age") int age);
}

4 3. Specification API: filters

The Specification API allows you to build dynamic queries programmatically. This is useful when you need to create complex queries based on multiple conditions.

public interface BookRepository extends JpaRepository, JpaSpecificationExecutor {
}

public class BookSpecifications {
    public static Specification isAvailable() {
        return (root, query, cb) -> cb.isTrue(root.get("available"));
    }

    public static Specification hasAuthor(String author) {
        return (root, query, cb) -> cb.equal(root.get("author"), author);
    }
}

// Usage
List availableBooksByAuthor = bookRepository.findAll(where(isAvailable()).and(hasAuthor("J.K. Rowling")));

5 4. Query by Example (QBE)

Query by Example allows you to create queries using domain object instances as examples.

public interface BookRepository extends JpaRepository, QueryByExampleExecutor {
}

// Usage
Book exampleBook = new Book();
exampleBook.setAuthor("George Orwell");
exampleBook.setAvailable(true);

Example example = Example.of(exampleBook);
List books = bookRepository.findAll(example);

6 5. @NamedQueries annotation

@NamedQueries is another powerful way to create queries in Spring Boot JPA. This method allows you to define queries at the entity level, separating the query definition from the repository interface.

Here’s how you can use @NamedQueries with our entities:

@Entity
@NamedQueries({
    @NamedQuery(name = "Book.findAvailableByAuthor",
        query = "SELECT b FROM Book b WHERE b.author = :author AND b.available = true"),
    @NamedQuery(name = "Book.countByPublicationYear",
        query = "SELECT COUNT(b) FROM Book b WHERE FUNCTION('YEAR', b.publicationDate) = :year")
})
public class Book {
    // ... existing fields and methods
}

@Entity
@NamedQueries({
    @NamedQuery(name = "Borrow.findOverdueBorrows",
        query = "SELECT b FROM Borrow b WHERE b.returnDate  {
    List findAvailableByAuthor(@Param("author") String author);
    long countByPublicationYear(@Param("year") int year);
}

public interface BorrowRepository extends JpaRepository {
    List findOverdueBorrows();
    int sumPointsByUser(@Param("userId") String userId);
}

public interface UserAppRepository extends JpaRepository {
    List findActiveUsersByAgeRange(@Param("minAge") int minAge, @Param("maxAge") int maxAge);
    @Modifying
    @Query(name = "UserApp.updateUserAddress")
    int updateUserAddress(@Param("newAddress") String newAddress, @Param("userId") String userId);
}

Using @NamedQueries offers several advantages:

1. It separates query logic from the repository interface, improving code organization.
2. It allows for easy reuse of complex queries across multiple repositories.
3. It provides better performance as named queries are parsed and prepared when the persistence unit is created[1].

Remember to follow the naming convention . for Spring Data JPA to automatically resolve and use the named queries.

7 Additional Resources

7.1 JPQL

• JPQL - Java Persistence Query Language
  
• JPA Query Language
  
• JPQL Reference
  
• Spring Data JPA - Query Creation

7.2 JPA Queries

For more information on Spring Boot JPA query methods, check out these resources:

• Defining Query Methods
  
• Query by Example
  
• JPA Query Methods
  
• Spring Boot: JPA & DI