Intro to jOOQ

Gr8Conf 2015 presentation

Transcript

1. INTRODUCTION TO JOOQ Embrace SQL again
2. WHO AM I? Kyle Boon @kyleboon Senior Software Engineer @ SmartThings Co-owner and maintainer of CellarHQ. kyle.f.boon@gmail.com
3. WHAT IS JOOQ java Object Oriented Querying Alternative to ORMs or JDBC and Strings.
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
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
6. SUPPORTED WITH LICENSE
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
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
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 )
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 )
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 )
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 )
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 ( )
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 )
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 ( ) ;
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 ( )
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 )
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 ( )
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
20. CODE GENERATION tables records POJOs Interfaces DAOs sequences procedures UDTs
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 >
22. GRADLE PLUGIN can run code generation at compile time. Easy to configure and fully participates in gradle up-to-date checking. Gradle Plugin
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
24. ADVANTAGES OVER HIBERNATE Lower level of abstraction SQL > HQL 'Object relational impendence mismatch' http://www.hibernate­ alternative.com/
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
26. WHAT ABOUT MONGODB?