Intro to jOOQ

Intro to jOOQ

Gr8Conf 2015 presentation

E655ab825a4bd2f23b90c85d57c3ee01?s=128

Kyle Boon

July 30, 2015
Tweet

Transcript

  1. 2.

    WHO AM I? Kyle Boon @kyleboon Senior Software Engineer @

    SmartThings Co-owner and maintainer of CellarHQ. kyle.f.boon@gmail.com
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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 )
  7. 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 )
  8. 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 )
  9. 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 )
  10. 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 ( )
  11. 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 )
  12. 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 ( ) ;
  13. 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 ( )
  14. 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 )
  15. 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 ( )
  16. 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
  17. 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 >
  18. 22.

    GRADLE PLUGIN can run code generation at compile time. Easy

    to configure and fully participates in gradle up-to-date checking. Gradle Plugin
  19. 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
  20. 24.

    ADVANTAGES OVER HIBERNATE Lower level of abstraction SQL > HQL

    'Object relational impendence mismatch' http://www.hibernate­ alternative.com/
  21. 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