Intro to jOOQ

INTRODUCTION TO JOOQ
Embrace SQL again

View Slide

WHO AM I?
Kyle Boon
@kyleboon
Senior Software Engineer @ SmartThings
Co-owner and maintainer of CellarHQ.
[email protected]

View Slide

WHAT IS JOOQ
java Object Oriented Querying
Alternative to ORMs or JDBC and Strings.

View Slide

FEATURES
Code Generation
Type Safe SQL
Supports all SQL features as well as vendor specific features
Supports stored procedures natively
Open Source and free for open source databases
Does not manage transactions for you

View Slide

SUPPORTED FOR FREE
CUBRID 8.4
Derby 10.10
Firebird 2.5
H2 1.3
HSQLDB 2.2
MariaDB 5.2
MySQL 5.5
PostgreSQL 9.0
SQLite

View Slide

SUPPORTED WITH LICENSE

View Slide

Microsoft Access 2013
Oracle 11g (All editions)
SQL Server 2008 (All editions)
Amazon Redshift
SQL Azure
DB2 9.7
HANA (All editions)
Informix 12.10
Ingres 10.1
Sybase ASE 15.5
Sybase SQL Anywhere 12
Vertica 7.1

View Slide

WHO MAKES IT?
is the founder and CEO of Data Geekery. He’s the creator and
maintainer of jOOQ.
Frequent Answerer
Blog In Response to
Stack Overflow
If you ask a question on the internet about java and sql, he will find it.
Lukas Eder
Quora
Pivotal Dropping Groovy
too

View Slide

SIMPLE SELECT
create is an instance of the DSLContext, a core jOOQ class
DRINK is a static reference to table. Code Generation
Drink is a pojo holding the data.
L
i
s
t
<
D
r
i
n
k
> d
r
i
n
k
s = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
f
e
t
c
h
I
n
t
o
(
D
r
i
n
k
)

View Slide

SELECTS CONTINUED
L
i
s
t
<
S
t
r
i
n
g
> n
a
m
e
s = c
r
e
a
t
e
.
s
e
l
e
c
t
(
D
R
I
N
K
.
N
A
M
E
)
.
f
r
o
m
(
D
R
I
N
K
)
.
f
e
t
c
h
I
n
t
o
(
S
t
r
i
n
g
)

View Slide

SELECTS CONTINUED
D
r
i
n
k f
u
r
i
o
u
s = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
w
h
e
r
e
(
D
R
I
N
K
.
N
A
M
E
.
e
q
(
'
F
u
r
i
o
u
s
'
)
)
.
f
e
t
c
h
O
n
e
I
n
t
o
(
D
R
I
N
K
)
D
r
i
n
k f
u
r
i
o
u
s = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
w
h
e
r
e
(
D
R
I
N
K
.
N
A
M
E
.
e
q
u
a
l
I
g
n
o
r
e
C
a
s
e
(
'
F
u
r
I
O
U
S
'
)
)
.
f
e
t
c
h
O
n
e
I
n
t
o
(
D
R
I
N
K
)
L
i
s
t
<
D
r
i
n
k
> i
p
a
s = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
w
h
e
r
e
(
D
R
I
N
K
.
N
A
M
E
.
i
n
(
[
'
F
u
r
i
o
u
s
'
, '
T
w
o H
e
a
r
t
e
d
'
]
)
)
.
f
e
t
c
h
I
n
t
o
(
D
R
I
N
K
)

View Slide

JOINS
L
i
s
t
<
D
r
i
n
k
> i
p
a
s = c
r
e
a
t
e
.
s
e
l
e
c
t
(
D
R
I
N
K
.
f
i
e
l
d
s
(
)
)
.
f
r
o
m
(
D
R
I
N
K
)
.
j
o
i
n
(
S
T
Y
L
E
)
.
o
n
K
e
y
(
K
e
y
s
.
D
R
I
N
K
_
_
F
K
_
D
R
I
N
K
_
S
T
Y
L
E
_
I
D
)
.
w
h
e
r
e
(
S
T
Y
L
E
.
N
A
M
E
.
e
q
(
'
I
P
A
'
)
)
.
f
e
t
c
h
I
n
t
o
(
D
r
i
n
k
)

