Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Intro to jOOQ

Intro to jOOQ

Gr8Conf 2015 presentation

Kyle Boon

July 30, 2015
Tweet

More Decks by Kyle Boon

Other Decks in Programming

Transcript

  1. INTRODUCTION TO JOOQ
    Embrace SQL again

    View Slide

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

    View Slide

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

    View Slide

  4. 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

  5. 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

  6. SUPPORTED WITH LICENSE

    View Slide

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. CODE GENERATION
    tables
    records
    POJOs
    Interfaces
    DAOs
    sequences
    procedures
    UDTs

    View Slide

  21. 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

  22. 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

  23. 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

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

    View Slide

  25. 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

  26. WHAT ABOUT MONGODB?

    View Slide