The jOOQ™ User Manual SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL! The jOOQ User Manual Overview This manual is divided into six main sections: - Getting started with jOOQ This section will get you started with jOOQ quickly. It contains simple explanations about what jOOQ is, what jOOQ isn't and how to set it up for the first time - SQL building This section explains all about the jOOQ syntax used for building queries through the query DSL and the query model API. It explains the central factories, the supported SQL statements and various other syntax elements - Code generation This section explains how to configure and use the built-in source code generator - SQL execution This section will get you through the specifics of what can be done with jOOQ at runtime, in order to execute queries, perform CRUD operations, import and export data, and hook into the jOOQ execution lifecycle for debugging - Tools This section is dedicated to tools that ship with jOOQ, such as the jOOQ's JDBC mocking feature - Reference This section is a reference for elements in this manual © 2009 - 2018 by Data Geekery™ GmbH. Page 2 / 273 The jOOQ User Manual Table of contents 1. Preface.................................................................................................................................................................................................................. 9 2. Copyright, License, and Trademarks.......................................................................................................................................................... 11 3. Getting started with jOOQ............................................................................................................................................................................ 16 3.1. How to read this manual........................................................................................................................................................................... 16 3.2. The sample database used in this manual........................................................................................................................................... 17 3.3. Different use cases for jOOQ................................................................................................................................................................... 18 3.3.1. jOOQ as a SQL builder........................................................................................................................................................................... 19 3.3.2. jOOQ as a SQL builder with code generation.................................................................................................................................. 20 3.3.3. jOOQ as a SQL executor........................................................................................................................................................................ 20 3.3.4. jOOQ for CRUD......................................................................................................................................................................................... 21 3.3.5. jOOQ for PROs.......................................................................................................................................................................................... 22 3.4. Tutorials........................................................................................................................................................................................................... 22 3.4.1. jOOQ in 7 easy steps.............................................................................................................................................................................. 22 3.4.1.1. Step 1: Preparation............................................................................................................................................................................... 22 3.4.1.2. Step 2: Your database.......................................................................................................................................................................... 24 3.4.1.3. Step 3: Code generation..................................................................................................................................................................... 24 3.4.1.4. Step 4: Connect to your database................................................................................................................................................... 26 3.4.1.5. Step 5: Querying.................................................................................................................................................................................... 27 3.4.1.6. Step 6: Iterating..................................................................................................................................................................................... 27 3.4.1.7. Step 7: Explore!...................................................................................................................................................................................... 28 3.4.2. Using jOOQ in modern IDEs................................................................................................................................................................. 28 3.4.3. Using jOOQ with Spring and Apache DBCP...................................................................................................................................... 28 3.4.4. Using jOOQ with Flyway.......................................................................................................................................................................... 33 3.4.5. Using jOOQ with JAX-RS.......................................................................................................................................................................... 39 3.4.6. A simple web application with jOOQ.................................................................................................................................................. 44 3.5. jOOQ and Java 8.......................................................................................................................................................................................... 44 3.6. jOOQ and JavaFX.......................................................................................................................................................................................... 46 3.7. jOOQ and Nashorn...................................................................................................................................................................................... 49 3.8. jOOQ and Scala............................................................................................................................................................................................ 49 3.9. jOOQ and Groovy......................................................................................................................................................................................... 50 3.10. jOOQ and Kotlin......................................................................................................................................................................................... 51 3.11. jOOQ and NoSQL...................................................................................................................................................................................... 51 3.12. jOOQ and JPA.............................................................................................................................................................................................. 51 3.13. Dependencies............................................................................................................................................................................................. 52 3.14. Build your own........................................................................................................................................................................................... 53 3.15. jOOQ and backwards-compatibility...................................................................................................................................................... 53 4. SQL building...................................................................................................................................................................................................... 55 4.1. The query DSL type..................................................................................................................................................................................... 55 4.1.1. DSL subclasses.......................................................................................................................................................................................... 56 4.2. The DSLContext class.................................................................................................................................................................................. 56 4.2.1. SQL Dialect................................................................................................................................................................................................. 57 4.2.2. SQL Dialect Family.................................................................................................................................................................................... 58 4.2.3. Connection vs. DataSource.................................................................................................................................................................... 59 4.2.4. Custom data............................................................................................................................................................................................... 60 4.2.5. Custom ExecuteListeners....................................................................................................................................................................... 60 4.2.6. Custom Settings........................................................................................................................................................................................ 61 4.2.6.1. Object qualification............................................................................................................................................................................... 62 4.2.6.2. Runtime schema and table mapping............................................................................................................................................... 62 4.2.6.3. Identifier style......................................................................................................................................................................................... 65 4.2.6.4. Keyword style.......................................................................................................................................................................................... 66 © 2009 - 2018 by Data Geekery™ GmbH. Page 3 / 273 The jOOQ User Manual 4.2.6.5. Parameter types.................................................................................................................................................................................... 66 4.2.6.6. Statement Type...................................................................................................................................................................................... 67 4.2.6.7. Execute Logging..................................................................................................................................................................................... 67 4.2.6.8. Optimistic Locking................................................................................................................................................................................. 68 4.2.6.9. Auto-attach Records............................................................................................................................................................................. 68 4.2.6.10. Updatable Primary Keys.................................................................................................................................................................... 69 4.2.6.11. Reflection caching............................................................................................................................................................................... 69 4.2.6.12. Fetch Warnings.................................................................................................................................................................................... 70 4.2.6.13. Return All Columns On Store.......................................................................................................................................................... 70 4.2.6.14. Map JPA Annotations.......................................................................................................................................................................... 70 4.2.6.15. JDBC Flags............................................................................................................................................................................................. 71 4.2.6.16. Backslash Escaping............................................................................................................................................................................. 71 4.2.6.17. Scalar subqueries for stored functions......................................................................................................................................... 72 4.3. SQL Statements (DML)............................................................................................................................................................................... 72 4.3.1. jOOQ's DSL and model API.................................................................................................................................................................... 73 4.3.2. The WITH clause....................................................................................................................................................................................... 74 4.3.3. The SELECT statement............................................................................................................................................................................ 76 4.3.3.1. The SELECT clause................................................................................................................................................................................ 77 4.3.3.2. The FROM clause.................................................................................................................................................................................. 78 4.3.3.3. The JOIN clause...................................................................................................................................................................................... 78 4.3.3.4. The WHERE clause................................................................................................................................................................................ 81 4.3.3.5. The CONNECT BY clause..................................................................................................................................................................... 82 4.3.3.6. The GROUP BY clause......................................................................................................................................................................... 83 4.3.3.7. The HAVING clause............................................................................................................................................................................... 84 4.3.3.8. The WINDOW clause............................................................................................................................................................................ 84 4.3.3.9. The ORDER BY clause.......................................................................................................................................................................... 85 4.3.3.10. The LIMIT .. OFFSET clause............................................................................................................................................................... 86 4.3.3.11. The SEEK clause.................................................................................................................................................................................. 88 4.3.3.12. The FOR UPDATE clause................................................................................................................................................................... 89 4.3.3.13. UNION, INTERSECTION and EXCEPT.............................................................................................................................................. 91 4.3.3.14. Oracle-style hints................................................................................................................................................................................. 92 4.3.3.15. Lexical and logical SELECT clause order....................................................................................................................................... 93 4.3.4. The INSERT statement............................................................................................................................................................................. 94 4.3.4.1. INSERT .. VALUES................................................................................................................................................................................... 94 4.3.4.2. INSERT .. DEFAULT VALUES................................................................................................................................................................. 95 4.3.4.3. INSERT .. SET........................................................................................................................................................................................... 96 4.3.4.4. INSERT .. SELECT.................................................................................................................................................................................... 96 4.3.4.5. INSERT .. ON DUPLICATE KEY............................................................................................................................................................. 96 4.3.4.6. INSERT .. RETURNING........................................................................................................................................................................... 97 4.3.5. The UPDATE statement........................................................................................................................................................................... 98 4.3.6. The DELETE statement............................................................................................................................................................................ 99 4.3.7. The MERGE statement............................................................................................................................................................................ 99 4.4. SQL Statements (DDL).............................................................................................................................................................................. 100 4.4.1. The ALTER statement............................................................................................................................................................................ 100 4.4.2. The CREATE statement.......................................................................................................................................................................... 101 4.4.3. The DROP statement............................................................................................................................................................................. 103 4.4.4. The TRUNCATE statement.................................................................................................................................................................... 103 4.4.5. Generating DDL from objects............................................................................................................................................................. 104 4.5. Catalog and schema expressions.......................................................................................................................................................... 104 4.6. Table expressions...................................................................................................................................................................................... 105 4.6.1. Generated Tables................................................................................................................................................................................... 105 4.6.2. Aliased Tables.......................................................................................................................................................................................... 106 4.6.3. Joined tables............................................................................................................................................................................................ 107 4.6.4. The VALUES() table constructor......................................................................................................................................................... 109 © 2009 - 2018 by Data Geekery™ GmbH. Page 4 / 273 The jOOQ User Manual 4.6.5. Nested SELECTs...................................................................................................................................................................................... 109 4.6.6. The Oracle 11g PIVOT clause.............................................................................................................................................................. 110 4.6.7. jOOQ's relational division syntax........................................................................................................................................................ 111 4.6.8. Array and cursor unnesting................................................................................................................................................................. 111 4.6.9. Table-valued functions.......................................................................................................................................................................... 112 4.6.10. The DUAL table.................................................................................................................................................................................... 112 4.7. Column expressions.................................................................................................................................................................................. 113 4.7.1. Table columns......................................................................................................................................................................................... 114 4.7.2. Aliased columns...................................................................................................................................................................................... 114 4.7.3. Cast expressions..................................................................................................................................................................................... 115 4.7.4. Datatype coercions................................................................................................................................................................................ 116 4.7.5. Arithmetic expressions.......................................................................................................................................................................... 116 4.7.6. String concatenation.............................................................................................................................................................................. 117 4.7.7. General functions................................................................................................................................................................................... 117 4.7.8. Numeric functions.................................................................................................................................................................................. 117 4.7.9. Bitwise functions..................................................................................................................................................................................... 118 4.7.10. String functions..................................................................................................................................................................................... 119 4.7.11. Case sensitivity with strings.............................................................................................................................................................. 120 4.7.12. Date and time functions.................................................................................................................................................................... 120 4.7.13. System functions.................................................................................................................................................................................. 120 4.7.14. Aggregate functions............................................................................................................................................................................. 121 4.7.15. Window functions................................................................................................................................................................................ 123 4.7.16. Grouping functions.............................................................................................................................................................................. 125 4.7.17. User-defined functions....................................................................................................................................................................... 127 4.7.18. User-defined aggregate functions................................................................................................................................................... 127 4.7.19. The CASE expression.......................................................................................................................................................................... 129 4.7.20. Sequences and serials........................................................................................................................................................................ 129 4.7.21. Tuples or row value expressions..................................................................................................................................................... 130 4.8. Conditional expressions........................................................................................................................................................................... 131 4.8.1. Condition building.................................................................................................................................................................................. 132 4.8.2. AND, OR, NOT boolean operators..................................................................................................................................................... 132 4.8.3. Comparison predicate........................................................................................................................................................................... 133 4.8.4. Boolean operator precedence............................................................................................................................................................ 134 4.8.5. Comparison predicate (degree > 1).................................................................................................................................................. 134 4.8.6. Quantified comparison predicate...................................................................................................................................................... 135 4.8.7. NULL predicate....................................................................................................................................................................................... 136 4.8.8. NULL predicate (degree > 1)............................................................................................................................................................... 136 4.8.9. DISTINCT predicate................................................................................................................................................................................ 136 4.8.10. BETWEEN predicate............................................................................................................................................................................. 137 4.8.11. BETWEEN predicate (degree > 1).................................................................................................................................................... 138 4.8.12. LIKE predicate....................................................................................................................................................................................... 138 4.8.13. IN predicate........................................................................................................................................................................................... 139 4.8.14. IN predicate (degree > 1)................................................................................................................................................................... 140 4.8.15. EXISTS predicate................................................................................................................................................................................... 140 4.8.16. OVERLAPS predicate........................................................................................................................................................................... 141 4.8.17. Query By Example (QBE)................................................................................................................................................................... 141 4.9. Dynamic SQL............................................................................................................................................................................................... 142 4.10. Plain SQL.................................................................................................................................................................................................... 143 4.11. Plain SQL Templating Language.......................................................................................................................................................... 145 4.12. Names and identifiers............................................................................................................................................................................ 146 4.13. Bind values and parameters................................................................................................................................................................ 147 4.13.1. Indexed parameters............................................................................................................................................................................ 148 4.13.2. Named parameters............................................................................................................................................................................. 149 4.13.3. Inlined parameters.............................................................................................................................................................................. 149 © 2009 - 2018 by Data Geekery™ GmbH. Page 5 / 273 The jOOQ User Manual 4.13.4. SQL injection......................................................................................................................................................................................... 150 4.14. QueryParts................................................................................................................................................................................................. 151 4.14.1. SQL rendering....................................................................................................................................................................................... 151 4.14.2. Pretty printing SQL.............................................................................................................................................................................. 152 4.14.3. Variable binding.................................................................................................................................................................................... 153 4.14.4. Custom data type bindings............................................................................................................................................................... 153 4.14.5. Custom syntax elements................................................................................................................................................................... 157 4.14.6. Plain SQL QueryParts.......................................................................................................................................................................... 159 4.14.7. Serializability.......................................................................................................................................................................................... 160 4.14.8. Custom SQL transformation............................................................................................................................................................. 161 4.14.8.1. Logging abbreviated bind values.................................................................................................................................................. 161 4.15. Zero-based vs one-based APIs............................................................................................................................................................ 162 4.16. SQL building in Scala.............................................................................................................................................................................. 163 5. SQL execution................................................................................................................................................................................................ 166 5.1. Comparison between jOOQ and JDBC................................................................................................................................................ 167 5.2. Query vs. ResultQuery.............................................................................................................................................................................. 167 5.3. Fetching........................................................................................................................................................................................................ 168 5.3.1. Record vs. TableRecord......................................................................................................................................................................... 170 5.3.2. Record1 to Record22............................................................................................................................................................................ 171 5.3.3. Arrays, Maps and Lists.......................................................................................................................................................................... 171 5.3.4. RecordHandler........................................................................................................................................................................................ 172 5.3.5. RecordMapper......................................................................................................................................................................................... 172 5.3.6. POJOs......................................................................................................................................................................................................... 173 5.3.7. POJOs with RecordMappers................................................................................................................................................................ 176 5.3.8. Lazy fetching............................................................................................................................................................................................ 177 5.3.9. Lazy fetching with Streams.................................................................................................................................................................. 177 5.3.10. Many fetching........................................................................................................................................................................................ 178 5.3.11. Later fetching........................................................................................................................................................................................ 179 5.3.12. ResultSet fetching................................................................................................................................................................................ 181 5.3.13. Data type conversion.......................................................................................................................................................................... 182 5.3.14. Interning data........................................................................................................................................................................................ 183 5.4. Static statements vs. Prepared Statements........................................................................................................................................ 184 5.5. Reusing a Query's PreparedStatement................................................................................................................................................ 185 5.6. JDBC flags..................................................................................................................................................................................................... 185 5.7. Using JDBC batch operations................................................................................................................................................................. 187 5.8. Sequence execution.................................................................................................................................................................................. 188 5.9. Stored procedures and functions......................................................................................................................................................... 188 5.9.1. Oracle Packages...................................................................................................................................................................................... 190 5.9.2. Oracle member procedures................................................................................................................................................................ 191 5.10. Exporting to XML, CSV, JSON, HTML, Text......................................................................................................................................... 191 5.10.1. Exporting XML....................................................................................................................................................................................... 191 5.10.2. Exporting CSV........................................................................................................................................................................................ 192 5.10.3. Exporting JSON..................................................................................................................................................................................... 192 5.10.4. Exporting HTML.................................................................................................................................................................................... 193 5.10.5. Exporting Text....................................................................................................................................................................................... 193 5.11. Importing data.......................................................................................................................................................................................... 194 5.11.1. Importing CSV....................................................................................................................................................................................... 194 5.11.2. Importing JSON..................................................................................................................................................................................... 195 5.11.3. Importing Records............................................................................................................................................................................... 196 5.11.4. Importing Arrays................................................................................................................................................................................... 196 5.11.5. Importing XML...................................................................................................................................................................................... 197 5.12. CRUD with UpdatableRecords............................................................................................................................................................. 197 5.12.1. Simple CRUD......................................................................................................................................................................................... 197 5.12.2. Records' internal flags........................................................................................................................................................................ 199 © 2009 - 2018 by Data Geekery™ GmbH. Page 6 / 273 The jOOQ User Manual 5.12.3. IDENTITY values.................................................................................................................................................................................... 199 5.12.4. Navigation methods............................................................................................................................................................................ 200 5.12.5. Non-updatable records...................................................................................................................................................................... 201 5.12.6. Optimistic locking................................................................................................................................................................................. 201 5.12.7. Batch execution.................................................................................................................................................................................... 202 5.12.8. CRUD SPI: RecordListener................................................................................................................................................................. 203 5.13. DAOs........................................................................................................................................................................................................... 203 5.14. Transaction management...................................................................................................................................................................... 204 5.15. Exception handling.................................................................................................................................................................................. 207 5.16. ExecuteListeners...................................................................................................................................................................................... 208 5.17. Database meta data............................................................................................................................................................................... 210 5.18. Logging....................................................................................................................................................................................................... 211 5.19. Performance considerations................................................................................................................................................................ 211 5.20. Alternative execution models............................................................................................................................................................... 212 5.20.1. Using jOOQ with Spring's JdbcTemplate........................................................................................................................................ 212 5.20.2. Using jOOQ with JPA........................................................................................................................................................................... 213 5.20.2.1. Using jOOQ with JPA Native Query.............................................................................................................................................. 214 5.20.2.2. Using jOOQ with JPA entities......................................................................................................................................................... 214 5.20.2.3. Using jOOQ with JPA EntityResult................................................................................................................................................ 215 6. Code generation............................................................................................................................................................................................ 218 6.1. Configuration and setup of the generator......................................................................................................................................... 218 6.2. Advanced generator configuration....................................................................................................................................................... 224 6.3. Output target configuration.................................................................................................................................................................... 228 6.4. Programmatic generator configuration................................................................................................................................................ 228 6.5. Custom generator strategies.................................................................................................................................................................. 229 6.6. Matcher strategies..................................................................................................................................................................................... 232 6.7. Custom code sections.............................................................................................................................................................................. 234 6.8. Generated global artefacts..................................................................................................................................................................... 236 6.9. Generated tables....................................................................................................................................................................................... 237 6.10. Generated records.................................................................................................................................................................................. 238 6.11. Generated POJOs.................................................................................................................................................................................... 239 6.12. Generated Interfaces.............................................................................................................................................................................. 240 6.13. Generated DAOs...................................................................................................................................................................................... 240 6.14. Generated sequences............................................................................................................................................................................ 241 6.15. Generated procedures........................................................................................................................................................................... 241 6.16. Generated UDTs...................................................................................................................................................................................... 242 6.17. Data type rewrites................................................................................................................................................................................... 243 6.18. Custom data types and type conversion.......................................................................................................................................... 243 6.19. Custom data type binding.................................................................................................................................................................... 245 6.20. Mapping generated schemata and tables........................................................................................................................................ 247 6.21. Code generation for large schemas................................................................................................................................................... 248 6.22. Code generation and version control................................................................................................................................................ 248 6.23. JPADatabase: Code generation from entities................................................................................................................................... 249 6.24. XMLDatabase: Code generation from XML files.............................................................................................................................. 251 6.25. Running the code generator with Maven......................................................................................................................................... 253 6.26. Running the code generator with Ant............................................................................................................................................... 253 6.27. Running the code generator with Gradle......................................................................................................................................... 254 7. Tools.................................................................................................................................................................................................................. 256 7.1. JDBC mocking for unit testing................................................................................................................................................................ 256 7.2. SQL 2 jOOQ Parser................................................................................................................................................................................... 258 7.3. jOOQ Console............................................................................................................................................................................................. 259 8. Reference......................................................................................................................................................................................................... 260 8.1. Supported RDBMS..................................................................................................................................................................................... 260 8.2. Data types.................................................................................................................................................................................................... 260 © 2009 - 2018 by Data Geekery™ GmbH. Page 7 / 273 The jOOQ User Manual 8.2.1. BLOBs and CLOBs.................................................................................................................................................................................. 261 8.2.2. Unsigned integer types......................................................................................................................................................................... 261 8.2.3. INTERVAL data types............................................................................................................................................................................. 262 8.2.4. XML data types....................................................................................................................................................................................... 262 8.2.5. Geospacial data types........................................................................................................................................................................... 262 8.2.6. CURSOR data types............................................................................................................................................................................... 263 8.2.7. ARRAY and TABLE data types.............................................................................................................................................................. 263 8.2.8. Oracle DATE data type.......................................................................................................................................................................... 263 8.3. SQL to DSL mapping rules...................................................................................................................................................................... 264 8.4. Quality Assurance...................................................................................................................................................................................... 267 8.5. Migrating to jOOQ 3.0.............................................................................................................................................................................. 269 8.6. Credits........................................................................................................................................................................................................... 273 © 2009 - 2018 by Data Geekery™ GmbH. Page 8 / 273 The jOOQ User Manual 1. Preface 1. Preface jOOQ's reason for being - compared to JPA Java and SQL have come a long way. SQL is an "old", yet established and well-understood technology. Java is a legacy too, although its platform JVM allows for many new and contemporary languages built on top of it. Yet, after all these years, libraries dealing with the interface between SQL and Java have come and gone, leaving JPA to be a standard that is accepted only with doubts, short of any surviving options. So far, there had been only few database abstraction frameworks or libraries, that truly respected SQL as a first class citizen among languages. Most frameworks, including the industry standards JPA, EJB, Hibernate, JDO, Criteria Query, and many others try to hide SQL itself, minimising its scope to things called JPQL, HQL, JDOQL and various other inferior query languages jOOQ has come to fill this gap. jOOQ's reason for being - compared to LINQ Other platforms incorporate ideas such as LINQ (with LINQ-to-SQL), or Scala's SLICK, or also Java's QueryDSL to better integrate querying as a concept into their respective language. By querying, they understand querying of arbitrary targets, such as SQL, XML, Collections and other heterogeneous data stores. jOOQ claims that this is going the wrong way too. In more advanced querying use-cases (more than simple CRUD and the occasional JOIN), people will want to profit from the expressivity of SQL. Due to the relational nature of SQL, this is quite different from what object-oriented and partially functional languages such as C#, Scala, or Java can offer. It is very hard to formally express and validate joins and the ad-hoc table expression types they create. It gets even harder when you want support for more advanced table expressions, such as pivot tables, unnested cursors, or just arbitrary projections from derived tables. With a very strong object-oriented typing model, these features will probably stay out of scope. In essence, the decision of creating an API that looks like SQL or one that looks like C#, Scala, Java is a definite decision in favour of one or the other platform. While it will be easier to evolve SLICK in similar ways as LINQ (or QueryDSL in the Java world), SQL feature scope that clearly communicates its underlying intent will be very hard to add, later on (e.g. how would you model Oracle's partitioned outer join syntax? How would you model ANSI/ISO SQL:1999 grouping sets? How can you support scalar subquery caching? etc...). jOOQ has come to fill this gap. jOOQ's reason for being - compared to SQL / JDBC So why not just use SQL? SQL can be written as plain text and passed through the JDBC API. Over the years, people have become wary of this approach for many reasons: © 2009 - 2018 by Data Geekery™ GmbH. Page 9 / 273 The jOOQ User Manual 1. Preface - No typesafety - No syntax safety - No bind value index safety - Verbose SQL String concatenation - Boring bind value indexing techniques - Verbose resource and exception handling in JDBC - A very "stateful", not very object-oriented JDBC API, which is hard to use For these many reasons, other frameworks have tried to abstract JDBC away in the past in one way or another. Unfortunately, many have completely abstracted SQL away as well jOOQ has come to fill this gap. jOOQ is different SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL! © 2009 - 2018 by Data Geekery™ GmbH. Page 10 / 273