View Slide

JOINS CONTINUED
c
r
e
a
t
e
.
s
e
l
e
c
t
(
D
R
I
N
K
.
N
A
M
E
,
O
R
G
A
N
I
Z
A
T
I
O
N
.
N
A
M
E
.
a
s
(
'
o
r
g
a
n
i
z
a
t
i
o
n
N
a
m
e
'
)
,
S
T
Y
L
E
.
N
A
M
E
.
a
s
(
'
s
t
y
l
e
N
a
m
e
'
)
)
.
f
r
o
m
(
D
R
I
N
K
)
.
j
o
i
n
(
O
R
G
A
N
I
Z
A
T
I
O
N
)
.
o
n
K
e
y
(
K
e
y
s
.
D
R
I
N
K
_
_
F
K
_
D
R
I
N
K
_
O
R
G
A
N
I
Z
A
T
I
O
N
_
I
D
)
.
l
e
f
t
O
u
t
e
r
J
o
i
n
(
S
T
Y
L
E
)
.
o
n
K
e
y
(
K
e
y
s
.
D
R
I
N
K
_
_
F
K
_
D
R
I
N
K
_
S
T
Y
L
E
_
I
D
)
.
f
e
t
c
h
(
)

View Slide

COUNTS
F
i
e
l
d
<
I
n
t
e
g
e
r
> o
r
g
a
n
i
z
a
t
i
o
n
s = c
r
e
a
t
e
.
s
e
l
e
c
t
C
o
u
n
t
(
)
.
f
r
o
m
(
O
R
G
A
N
I
Z
A
T
I
O
N
)
.
a
s
F
i
e
l
d
(
'
o
r
g
a
n
i
z
a
t
i
o
n
s
'
)
F
i
e
l
d
<
I
n
t
e
g
e
r
> d
r
i
n
k
s = c
r
e
a
t
e
.
s
e
l
e
c
t
C
o
u
n
t
(
)
.
f
r
o
m
(
D
R
I
N
K
)
.
a
s
F
i
e
l
d
(
'
d
r
i
n
k
s
'
)
F
i
e
l
d
<
I
n
t
e
g
e
r
> c
e
l
l
a
r
s = c
r
e
a
t
e
.
s
e
l
e
c
t
C
o
u
n
t
(
)
.
f
r
o
m
(
C
E
L
L
A
R
)
.
a
s
F
i
e
l
d
(
'
c
e
l
l
a
r
s
'
)
F
i
e
l
d
<
I
n
t
e
g
e
r
> c
e
l
l
a
r
e
d
D
r
i
n
k
s = c
r
e
a
t
e
.
s
e
l
e
c
t
(
D
S
L
.
s
u
m
(
C
E
L
L
A
R
E
D
_
D
R
I
N
K
.
Q
U
A
N
T
I
T
Y
)
)
.
f
r
o
m
(
C
E
L
L
A
R
E
D
_
D
R
I
N
K
)
.
a
s
F
i
e
l
d
(
'
c
e
l
l
a
r
e
d
D
r
i
n
k
s
'
)
c
r
e
a
t
e
.
s
e
l
e
c
t
(
o
r
g
a
n
i
z
a
t
i
o
n
s
, d
r
i
n
k
s
, c
e
l
l
a
r
s
, c
e
l
l
a
r
e
d
D
r
i
n
k
s
)
.
f
e
t
c
h
I
n
t
o
(
H
o
m
e
p
a
g
e
S
t
a
t
i
s
t
i
c
s
)

View Slide

