Version v1.12 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.

Query Debugging

Overview

You can see the SQL generated from a Query without having to connect to the database.

dryRun

Calling the dryRun function on a Query allows you to see the SQL and the arguments bound to the query:

val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }
val result: DryRunResult = query.dryRun()
println(result)

The output result of the above code is as follows (line breaks are inserted for readability):

DryRunResult(
  sql=select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?, 
  sqlWithArgs=select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = 1, 
  args=[Value(any=1, klass=class kotlin.Int)], 
  throwable=null, 
  description=This data was generated using DryRunDatabaseConfig. To get more correct information, specify the actual DatabaseConfig instance.
)

The meaning of the properties of the DryRunResult class is as follows:

sql
The SQL generated from the query. Bind variables are represented by ?. If an exception occurs, the exception message is expressed instead of SQL.
sqlWithArgs
The SQL with arguments generated from the query. Bind variables are replaced by string representations of the arguments. If an exception occurs, the exception message is expressed instead of SQL.
args
Argument value/type pairs.
throwable
Exception thrown during SQL generation. If no exception was thrown, null.
description
Description for the instance of DryRunResult.

Using Dialect

The dryRun function with no arguments returns a result without considering the Dialect of the destination database. If you want to get the result considering Dialect, pass a DatabaseConfig instance.

val database: JdbcDatabase = ...
val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }
val result: DryRunResult = query.dryRun(database.config)
println(result)

Or call the dryRun function of the Database instance.

val database: JdbcDatabase = ...
val query: Query<List<Address>> = QueryDsl.from(a).where { a.addressId eq 1 }
val result: DryRunResult = database.dryRun(query)
println(result)

Debugging during query construction

Combined with the also function, you can check query information during the construction process.

val query: Query<List<Address>> = QueryDsl.from(a)
    .also {
        println("1:" + it.dryRun().sql)
    }.where {
        a.addressId eq 1
    }.also {
        println("2:" + it.dryRun().sql)
    }.orderBy(a.addressId)
    .also {
        println("3:" + it.dryRun().sql)
    }

The results of executing the above code are as follows:

1:select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_
2:select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ?
3:select t0_.ADDRESS_ID, t0_.STREET, t0_.VERSION from ADDRESS as t0_ where t0_.ADDRESS_ID = ? order by t0_.ADDRESS_ID asc
Last modified March 11, 2022: Update the title and linkTitle (cf9ed4f)