Explore JPA (Java Persistence API) Mastery with Java and Spring Boot (SQL) - Part 1

Introduction:

Java Persistence API (JPA) has become a cornerstone in the world of Java enterprise development, providing a powerful and flexible way to interact with relational databases. While many developers are familiar with the basics of JPA, mastering its advanced features and understanding best practices is crucial for building robust and efficient database applications. In this blog, we will explore some advanced techniques and best practices to take your JPA skills to the next level.

Spring Data JPA

Note 1: A foundational comprehension of JPA is essential since this is not a tutorial.

Note 2: When discussing various approaches to database interaction, it’s crucial to consider JPA, JDBC Template, and Hibernate. However, it’s essential to note that declaring one as definitively superior is not feasible; the optimal choice hinges on the project’s unique requirements. In summary, the decision between JPA, JDBC Template, and Hibernate is contingent on the project’s needs, the team’s expertise, and individual preferences. JPA stands out for its standardized interface and adaptability, JDBC Template excels in providing fine-grained control and simplicity, while Hibernate offers potent ORM features with a heightened level of abstraction. Ultimately, the most suitable choice is the one that aligns seamlessly with the specific application’s objectives and constraints.

Prerequisite:

1. Begin with a Spring Boot application by visiting https://start.spring.io/
2. Ensure a running SQL database (PostgreSQL, MySQL, Oracle, MSSQL, DB2 or any other RDBMS) to connect with Spring Boot. If you prefer a hassle-free option, consider using an in-memory database like H2/ Employ a Docker image to run the database if Docker is already installed on your system.
3. Ensure that you include the necessary dependencies, such as Spring Data JPA and the SQL Database Driver (choose the PostgreSQL driver if you’re using a PostgreSQL database, based on your SQL Database selection).

Types:

1. Return Entity as response
2. Return DTO as response using DTO Constructor (Applicable only when using JPQL)
3. Return Interface as response (Applicable when using JPQL/Native Queries)
4. Return DTO as response (Applicable when using Native Queries achieved through Named Native Queries)
5. Miscellaneous

Type 1 : Return Entity as response

In these scenarios, we apply filters, utilize various operators, sort the data, employ limit and offset, use the like clause, perform joins, and utilize inner queries to obtain the result as an entity object.

Step1 : Certainly, let’s commence with a straightforward Order entity

@Entity
@Table(name = "orders")
@Builder
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id")
    private Long orderId;
    @Column(name = "order_date")
    private LocalDate orderDate;
    @Column(name = "customer_id")
    private Long customerId;
}

Step2 : Let’s proceed to develop a repository layer for the mentioned entity

public interface OrderRepository extends JpaRepository<Order, Long> {

}

Assuming everyone is familiar with JPA and its functionality, let’s craft our bespoke implementation utilizing JPA, JPQL, Native Queries, and Named Native Queries.

Let’s fetch list of Orders by Customer ID, using custom JPA method

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByCustomerId(Long customerId);
}

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id = ?

Achieving the same outcome is possible through the utilization of JPQL (Java Persistence Query Language)

public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("SELECT o FROM Order o WHERE o.customerId = :customerId")
    List<Order> findByCustomerId(@Param("customerId") Long customerId);
}

If the customerId specified in the parameter is identical to the one used in the query, there’s no need to explicitly state its name using @Param annotation.

@Query("SELECT o FROM Order o WHERE o.customerId = :customerId")
List<Order> findByCustomerId(Long customerId);

An alternative method to substitute parameters in the SQL query when using the @Query annotation is by employing ?1, where the number signifies the position of the parameter to be replaced.

@Query(value = "SELECT o FROM Order o WHERE o.customerId = ?1")
List<Order> findByCustomerId(Long customerId);

Achieving the same outcome is possible through the utilization of Native Queries (SQL Query)

@Query(value = "SELECT o.* FROM orders o WHERE o.customer_id = :customerId", nativeQuery = true)
List<Order> findByCustomerId(Long customerId);

OR

