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
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
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 )
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 )
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 )
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 )
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 ( )
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 )
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 ( ) ;
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 )
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 ( )
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
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 >
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
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