Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › JPA › Queries

Dynamic queries and Querydsl

How do you add a search condition to where only when it is given?

On a member search screen, the name and the age may be filled in or left blank. Only the conditions that were filled in must go into where, so the shape of the query differs from request to request. A query built at run time from whichever conditions were supplied is called a dynamic query.

Partly checked against real execution · The 16 combinations of the string methods and the Criteria API were run on Hibernate 7.4.5 and H2 and checked against the results. The 4 Querydsl combinations follow the official documentation because the library is not available

Concepts in this lab

Dynamic query
A query built at run time from whichever conditions were supplied, so its shape changes with the input. It is needed when only the conditions that were entered must go into where, as on a search screen. If you build it by joining strings, mistakes happen: only where is left when there is no condition, an and ends up in the wrong place, or a space is missing. Such errors show up not at compile time but only when a request with that combination arrives. So you use the Criteria API or Querydsl, which assemble the conditions as objects. With any approach, values are bound as parameters instead of being put directly into the string.
JPQL
The JPA query language that you write against entities and their fields, not tables. Its syntax is similar to SQL, and when a query runs, the JPA provider translates it into SQL for the database in use. Its advantage is that it does not depend on the syntax of a specific database.
Criteria API
An API in the JPA standard that builds a query as Java objects instead of a string. You create condition objects (Predicate) with a CriteriaBuilder, collect them in a list, and pass them to where, and then where and and are created to match the number of conditions. With 0 conditions, no where clause is created. You can use it without a separate library, but the code is long and it is hard to read what query it is. If you write a property name as a string, a wrong name still compiles and throws an exception while running.
Querydsl
A library that lets you build queries in Java code. It generates Q classes (for example QMember) in advance by looking at your entity classes, and you write the properties and conditions as methods, as in m.name.eq("Alice"). A wrong property name or type is a compile error, so you can find the mistake before running. If you pass several conditions to where, they are combined with and and null conditions are skipped, so you can write a dynamic query briefly by making condition methods that return null when there is no value. You can also stack conditions one at a time in a BooleanBuilder. It is not a JPA standard, so you have to add the dependency and the build setting that generates the Q classes separately.
SQL injection
An attack in which user input is interpreted as part of a SQL statement, so a query that the attacker intended is executed. It happens when SQL is built by concatenating input values as strings. Parameterized queries with bind variables (PreparedStatement) separate the SQL structure from the values, so input is always treated only as a value, which prevents the attack.

Try these

Running with the default settings

  1. Search conditions: the name is null because it was not entered, and the age is 20. Only the conditions that are not null must go into the where of the query.
  2. Line 1: Builds the base statement. Expecting conditions to follow, it attaches where at the end in advance.
  3. Line 2: The name condition is null, so it is skipped. The string stays as it is.
  4. Line 3: There is a age condition, so the piece " and m.age >= :age" is attached at the end. This line assumes that the name condition is already attached before it, so it starts with and. But this time the name condition was not attached.
  5. em.createQuery(jpql, Member.class): Hibernate reads the string from the beginning and checks the syntax. and came right after where. The first condition that should be before and is missing. In the Hibernate grammar, a word such as and can also be used as a name, so it reads up to and as a name and moves on, and it stops at the next token, m.
  6. Exception: createQuery throws an IllegalArgumentException (the cause is a SyntaxException from Hibernate). The text "At 1:33" in the message means that it stopped at character 33 of line 1 (counted from 0). No SQL was sent to the DB, and the search request fails.

Result of this run

Code

List<Member> search(String name, Integer age) {
    String jpql = "select m from Member m where";
    if (name != null) jpql += " m.name = :name";
    if (age != null)  jpql += " and m.age >= :age";

    TypedQuery<Member> query = em.createQuery(jpql, Member.class);
    if (name != null) query.setParameter("name", name);
    if (age != null)  query.setParameter("age", age);
    return query.getResultList();
}

search(null, 20);   // the name is not entered, the age is 20 or older

Assumptions of this model

The 16 combinations of the three string-concatenation methods and the JPA Criteria API method were run on Hibernate 7.4.5 and an H2 database on the PC used to write this lab, using code with the same structure as the screen, and the built string, whether it succeeded, the kind of exception, the error position and the failing token, the where of the SQL sent to the DB, and the number of members found were checked against the screen. The 4 Querydsl combinations could not be checked by execution because the Querydsl library is not on the PC used to write this lab. That part is an explanation that follows the official Querydsl documentation: the API documentation of FilteredClause.where(Predicate...) ("Adds the given filter conditions. Skips null arguments"), the example of combining several filters with and in the Querying JPA chapter of the reference documentation, and the explanation of BooleanBuilder in the Complex predicates section. The where and the number of members written in the result cells of Querydsl are the values assuming that it works as the documentation says, and they are not execution results. The position of the syntax error and the failing token are based on the message produced by Hibernate 7.4.5, and they may come out differently on other versions or other JPA implementations. The explanation that, for "where and", Hibernate reads and as a name and moves on is an interpretation based on the execution result that the error occurred not at and but at the token after it. The SQL where on the screen has the table aliases removed and is shortened to be easy to read. It was also confirmed by execution that, with the Criteria API, no where clause is created when there are 0 conditions, and that if you give m.get a property name that does not exist, it compiles and an exception (a PathElementException in Hibernate 7.4.5) is thrown while running. In a real service there are many more conditions, and or, like, and sorting are mixed in, so the mistakes of the string method are easier to make than here.

Previous lab: Spring Data JPA · Next lab: Connection pool and OSIV

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these