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 well versed in SQL (or not at all), because predicates allow you to work with a database using the usual object representation of essential dependencies.



Predicates allow you to work with database elements as normal class fields. During assembly, gradle creates special dependency classes through which the necessary records are searched in the database.

If you already successfully work with QueryDSL and you have constructive comments and suggestions for the article, I will be glad to read them and, if necessary, supplement them with the article.

At the end of the article there is a link to the repository from which you can clone (or even fork) an example. I honestly warn you - I did not test it at the base, but if it rises for you (and it rises), it will definitely work. I even started to write tests, but I’m sure you’ll write tests no worse, and today I would like to analyze the topic of the article - predicates.

We will create entities with which we will work. Let it be User with the fields name and age and UserGroup, which will be inherited from AbstractEntity. Let's create a one-to-many relationship between them - in one group there can be many users. Predicates will be parsed only by 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 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 getUsers() {
        return users;
    }
    public void setUsers(List 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;
    }
}

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

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

• Find users by ID.

• Find all users in a particular group, as well as users in several 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 JpaRepository in the same way as in other cases when we work with the database through Spring JPA, but it is customized under QueryDSL:

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

Important note. If org.springframework.data.mapping.PropertyReferenceException: No property customize found for type User pops up when you try to raise Spring, you have not implemented 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 UserRepository interface from ExCustomRepository as follows, explicitly specifying User, QUser and Long:

@Repository
public interface UserRepository extends ExCustomRepository {
}

Now, finally, 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 getByAgeExcluding(Integer minAge, Integer maxAge) {
    }
    //ищем по возрасту, включая границы
    public List getByAgeIncluding(Integer minAge, Integer maxAge) {
    }
    //ищем по ID
    public User getById(Long id) {
    }
    //ищем по группам
    public List getByGroups(List groups) {
    }
    //ищем по имени
    public List get(String name) {
    }
}

In order for Spring to work with the object representation of table entities, it needs to create a relationship between them. By default, it places all the links in the build.generated.source.apt.project_structure folder , in order to create these links you need to clear the project and collect its classes. In gradle, this is achieved by sequentially executing clean and classes tasks (gradle -> Tasks -> build -> clean, classes). If the project structure appeared in build.generated.source.apt , and the classes with the Q prefix appear in it, then you did everything right.

Suppose that you did everything correctly and the above classes appeared. Let's, as we wanted, request from the repository 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 Q prefix. For the User class, this will be QUser. QUser is the entire repository. It has users: QUser.user , users have names: QUser.user.name , and also, in our case, age: QUser.user.age . Thus, to get age, we will work with QUser.user.age .

QueryDSL has 4 main methods that allow you to produce a specific result:

• findOne () - allows you to search for any one element. You must be sure that the required element in the database is only one, otherwise the exception is tratti.

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

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

• exists () - returns a Boolean value, whether such an element exists in the table or not.

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

To fulfill the conditions on the elements there are a myriad of 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 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 given range (between (minAge, maxAge)). And that’s all the request. By this request, we get a ready-made list of users. We don’t need to write SQL queries, but at the slightest change, rewrite again, otherwise everything will fail - QueryDSL provides the maximum flexibility that object communication of entities can provide, which means that such a request will be easy to refactor, if necessary, and never break.

It was a slight digression, and we still have 4 declared examples ahead. Let's move on to the next one. We found all the users in the range, but the selection will exclude the boundary values ​​themselves. To include boundary values ​​in the search criteria, we have to use other methods:

• goe - greater or equal (greater or equal)

• loe - less or equal

The request we get is this:

    public List 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 request, we need to use two conditions. To do this, we use the and () binding method, which filters by all conditions connected 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 - and all in one request. There can be a large number of such bundles, there are also a bunch 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 method findById (), but since we are parsing QueryDSL, we will compose the corresponding query:

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

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

Move on. In order to find all users in a group, in our case, we don’t even need to look for anything - just take the necessary UserGroup with its List field, and if we need to find all users that are in several groups? And this task can be accomplished with a very simple query through QueryDSL:

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

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

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

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

All non-Ivans, thanks to this query, will be found.

In this article, we've looked at 5 different QueryDSL queries. In a real project, the number of variations can be limited only by the number of entity fields and the relationships between them. QueryDSL is a very powerful and, at the same time, very understandable Java programmer framework. Having studied it, you will love working with databases in the same way as your own code :)

Promised example on github.