Statements

The JPQL supports select, update, and delete statements. Kotlin JDSL provides a DSL to build them.

Select statement

Call select() in jpql() to build a select statement.

val query = jpql {
    select(
        path(Employee::employeeId),
    ).from(
        entity(Employee::class),
        join(Employee::departments),
    ).where(
        type(entity(Employee::class)).eq(FullTimeEmployee::class)
    ).groupBy(
        path(Employee::employeeId),
    ).having(
        count(Employee::employeeId).greaterThan(1L),
    ).orderBy(
        count(Employee::employeeId).desc(),
        path(Employee::employeeId).asc(),
    )
}

Select clause

Use select() and pass Expression to build a select clause in the select statement. If you pass only one Expression to select(), it will infer a return type from Expression. However, if you pass more than one Expression, you need to specify the type as it cannot infer the type.

// It can infer the result type.
select(path(Author::authorId))

// It cannot infer the result type.
select(path(Author::authorId), path(Author::name))

// This allows it to know the result type.
select<CustomEntity>(path(Author::authorId), path(Author::name))

DTO projection

Specify a DTO class and pass parameters of the constructor to selectNew() to build a DTO projection.

data class Row(
    val departmentId: Long,
    val count: Long,
)

selectNew<Row>(
    path(EmployeeDepartment::departmentId),
    count(Employee::employeeId),
)

From clause

Use from() and pass Entity and Join to specify the entities for selection when building a from clause in the select statement.

from(
    entity(Author::class),
    join(BookAuthor::class).on(path(Author::authorId).equal(path(BookAuthor::authorId))),
)

Join

Use join() and fetchJoin() to combine the entities for selection. There are two types of join(): Join and Association Join This is distinguished by whether join() is used between two unrelated entities or between two related entities.

@Entity
// ...
class Book(
    // ...

    @OneToMany(mappedBy = "book", cascade = [CascadeType.ALL], orphanRemoval = true)
    val authors: MutableSet<BookAuthor>,
)

@Entity
// ...
class BookAuthor(
    @Id
    @Column(name = "author_id")
    val authorId: Long,
) {
    @Id
    @ManyToOne
    @JoinColumn(name = "isbn")
    lateinit var book: Book
}

@Entity
// ...
class Author(
    @Id
    @Column(name = "author_id")
    val authorId: Long,

    // ...
)

from(
    entity(Book::class),
    join(Book::authors), // Association Join
    join(Author::class).on(path(BookAuthor::authorId).eq(path(Author::authorId))), // Join
)

Call as() after join() to alias the entity being joined. This can be useful if you use multiple entities with the same type in a from clause.

from(
    entity(Book::class),
    join(Book::authors).`as`(entity(BookAuthor::class, "author")),
)

Where clause

Use where() and pass Predicate to restrict the data when building a where clause in the select statement. You can use whereAnd() as a short form of where() and and(). You can also use whereOr() as a short form of where() and or().

where(
    path(Book::publishDate).between(
        OffsetDateTime.parse("2023-01-01T00:00:00+09:00"),
        OffsetDateTime.parse("2023-06-30T23:59:59+09:00"),
    ),
)

Group by clause

Use groupBy() and pass Expression to create unique groups of data when building a group by clause in the select statement.

groupBy(
    path(EmployeeDepartment::departmentId),
)

Having clause

Use having() and pass Expression to further restrict the data when building a having clause in the select statement. You can use havingAnd() as a short form of having() and and(). You can also use havingOr() as a short form of having() and or().

having(
    count(Employee::employeeId).greaterThan(1L),
)

Set Operations (`UNION`, `UNION ALL`, `EXCEPT`, `EXCEPT ALL`, `INTERSECT`, `INTERSECT ALL`)

JPQL allows combining the results of two or more SELECT queries using set operators. Kotlin JDSL supports UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, and INTERSECT ALL operations, which are standard features in JPQL and are also part of the JPA 3.2 specification.

UNION: Combines the result sets of two queries and removes duplicate rows.
UNION ALL: Combines the result sets of two queries and includes all duplicate rows.
EXCEPT: Returns rows from the first query that are not present in the second query, removing duplicates.
EXCEPT ALL: Returns rows from the first query that are not present in the second query, including all duplicates.
INTERSECT: Returns only the rows that are present in both result sets, removing duplicates.
INTERSECT ALL: Returns only the rows that are present in both result sets, including all duplicates.

