1. Introduction
In this tutorial, we’ll take a look at how to integrate Kotlin with jOOQ. It’s written in Java, and we already have an entire article about jOOQ and Java integration. Nevertheless, jOOQ also offers special support for Kotlin, which we’ll explore.
2. Schema Creation
For demonstration purposes, let’s create three of tables to work with. We’ll create three tables — Aircraft, Flight, and Company:
CREATE TABLE company(
id BIGINT PRIMARY KEY,
name VARCHAR(200),
address VARCHAR(200)
);
CREATE TABLE aircraft(
id BIGINT PRIMARY KEY,
serial_number TEXT UNIQUE NOT NULL,
producer_name TEXT,
company_id BIGINT REFERENCES company(id)
);
CREATE TABLE flight(
id BIGINT PRIMARY KEY,
departure_at TIMESTAMP NOT NULL,
arrival_at TIMESTAMP NOT NULL,
departure_from TEXT NOT NULL,
departure_to TEXT NOT NULL,
aircraft_id BIGINT REFERENCES aircraft(id)
);
As we can notice, the company owns particular aircraft that perform the actual flights.
3. jOOQ Kotlin Code Generator
First, in order to use jOOQ utilities, we’ll generate POJOs for the tables we just created. Although it’s possible to use jOOQ without a code generator, it’s advisable to use automatically generated models. For generation purposes, we’ll use the Gradle jooq plugin:
plugins {
id 'nu.studer.jooq' version '8.2'
}
dependencies {
jooqGenerator 'org.postgresql:postgresql:42.5.1'
}
import org.jooq.meta.jaxb.Logging
jooq {
version = '3.18.4'
configurations {
main {
generateSchemaSourceOnCompilation = true
generationTool {
logging = Logging.WARN
jdbc {
driver = 'org.postgresql.Driver'
url = 'jdbc:postgresql://localhost:5999/local'
user = 'local'
password = 'local'
}
generator {
name = 'org.jooq.codegen.KotlinGenerator'
database {
name = 'org.jooq.meta.postgres.PostgresDatabase'
inputSchema = 'public'
}
generate {
deprecated = false
kotlinSetterJvmNameAnnotationsOnIsPrefix = true
pojosAsKotlinDataClasses = true
fluentSetters = true
}
target {
packageName = 'com.baeldung.jooq.codegen.models'
directory = 'build/generated-src/jooq/main'
}
strategy.name = 'org.jooq.codegen.DefaultGeneratorStrategy'
}
}
}
}
}
So here, the plugin we just included does some simple things. Initially, it scans the provided database schema (or/and catalog, if we deal with SQL Server, for instance). Then, the plugin generates POJOs for each table that it has found. We can actually specify filtering, so that it generates models only for some particular tables, but that is off-topic for this article.
Also, there are two things to note here. First, it’s important to include the appropriate JDBC driver in the classpath when executing the code generator plugin. In our case, since the plugin needs to connect to the PostgreSQL database, we include the postgresql driver. Secondly, we need to use KotlinGenerator for the plugin to generate Kotlin classes.
By using KotlinGenerator, we can specify additional configuration parameters that are available for this generator. For instance, we’re using the pojosAsKotlinDataClasses setting to ask the code generator plugin to generate models as Kotlin immutable data classes.
4. Inserting Data Using jOOQ
Now, during build time, we’ll have models generated. We can finally insert data using jOOQ:
val insert: Insert<CompanyRecord> = dslContext.insertInto(COMPANY)
.columns(COMPANY.ID, COMPANY.NAME, COMPANY.ADDRESS)
.values(1L, "SomeAirlines", "Solar system, Mars")
.execute()
Here, we generated an INSERT query and then executed it. We’re using a builder pattern to build the query to execute. This is convenient since jOOQ abstracts away the underlying SQL. This might be important since all RDBMS have slight deviations from ANSI SQL. Also, it provides us with additional type safety at compile time.
It’s also worth mentioning that jOOQ can be used just for SQL query generation. In other words, the generated SQL can also be executed by means other than jOOQ. For instance, for actual execution, we can use plain JdbcTemplate from the spring-jdbc framework:
val sql = dslContext.insertInto(COMPANY)
.columns(COMPANY.ID, COMPANY.NAME, COMPANY.ADDRESS)
.values(1L, "SomeAirlines", "Solar system, Mars")
.getSQL(ParamType.INLINED)
jdbcTemplate.execute(sql)
For simplicity, in the example above, we asked jOOQ to inline SQL parameters in the query. This allows us to just execute the query via spring JdbcTemplate and avoid having to provide additional arguments since they’re already inlined in the query.
So, this way, we can use jOOQ only for SQL generation, but not for query execution. Such an approach can also be useful if we want to migrate incrementally to jOOQ.
5. Reading Data Using jOOQ
Reading data is also quite straightforward. For instance, let’s find an aircraft by its serial number:
val query = dslContext.select(AIRCRAFT.field(AIRCRAFT.ID))
.from(AIRCRAFT)
.where(AIRCRAFT.SERIAL_NUMBER.eq("123456"))
.getSQL(ParamType.INLINED)
jdbcTemplate.query(query, myRowMapper)
The more complex queries will include multiple joins and conditions. For example, let’s find all flights performed by the company yesterday:
val query = dslContext.select(FLIGHT.ID)
.from(FLIGHT)
.innerJoin(AIRCRAFT).on(AIRCRAFT.ID.eq(FLIGHT.AIRCRAFT_ID))
.innerJoin(COMPANY).on(COMPANY.ID.eq(AIRCRAFT.COMPANY_ID))
.where(COMPANY.NAME.eq("SomeAirlines"))
.and(FLIGHT.DEPARTURE_AT.between(LocalDateTime.now().minusDays(1), LocalDateTime.now())).getSQL(ParamType.INLINED)
jdbcTemplate.query(query, myRowMapper)
Thus, it’s quite easy to construct SQL SELECT statements with jOOQ as well.
6. Conclusion
As we’ve seen, the usage of jOOQ in Kotlin is really similar to its usage in Java. The library function does not change, so we can do the same things in Kotlin, such as writing or reading data. Still, there’s a difference in the code generator that we’ll use when dealing with Kotlin. By using the Kotlin code generator, we can configure it to generate the code exactly as we need.
As always, the source code for the article is available over on GitHub.