Java Database Search Made Easy

Search functionality is so ubiquitous in any web app that at some point you start asking yourself if it can be done any easier. Introducing CrOp, a lightweight, zero dependency, and type-safe wrapper around Java Persistence Criteria API, which simplifies building queries, particularly useful within REST SQL (RSQL) context.

Let’s dive into it’s core principles.

Zero Dependency

We all know the pain of upgrading dependencies. What I find the most troublesome is going through compatibility matrices (if you are lucky enough to find them). For example, you can’t use Spring 3 with Hibernate 5. And if you built something with Hibernate 5, you’ll have to rewrite it before upgrading Spring.

That’s why CrOp relies on the JPA specification and is agnostic to the underlying implementation, whether it’s Hibernate, EclipseLink, Apache OpenJPA, or any other.

Type Safe

CrOp guarantees compile type-safety without relying on reflection. It has specialized wrapper classes for most common data types and allows mapping only JPA’s Metamodel. Meaning for example that you cannot use a String to query a Long entity field. Your code won’t simply compile if your try to do such thing.

Optimized

Database optimization can refer to a lot of things, but what CrOp can do (for now) is to promise that it won’t add unnecessary joins. For example, you have two entities Pet and PetType with a one-to-one relationship between them, and sometimes you wish to include pet type in search. Guess what, only when the lookup parameter is present the join with PetType will happen.

@GetMapping("/pets")
List<PetRecord> findAll(PetCriteriaOperator searchCriteria, CriteriaOperatorOrder order, CriteriaOperatorPage page) {
    return cropService.create(Pet.class, searchCriteria, order, page)
        .match(Pet_.name, PetCriteriaOperator::getNickname)
        .join(Pet_.petType)
            .match(PetType_.code, PetCriteriaOperator::getType)
        .endJoin()
        .getResultList()
        .stream()
        .map(petMapper::map)
        .toList();
}

Example

A GET request like http://localhost:64503/pets?birthdate.gte=2010-01-11&nickname.like=Ba, is transformed by CrOp (via an ORM) into the following SQL:

select
    p1_0.id,
    p1_0.birthdate,
    p1_0.name,
    p1_0.pet_type_id 
from
    pet p1_0 
where
    p1_0.name like ? escape '' 
    and p1_0.birthdate>=?

There are a couple of easy steps to achieve the above:

• Add the dependency

<dependency>
    <groupId>com.apulbere</groupId>
    <artifactId>crop</artifactId>
    <version>0.1.0</version>
</dependency>

• Define a DTO that contains all fields necessary for filtering. For example, if you want to do filtering on String type then you choose StringCriteriaOperator from com.apulbere.crop.operator package.

@Getter
@Setter
public class PetSearchCriteria {
    private StringCriteriaOperator nickname;
    private LocalDateCriteriaOperator birthdate;
}

• Create an instance of the service that will parse the query. It requires EntityManager in the constructor.

  @Bean
  CriteriaOperatorService cropService(EntityManager entityManager) {
    return new CriteriaOperatorService(entityManager);
  }
  

• Finally, invoke the service’s create method with the root entity and filter object. The result is a builder that lets you match the entity’s meta-model with each field from the DTO. When done, execute the query.

  @GetMapping("/pets")
  List<PetRecord> search(PetSearchCriteria petSearchCriteria) {
    return cropService.create(Pet.class, petSearchCriteria)
            .match(PetSearchCriteria::getNickname, Pet_.name)
            .match(PetSearchCriteria::getBirthdate, Pet_.birthdate)
            .getResultList()
            .stream()
            .map(petMapper::map)
            .toList();
  }
  

For full example and to see all the capabilities of the library checkout pet-shop demo repository.