Super QBE

Super QBE is a very powerful feature that uses DTO Language to generate parameter types for complex queries and automatically implement query logic.

Create File

In any project that can access entity types, create the src/main/dto directory, and create the Book.dto file in this directory.

For Java projects, if the current project is not the project that defines the entity type, you need to add the @EnableDtoGeneration annotation to any class in the current project.

Add the following code to the head of the file:

export com.yourcompany.yourproject.model.Book
    -> package com.yourcompany.yourproject.dto

The above steps, as well as how to compile DTO files, are discussed in detail in DTO Language #2. Create File, and will not be repeated here.

Define Specification Type

...export statement omitted... 

specification BookSpecification {

}

Compiled Code

After compilation, Java/Kotlin code like this will be generated:

Java

BookSpecification.java

package com.yourcompany.yourproject.dto;

import org.babyfish.jimmer.sql.ast.query.specification.JSpecification;
import org.babyfish.jimmer.sql.ast.query.specification.SpecificationArgs;
...other imports omitted...

public class BookSpecification implements JSpecification<Book, BookTable> {

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {}
}

Kotlin

BookSpecification.kt

package com.yourcompany.yourproject.dto;

import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecification
import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecificationArgs
...other imports omitted...

data class BookSpecification(
    // Now data class has no fields, will cause compile error  
) : KSpecification<Book> {

    override fun applyTo(args: KSpecificationArgs<Book>) {}
}

info

applyTo is a method specific to the compiled specification code. It dynamically adds where conditions to Jimmer queries according to the information of the current object.

This method does not require user invocation (invoked by internal Jimmer behaviors), and users do not need to care about its internal code implementation. Just knowing what this method does is enough here.

In subsequent discussions, we will gradually add properties to BookSpecification in the DTO file.

Accordingly, for the automatically generated BookSpecification class, on the one hand, properties will be added synchronously, on the other hand, the code in the applyTo method will also grow.

Usage

• Use in queries

  Java

  public List<Book> find(
      Specification<Book> specification ❶
  ) {
      
      BookTable table = Tables.BOOK_TABLE;
      
      return sqlClient
          .createQuery(table)
          .where(specification) ❷
          .select(table)
          .execute();
  }

  Kotlin

  fun find(
      specification: Specification<Book> ❶
  ): List<Book> =
      sqlClient.createQuery(Book::class) {
          where(specification) ❷
          select(table)
      }.execute()

  • ❶ The argument whose type is org.babyfish.jimmer.Specification<Book> type, which is used to generate dynamic SQL predicates.

  • ❷ No matter how simple or complex the specification format is, it can be used with just a simple where statement.

• Use in Spring Data Repository

  Jimmer integrates Spring Data, so Spring Data Repository can be defined, please refer to Spring Data Style to learn more.

  Jimmer's Spring Data Repository has two query styles, abstract methods and default methods. The code using specification in default methods is the same as above, so let's look at an example of using specification in abstract query methods:

  Java

  public interface BookRepository : JRepository<Book, Long> {
  
      public List<Book> find(
          Specification<Book> specification
      )
  }

  Kotlin

  interface BookRepository : KRepository<Book, Long> {
  
      fun find(
          specification: Specification<Book>
      ): List<Book>
  }

Attribute Mapping

Map Attributes

...export statement omitted...

specification BookSpecification {
    name
} 

This maps entity properties to DTOs. The generated code is:

Java

BookSpecification.java

public class BookSpecification implements JSpecification<Book, BookTable> {

    @Nullable
    private String name;

    ...getters and setters omitted...

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {
        ...omitted...
    }
}

Kotlin

BookSpecification.kt

package com.yourcompany.yourproject.dto;

import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecification
import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecificationArgs
...other imports omitted...

data class BookSpecification(
    val name: String? = null
) : KSpecification<Book> {

    override fun applyTo(args: KSpecificationArgs<Book>) {
        ...omitted... 
    }
}

Nullability

We find that in the generated code, the name field can be null, which is where the specialty of specification lies.

tip