INSERTS AND UPDATES
R
e
c
o
r
d
<
?
> r
e
c
o
r
d =
c
r
e
a
t
e
.
i
n
s
e
r
t
I
n
t
o
(
D
R
I
N
K
, D
R
I
N
K
.
N
A
M
E
)
.
v
a
l
u
e
s
(
"
K
y
l
e
'
s B
e
e
r
"
)
.
r
e
t
u
r
n
i
n
g
(
D
R
I
N
K
.
I
D
)
.
f
e
t
c
h
O
n
e
(
)
c
r
e
a
t
e
.
u
p
d
a
t
e
(
D
R
I
N
K
)
.
s
e
t
(
D
R
I
N
K
.
N
A
M
E
, "
K
y
l
e
'
s N
e
w N
a
m
e
"
)
.
w
h
e
r
e
(
D
R
I
N
K
.
N
A
M
E
.
e
q
u
a
l
(
K
y
l
e
'
s B
e
e
r
)
)
.
e
x
e
c
u
t
e
(
)
;

View Slide

DELETING
c
r
e
a
t
e
.
d
e
l
e
t
e
(
D
R
I
N
K
)
.
w
h
e
r
e
(
D
R
I
N
K
.
I
D
.
e
q
u
a
l
(
1
0
0
)
)
.
e
x
e
c
u
t
e
(
)

View Slide

ACTIVE RECORD PATTERN
D
r
i
n
k
R
e
c
o
r
d d
r
i
n
k
R
e
c
o
r
d = c
r
e
a
t
e
.
n
e
w
R
e
c
o
r
d
(
D
R
I
N
K
, d
r
i
n
k
)
d
r
i
n
k
R
e
c
o
r
d
.
r
e
s
e
t
(
D
R
I
N
K
.
D
A
T
A
)
d
r
i
n
k
R
e
c
o
r
d
.
r
e
s
e
t
(
D
R
I
N
K
.
C
R
E
A
T
E
D
_
D
A
T
E
)
d
r
i
n
k
R
e
c
o
r
d
.
r
e
s
e
t
(
D
R
I
N
K
.
M
O
D
I
F
I
E
D
_
D
A
T
E
)
i
f (
d
r
i
n
k
R
e
c
o
r
d
.
i
d
) {
d
r
i
n
k
R
e
c
o
r
d
.
u
p
d
a
t
e
(
)
} e
l
s
e {
d
r
i
n
k
R
e
c
o
r
d
.
r
e
s
e
t
(
D
R
I
N
K
.
I
D
)
d
r
i
n
k
R
e
c
o
r
d
.
s
t
o
r
e
(
)
}
d
r
i
n
k
R
e
c
o
r
d
.
i
n
t
o
(
D
r
i
n
k
)

View Slide

IMPORTING/EXPORTING DATA
S
t
r
i
n
g c
s
v = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
f
e
t
c
h
(
)
.
f
o
r
m
a
t
C
S
V
(
)
S
t
r
i
n
g x
m
l = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
f
e
t
c
h
(
)
.
f
o
r
m
a
t
X
M
L
(
)
S
t
r
i
n
g j
s
o
n = c
r
e
a
t
e
.
s
e
l
e
c
t
F
r
o
m
(
D
R
I
N
K
)
.
f
e
t
c
h
(
)
.
f
o
r
m
a
t
J
S
O
N
(
)
c
r
e
a
t
e
.
l
o
a
d
I
n
t
o
(
D
R
I
N
K
)
.
l
o
a
d
C
S
V
(
i
n
p
u
t
s
t
r
e
a
m
, e
n
c
o
d
i
n
g
)
.
f
i
e
l
d
s
(
D
R
I
N
K
.
I
D
, D
R
I
N
K
.
N
A
M
E
)
.
e
x
e
c
u
t
e
(
)

View Slide

SO MUCH MORE
The documentation is the best place to see examples; this presentation
would be (even more) boring if I covered every aspect of SQL Generation
and Execution

View Slide

CODE GENERATION
tables
records
POJOs
Interfaces
DAOs
sequences
procedures
UDTs

View Slide

