QueryDSL: Predicates

QueryDSL Predicate is a powerful and extremely flexible tool for working with databases and just a gift for Java developers who are not very good at SQL (or do not understand at all), because predicates allow you to work with databases using the usual object representation of entity dependencies.



Predicates allow you to work with database items as with ordinary class fields. When building gradle, it creates special dependency classes, through which the search for the necessary records in the database takes place.
')
If you are already successfully working with QueryDSL and you have constructive comments and suggestions on the article - I will be glad to read them and, if necessary, supplement the article with them.

At the end of the article there is a link to the repository, from which you can clone (or even fork) an example. Honestly I warn you - I have not tested it on the base, but if you get it up (and it goes up), it will definitely work. I even started writing tests, but I am sure you will write tests no worse, but today I would like to make out the topic of the article - predicates.

We will create entities with which we will work. Let it be a User with the name and age fields and a UserGroup that will inherit from AbstractEntity. Let's create a one-to-many connection between them - there can be many users in the same group. Predicates will be parsed only on User.

AbstractEntity:

package entity; import javax.persistence.*; @MappedSuperclass public class AbstractEntity { private Long id; @Id @Column(name = "id") @SequenceGenerator(name = "general_seq", sequenceName = "generalSequenceGenerator") @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "general_seq") public Long getId() { return id; } public void setId(Long id) { this.id = id; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; AbstractEntity that = (AbstractEntity) o; return id != null ? id.equals(that.id) : that.id == null; } @Override public int hashCode() { return id != null ? id.hashCode() : 0; } } 

User:

 package entity; import javax.persistence.*; @Entity @Table(name = "users") public class User extends AbstractEntity { private String name; private Integer age; private UserGroup group; @Column(name = "name") public String getName() { return name; } public void setName(String name) { this.name = name; } @Column(name = "age") public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "category") public UserGroup getGroup() { return group; } public void setGroup(UserGroup group) { this.group = group; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; if (!super.equals(o)) return false; User user = (User) o; if (name != null ? !name.equals(user.name) : user.name != null) return false; if (age != null ? !age.equals(user.age) : user.age != null) return false; return group != null ? group.equals(user.group) : user.group == null; } @Override public int hashCode() { int result = super.hashCode(); result = 31 * result + (name != null ? name.hashCode() : 0); result = 31 * result + (age != null ? age.hashCode() : 0); result = 31 * result + (group != null ? group.hashCode() : 0); return result; } } 

UserGroup:

 package entity; import javax.persistence.*; import java.util.List; @Entity @Table(name = "user_groups") public class UserGroup extends AbstractEntity { private String name; private List<User> users; @Column(name = "name") public String getName() { return name; } public void setName(String name) { this.name = name; } @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "group") public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; if (!super.equals(o)) return false; UserGroup userGroup = (UserGroup) o; if (name != null ? !name.equals(userGroup.name) : userGroup.name != null) return false; return users != null ? users.equals(userGroup.users) : userGroup.users == null; } @Override public int hashCode() { int result = super.hashCode(); result = 31 * result + (name != null ? name.hashCode() : 0); result = 31 * result + (users != null ? users.hashCode() : 0); return result; } } 

Great, we have entities, now let's think about what we might need in working with our repository. We can:

• Find all users within the required age limits, both including limits and excluding.

• Find users by ID.

• Find all users in a specific group, as well as users in multiple groups.

• Find users by name.

... search, sort, filter users as you like, depending on our needs and the number of fields.

To work with the database through QueryDSL, we need a separate, customized, repository. It extends from the JpaRepository as well as in other cases when we work with the database through Spring JPA, but it is customized for QueryDSL:

 @NoRepositoryBean public interface ExCustomRepository<T extends AbstractEntity, P extends EntityPathBase<T>, ID extends Serializable> extends JpaRepository<T, ID>, QuerydslPredicateExecutor<T>, QuerydslBinderCustomizer<P> { @Override default void customize(QuerydslBindings bindings, P root) { } } 

Important note. If you try to raise Spring with org.springframework.data.mapping.PropertyReferenceException: No property customize found for type User pops up, then you have not implemented (Implement Methods) the customize () method. Just redefine it, it will be enough (if you do not want to customize it too).

So, to work with the repository, it will be enough for us to inherit our ExCustomRepository UserRepository interface as follows, explicitly specifying User, QUser and Long:

 @Repository public interface UserRepository extends ExCustomRepository<User, QUser, Long> { } 