@Query(value = "SELECT * FROM orders WHERE customer_id = :customerId", nativeQuery = true)
List<Order> findByCustomerId(Long customerId);

In All the above repository method it generates this query

Hibernate: SELECT * FROM orders WHERE customer_id = ?

Let’s explore couple of other types of JPA methods

1. Retrieve a list of orders by customer IDs using the IN clause.

Repository Method:
List<Order> findByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?)

OR
Repository Method:
@Query("SELECT o FROM Order o WHERE o.customerId IN (:customerIds)")
List<Order> findByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?)

OR
Repository Method:
@Query(value = "SELECT o.* FROM orders o WHERE o.customer_id IN (:customerIds)", nativeQuery = true)
List<Order> findByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?)

OR
Repository Method:
@Query(value = "SELECT * FROM orders WHERE customer_id IN (:customerId)", nativeQuery = true)
List<Order> findByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?)

2. Retrieve a list of orders by customer IDs with some operators (NOTIN, LESSTHAN, LESSTHANEQUAL, GREATERTHAN, GREATERTHANEQUAL).

1) Retrieve orders that were not placed by specific customer IDs
Repository Method:
List<Order> findByCustomerIdNotIn(@Param("customerIds") Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id not in(?,?)

2) Retrieve orders that were placed before the specified date
Repository Method:
List<Order> findByCustomerIdInAndOrderDateLessThan(@Param("customerIds") Set<Long> customerIds, @Param("orderDate") LocalDate orderDate);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) and o1_0.order_date<?

3) Retrieve orders that were placed after the specified date
Repository Method:
List<Order> findByCustomerIdInAndOrderDateGreaterThan(@Param("customerIds") Set<Long> customerIds, @Param("orderDate") LocalDate orderDate);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) and o1_0.order_date>?

4) Retrieve orders that were placed before or equal to the specified date
Repository Method:
List<Order> findByCustomerIdInAndOrderDateLessThanEqual(@Param("customerIds") Set<Long> customerIds, @Param("orderDate") LocalDate orderDate);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) and o1_0.order_date<=?

5) Retrieve orders that were placed after or equal to the specified date
Repository Method:
List<Order> findByCustomerIdInAndOrderDateGreaterThanEqual(@Param("customerIds") Set<Long> customerIds, @Param("orderDate") LocalDate orderDate);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) and o1_0.order_date>=?

3. Retrieve a list of orders by customer IDs using the IN clause and sorting the results.

1) Fetch Orders by customer ids and order by order id in ascending order using JPA
Repository Method:
List<Order> findByCustomerIdInOrderByOrderIdAsc(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id asc

2) Fetch Orders by customer ids and order by order id in descending order using JPA
Repository Method:
List<Order> findByCustomerIdInOrderByOrderIdDesc(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id desc

3) Fetch Orders by customer ids and order by order id in descending and order date ascending order using JPA
Repository Method:
List<Order> findByCustomerIdInOrderByOrderIdDescOrderDateAsc(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id desc,o1_0.order_date asc

OR 
4) Fetch Orders by customer ids and order by order id in desc order using Sort Object
List<Order> orders = orderRepository.findByCustomerIdIn(Set.of(1l, 2l), Sort.by("orderId").ascending());

Repository Method:
List<Order> findByCustomerIdIn(Set<Long> customerIds, Sort sort);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id asc

OR
5) Fetch Orders by customer ids and order by order id in descending and order date ascending using Sort Object
List<Order> orders = orderRepository.findByCustomerIdIn(Set.of(1l, 2l), Sort.by(Sort.Order.desc("orderId"), Sort.Order.asc("orderDate")));

Repository Method:
List<Order> findByCustomerIdIn(Set<Long> customerIds, Sort sort);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id desc,o1_0.order_date asc

OR

6) Fetch Orders by customer ids and order by order id in descending and order date ascending using JPQL
List<Order> orders = orderRepository.findByCustomerIdIn(Set.of(1l, 2l));

