Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
SQL building in Scala
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ-scala is a maven module used for leveraging some advanced Scala features for those users that wish to use jOOQ with Scala.
You can integrate it using the following dependency:
<dependency>
<!-- Use org.jooq for the Open Source Edition
org.jooq.pro for commercial editions with Java 17 support,
org.jooq.pro-java-11 for commercial editions with Java 11 support,
org.jooq.pro-java-8 for commercial editions with Java 8 support,
org.jooq.trial for the free trial edition with Java 17 support,
org.jooq.trial-java-11 for the free trial edition with Java 11 support,
org.jooq.trial-java-8 for the free trial edition with Java 8 support
Note: Only the Open Source Edition is hosted on Maven Central.
Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org -->
<groupId>org.jooq</groupId>
<!-- Scala 2.11 and 2.12 are also supported -->
<artifactId>jooq-scala_2.13</artifactId>
<version>3.18.7</version>
</dependency>
Using Scala's implicit defs to allow for operator overloading
The most obvious Scala feature to use in jOOQ are implicit defs for implicit conversions in order to enhance the org.jooq.Field type with SQL-esque operators.
The following depicts a trait which wraps all fields:
/**
* A Scala-esque representation of {@link org.jooq.Field}, adding overloaded
* operators for common jOOQ operations to arbitrary fields
*/
trait SAnyField[T] extends Field[T] {
// String operations
// -----------------
def ||(value : String) : Field[String]
def ||(value : Field[_]) : Field[String]
// Comparison predicates
// ---------------------
def ===(value : T) : Condition
def ===(value : Field[T]) : Condition
def !==(value : T) : Condition
def !==(value : Field[T]) : Condition
def <>(value : T) : Condition
def <>(value : Field[T]) : Condition
def >(value : T) : Condition
def >(value : Field[T]) : Condition
def >=(value : T) : Condition
def >=(value : Field[T]) : Condition
def <(value : T) : Condition
def <(value : Field[T]) : Condition
def <=(value : T) : Condition
def <=(value : Field[T]) : Condition
def <=>(value : T) : Condition
def <=>(value : Field[T]) : Condition
}
The following depicts a trait which wraps numeric fields:
/**
* A Scala-esque representation of {@link org.jooq.Field}, adding overloaded
* operators for common jOOQ operations to numeric fields
*/
trait SNumberField[T <: Number] extends SAnyField[T] {
// Arithmetic operations
// ---------------------
def unary_- : Field[T]
def +(value : Number) : Field[T]
def +(value : Field[_ <: Number]) : Field[T]
def -(value : Number) : Field[T]
def -(value : Field[_ <: Number]) : Field[T]
def *(value : Number) : Field[T]
def *(value : Field[_ <: Number]) : Field[T]
def /(value : Number) : Field[T]
def /(value : Field[_ <: Number]) : Field[T]
def %(value : Number) : Field[T]
def %(value : Field[_ <: Number]) : Field[T]
// Bitwise operations
// ------------------
def unary_~ : Field[T]
def &(value : T) : Field[T]
def &(value : Field[T]) : Field[T]
def |(value : T) : Field[T]
def |(value : Field[T]) : Field[T]
def ^(value : T) : Field[T]
def ^(value : Field[T]) : Field[T]
def <<(value : T) : Field[T]
def <<(value : Field[T]) : Field[T]
def >>(value : T) : Field[T]
def >>(value : Field[T]) : Field[T]
}
An example query using such overloaded operators would then look like this:
select (
BOOK.ID * BOOK.AUTHOR_ID,
BOOK.ID + BOOK.AUTHOR_ID * 3 + 4,
BOOK.TITLE || " abc" || " xy")
from BOOK
leftOuterJoin (
select (x.ID, x.YEAR_OF_BIRTH)
from x
limit 1
asTable x.getName()
)
on BOOK.AUTHOR_ID === x.ID
where (BOOK.ID <> 2)
or (BOOK.TITLE in ("O Alquimista", "Brida"))
fetch
Scala 2.10 Macros
This feature is still being experimented with. With Scala Macros, it might be possible to inline a true SQL dialect into the Scala syntax, backed by the jOOQ API. Stay tuned!
Feedback
Do you have any feedback about this page? We'd love to hear it!