Now, finally, we will create a service class in which we will access the database and search for users. While it is empty.

 @Service public class UserService { @Autowired UserRepository repository; //  ,   public List<User> getByAgeExcluding(Integer minAge, Integer maxAge) { } //  ,   public List<User> getByAgeIncluding(Integer minAge, Integer maxAge) { } //  ID public User getById(Long id) { } //   public List<User> getByGroups(List<UserGroup> groups) { } //   public List<User> get(String name) { } } 

In order for Spring to work with the object representation of table entities, it needs to create a connection between them. He places all the links to the folder in the build.generated.source.apt.project_structure , by default, in order to create these links you need to clear the project and build its classes. In gradle, this is achieved by sequential execution of clean and classes tasks (gradle -> Tasks -> build -> clean, classes). If a project structure appeared in build.generated.source.apt , and in it there are classes with the prefix Q, it means that you did everything correctly.

Suppose you did everything correctly and the classes described above appeared. Let's, as we wanted, request from the repository of all users, for example, from 18 to 60 years. As I already mentioned, the relationship between table entities in QueryDSL is formed in the corresponding class with the prefix Q. For the class User, this will be QUser. QUser is the entire repository. There are users in it: QUser.user , users have names: QUser.user.name , and also, in our case, age: QUser.user.age . So in order to get age, we will work with QUser.user.age .

There are 4 main methods in QueryDSL that allow to produce a certain result:

• findOne () - allows you to search for a single item. You must be sure that the required element in the database is only one, otherwise dratuti is an exception.

• findAll () and several of its overloads - returns an iterable list of found records that meet the conditions. Usually this list then has to be wrapped in a List (we will have it).

• count () - returns the number of items found.

• exists () - returns a boolean value, there is such an element in the table or not.

You can find these methods and study them in more detail in org.springframework.data.querydsl.QuerydslPredicateExecutor. As we can see from the package names, Spring provides them.

To fulfill the conditions for the same elements, there are countless methods stored in com.querydsl.core.types.dsl.SimpleExpression. We will study them in more detail.

So, in the first method, we need to get all users in a given age range.

In HQL, this query would look like this:

 SELECT u FROM User u WHERE u.age BETWEEN :minAge AND :maxAge 

In the implementation of QueryDSL, this method will look like this:

  public List<User> getByAgeExcluding(Integer minAge, Integer maxAge) { return Lists.newArrayList(repository.findAll(QUser.user.age.between(minAge, maxAge))); } 

We are looking for all users (findAll ()) whose age ( QUser.user.age ) is in the specified range (between (minAge, maxAge)). And that’s all the request. At this request, we get a ready list of users. We do not need to write SQL queries, and rewrite again with the slightest change, otherwise everything will fall down - QueryDSL provides the maximum flexibility that object entities can provide, and therefore such a query will, if necessary, be easy to refactor and will never break.

It was a small lyrical digression, and we still have 4 more stated examples ahead. Let's go to the next. We found all users in the range, but the selection will exclude the values ​​of the borders themselves. To include the boundary values ​​in the search criteria, we will have to use other methods:

• goe - greater or equal

• loe - less or equal (less or equal)

Our request will be:

  public List<User> getByAgeIncluding(Integer minAge, Integer maxAge) { return Lists.newArrayList(repository.findAll(QUser.user.age.goe(minAge).and(QUser.user.age.loe(maxAge)))); } 

In order to fulfill this query, we need to use two conditions. To do this, we use the and () method-bundle, which filters for all conditions related in this way. The framework will first select all objects that are greater than or equal to minAge, and then all objects that are less than or equal to maxAge — all in one request. There may be a large number of such bundles, there is also a bundle of or () and others, you can find them in com.querydsl.core.types.dsl.BooleanExpression .

Now let's find the user by his ID. Of course, this is best done using the appropriate Spring JPA findById () method, but since we are parsing QueryDSL, we will write the corresponding query:

  public User getById(Long id) { return repository.findOne(QUser.user.id.eq(id)).orElse(new User()); } 

We use the operator eq (), which searches for fields equal to the condition (eq = equals).

Go ahead. In order to find all the users in a group, we, in our case, do not even need to look for anything - it is enough to simply take the desired UserGroup with its List field, and if we need to find all users in several groups? And this task can be accomplished with a very simple query through QueryDSL:

  public List<User> getByGroups(List<UserGroup> groups) { return Lists.newArrayList(repository.findAll(QUser.user.group.in(groups))); } 

In this case, the in () operator allows you to specify not one value but several for the search condition.

And finally, we will find all users whose name is different from the one requested (for example, not all Ivanov). The request will look like this:

  public List<User> get(String name) { return Lists.newArrayList(repository.findAll(QUser.user.name.ne(name))); } 

All not Ivana, thanks to this request, will be found.

In this article, we sorted out 5 different QueryDSL queries. In a real project, the number of variations can be limited only by the number of entity fields and the connections between them. QueryDSL is a very powerful and, at the same time, very understandable framework for a Java programmer. Having studied it, you will love working with databases just like your own code :)

Promised example on github.