Is there a way to query ltrees in JOOQ?

Is there any sort of extension that would allow me to use Postgres ltrees in JOOQ without having to use raw SQL?

Any way to make one myself maybe?

Answer

Starting from jOOQ 3.17:

With #13188, jOOQ has added native support for LTREE types (and similar) via the jooq-postgres-extensions module:

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-postgres-extensions</artifactId>
</dependency>

Just add that module to both code generation and runtime classpaths, and the code generator will auto-configure the relevant Binding for you.

Before jOOQ 3.17:

You have to implement a custom data type binding (org.jooq.Binding) and apply that to your generated code: https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings.

A Binding allows you to tell jOOQ how to:

• generate SQL for it (probably something like ?::ltree in your case)
• bind your data type to JDBC
  • PreparedStatement
  • SQLOutput (optional, when your type is contained in a UDT, currently only in Oracle)
• read it from JDBC
  • ResultSet
  • CallableStatement (optional, when you fetch it from a function OUT parameter)
  • SQLInput (optional, when your type is contained in a UDT, currently only in Oracle)