CODE GEN EXAMPLE
/
*
*
* T
h
e s
i
n
g
l
e
t
o
n i
n
s
t
a
n
c
e o
f <
c
o
d
e
>
p
u
b
l
i
c
.
d
r
i
n
k
<
/
c
o
d
e
>
*
/
p
u
b
l
i
c s
t
a
t
i
c f
i
n
a
l c
o
m
.
c
e
l
l
a
r
h
q
.
g
e
n
e
r
a
t
e
d
.
t
a
b
l
e
s
.
D
r
i
n
k D
R
I
N
K = n
e
w c
o
m
.
c
e
l
l
a
r
h
q
.
g
e
n
e
r
a
t
e
d
.
t
a
b
l
e
s
.
D
r
i
n
k
/
*
*
* T
h
e c
l
a
s
s h
o
l
d
i
n
g r
e
c
o
r
d
s f
o
r t
h
i
s t
y
p
e
*
/
@
O
v
e
r
r
i
d
e
p
u
b
l
i
c j
a
v
a
.
l
a
n
g
.
C
l
a
s
s
<
c
o
m
.
c
e
l
l
a
r
h
q
.
g
e
n
e
r
a
t
e
d
.
t
a
b
l
e
s
.
r
e
c
o
r
d
s
.
D
r
i
n
k
R
e
c
o
r
d
> g
e
t
R
e
c
o
r
d
T
y
p
e
(
) {
r
e
t
u
r
n c
o
m
.
c
e
l
l
a
r
h
q
.
g
e
n
e
r
a
t
e
d
.
t
a
b
l
e
s
.
r
e
c
o
r
d
s
.
D
r
i
n
k
R
e
c
o
r
d
.
c
l
a
s
s
;
}
/
*
*
* T
h
e c
o
l
u
m
n <
c
o
d
e
>
p
u
b
l
i
c
.
d
r
i
n
k
.
i
d
<
/
c
o
d
e
>
.
*
/
p
u
b
l
i
c f
i
n
a
l o
r
g
.
j
o
o
q
.
T
a
b
l
e
F
i
e
l
d
<
c
o
m
.
c
e
l
l
a
r
h
q
.
g
e
n
e
r
a
t
e
d
.
t
a
b
l
e
s
.
r
e
c
o
r
d
s
.
D
r
i
n
k
R
e
c
o
r
d
, j
a
v
a
.
l
a
n
g
.
L
o
n
g
> I
D
/
*
*
* T
h
e c
o
l
u
m
n <
c
o
d
e
>
p
u
b
l
i
c
.
d
r
i
n
k
.
v
e
r
s
i
o
n
<
/
c
o
d
e
>
.
*
/
p
u
b
l
i
c f
i
n
a
l o
r
g
.
j
o
o
q
.
T
a
b
l
e
F
i
e
l
d
<
c
o
m
.
c
e
l
l
a
r
h
q
.
g
e
n
e
r
a
t
e
d
.
t
a
b
l
e
s
.
r
e
c
o
r
d
s
.
D
r
i
n
k
R
e
c
o
r
d
, j
a
v
a
.
l
a
n
g
.
I
n
t
e
g
e
r
>

View Slide

GRADLE PLUGIN
can run code generation at compile time. Easy to configure
and fully participates in gradle up-to-date checking.
Gradle Plugin

View Slide

ADVANTAGES OVER STRAIGHT JDBC
jOOQ is built on top of JDBC.
Type safety
Compile Time errors vs Run Time errors
Auto completion in IDE helps you to remember the schema

View Slide

ADVANTAGES OVER HIBERNATE
Lower level of abstraction
SQL > HQL
'Object relational impendence mismatch'
http://www.hibernate
alternative.com/

View Slide

JOOQ AND NON BLOCKING, ASYNCHRONOUS
FRAMEWORKS
ORMs make it difficult to understand when blocking operations happen.
With jOOQ, you are forced to understand the database on a deeper level.
Less abstraction makes it easier to use correctly with Ratpack or Grails in
an async manner.
B
o
o
k b = B
o
o
k
.
g
e
t
(
1
)
b
o
o
k
.
a
u
t
h
o
r

View Slide

WHAT ABOUT MONGODB?

View Slide

Intro to jOOQ

Intro to jOOQ

Kyle Boon

More Decks by Kyle Boon

Other Decks in Programming

Featured

Transcript