The SELECT statements involved in a UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, or INTERSECT ALL operation must have the same number of columns in their select lists, and the data types of corresponding columns must be compatible.

Using with Chained Selects:

You can chain union(), unionAll(), except(), exceptAll(), intersect(), or intersectAll() after a select query structure (e.g., after select, from, where, groupBy, or having clauses). The orderBy() clause, if used, applies to the final result of the set operation.

// Example with UNION
val unionQuery = jpql {
    select(
        path(Book::isbn)
    ).from(
        entity(Book::class)
    ).where(
        path(Book::price)(BookPrice::value).lessThan(BigDecimal.valueOf(20))
    ).union( // The right-hand side query is also a select structure
        select(
            path(Book::isbn)
        ).from(
            entity(Book::class)
        ).where(
            path(Book::salePrice)(BookPrice::value).lessThan(BigDecimal.valueOf(15))
        )
    ).orderBy(
        path(Book::isbn).asc()
    )
}

// Example with UNION ALL
val unionAllQuery = jpql {
    select(
        path(Author::name)
    ).from(
        entity(Author::class)
    ).where(
        path(Author::name).like("%Rowling%")
    ).unionAll( // The right-hand side query is also a select structure
        select(
            path(Author::name)
        ).from(
            entity(Author::class)
        ).where(
            path(Author::name).like("%Tolkien%")
        )
    ).orderBy(
        path(Author::name).desc()
    )
}

// Example with EXCEPT
val exceptQuery = jpql {
    select(
        path(Book::isbn)
    ).from(
        entity(Book::class)
    ).where(
        path(Book::price)(BookPrice::value).lessThan(BigDecimal.valueOf(30))
    ).except( // The right-hand side query is also a select structure
        select(
            path(Book::isbn)
        ).from(
            entity(Book::class)
        ).where(
            path(Book::salePrice)(BookPrice::value).lessThan(BigDecimal.valueOf(20))
        )
    ).orderBy(
        path(Book::isbn).asc()
    )
}

// Example with EXCEPT ALL
val exceptAllQuery = jpql {
    select(
        path(Author::name)
    ).from(
        entity(Author::class)
    ).where(
        path(Author::name).like("%Fantasy%")
    ).exceptAll( // The right-hand side query is also a select structure
        select(
            path(Author::name)
        ).from(
            entity(Author::class)
        ).where(
            path(Author::name).like("%Mystery%")
        )
    ).orderBy(
        path(Author::name).desc()
    )
}

// Example with INTERSECT
val intersectQuery = jpql {
    select(
        path(Book::isbn)
    ).from(
        entity(Book::class)
    ).where(
        path(Book::price)(BookPrice::value).lessThan(BigDecimal.valueOf(20))
    ).intersect( // The right-hand side query is also a select structure
        select(
            path(Book::isbn)
        ).from(
            entity(Book::class)
        ).where(
            path(Book::salePrice)(BookPrice::value).lessThan(BigDecimal.valueOf(15))
        )
    ).orderBy(
        path(Book::isbn).asc()
    )
}

// Example with INTERSECT ALL
val intersectAllQuery = jpql {
    select(
        path(Author::name)
    ).from(
        entity(Author::class)
    ).where(
        path(Author::name).like("%Fantasy%")
    ).intersectAll( // The right-hand side query is also a select structure
        select(
            path(Author::name)
        ).from(
            entity(Author::class)
        ).where(
            path(Author::name).like("%Sci-Fi%")
        )
    ).orderBy(
        path(Author::name).desc()
    )
}

Using as Top-Level Operations:

You can also use union(), unionAll(), except(), exceptAll(), intersect(), and intersectAll() as top-level operations within a jpql block, combining two JpqlQueryable<SelectQuery<T>> instances.

val query1 = jpql {
    select(
        path(Book::isbn)
    ).from(
        entity(Book::class)
    ).where(
        path(Book::price)(BookPrice::value).eq(BigDecimal.valueOf(10))
    )
}

val query2 = jpql {
    select(
        path(Book::isbn)
    ).from(
        entity(Book::class)
    ).where(
        path(Book::salePrice)(BookPrice::value).eq(BigDecimal.valueOf(10))
    )
}

// Top-level UNION ALL
val topLevelUnionAllQuery = jpql {
    unionAll(query1, query2)
        .orderBy(path(Book::isbn).asc())
}

