In this article you will get to know Querity, an open source Java query builder for SQL and NoSQL, and hopefully discover that it can be useful for you.
Of course you’ve already created some Java web application that expose some REST APIs to query a database, right?
Let’s say that your frontend is something like this, and you have to create a backend with REST APIs to provide the data to display:
Example of a frontend application with data displayed in a grid
With Java and Spring Web / Spring Data is easy: use Spring Initializr to create a draft application, add a JPA entity, a JpaRepository and you’re good to go.
Ok, let’s see some code!
A Maven configuration file (pom.xml)…
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.4.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>my.demo</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-data-jpa-demo</name>
<description>spring-data-jpa-demo</description>
<properties>
<java.version>21</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
and a Spring Boot Application class…
@SpringBootApplication
public class MyApplication {
public static void main(String[] args) {
SpringApplication.run(MyApplication.class, args);
}
}
This is what you’ll get for free using Spring Initializr.
By now you’ll have a ready-to-run empty application connected to an H2 in-memory database. Let’s fill it!
We add a JPA entity…
@Entity
@Table(name = "`order`")
@Getter @Setter
@EqualsAndHashCode(callSuper = true)
@ToString
public class Order extends AbstractPersistable<Long> {
private Long orderNumber;
private ZonedDateTime placementDate;
private Currency currency;
private BigDecimal totalPrice;
}
a JPA repository…
@Repository
public interface OrderRepository extends
JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {
}
a data structure for our response…
public record Result<T>(List<T> items, long totalCount) {
}
a service for our business logic…
@Service
public class OrderService {
private final OrderRepository repository;
private OrderService(OrderRepository repository) {
this.repository = repository;
}
public Result<Order> getOrders(int page, int pageSize) {
Page<Order> orders = repository.findAll(PageRequest.of(page - 1, pageSize));
return new Result<>(orders.toList(), orders.getTotalElements());
}
}
and a REST controller to handle HTTP requests to a GET endpoint…
@RestController
@RequestMapping("/api/orders")
public class OrderController {
private final OrderService service;
public OrderController(OrderService service) {
this.service = service;
}
@GetMapping
public Result<Order> getOrders(
@RequestParam int page, @RequestParam int pageSize) {
return service.getOrders(page, pageSize);
}
}
Directly exposing the JPA entities from the controller may not be a good idea, because it forces you to keep the transaction open for the duration of the request; usually you want to map the entities to DTO (data transfer object) classes in your service.
But let’s keep it simple for now.
Start the application with ./mvnw spring-boot:run and your application will be up and running in your local host.
Just a simple test with cURL…
curl http://localhost:8080/api/orders?page=1&pageSize=20
and you’ll get a JSON response like this:
{"items":[],"totalCount":0}
Fair enough… the database is empty. But you get the point.
We have a REST API that returns a paginated list of orders.
Spoiler: if you’ll get to the end of the article, you’ll find the link to the repo of the demo application, that has an in-memory database with 1000 records.
Welcome to the real world
Usually, in a real world scenario you need to filter the orders by some value of the entity fields.
Cool. You add some more query parameters to the API, and then your service will have to invoke the repository by passing the filtering info.
In Spring Data JPA this is done with a Specification. So your service method will become something like this…
public Result<Order> getOrders(Long orderNumber,
ZonedDateTime placementDateFrom, ZonedDateTime placementDateTo,
Currency currency,
BigDecimal totalPriceFrom, BigDecimal totalPriceTo,
int page, int pageSize) {
Specification<Order> specification = (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
if(orderNumber != null)
predicates.add(criteriaBuilder.equal(root.get("orderNumber"), orderNumber));
if(placementDateFrom != null)
predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("placementDate"), placementDateFrom));
if(placementDateTo != null)
predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("placementDate"), placementDateTo));
if(currency != null)
predicates.add(criteriaBuilder.equal(root.get("currency"), currency));
if(totalPriceFrom != null)
predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("totalPrice"), totalPriceFrom));
if(totalPriceTo != null)
predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("totalPrice"), totalPriceTo));
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
Page<Order> orders = repository.findAll(specification, PageRequest.of(page - 1, pageSize));
return new Result<>(orders.toList(), orders.getTotalElements());
}
Ok, but consider that this is a very simplified entity class with 4 fields only.
Now think to do this for all the fields of a “real world entity class”, then remember to update the filter every time you add a new field, also support filtering by nested fields (for example on order rows), and finally support the sorting of the results.
It’s starting to look like a less simple task.
Also the REST API documentation will be messy at that point… how many parameters can you add before it being unreadable?
Here comes Querity
Querity is a Java query builder that helps you querying the database with one simple query language.
It works with SQL databases (like the one in this example) and NoSQL databases such as MongoDB and Elasticsearch.
You don’t have to manage a REST API that gets more and more complex, you don’t have to continuously change your business logic when you add more fields… with Querity you will immediately get filtering, sorting and pagination with very few lines of code.
Just add the depedencies to your project…
<dependency>
<groupId>io.github.queritylib</groupId>
<artifactId>querity-spring-data-jpa</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.github.queritylib</groupId>
<artifactId>querity-parser</artifactId>
<version>3.1.0</version>
</dependency>
And your REST controller can be immediately simplified by having only one query string as request parameter:
@GetMapping
public Result<Order> getOrders(
@RequestParam(name = "q", required = false) String query) {
return service.getOrders(query);
}
Your service is also greatly simplified, because all the logic of creating the Specification is inside Querity:
//...
import io.github.queritylib.querity.api.Querity;
import io.github.queritylib.querity.api.Query;
import io.github.queritylib.querity.parser.QuerityParser;
//...
@Service
public class OrderService {
private final Querity querity;
public OrderService(Querity querity) {
this.querity = querity;
}
@Transactional(readOnly = true)
public Result<Order> getOrders(String query) {
Query q = query != null ?
QuerityParser.parseQuery(query) : // parse query language
Query.builder().build(); // create an empty query if null
List<Order> items = querity.findAll(Order.class, q);
Long totalCount = querity.count(Order.class, q.getFilter());
return new Result<>(items, totalCount);
}
}
You’ll never need to touch that method again. Why? Because the query string that you’re receiving from the REST API can do filtering, sorting and pagination on whatever field is inside your entity!
Let’s complicate the domain model to see the real advantages. We will add order rows and customer infos…
@Entity
@Table(name = "`order`")
@Getter @Setter
@EqualsAndHashCode(callSuper = true)
@ToString(exclude = "rows")
public class Order extends AbstractPersistable<Long> {
private Long orderNumber;
private ZonedDateTime placementDate;
private String ipAddress;
private Currency currency;
@JoinColumn(name = "billing_customer_id", referencedColumnName = "id")
@OneToOne(cascade = CascadeType.ALL)
private CustomerInfo billingCustomer;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "shipping_customer_id", referencedColumnName = "id")
private CustomerInfo shippingCustomer;
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
private List<OrderRow> rows = new ArrayList<>();
private BigDecimal totalPrice;
}
@Entity
@Getter @Setter
@EqualsAndHashCode(callSuper = true)
@ToString(exclude = "order")
public class OrderRow extends AbstractPersistable<Long> {
@ManyToOne
@JsonIgnore
private Order order;
private Long rowNumber;
private String sku;
private String description;
private Long quantity;
private BigDecimal unitPrice;
private BigDecimal totalPrice;
}
@Entity
@Getter @Setter
@EqualsAndHashCode(callSuper = true)
@ToString
public class CustomerInfo extends AbstractPersistable<Long> {
private String firstName;
private String lastName;
private String email;
@Embedded
private Address address;
@Embeddable
@Getter @Setter
@EqualsAndHashCode
@ToString
public static class Address {
private String streetAddress;
private String city;
private String postalCode;
private String country;
}
}
Our domain model now looks like this:
Without implementing any new line of code, we are now be able to run this kind of queries:
orderNumber=500
placementDate>="2024-07-01T00:00:00.000Z" page 1,10
shippingCustomer.address.country="Italy"
distinct and(rows.totalPrice>1000,currency="EUR") sort by placementDate desc page 1,20
As you can see, we’re even able to filter by nested fields , like the order rows price or the shipping country.
Querity has a lot of other features, and this article is not big enough to cover all of them in detail. Just to mention two of them, there’s a module to support queries deserialization from JSON in Spring Web MVC; and also support for DTOs , because you may not want to expose the database entities to the presentation layer.
We’ve used a SQL database and JPA for this tutorial, but you can switch to MongoDB or Elasticsearch just by changing the imported module. The query language will not change.
Even Spring is not mandatory: if you have a Jakarta EE application, you can import the querity-jpa module and leverage the plain Jakarta Persistence API.
A Java DSL is also available to create queries with a fluent syntax , so you can easily use Querity for your internal business logic.
Just like this:
Query query = Querity.query()
.distinct(true)
.filter(
and(
filterBy("rows.totalPrice", GREATER_THAN, 1000),
filterBy("currency", EQUALS, "EUR")
)
)
.sort(sortBy("placementDate", DESC))
.pagination(1, 20)
.build();
Conclusions
If you think that Querity could be useful for you, give it a⭐ on Github.
The full Querity documentation is available in its own website, with all the details about the features and the query language.
Last but not least, you can also see the code of the Querity demo application that inspired this post.
Have fun! 😎
Top comments (0)