How to use WHERE Clause in JPA Query

by

The WHERE Clause in JPA filters database table records based on the query’s specified conditions. We can use the WHERE Clause in JPA queries using JPQL and Native SQL queries. In this topic, we will learn how to use the WHERE clause in JPA Queries in various ways.

use WHERE clause in JPA query

Use WHERE Clause in JPA Query

There are two ways to use WHERE Clause in JPA query to filter records on the provided condition. These are the ways:

  • Using WHERE Clause in JPQL
  • Using WHERE Clause in Native SQL Query

Using WHERE Clause in JPQL

In JPQL, the WHERE clause works on the entity’s properties instead of columns making database queries independent.

Syntax of JPQL with WHERE Clause

@Query("SELECT e FROM EntityName e WHERE e.attribute = :value")
List<EntityName> methodName(DataType value);

Example: Filter Orders by Status

Entity Class

@Data
@Entity
public class Orders {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String product;
private double price;
private String status;
}

Repository Method:

@Query("SELECT o FROM Orders o WHERE o.status = :status")
List<Orders> findByStatus(String status);

Usage:

List<Orders> pendingOrders = orderRepository.findByStatus("PENDING");

Using WHERE Clause with Multiple Conditions

We can combine multiple conditions in the WHERE clause using logical operators such as AND, OR and etc.

Example: Filter Orders by Status and Minimum Price

@Query("SELECT o FROM Orders o WHERE o.status = :status AND o.price > :price")
List<Orders> findByStatusAndPriceGreaterThan(String status, double price);

Usage:

List<Orders> expensivePendingOrders = orderRepository.findByStatusAndPriceGreaterThan("PENDING", 100.0);

Using WHERE Clause with LIKE for Pattern Matching

JPQL supports the LIKE operator for partial string matching.

Example: Filter Orders by Product Name Pattern

@Query("SELECT o FROM Orders o WHERE o.product LIKE :productNamePattern")
List<Orders> findByProductNamePattern(String productNamePattern);

Usage:

List<Orders> laptopOrders = orderRepository.findByProductNamePattern("%Laptop%");

Using WHERE Clause with IN Clause

The IN operator filters records where a value matches any value in a given list.

Example: Filter Orders by Multiple Statuses

@Query("SELECT o FROM Orders o WHERE o.status IN :statuses")
List<Orders> findByStatuses(List<String> statuses);

Usage:

List<String> statuses = Arrays.asList("PENDING", "COMPLETED");
List<Orders> orders = orderRepository.findByStatuses(statuses);

Using WHERE Clause in Native SQL Queries

In Native SQL queries, the WHERE clause uses raw SQL syntax and directly interacts with database tables and columns.

Example: Filter Orders by Status

@Query(value = "SELECT * FROM orders WHERE status = :status", nativeQuery = true)
List<Orders> findByStatusNative( String status);

Usage:

List<Orders> pendingOrders = orderRepository.findByStatusNative("PENDING");

Using WHERE Clause with Advanced SQL Functions

Native SQL queries allow the use of database-specific functions like BETWEEN.

Example: Filter Orders by Price Range

@Query(value = "SELECT * FROM orders WHERE price BETWEEN :minPrice AND :maxPrice", nativeQuery = true)
List<Orders> findByPriceRange(double minPrice,double maxPrice);

Usage:

List<Orders> orders = orderRepository.findByPriceRange(50.0, 200.0);

Best Practices for Using WHERE Clause in JPA

  • Use Parameters: Always use named parameters (e.g., :paramName) to prevent SQL injection.
  • Index Key Columns: Ensure columns used in the WHERE clause are indexed for better query performance.
  • Avoid Over-Complex Queries: Break down overly complex queries into manageable parts for better readability.
  • Test Query Performance: Test both JPQL and Native SQL queries to ensure optimal performance.
  • Use JPQL for Portability: Use JPQL for queries that need to work across multiple databases.

Conclusion

The WHERE clause in JPA is a powerful tool for filtering data using JPQL and Native SQL queries. While JPQL is preferred for portability and working with entity attributes, Native SQL is suitable for complex queries and database-specific optimizations. By using WHERE clauses effectively and following best practices, we can build efficient and maintainable data access layers in our Spring Boot applications.

Leave a Comment