Spring Data JPA: Custom Query with @Query

This snippet demonstrates how to define custom queries in a Spring Data JPA repository using the `@Query` annotation. This allows you to write more complex queries than can be derived from method names alone.

Entity Definition (Customer.java)

This code defines a `Customer` entity with fields for `id`, `firstName`, `lastName`, and `email`. JPA annotations are used to map the class to a database table and the fields to database columns. Getters and setters are crucial for JPA to access and modify the entity's properties.

import jakarta.persistence.*;

@Entity
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String firstName;

    @Column(nullable = false)
    private String lastName;

    @Column(nullable = false)
    private String email;

    public Customer() {}

    public Customer(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    // Getters and setters (omitted for brevity)

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Repository Interface (CustomerRepository.java)

This code defines a `CustomerRepository` interface that extends `JpaRepository`. The `@Query` annotation is used to define custom queries. `findByFirstNameCustomQuery` uses JPQL (Java Persistence Query Language) to query the database. `findByLastNameNativeQuery` uses a native SQL query. `@Param` is used to bind method parameters to query parameters.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query("SELECT c FROM Customer c WHERE c.firstName = :firstName")
    List<Customer> findByFirstNameCustomQuery(@Param("firstName") String firstName);

    @Query(value = "SELECT * FROM customers WHERE last_name = :lastName", nativeQuery = true)
    List<Customer> findByLastNameNativeQuery(@Param("lastName") String lastName);

}

Service Class (CustomerService.java)

This code defines a `CustomerService` class that uses the `CustomerRepository` to perform database operations. It demonstrates how to call the custom query methods defined in the repository. The `getCustomersByFirstName` and `getCustomersByLastName` methods use the custom queries to retrieve data. Constructor injection is used to inject the `CustomerRepository` instance.

import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CustomerService {

    private final CustomerRepository customerRepository;

    public CustomerService(CustomerRepository customerRepository) {
        this.customerRepository = customerRepository;
    }

    public List<Customer> getCustomersByFirstName(String firstName) {
        return customerRepository.findByFirstNameCustomQuery(firstName);
    }

    public List<Customer> getCustomersByLastName(String lastName) {
        return customerRepository.findByLastNameNativeQuery(lastName);
    }

    public Customer createCustomer(Customer customer) {
        return customerRepository.save(customer);
    }
}

Real-Life Use Case

In a CRM (Customer Relationship Management) system, you might need to retrieve customers based on complex criteria that cannot be easily expressed using derived query methods. For example, you might need to search for customers whose first name starts with a specific prefix, or who have placed orders within a certain date range. Custom queries using `@Query` allow you to implement these complex search requirements.

Concepts Behind the Snippet

This snippet illustrates the following key concepts:

• Custom Queries: Using the `@Query` annotation to define custom queries in Spring Data JPA repositories.
• JPQL: Java Persistence Query Language, used for writing queries against JPA entities.
• Native SQL: Using native SQL queries when JPQL is not sufficient.
• Parameter Binding: Using `@Param` to bind method parameters to query parameters.

Best Practices

• Use JPQL whenever possible, as it is more portable and less dependent on the specific database.
• Use native SQL queries only when JPQL is not sufficient.
• Use parameter binding to prevent SQL injection vulnerabilities.
• Test your custom queries thoroughly to ensure they return the correct results.

When to use them

Use `@Query` when your query logic exceeds the capabilities of method name-based query derivation. This often happens with complex filtering, joins, or aggregate functions.

Interview Tip

Be prepared to explain the difference between JPQL and native SQL queries. Also, be prepared to discuss the benefits and drawbacks of using custom queries.

Alternatives

Alternatives to using `@Query` include:

• Criteria API: A programmatic way to build queries using JPA.
• Querydsl: A type-safe query builder for JPA and other data access technologies.

Pros

• Flexibility: Allows you to write complex queries that cannot be derived from method names.
• Control: Provides more control over the generated SQL.

Cons

• Complexity: Requires writing JPQL or native SQL, which can be more complex than using derived query methods.
• Maintainability: Can be more difficult to maintain than derived query methods.