Specifications are used as query parameters. To support dynamic queries, all properties are nullable by default unless explicitly specified as non-nullable using ! (see DTO Language #7. Nullability).

Usage

• Let the name field of BookSpecification be null

  Java

  BookSpecification specification = new BookSpecification();
  List<Book> books = bookRepository.find(specification);

  Kotlin

  val specification = BookSpecification()
  val books = bookRepository.find(specification) 

  Since specification.name is null, the generated SQL does not contain any where conditions.

  The generated SQL is:

  select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
  from BOOK tb_1_ 
  where tb_1_.NAME = ?
  info

  Such queries that do not specify relevant specification properties will inevitably lead to queries without any conditions.

  This example is only demonstrated once in this article.

• Make the name field of BookSpecification non-null

  Java

  BookSpecification specification = new BookSpecification();
  specification.setName("GraphQL in Action");
  List<Book> books = bookRepository.find(specification);

  Kotlin

  val specification = BookSpecification(
      name = "GraphQL in Action"
  )
  val books = bookRepository.find(specification)

  The generated SQL is:

  select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
  from BOOK tb_1_
  /* highlight-next-line */    
  where tb_1_.NAME = ? /* GraphQL in Action */

QBE Functions

Introduction to QBE Functions

In the code above, when specification.name is specified, the generated where condition is equality.

Equality is not necessarily what we need. QBE functions can be applied to mapped properties to change the operator. Take like as an example:

...export statement omitted...

specification BookSpecification {
    like(name)
}

The like function does not affect the properties of the generated BookSpecification class, but affects its applyTo method, whose internal implementation is the where condition addition logic that users do not need to care about.

Execute:

Java

BookSpecification specification = new BookSpecification();
specification.setName("GraphQL");
List<Book> books = bookRepository.find(specification);

Kotlin

val specification = BookSpecification(
    name = "GraphQL" 
)
val books = bookRepository.find(specification)

The generated SQL is:

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_
/* highlight-next-line */  
where tb_1_.NAME like ? /* %GraphQL% */

like Function Options

like is a special one among all QBE functions, supporting 3 options:

• i: Case insensitive

• ^: Exact match at the beginning (Jimmer does not automatically add % before the parameter value)

• $: Exact match at the end (Jimmer does not automatically add % after the parameter value)

If options are needed, / can be added after like, followed by the required options. Such as: like/i, like/^, like/$, like/i^, like/i$, and like/i^$.

caution

Although i, ^ and $ are optional, their order is fixed.

Let's try case-insensitive like by modifying the DTO code as follows:

...export statement omitted...   

specification BookSpecification {
    like/i(name)
}

Execute the code above again, the following SQL is executed:

• Databases that do not support ilike:

  select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID 
  from BOOK tb_1_
  /* highlight-next-line */
  where lower(tb_1_.NAME) like ? /* %graphql% */

• Databases that support ilike:

  select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
  from BOOK tb_1_ 
  /* highlight-next-line */  
  where tb_1_.NAME ilike ? /* %graphql% */

All QBE Functions

In fact, in addition to like, speciation supports a large number of QBE functions covering common SQL judgments, as shown in the table below:

QBE Function	Original Entity Attribute Type (or Requirement)	Generated DTO Class Attribute Type	Remarks
eq	Any non-associative property	Original type	Equal, equivalent to not using any QBE function
ne	Any non-associative property	Original type	Not equal
gt	Any non-associative property	Original type	Greater than
ge	Any non-associative property	Original type	Greater than or equal to
lt	Any non-associative property	Original type	Less than
le	Any non-associative property	Original type	Less than or equal to
like	String	String	Fuzzy match
notLike	String	String	Fuzzy mismatch
null	Any property	boolean	is null judgment if DTO property is true
notNull	Any property	boolean	is not null judgment if DTO property is true
valueIn	Any non-associative property	List<Original type>	in(...)
valueNotIn	Any non-associative property	List<Original type>	not in(...)
associatedIdEq	Any associative property	Type of associated entity's id property	Associated id = ?. Note that it is equivalent to id (inherent DTO language function)
associatedIdNe	Any associative property	Type of associated entity's id property	Associated id <> ?
associatedIdIn	Any associative property	List<Type of associated entity's id property>	Associated id in(...)
associatedIdNotIn	Any associative property	List<Type of associated entity's id property>	Associated id not in(...)

Comprehensive Example

Modify the DTO code:

...export statement omitted...

specification BookSpecification {
    like/i(name)
    ge(price) 
    le(price)
}

ge(price) will specify the default alias minPrice, le(price) will specify the default alias maxPrice, so the above code can also be written as:

...export statement omitted...  

specification BookSpecification {
    like/i(name)
    ge(price) as minPrice
    le(price) as maxPrice
} 

As you can see, some QBE functions have built-in default alias functionality.

Assuming the original property name is Prop, the default alias behaviors of all QBE functions are as follows:

QBE Function	Default alias
eq (or no QBE function specified)	prop
ne	Not supported, no alias specified will cause compile error
gt	minPropExclusive
ge	minProp
lt	maxPropExclusive
le	maxProp
like	prop
notLike	Not supported, no alias specified will cause compile error
valueIn	Not supported, no alias specified will cause compile error
valueNotIn	Not supported, no alias specified will cause compile error
associatedIdEq or id	If the association is a reference (non-collection), propId; otherwise, compile error
associatedIdNe	If the association is a reference (non-collection), excludedPropId; otherwise, compile error
associatedIdIn	If the association is a reference (non-collection), propIds; otherwise, compile error
associatedIdNotNull	If the association is a reference (non-collection), excludedPropIds; otherwise, compile error

The compiled code is:

Java

BookSpecification.java

public class BookSpecification implements JSpecification<Book, BookTable> {

    @Nullable
    private String name;

    @Nullable  
    private BigDecimal minPrice;

    @Nullable
    private BigDecimal maxPrice;

    ...getters and setters omitted...

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {
        ...omitted...
    }
}

Kotlin

BookSpecification.kt

package com.yourcompany.yourproject.dto;

import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecification
import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecificationArgs  
...other imports omitted...  

data class BookSpecification(
    val name: String? = null,
    val minPrice: BigDecimal? = null,
    val maxPrice: BigDecimal? = null
) : KSpecification<Book> {

    override fun applyTo(args: KSpecificationArgs<Book>) {
        ...omitted...
    }
}

Execute:

Java

BookSpecification specification = new BookSpecification();
specification.setName("GraphQL");
specification.setMinPrice(new BigDecimal(40));  
specification.setMaxPrice(new BigDecimal(40));
List<Book> books = bookRepository.find(specification);

Kotlin

val specification = BookSpecification(
    name = "GraphQL",
    minPrice = BigDecimal(40),
    maxPrice = BigDecimal(60)  
)
val books = bookRepository.find(specification)

The generated SQL is:

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_  
where
        lower(tb_1_.NAME) like ? /* %graphql% */  
    and 
        tb_1_.PRICE >= ? /* 40 */
    and
        tb_1_.PRICE <= ? /* 60 */
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc

Associated Objects

In previous examples, all filtering rules are for fields of the current table. Now let's filter on associated objects. Modify the DTO file as follows:

...export statement omitted...  

specification BookSpecification {
    like/i(name)
    ge(price)
    le(price)
    flat(store) { ❶
        like/i(name) as storeName
    }
    flat(authors) { ❷
        like/i(firstName) as authorName 
    }
}

We find that the code above uses the flat function. The flat function is discussed in detail in DTO Language #10.4-flat function, and will not be repeated here.

info

Since specification serves as a parameter for complex queries, it is very likely to be HTTP GET parameters. Using the flat function to eliminate associations and generate flattened DTO objects is more beneficial for developing HTTP GET APIs based on Spring MVC.

• ❶ Book.store is a reference association

• ❷ Book.authors is a collection association

The compiled code is:

Java

BookSpecification.java

public class BookSpecification implements JSpecification<Book, BookTable> {

    @Nullable
    private String name;

    @Nullable
    private BigDecimal minPrice;

    @Nullable
    private BigDecimal maxPrice;

    @Nullable
    private String storeName;

    @Nullable
    private String authorName;

    ...getters and setters omitted... 

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {
        ...omitted...
    }
} 

Kotlin

BookSpecification.kt

package com.yourcompany.yourproject.dto;

import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecification
import org.babyfish.jimmer.sql.kt.ast.query.specification.KSpecificationArgs
...other imports omitted...

data class BookSpecification(
    val name: String? = null,
    val minPrice: BigDecimal? = null,
    val maxPrice: BigDecimal? = null, 
    val storeName: String? = null,
    val authorName: String? = null
) : KSpecification<Book> {

    override fun applyTo(args: KSpecificationArgs<Book>) {
        ...omitted...
    }
} 

Execute:

Java

BookSpecification specification = new BookSpecification();
specification.setStoreName("MANNING");
specification.setAuthorName("a");
List<Book> books = bookRepository.find(specification);

Kotlin

val specification = BookSpecification(
    storeName = "MANNING",
    authorName = "a"  
)
val books = bookRepository.find(specification)

The generated SQL is:

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_ 
/* highlight-next-line */  
inner join BOOK_STORE tb_2_ /* ❶ */
    on tb_1_.STORE_ID = tb_2_.ID
where
        tb_2_.NAME ilike ? /* %manning% */  
    and
        /* highlight-next-line */
        exists( /* ❷ */
            select 
                1
            from AUTHOR tb_3_
            inner join BOOK_AUTHOR_MAPPING tb_4_ 
                on tb_3_.ID = tb_4_.AUTHOR_ID
            where
                    tb_1_.ID = tb_4_.BOOK_ID
                and  
                    tb_3_.FIRST_NAME ilike ? /* %a% */
                        
        )

• ❶ Book.store is a reference association that does not break pagination safety.

  So only dynamic JOIN is needed to apply the filtering condition represented by the storeName property of the DTO object.

• ❷ Book.authors is a collection association that breaks pagination safety.

  So subqueries must be used instead of dynamic JOIN to apply the filtering condition represented by the authorName property of the DTO object.

Logical OR

In previous examples, Jimmer generates multiple where conditions according to each property of the DTO object, and the relationship between these conditions is logical AND. But can logical OR be supported?

In addition, the entity type Author has the lastName property in addition to the firstName property. Mapping only firstName to authorName in the previous example does not seem reasonable. Is there a better way?

The above two issues are the same issue. To solve this problem, Super QBE supports logical OR, also known as multi-property mapping.

Modify the DTO code as follows:

...export statement omitted...

specification BookSpecification {
    like/i(name)
    ge(price)
    le(price)
    flat(store) {
        like/i(name) as storeName
    }
    flat(authors) {
        like/i(firstName, lastName) as authorName
    }
}

Here, the DTO property authorName maps two properties, firstName and lastName. This is called multi-property mapping.

• Multi-property mapping can only be used in specification and cannot be used in other types described in DTO Language.

• The types of multiple properties mapped by QBE functions must be completely consistent (but nullability is allowed to differ). For example, both firstName and lastName here are of type String.

• The DTO property of multi-property mapping must be aliased via as, otherwise compilation errors will occur.

• Not all QBE functions support multi-property mapping. QBE functions that support multi-property mapping are:

  • eq
  • ne
  • null
  • notNull
  • valueIn
  • associatedIdEq
  • associatedIdIn

  Allowing all QBE functions to support multi-property mapping blindly would lead to ambiguous understanding. So there is this restriction.

Repeating the query in the previous example generates the following SQL:

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID 
from BOOK tb_1_
inner join BOOK_STORE tb_2_
    on tb_1_.STORE_ID = tb_2_.ID
where
        tb_2_.NAME ilike ? /* %manning% */
    and
        exists(
            select
                1 
            from AUTHOR tb_3_
            inner join BOOK_AUTHOR_MAPPING tb_4_
                on tb_3_.ID = tb_4_.AUTHOR_ID
            where  
                    tb_1_.ID = tb_4_.BOOK_ID
                and
                    (
                        tb_3_.FIRST_NAME ilike ? /* %a% */  
                    /* highlight-next-line */  
                        or
                        tb_3_.LAST_NAME ilike ? /* %a% */
                    )
        )

It is not difficult to see that multi-property mapping is logical OR.