Haom.in!

Hi!

Hello, jOOQ!

Study
1291 words
6 min read
#Java/Kotlin #Spring Boot #jOOQ #SQL
I've been using MyBatis for a long time to write dynamic SQL for Spring Boot application. However, each time I need to write many mappers and '.xml's. Moreover, when I want to integer cache with mapper, I need to create another repository, implement logic or add the '@Cachable' in it, and ensure I am using repo, not mapper. ChatGPT recommend jOOQ for me, say it is Type-Safe and SQL-first. 🤔, let's give it a try.
Hello-Jooq

jOOQ

“The easiest way to write SQL in Java”

There are serval points ChatGPT recommened me to use jOOQ:

  • it is Type-Safe, not only is runtime-only (like what mybatis does), but also at compile time
  • provide strong SQL control, but not in xml, we can write in java/kotlin
  • fit with kotlin (I like kotlin ヽ(●´∀`●)ﾉ)
  • and it is best for Secure, modern backends

As I am trying to develop some project to fill up my resume, and want to learn something new (so I can put another framework on my resume), I decide to give it a change.

With Spring Boot

I will directly start with Spring Boot, since it is actually easy to set up, only need to config the database, and there is a official document for easy set up. In this post, I will focus on Gradle code generation and integration with Spring Boot.

Preparation

Of course, we need to prepare a database first. I will use the PostgreSQL 17 in docker, and please notice that, the open source version of Jooq (which hosted on Maven) only supports serval database with specific version. Luckily, it supports MySql, PostgreSQL, SQLite, … and is usually enough for most development.

However, each open source version has a minimum version limitation for each database, for example, jooq:3.20.10, which I am going to use, supports PostgreSQL:17 and above, while if I switch to 3.21, it will ask you to use PostgreSQL:18.

Although it did not crash when I used a PostgreSQL:16, but it will keep warn you and may have some issue to fix, so I recommend to check the matrix, and choose the correct database or version.

The limitation of open source version is one of its advantages

I will directly use the spring-boot-starter:jooq package, which will include the core package and config jooq automatically. But you can still add the dependency manually, but you will also need to config it manually, like the document.

The Gradle looks like:

dependencies {
    // ...
	implementation("org.springframework.boot:spring-boot-starter-jooq")
	// ... 
	testImplementation("org.springframework.boot:spring-boot-starter-jooq-test")
	// ...
}

and we config the application.yaml so the Spring Boot will config the datasource and load the jooq properly.

spring:
  application:
    name: filebox
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/...
    username: ...
    password: ...

Say hi!

Now we can use the jooq, for example, if I want to select from one table, we can do:

@SpringBootTest
class MapperTest {

    @Autowired
    lateinit var dsl: DSLContext
    
    @Test
    fun `select sql`() {
        dsl.selectFrom("accounts").where("id = 1").fetchOne()
    }
}

Now, we can write SQL in code, but it can be better if we used the code generation plugin.

Code Generation

The code generation plugin will generate Table, Record, Field … for us, so we do not need to write “account”, and “id = 1” anymore, but use ACCOUNTS or ACCOUNTS.id.eq(1), which is much better.

There are usually two plugins, one is the official plugin, and gradle-jooq-plugin, which is built based on the official one, I guess.

The later one will add the version of jooq and jooq-codege in Gradle automatically, and some other functionality. But I will go with the official one, since it is maintained by jOOQ, stays in sync with new features, keeps config close to jOOQ docs.

First, we need to add the dependencies. I am using “3.20.10” at here, which should be the same version of the jooq.

plugin {
    // ...
    id("org.jooq.jooq-codegen-gradle") version "3.20.10"
}

dependencies {
    // ...
	implementation("org.jooq:jooq-postgres-extensions:3.20.10") // add support for citext, inet, ...
	jooqCodegen("org.postgresql:postgresql")
}

Next step is to config the code generation,

fun dotEnv(fileName: String = ".env"): Map<String, String> {
	val file = rootProject.file(fileName)
	if (!file.exists()) return emptyMap()

	return file.readLines()
		.asSequence()
		.map { it.trim() }
		.filter { it.isNotEmpty() && !it.startsWith("#") && it.contains("=") }
		.associate { line ->
			val idx = line.indexOf("=")
			val key = line.substring(0, idx).trim()
			val value = line.substring(idx + 1).trim().removeSurrounding("\"")
			key to value
		}
}

val envs = dotEnv()

fun envOrDotEnv(key: String): String =
	System.getenv(key)
		?: envs[key]
		?: error("$key not set (env var or .env)")
  • Since I prefer to store database configuration in .env, I create a simple method, which help me read data from it
extra["jooq.version"] = "3.20.10"

specify the jooq version we are going to use, override the spring boot starter’s default value.

now we start to config the generator, let me paste the code first, and explain later:

jooq {
	version = "3.20.10"

	configurations {
		create("main", Action {
			configuration {
				logging = org.jooq.meta.jaxb.Logging.WARN

				jdbc {
					driver = "org.postgresql.Driver"
					url = "jdbc:postgresql://localhost:5432/${envOrDotEnv("POSTGRES_DB")}"
					user = envOrDotEnv("POSTGRES_USER")
					password = envOrDotEnv("POSTGRES_PASSWORD")
				}

				generator {
					name = "org.jooq.codegen.KotlinGenerator"

					database {
						name = "org.jooq.meta.postgres.PostgresDatabase"
						inputSchema = "public"
						includes = ".*"
						excludes = "flyway_schema_history|regexp_matches|regexp_split_to_table"

						forcedTypes {
							forcedType {
								userType = "java.lang.String"
								binding = "org.jooq.postgres.extensions.bindings.CitextBinding"
								includeTypes = "citext"
								priority = -2147483648
							}
							forcedType {
								userType = "org.jooq.postgres.extensions.types.Inet"
								binding = "org.jooq.postgres.extensions.bindings.InetBinding"
								includeTypes = "inet"
								priority = -2147483648
							}
						}
					}

					generate {
						isRecords = true
						isPojos = true
						isImmutablePojos = true
						isDaos = false
						isFluentSetters = false
					}

					target {
						packageName = "dev.haomin.secure_share.jooq"
						directory = "build/generated-src/jooq/main"
					}

					strategy {
						matchers {
							tables {
								table {
									tableIdentifier {
										transform = MatcherTransformType.UPPER
										expression = "$0"
									}

									tableClass {
										transform = MatcherTransformType.PASCAL
										expression = "T__$0"
									}

									recordClass {
										transform = MatcherTransformType.PASCAL
										expression = "R__$0"
									}

									pojoClass {
										transform = MatcherTransformType.PASCAL
										expression = "P__$0"
									}
								}
							}

							fields {
								field {
									fieldIdentifier {
										transform = MatcherTransformType.CAMEL
										expression = "$0"
									}
								}
							}

							enums {
								enum_ {
									enumClass {
										transform = MatcherTransformType.PASCAL
										expression = "E__$0"
									}

									enumLiteral {
										transform = MatcherTransformType.UPPER
										expression = "$0"
									}
								}
							}
						}
					}
				}
			}
		})
	}
}
  • First, specify the version again
  • Config the datasource using the data read from .env
  • Since I am going to use Kotlin, I choose to generate code in Kotlin
  • In database section, first specify which tables I will use, then config our ‘typeHandler’. In this stage, I convert uuid and inet (according to document, this should be added automatically, since I load the postgreSQL extension, but it does not work)
  • Choose what data we are going to generate, I choose:
    • Records: for easily insert
    • Pojo and ImmutablePojo: for easily read
    • no Daos
    • no Flute Setters: Kotlin is enough
    • it will automatically generate table
  • in target section, define where to output, the final destination will be ${directory}${packageName}
  • under strategy, specify the format of the code generated, for example, my record will become R_Accounts for Accounts.

We also need to include the generated code in the module, by adding:

sourceSets {
	main {
		java {
			srcDir(layout.buildDirectory.dir("generated-src/jooq/main")) // match the target seciont
		}
	}
}

All right, now we can call ./gradlew jooqCodegen, and hope we can see the code generated at the folder we specified.

Next

Now, we can write code like this:

fun selectById(id: UUID, forUpdate: Boolean = false): Account? =
    dsl.selectFrom(ACCOUNTS)
        .where(ACCOUNTS.id.eq(id))
        .apply { if(forUpdate) forUpdate() }
        .fetchOneInto(P_Accounts::class.java)
        ?.toDomain() // I implement `toDomain` to generate the project entity from R_Accounts

which is much nicer and cleaner.

Conclusion

I am just starting using jooq, so I cannot tell too much about it.

  • No need to write many ‘.xml’ and mappers
  • No need to create another repo if I want to add something to the mapper, like cache
  • Compile time safe, if your SQL has an error, it will throw it at you when compiling
  • More dynamical SQL than mybatis, since it stays in code

On the other hand,

  • More setting up needed
  • if not use the generated POJO as models in project, we still need to add some ‘resultMap’ or ‘typeHandler’ to map selected record into project entity