Search code examples
sqlitejooq

Call db-specific function from jOOQ

In the new fts5 virtual tables in SQLite there is a ranking function bm25. What is the best way to call it from jOOQ? Is there any way to introduce this function as Java/Kotlin function or should it be just a string literal?

What I do currently is

.orderBy(field("bm25(books_fts)"))

but indeed this approach is error-prone. I can make a typo in any part and will have an error only in runtime.

Is there any better way?

Solution

  • Instead of using the plain SQL template directly in your query, better create a custom library for these purposes, e.g.

    fun bm25(t: Table<*>): Field<Double> = field("bm25({0})", SQLDataType.DOUBLE, t)

    Now you can pretend this is a natively supported function in jOOQ and use it in a type safe way everywhere.