// Top-level EXCEPT ALL
val topLevelExceptAllQuery = jpql {
    exceptAll(query1, query2)
        .orderBy(path(Book::isbn).asc())
}

// Top-level INTERSECT
val topLevelIntersectQuery = jpql {
    intersect(query1, query2)
        .orderBy(path(Book::isbn).asc())
}

Important Note on ORDER BY:

The ORDER BY clause is applied to the final result set of the set operation (UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, or INTERSECT ALL). It cannot be applied to the individual SELECT queries that are part of the set operation in a way that affects the set operation itself (though subqueries might have their own ORDER BY for other purposes like limiting results before the set operation, this is generally not how ORDER BY interacts with set operations in JPQL for final sorting). The sorting criteria in the ORDER BY clause usually refer to columns by their alias from the SELECT list of the first query, or by their position.

Database Compatibility Note:

While these set operations are part of the JPA 3.2 specification, not all databases support all operations. For example:

H2 database (versions 1.4.192 - 2.3.232) supports UNION, UNION ALL, INTERSECT, and EXCEPT but does not support EXCEPT ALL or INTERSECT ALL.
PostgreSQL, Oracle, and SQL Server support all six operations (UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL).
MySQL supports UNION and UNION ALL but has limitations on other operators:
- It does not support EXCEPT. This can be emulated using NOT EXISTS or LEFT JOIN.
- It does not support INTERSECT. This can be emulated using INNER JOIN or IN.
- It does not support EXCEPT ALL or INTERSECT ALL, and there are no simple alternative queries.

When using these operations, ensure your target database supports them, or provide alternative query strategies for unsupported databases.

Order by clause

Use orderBy() and pass Sort to return data in the declared order when building an order by clause in the select statement.

orderBy(
    path(Book::isbn).asc(),
)

Update statement

Call update() in jpql() to build an update statement.

val query = jpql {
    update(
        entity(Book::class)
    ).set(
        path(Book::price)(BookPrice::value),
        BigDecimal.valueOf(100)
    ).set(
        path(Book::salePrice)(BookPrice::value),
        BigDecimal.valueOf(80)
    ).where(
        path(Book::isbn).eq(Isbn("01"))
    )
}

Update clause

Use update() and pass Entity to specify the entity to modify when building an update clause in the update statement.

update(
    entity(Employee::class),
)

Set clause

Use set() and pass Expression to assign values when building a set clause in the update statement. You can use multiple assignments by adding set() after set().

set(
    path(Book::price)(BookPrice::value),
    BigDecimal.valueOf(100)
).set(
    path(Book::salePrice)(BookPrice::value),
    BigDecimal.valueOf(80)
)

Where clause

Use where() and pass Predicate to restrict the data when building a where clause in the update statement. You can use whereAnd() as a short form of where() and and(). You can also use whereOr() as a short form of where() and or().

where(
    path(Book::publishDate).between(
        OffsetDateTime.parse("2023-01-01T00:00:00+09:00"),
        OffsetDateTime.parse("2023-06-30T23:59:59+09:00"),
    ),
)

Delete statement

Call deleteFrom() in jpql() to build a delete statement.

val query = jpql {
    deleteFrom(
        entity(Book::class),
    ).where(
        path(Book::publishDate).ge(OffsetDateTime.parse("2023-06-01T00:00:00+09:00")),
    )
}

Delete from clause

Use deleteFrom() and pass Entity to specify the entity to delete when building a delete from clause in the delete statement.

deleteFrom(
    entity(Book::class),
)

Where clause

Use where() and pass Predicate to restrict the data when building a where clause in the delete statement. You can use whereAnd() as a short form of where() and and(). You can also use whereOr() as a short form of where() and or().

where(
    path(Book::publishDate).between(
        OffsetDateTime.parse("2023-01-01T00:00:00+09:00"),
        OffsetDateTime.parse("2023-06-30T23:59:59+09:00"),
    ),
)

PreviousJPQL with Kotlin JDSL NextEntities

Last updated 2 months ago

Select statement

Select clause

DTO projection

From clause

Join

Where clause

Group by clause

Having clause

Set Operations (UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL)

Order by clause

Update statement

Update clause

Set clause

Where clause

Delete statement

Delete from clause

Where clause

Set Operations (`UNION`, `UNION ALL`, `EXCEPT`, `EXCEPT ALL`, `INTERSECT`, `INTERSECT ALL`)