Repository Method:
@Query(value = "SELECT o FROM Order o WHERE o.customerId IN (:customerIds) ORDER BY orderId DESC, orderDate ASC")
List<Order> findByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id desc,o1_0.order_date asc

OR

7) Fetch Orders by customer ids and order by order id in descending and order date ascending using Native Query
List<Order> orders = orderRepository.findByCustomerIdIn(Set.of(1l, 2l));

Repository Method:
@Query(value = "SELECT o.* FROM orders o WHERE o.customer_id IN (:customerIds) ORDER BY order_id desc, order_date asc", nativeQuery = true)
List<Order> findByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) order by o1_0.order_id desc,o1_0.order_date asc

4. Retrieve a list of orders by customer IDs using the IN clause and pagination (Page and Size) (TOP, PAGEABLE, OFFSET, LIMIT)

1) Fetch Top 5 Orders filter by customer ids using JPA
Repository Method:
List<Order> findTop5ByCustomerIdIn(Set<Long> customerIds);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) fetch first 5 rows only

OR
Page = 0
Size = 5
List<Order> orders = orderRepository.findByCustomerIdInOffsetAndLimit(Set.of(1l, 2l), PageRequest.of(0, 5));

Repository Method:
@Query("SELECT o FROM Order o WHERE o.customerId IN (:customerIds)")
List<Order> findByCustomerIdInOffsetAndLimit(Set<Long> customerIds, Pageable pageable);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) offset 0 rows fetch first 5 rows only


OR
Page = 0
Size = 5
List<Order> orders = orderRepository.findByCustomerIdInOffsetAndLimit(Set.of(1l, 2l), 0, 5);

Repository Method:
@Query(value = "SELECT * FROM orders WHERE customer_id IN (:customerIds) OFFSET :offset LIMIT :size", nativeQuery = true)
List<Order> findByCustomerIdInOffsetAndLimit(Set<Long> customerIds, int offset, int size);

Generated SQL Query:
Hibernate: SELECT * FROM orders WHERE customer_id IN (?,?) OFFSET ? LIMIT ?

5. Retrieve a list of orders by customer IDs using the IN, LIKE clause, and narrow down the records by filtering for orders placed on the 17th day.

Repository Method:
@Query("SELECT o FROM Order o WHERE o.customerId IN :customerIds AND TO_CHAR(o.orderDate, 'YYYY-MM-DD') LIKE :day")
List<Order> findByCustomerIdInAndDay(Set<Long> customerIds, String day);

Generated SQL Query:
Hibernate: select o1_0.* from orders o1_0 where o1_0.customer_id in(?,?) and to_char(o1_0.order_date,'YYYY-MM-DD') like ? escape ''

Let’s establish a Customer entity.

@Entity
@Table(name = "customers")
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Long customerId;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "email")
    private String email;
    @Column(name = "address")
    private String address;
}

6. Get a list of customers based on order IDs (JOIN).

Repository Method:
@Query("SELECT c FROM Order o LEFT JOIN Customer c ON o.customerId = c.customerId WHERE o.orderId IN (:orderIds)")
List<Customer> findCustomersByOrderIdIn(Set<Long> orderIds);

Generated SQL Query:
Hibernate: select c1_0.* from orders o1_0 left join customers c1_0 on o1_0.customer_id=c1_0.customer_id where o1_0.order_id in(?,?)

7. Retrieve a customer details who ordered last (INNER QUERIES/ NESTED QUERIES)

1) Retrieve the customer object for the most recent order.
Repository Method:
@Query("SELECT c FROM Customer c WHERE c.customerId = (SELECT o.customerId FROM Order o ORDER BY orderDate DESC LIMIT 1)")
Customer findCustomerWhoOrderedLast();

Generated SQL Query:
Hibernate: select c1_0.* from customers c1_0 where c1_0.customer_id=(select o1_0.customer_id from orders o1_0 order by o1_0.order_date desc fetch first 1 rows only)

I recommend visiting the provided link for Part 2 of this discussion:

Explore JPA (Java Persistence API) Mastery with Java and Spring Boot (SQL) - Part 2