Get WHERE clause as HQL string from Criteria or: Dynamic WHERE clause with dynamic columns in select

Question

I want to build a filter search for my application. However, there are a few restrictions. I have to be able to dynamically add conditions as well as dynamically modify the columns and joins I want to fetch.

I have tried using a JPA Repository (from Spring Data) with Specifications, and it builds the WHERE clause as desired, however I have no control over what columns and what joins Hibernate issues to the DB.

I have also tried using the Hibernate Criteria API with Projections. The issued Query was as desired and I had full control over the selected columns and joins, as well as the WHERE clause, however, the result transformation proved to be too troublesome for parsing nested objects (I tried using a custom result transformer, but to no avail, for a single level object, the default behaviour was fine).

The "solution" I have for now is building the HQL string depending on the case. However, I have written code for building WHERE clause and I was wondering if there is a way to reuse it (e.g. get the HQL string from the Predicates of Spring Specifications, or from the Hibernate Restrictions).

Of course, if there is a better way to do all that, I would be thankful if you shared.

Answer 1

You need to use QueryDSL.

Step 1: Add com.mysema.querydsl:querydsl-apt, com.mysema.querydsl:querydsl-core and com.mysema.querydsl:querydsl-jpa dependencies to your project.

Step 2: Add @QueryEntity annotation to entity classes on which you wish to run dynamic queries. If the queries will included nested classes, add the annotation to the nested entity classes as well. An example below:

@Entity
@QueryEntity
@Table(name = "users")
public class User {
  @OneToMany
  private Address address;
}

@Entity
@QueryEntity
@Table(name = "address")
public class Address {
  @ManyToOne
  private Country country;
}

@Entity
@QueryEntity
@Table(name = "country")
public class Country { }

Step 3: Run the com.mysema.maven:apt-maven-plugin Maven plugin during the process phase as follows:

<build>
  <plugins>
    <plugin>
      <groupId>com.mysema.maven</groupId>
      <artifactId>apt-maven-plugin</artifactId>
      <version>1.1.1</version>
      <executions>
        <execution>
          <goals>
            <goal>process</goal>
          </goals>
          <configuration>
            <outputDirectory>target/generated-sources/java</outputDirectory>
            <processor>com.mysema.query.apt.QuerydslAnnotationProcessor</processor>
          </configuration>
        </execution>
      </executions>
    </plugin>
  </plugins>
</build>

Step 4: Change the Spring Data JPA repository interface to extend QueryDslPredicateExecutor as well.

interface UserRepository extends JpaRepository<User, Long>, QueryDslPredicateExecutor<User> {}

---

With this set up, the APT plugin will generate Q classes for every class annotated with QueryEntity. You can then generate dynamic queries using these classes. For example:

QUser user = QUser.user;

BooleanExpression query = and(eq(user.active, Boolean.TRUE), eq(user.address.country.name, "Belgium"));

Collection<User> activeUsersFromBelgium = userRepository.findAll(query);