Introduction
Spring Data's Query by Example (QBE) provides a user-friendly API for dynamic query execution without requiring extensive repository method declarations. Unlike traditional method name derivation or @Query annotations, QBE allows you to create query examples at runtime, making it ideal for search forms, dynamic filters, and scenarios where query criteria are not known at compile time.
This comprehensive guide explores Spring Data QBE through practical examples, demonstrating how to build flexible, type-safe queries for various use cases.
Core Concepts and Setup
1. Project Dependencies
<dependencies> <!-- Spring Data JPA --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <!-- H2 Database for examples --> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency> <!-- Validation --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <!-- Test dependencies --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
2. Domain Entities
@Entity
@Table(name = "customers")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String firstName;
@Column(nullable = false)
private String lastName;
@Column(unique = true)
private String email;
private String phone;
@Enumerated(EnumType.STRING)
private CustomerStatus status;
private LocalDate birthDate;
private LocalDateTime createdAt;
private Boolean active;
private Integer loyaltyPoints;
@Embedded
private Address address;
@PrePersist
protected void onCreate() {
this.createdAt = LocalDateTime.now();
}
}
@Embeddable
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Address {
private String street;
private String city;
private String state;
private String zipCode;
private String country;
}
@Entity
@Table(name = "products")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
private String description;
@Column(nullable = false)
private BigDecimal price;
@Enumerated(EnumType.STRING)
private ProductCategory category;
private Integer stockQuantity;
private Boolean available;
private LocalDateTime createdDate;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "supplier_id")
private Supplier supplier;
@ElementCollection
@CollectionTable(name = "product_tags", joinColumns = @JoinColumn(name = "product_id"))
@Column(name = "tag")
private Set<String> tags;
@PrePersist
protected void onCreate() {
this.createdDate = LocalDateTime.now();
}
}
@Entity
@Table(name = "suppliers")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Supplier {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String contactEmail;
private String phone;
private Boolean active;
}
@Entity
@Table(name = "orders")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String orderNumber;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_id")
private Customer customer;
@Enumerated(EnumType.STRING)
private OrderStatus status;
private BigDecimal totalAmount;
private LocalDateTime orderDate;
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
private List<OrderItem> items;
}
@Entity
@Table(name = "order_items")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class OrderItem {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id")
private Order order;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "product_id")
private Product product;
private Integer quantity;
private BigDecimal unitPrice;
}
public enum CustomerStatus {
NEW, ACTIVE, INACTIVE, VIP, BLOCKED
}
public enum ProductCategory {
ELECTRONICS, CLOTHING, BOOKS, HOME_APPLIANCES, SPORTS, BEAUTY
}
public enum OrderStatus {
PENDING, CONFIRMED, SHIPPED, DELIVERED, CANCELLED, REFUNDED
}
Basic Query by Example Usage
1. Repository Setup with QueryByExampleExecutor
@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long>,
QueryByExampleExecutor<Customer> {
// Traditional derived queries can coexist with QBE
List<Customer> findByStatus(CustomerStatus status);
Optional<Customer> findByEmail(String email);
}
@Repository
public interface ProductRepository extends JpaRepository<Product, Long>,
QueryByExampleExecutor<Product> {
}
@Repository
public interface OrderRepository extends JpaRepository<Order, Long>,
QueryByExampleExecutor<Order> {
}
@Repository
public interface SupplierRepository extends JpaRepository<Supplier, Long>,
QueryByExampleExecutor<Supplier> {
}
2. Basic Example Queries Service
@Service
@Slf4j
@Transactional(readOnly = true)
public class BasicExampleService {
private final CustomerRepository customerRepository;
private final ProductRepository productRepository;
public BasicExampleService(CustomerRepository customerRepository,
ProductRepository productRepository) {
this.customerRepository = customerRepository;
this.productRepository = productRepository;
}
/**
* Basic exact match query
*/
public List<Customer> findCustomersByExactMatch(String firstName, String lastName) {
Customer probe = Customer.builder()
.firstName(firstName)
.lastName(lastName)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.findAll(example);
}
/**
* Query with enum value
*/
public List<Customer> findCustomersByStatus(CustomerStatus status) {
Customer probe = Customer.builder()
.status(status)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.findAll(example);
}
/**
* Query with Boolean field
*/
public List<Customer> findActiveCustomers() {
Customer probe = Customer.builder()
.active(true)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.findAll(example);
}
/**
* Query with embedded object properties
*/
public List<Customer> findCustomersByCity(String city) {
Address address = Address.builder()
.city(city)
.build();
Customer probe = Customer.builder()
.address(address)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.findAll(example);
}
/**
* Find one result with Example
*/
public Optional<Customer> findCustomerByEmail(String email) {
Customer probe = Customer.builder()
.email(email)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.findOne(example);
}
/**
* Check existence with Example
*/
public boolean existsCustomerWithEmail(String email) {
Customer probe = Customer.builder()
.email(email)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.exists(example);
}
/**
* Count with Example
*/
public long countCustomersByStatus(CustomerStatus status) {
Customer probe = Customer.builder()
.status(status)
.build();
Example<Customer> example = Example.of(probe);
return customerRepository.count(example);
}
}
Advanced Example Matcher Configurations
1. Custom Example Matcher Service
@Service
@Slf4j
@Transactional(readOnly = true)
public class AdvancedExampleService {
private final CustomerRepository customerRepository;
private final ProductRepository productRepository;
private final OrderRepository orderRepository;
public AdvancedExampleService(CustomerRepository customerRepository,
ProductRepository productRepository,
OrderRepository orderRepository) {
this.customerRepository = customerRepository;
this.productRepository = productRepository;
this.orderRepository = orderRepository;
}
/**
* String matching with contains (LIKE %value%)
*/
public List<Customer> findCustomersByFirstNameContains(String firstName) {
Customer probe = Customer.builder()
.firstName(firstName)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("firstName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());
Example<Customer> example = Example.of(probe, matcher);
return customerRepository.findAll(example);
}
/**
* String matching with startsWith (LIKE value%)
*/
public List<Customer> findCustomersByLastNameStartsWith(String lastName) {
Customer probe = Customer.builder()
.lastName(lastName)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.startsWith().ignoreCase());
Example<Customer> example = Example.of(probe, matcher);
return customerRepository.findAll(example);
}
/**
* Multiple field matching with different strategies
*/
public List<Customer> findCustomersAdvanced(String firstName, String lastName,
CustomerStatus status, Boolean active) {
Customer probe = Customer.builder()
.firstName(firstName)
.lastName(lastName)
.status(status)
.active(active)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("firstName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
.withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
.withIgnorePaths("id", "createdAt"); // Ignore null fields for these paths
Example<Customer> example = Example.of(probe, matcher);
return customerRepository.findAll(example);
}
/**
* Global matching strategy - apply to all string properties
*/
public List<Product> findProductsByAnyStringField(String searchTerm) {
Product probe = Product.builder()
.name(searchTerm)
.description(searchTerm)
.build();
ExampleMatcher matcher = ExampleMatcher.matchingAny()
.withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
.withIgnoreCase()
.withIgnorePaths("id", "price", "stockQuantity", "createdDate");
Example<Product> example = Example.of(probe, matcher);
return productRepository.findAll(example);
}
/**
* Numeric range queries (using minimum values)
*/
public List<Product> findProductsWithMinimumPrice(BigDecimal minPrice) {
Product probe = Product.builder()
.price(minPrice)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("price", ExampleMatcher.GenericPropertyMatchers.greaterThanOrEqualTo());
Example<Product> example = Example.of(probe, matcher);
return productRepository.findAll(example);
}
/**
* Query with nested entity properties
*/
public List<Product> findProductsBySupplierName(String supplierName) {
Supplier supplier = Supplier.builder()
.name(supplierName)
.build();
Product probe = Product.builder()
.supplier(supplier)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("supplier.name", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
.withIgnorePaths("id", "price", "description");
Example<Product> example = Example.of(probe, matcher);
return productRepository.findAll(example);
}
/**
* Query with date ranges
*/
public List<Customer> findCustomersCreatedAfter(LocalDateTime date) {
Customer probe = Customer.builder()
.createdAt(date)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("createdAt", ExampleMatcher.GenericPropertyMatchers.greaterThanOrEqualTo());
Example<Customer> example = Example.of(probe, matcher);
return customerRepository.findAll(example);
}
}
Real-World Use Cases
1. Dynamic Search Service
@Service
@Slf4j
@Transactional(readOnly = true)
public class DynamicSearchService {
private final CustomerRepository customerRepository;
private final ProductRepository productRepository;
public DynamicSearchService(CustomerRepository customerRepository,
ProductRepository productRepository) {
this.customerRepository = customerRepository;
this.productRepository = productRepository;
}
/**
* Dynamic customer search with multiple optional criteria
*/
public List<Customer> searchCustomers(CustomerSearchCriteria criteria) {
Customer probe = createCustomerProbe(criteria);
ExampleMatcher matcher = createCustomerMatcher(criteria);
Example<Customer> example = Example.of(probe, matcher);
return customerRepository.findAll(example, Sort.by("lastName", "firstName"));
}
/**
* Dynamic product search with pagination
*/
public Page<Product> searchProducts(ProductSearchCriteria criteria, Pageable pageable) {
Product probe = createProductProbe(criteria);
ExampleMatcher matcher = createProductMatcher(criteria);
Example<Product> example = Example.of(probe, matcher);
return productRepository.findAll(example, pageable);
}
private Customer createCustomerProbe(CustomerSearchCriteria criteria) {
return Customer.builder()
.firstName(criteria.getFirstName())
.lastName(criteria.getLastName())
.email(criteria.getEmail())
.status(criteria.getStatus())
.active(criteria.getActive())
.address(createAddressProbe(criteria))
.build();
}
private Address createAddressProbe(CustomerSearchCriteria criteria) {
if (criteria.getCity() == null && criteria.getState() == null && criteria.getCountry() == null) {
return null;
}
return Address.builder()
.city(criteria.getCity())
.state(criteria.getState())
.country(criteria.getCountry())
.build();
}
private ExampleMatcher createCustomerMatcher(CustomerSearchCriteria criteria) {
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("id", "createdAt", "loyaltyPoints", "phone", "birthDate");
if (criteria.getFirstName() != null) {
matcher = matcher.withMatcher("firstName",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());
}
if (criteria.getLastName() != null) {
matcher = matcher.withMatcher("lastName",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());
}
if (criteria.getCity() != null) {
matcher = matcher.withMatcher("address.city",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());
}
return matcher;
}
private Product createProductProbe(ProductSearchCriteria criteria) {
return Product.builder()
.name(criteria.getName())
.category(criteria.getCategory())
.available(criteria.getAvailable())
.price(criteria.getMinPrice() != null ? criteria.getMinPrice() : null)
.supplier(createSupplierProbe(criteria))
.build();
}
private Supplier createSupplierProbe(ProductSearchCriteria criteria) {
if (criteria.getSupplierName() == null) {
return null;
}
return Supplier.builder()
.name(criteria.getSupplierName())
.build();
}
private ExampleMatcher createProductMatcher(ProductSearchCriteria criteria) {
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("id", "createdDate", "description", "tags");
if (criteria.getName() != null) {
matcher = matcher.withMatcher("name",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());
}
if (criteria.getSupplierName() != null) {
matcher = matcher.withMatcher("supplier.name",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());
}
if (criteria.getMinPrice() != null) {
matcher = matcher.withMatcher("price",
ExampleMatcher.GenericPropertyMatchers.greaterThanOrEqualTo());
}
return matcher;
}
@Data
public static class CustomerSearchCriteria {
private String firstName;
private String lastName;
private String email;
private CustomerStatus status;
private Boolean active;
private String city;
private String state;
private String country;
}
@Data
public static class ProductSearchCriteria {
private String name;
private ProductCategory category;
private Boolean available;
private BigDecimal minPrice;
private String supplierName;
}
}
2. REST API with Query by Example
@RestController
@RequestMapping("/api/customers")
@Validated
@Slf4j
public class CustomerController {
private final DynamicSearchService searchService;
public CustomerController(DynamicSearchService searchService) {
this.searchService = searchService;
}
@GetMapping("/search")
public ResponseEntity<List<Customer>> searchCustomers(
@RequestParam(required = false) String firstName,
@RequestParam(required = false) String lastName,
@RequestParam(required = false) String email,
@RequestParam(required = false) CustomerStatus status,
@RequestParam(required = false) Boolean active,
@RequestParam(required = false) String city,
@RequestParam(required = false) String state,
@RequestParam(required = false) String country) {
DynamicSearchService.CustomerSearchCriteria criteria =
new DynamicSearchService.CustomerSearchCriteria();
criteria.setFirstName(firstName);
criteria.setLastName(lastName);
criteria.setEmail(email);
criteria.setStatus(status);
criteria.setActive(active);
criteria.setCity(city);
criteria.setState(state);
criteria.setCountry(country);
List<Customer> customers = searchService.searchCustomers(criteria);
return ResponseEntity.ok(customers);
}
}
@RestController
@RequestMapping("/api/products")
@Validated
@Slf4j
public class ProductController {
private final DynamicSearchService searchService;
public ProductController(DynamicSearchService searchService) {
this.searchService = searchService;
}
@GetMapping("/search")
public ResponseEntity<Page<Product>> searchProducts(
@RequestParam(required = false) String name,
@RequestParam(required = false) ProductCategory category,
@RequestParam(required = false) Boolean available,
@RequestParam(required = false) BigDecimal minPrice,
@RequestParam(required = false) String supplierName,
@RequestParam(defaultValue = "0") int page,
@RequestParam(defaultValue = "20") int size,
@RequestParam(defaultValue = "name") String sort) {
DynamicSearchService.ProductSearchCriteria criteria =
new DynamicSearchService.ProductSearchCriteria();
criteria.setName(name);
criteria.setCategory(category);
criteria.setAvailable(available);
criteria.setMinPrice(minPrice);
criteria.setSupplierName(supplierName);
Pageable pageable = PageRequest.of(page, size, Sort.by(sort));
Page<Product> products = searchService.searchProducts(criteria, pageable);
return ResponseEntity.ok(products);
}
}
Performance Optimization and Best Practices
1. Optimized Example Service
@Service
@Slf4j
@Transactional(readOnly = true)
public class OptimizedExampleService {
private final CustomerRepository customerRepository;
private final ProductRepository productRepository;
// Reusable matchers for better performance
private final ExampleMatcher caseInsensitiveContainsMatcher;
private final ExampleMatcher startsWithMatcher;
public OptimizedExampleService(CustomerRepository customerRepository,
ProductRepository productRepository) {
this.customerRepository = customerRepository;
this.productRepository = productRepository;
// Pre-configure common matchers
this.caseInsensitiveContainsMatcher = ExampleMatcher.matching()
.withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
.withIgnoreCase()
.withIgnorePaths("id", "createdAt", "loyaltyPoints");
this.startsWithMatcher = ExampleMatcher.matching()
.withStringMatcher(ExampleMatcher.StringMatcher.STARTING)
.withIgnoreCase()
.withIgnorePaths("id", "createdAt");
}
/**
* Use pre-configured matchers for better performance
*/
public List<Customer> findCustomersQuickSearch(String searchTerm) {
Customer probe = Customer.builder()
.firstName(searchTerm)
.lastName(searchTerm)
.email(searchTerm)
.build();
Example<Customer> example = Example.of(probe, caseInsensitiveContainsMatcher);
return customerRepository.findAll(example, Sort.by("lastName", "firstName"));
}
/**
* Combine QBE with custom queries for complex scenarios
*/
public List<Customer> findHighValueActiveCustomers(String city) {
// Use QBE for simple criteria
Customer probe = Customer.builder()
.active(true)
.address(Address.builder().city(city).build())
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("id", "firstName", "lastName", "email", "createdAt");
Example<Customer> example = Example.of(probe, matcher);
List<Customer> candidates = customerRepository.findAll(example);
// Apply additional business logic in memory for complex criteria
return candidates.stream()
.filter(customer -> customer.getLoyaltyPoints() != null && customer.getLoyaltyPoints() > 1000)
.collect(Collectors.toList());
}
/**
* Batch processing with QBE
*/
@Transactional
public int deactivateInactiveCustomers() {
Customer probe = Customer.builder()
.active(true)
.build();
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("id", "firstName", "lastName", "email", "createdAt", "address");
Example<Customer> example = Example.of(probe, matcher);
List<Customer> activeCustomers = customerRepository.findAll(example);
List<Customer> toDeactivate = activeCustomers.stream()
.filter(customer -> customer.getCreatedAt().isBefore(LocalDateTime.now().minusYears(1)))
.peek(customer -> customer.setActive(false))
.collect(Collectors.toList());
customerRepository.saveAll(toDeactivate);
return toDeactivate.size();
}
}
Testing Query by Example
1. Comprehensive Test Suite
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
class QueryByExampleTest {
@Autowired
private TestEntityManager entityManager;
@Autowired
private CustomerRepository customerRepository;
@Autowired
private ProductRepository productRepository;
@BeforeEach
void setUp() {
// Create test data
Customer customer1 = Customer.builder()
.firstName("John")
.lastName("Doe")
.email("[email protected]")
.status(CustomerStatus.ACTIVE)
.active(true)
.address(Address.builder()
.city("New York")
.state("NY")
.country("USA")
.build())
.loyaltyPoints(1500)
.build();
Customer customer2 = Customer.builder()
.firstName("Jane")
.lastName("Smith")
.email("[email protected]")
.status(CustomerStatus.VIP)
.active(true)
.address(Address.builder()
.city("Los Angeles")
.state("CA")
.country("USA")
.build())
.loyaltyPoints(2500)
.build();
Customer customer3 = Customer.builder()
.firstName("Bob")
.lastName("Johnson")
.email("[email protected]")
.status(CustomerStatus.INACTIVE)
.active(false)
.address(Address.builder()
.city("Chicago")
.state("IL")
.country("USA")
.build())
.loyaltyPoints(500)
.build();
entityManager.persist(customer1);
entityManager.persist(customer2);
entityManager.persist(customer3);
Supplier supplier = Supplier.builder()
.name("Tech Supplies Inc")
.contactEmail("[email protected]")
.active(true)
.build();
entityManager.persist(supplier);
Product product1 = Product.builder()
.name("Laptop")
.description("High-performance laptop")
.price(new BigDecimal("999.99"))
.category(ProductCategory.ELECTRONICS)
.stockQuantity(50)
.available(true)
.supplier(supplier)
.tags(Set.of("electronics", "computers", "portable"))
.build();
Product product2 = Product.builder()
.name("Smartphone")
.description("Latest smartphone model")
.price(new BigDecimal("699.99"))
.category(ProductCategory.ELECTRONICS)
.stockQuantity(100)
.available(true)
.supplier(supplier)
.tags(Set.of("electronics", "mobile", "communication"))
.build();
entityManager.persist(product1);
entityManager.persist(product2);
entityManager.flush();
}
@Test
void testBasicExactMatch() {
BasicExampleService service = new BasicExampleService(customerRepository, productRepository);
List<Customer> customers = service.findCustomersByExactMatch("John", "Doe");
assertEquals(1, customers.size());
assertEquals("[email protected]", customers.get(0).getEmail());
}
@Test
void testStatusQuery() {
BasicExampleService service = new BasicExampleService(customerRepository, productRepository);
List<Customer> activeCustomers = service.findCustomersByStatus(CustomerStatus.ACTIVE);
assertEquals(1, activeCustomers.size());
assertEquals("John", activeCustomers.get(0).getFirstName());
}
@Test
void testContainsMatching() {
AdvancedExampleService service = new AdvancedExampleService(customerRepository, productRepository, null);
List<Customer> customers = service.findCustomersByFirstNameContains("oh");
assertEquals(1, customers.size());
assertEquals("John", customers.get(0).getFirstName());
}
@Test
void testEmbeddedPropertyQuery() {
BasicExampleService service = new BasicExampleService(customerRepository, productRepository);
List<Customer> customers = service.findCustomersByCity("New York");
assertEquals(1, customers.size());
assertEquals("New York", customers.get(0).getAddress().getCity());
}
@Test
void testDynamicSearch() {
DynamicSearchService service = new DynamicSearchService(customerRepository, productRepository);
DynamicSearchService.CustomerSearchCriteria criteria =
new DynamicSearchService.CustomerSearchCriteria();
criteria.setActive(true);
criteria.setCity("New York");
List<Customer> customers = service.searchCustomers(criteria);
assertEquals(1, customers.size());
assertEquals("John", customers.get(0).getFirstName());
}
@Test
void testExistsQuery() {
BasicExampleService service = new BasicExampleService(customerRepository, productRepository);
boolean exists = service.existsCustomerWithEmail("[email protected]");
assertTrue(exists);
}
@Test
void testCountQuery() {
BasicExampleService service = new BasicExampleService(customerRepository, productRepository);
long activeCount = service.countCustomersByStatus(CustomerStatus.ACTIVE);
assertEquals(1, activeCount);
}
}
Best Practices and Limitations
1. QBE Best Practices Service
@Service
@Slf4j
public class QueryByExampleBestPractices {
/**
* When to use Query by Example:
* 1. Dynamic queries with multiple optional parameters
* 2. Simple equality and string matching
* 3. Rapid prototyping
* 4. When query structure is not known at compile time
*/
/**
* When to avoid Query by Example:
* 1. Complex joins and aggregations
* 2. Complex OR conditions
* 3. Subqueries
* 4. When you need to select specific columns (projections)
* 5. Performance-critical queries with complex conditions
*/
/**
* Best Practice 1: Always ignore ID and version fields
*/
public ExampleMatcher createSafeMatcher() {
return ExampleMatcher.matching()
.withIgnorePaths("id", "version"); // Always ignore these
}
/**
* Best Practice 2: Use specific property matchers instead of global matching
*/
public ExampleMatcher createSpecificMatcher() {
return ExampleMatcher.matching()
.withMatcher("firstName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
.withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.exact())
.withIgnorePaths("id", "createdAt");
}
/**
* Best Practice 3: Combine QBE with custom queries for complex scenarios
*/
@Repository
public interface CustomCustomerRepository {
List<Customer> findComplexQuery(String name, String city, LocalDateTime startDate, LocalDateTime endDate);
}
/**
* Best Practice 4: Use pagination for large result sets
*/
public Page<Customer> searchWithPagination(Customer probe, ExampleMatcher matcher, Pageable pageable) {
Example<Customer> example = Example.of(probe, matcher);
// Assuming customerRepository implements QueryByExampleExecutor
// return customerRepository.findAll(example, pageable);
return Page.empty(); // Implementation would use actual repository
}
/**
* Best Practice 5: Cache frequently used matchers
*/
private final Map<String, ExampleMatcher> matcherCache = new ConcurrentHashMap<>();
public ExampleMatcher getCachedMatcher(String key) {
return matcherCache.computeIfAbsent(key, k -> createMatcherForKey(k));
}
private ExampleMatcher createMatcherForKey(String key) {
// Create and return appropriate matcher based on key
return ExampleMatcher.matching()
.withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
.withIgnoreCase()
.withIgnorePaths("id", "createdAt");
}
}
Configuration and Properties
1. Application Configuration
# application.yml spring: datasource: url: jdbc:h2:mem:testdb driverClassName: org.h2.Driver username: sa password: jpa: database-platform: org.hibernate.dialect.H2Dialect hibernate: ddl-auto: create-drop show-sql: true properties: hibernate: format_sql: true use_sql_comments: true h2: console: enabled: true path: /h2-console logging: level: org.springframework.data.jpa.repository: DEBUG org.hibernate.SQL: DEBUG
Conclusion
Spring Data Query by Example provides a powerful, type-safe alternative to traditional query methods for dynamic search scenarios. Key advantages include:
Development Benefits:
- Reduced boilerplate code for dynamic queries
- Type safety through domain object usage
- Runtime flexibility for unknown query criteria
- Integration with existing repositories via
QueryByExampleExecutor
Performance Considerations:
- Efficient for simple to moderate complexity queries
- Pre-configured matchers reduce overhead
- Pagination support for large datasets
- Combination with custom queries for complex scenarios
Best Practices:
- Use for dynamic forms and search screens
- Ignore ID and version fields in matchers
- Apply specific matchers per property rather than global matching
- Combine with custom repository methods for complex business logic
- Use pagination for potentially large result sets
Ideal Use Cases:
- Admin dashboards with multiple filter options
- Search forms with optional criteria
- Dynamic reporting interfaces
- Rapid prototyping of data access layers
While QBE has limitations for complex queries involving joins, aggregations, or subqueries, it excels in scenarios where query structure is dynamic and known only at runtime. When combined with traditional Spring Data JPA features, QBE provides a comprehensive toolkit for building flexible, maintainable data access layers.