The JOOQ User Manual.pdf(java对象转SQL)

【实例截图】

【核心代码】

Table of contents
1. Preface.................................................................................................................................................................................................................. 9
2. Copyright, License, and Trademarks.......................................................................................................................................................... 11
3. Getting started with jOOQ............................................................................................................................................................................ 15
3.1. How to read this manual........................................................................................................................................................................... 15
3.2. The sample database used in this manual........................................................................................................................................... 16
3.3. Different use cases for jOOQ................................................................................................................................................................... 17
3.3.1. jOOQ as a SQL builder........................................................................................................................................................................... 18
3.3.2. jOOQ as a SQL builder with code generation.................................................................................................................................. 19
3.3.3. jOOQ as a SQL executor........................................................................................................................................................................ 19
3.3.4. jOOQ for CRUD......................................................................................................................................................................................... 20
3.3.5. jOOQ for PROs.......................................................................................................................................................................................... 21
3.4. Tutorials........................................................................................................................................................................................................... 21
3.4.1. jOOQ in 7 easy steps.............................................................................................................................................................................. 21
3.4.1.1. Step 1: Preparation............................................................................................................................................................................... 21
3.4.1.2. Step 2: Your database.......................................................................................................................................................................... 23
3.4.1.3. Step 3: Code generation..................................................................................................................................................................... 23
3.4.1.4. Step 4: Connect to your database................................................................................................................................................... 25
3.4.1.5. Step 5: Querying.................................................................................................................................................................................... 26
3.4.1.6. Step 6: Iterating..................................................................................................................................................................................... 26
3.4.1.7. Step 7: Explore!...................................................................................................................................................................................... 27
3.4.2. Using jOOQ in modern IDEs................................................................................................................................................................. 27
3.4.3. Using jOOQ with Spring and Apache DBCP...................................................................................................................................... 27
3.4.4. Using jOOQ with Flyway.......................................................................................................................................................................... 32
3.4.5. Using jOOQ with JAX-RS.......................................................................................................................................................................... 38
3.4.6. A simple web application with jOOQ.................................................................................................................................................. 43
3.5. jOOQ and Java 8.......................................................................................................................................................................................... 43
3.6. jOOQ and JavaFX.......................................................................................................................................................................................... 45
3.7. jOOQ and Nashorn...................................................................................................................................................................................... 48
3.8. jOOQ and Scala............................................................................................................................................................................................ 48
3.9. jOOQ and Groovy......................................................................................................................................................................................... 49
3.10. jOOQ and Kotlin......................................................................................................................................................................................... 49
3.11. jOOQ and NoSQL...................................................................................................................................................................................... 50
3.12. Dependencies............................................................................................................................................................................................. 50
3.13. Build your own........................................................................................................................................................................................... 51
3.14. jOOQ and backwards-compatibility...................................................................................................................................................... 51
4. SQL building...................................................................................................................................................................................................... 53
4.1. The query DSL type..................................................................................................................................................................................... 53
4.1.1. DSL subclasses.......................................................................................................................................................................................... 54
4.2. The DSLContext class.................................................................................................................................................................................. 54
4.2.1. SQL Dialect................................................................................................................................................................................................. 55
4.2.2. SQL Dialect Family.................................................................................................................................................................................... 56
4.2.3. Connection vs. DataSource.................................................................................................................................................................... 57
4.2.4. Custom data............................................................................................................................................................................................... 58
4.2.5. Custom ExecuteListeners....................................................................................................................................................................... 58
4.2.6. Custom Settings........................................................................................................................................................................................ 59
4.2.6.1. Object qualification............................................................................................................................................................................... 60
4.2.6.2. Runtime schema and table mapping............................................................................................................................................... 60
4.2.6.3. Identifier style......................................................................................................................................................................................... 63
4.2.6.4. Keyword style.......................................................................................................................................................................................... 64
4.2.6.5. Parameter types.................................................................................................................................................................................... 64
The jOOQ User Manual
© 2009 - 2018 by Data Geekery™ GmbH. Page 4 / 300
4.2.6.6. Statement Type...................................................................................................................................................................................... 65
4.2.6.7. Execute Logging..................................................................................................................................................................................... 65
4.2.6.8. Optimistic Locking................................................................................................................................................................................. 66
4.2.6.9. Auto-attach Records............................................................................................................................................................................. 66
4.2.6.10. Updatable Primary Keys.................................................................................................................................................................... 67
4.2.6.11. Reflection caching............................................................................................................................................................................... 67
4.2.6.12. Fetch Warnings.................................................................................................................................................................................... 68
4.2.6.13. Return All Columns On Store.......................................................................................................................................................... 68
4.2.6.14. Map JPA Annotations.......................................................................................................................................................................... 69
4.2.6.15. JDBC Flags............................................................................................................................................................................................. 69
4.2.6.16. IN-list Padding...................................................................................................................................................................................... 70
4.2.6.17. Backslash Escaping............................................................................................................................................................................. 71
4.2.6.18. Scalar subqueries for stored functions......................................................................................................................................... 71
4.3. SQL Statements (DML)............................................................................................................................................................................... 72
4.3.1. jOOQ's DSL and model API.................................................................................................................................................................... 72
4.3.2. The WITH clause....................................................................................................................................................................................... 74
4.3.3. The SELECT statement............................................................................................................................................................................ 75
4.3.3.1. The SELECT clause................................................................................................................................................................................ 76
4.3.3.2. The FROM clause.................................................................................................................................................................................. 77
4.3.3.3. The JOIN clause...................................................................................................................................................................................... 78
4.3.3.4. The WHERE clause................................................................................................................................................................................ 81
4.3.3.5. The CONNECT BY clause..................................................................................................................................................................... 81
4.3.3.6. The GROUP BY clause......................................................................................................................................................................... 82
4.3.3.7. The HAVING clause............................................................................................................................................................................... 83
4.3.3.8. The WINDOW clause............................................................................................................................................................................ 84
4.3.3.9. The ORDER BY clause.......................................................................................................................................................................... 84
4.3.3.10. The LIMIT .. OFFSET clause............................................................................................................................................................... 86
4.3.3.11. The WITH TIES clause........................................................................................................................................................................ 88
4.3.3.12. The SEEK clause.................................................................................................................................................................................. 88
4.3.3.13. The FOR UPDATE clause................................................................................................................................................................... 89
4.3.3.14. UNION, INTERSECTION and EXCEPT.............................................................................................................................................. 91
4.3.3.15. Oracle-style hints................................................................................................................................................................................. 92
4.3.3.16. 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.......................................................................................................................................................................... 102
4.4.3. The DROP statement............................................................................................................................................................................. 103
4.4.4. The TRUNCATE statement.................................................................................................................................................................... 104
4.4.5. Generating DDL from objects............................................................................................................................................................. 105
4.5. Catalog and schema expressions.......................................................................................................................................................... 105
4.6. Table expressions...................................................................................................................................................................................... 106
4.6.1. Generated Tables................................................................................................................................................................................... 106
4.6.2. Aliased Tables.......................................................................................................................................................................................... 107
4.6.3. Joined tables............................................................................................................................................................................................ 108
The jOOQ User Manual
© 2009 - 2018 by Data Geekery™ GmbH. Page 5 / 300
4.6.4. The VALUES() table constructor......................................................................................................................................................... 110
4.6.5. Nested SELECTs...................................................................................................................................................................................... 110
4.6.6. The Oracle 11g PIVOT clause.............................................................................................................................................................. 111
4.6.7. jOOQ's relational division syntax........................................................................................................................................................ 112
4.6.8. Array and cursor unnesting................................................................................................................................................................. 112
4.6.9. Table-valued functions.......................................................................................................................................................................... 113
4.6.10. The DUAL table.................................................................................................................................................................................... 113
4.7. Column expressions.................................................................................................................................................................................. 114
4.7.1. Table columns......................................................................................................................................................................................... 115
4.7.2. Aliased columns...................................................................................................................................................................................... 115
4.7.3. Cast expressions..................................................................................................................................................................................... 115
4.7.4. Datatype coercions................................................................................................................................................................................ 116
4.7.5. Arithmetic expressions.......................................................................................................................................................................... 117
4.7.6. String concatenation.............................................................................................................................................................................. 118
4.7.7. General functions................................................................................................................................................................................... 118
4.7.8. Numeric functions.................................................................................................................................................................................. 118
4.7.9. Bitwise functions..................................................................................................................................................................................... 119
4.7.10. String functions..................................................................................................................................................................................... 120
4.7.11. Case sensitivity with strings.............................................................................................................................................................. 121
4.7.12. Date and time functions.................................................................................................................................................................... 121
4.7.13. System functions.................................................................................................................................................................................. 121
4.7.14. Aggregate functions............................................................................................................................................................................. 122
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. SQL Parser................................................................................................................................................................................................ 146
4.12.1. SQL Parser API...................................................................................................................................................................................... 147
4.12.2. SQL Parser Grammar.......................................................................................................................................................................... 148
4.13. Names and identifiers............................................................................................................................................................................ 149
The jOOQ User Manual
© 2009 - 2018 by Data Geekery™ GmbH. Page 6 / 300
4.14. Bind values and parameters................................................................................................................................................................ 149
4.14.1. Indexed parameters............................................................................................................................................................................ 150
4.14.2. Named parameters............................................................................................................................................................................. 151
4.14.3. Inlined parameters.............................................................................................................................................................................. 151
4.14.4. SQL injection......................................................................................................................................................................................... 152
4.15. QueryParts................................................................................................................................................................................................. 153
4.15.1. SQL rendering....................................................................................................................................................................................... 153
4.15.2. Pretty printing SQL.............................................................................................................................................................................. 154
4.15.3. Variable binding.................................................................................................................................................................................... 155
4.15.4. Custom data type bindings............................................................................................................................................................... 155
4.15.5. Custom syntax elements................................................................................................................................................................... 159
4.15.6. Plain SQL QueryParts.......................................................................................................................................................................... 161
4.15.7. Serializability.......................................................................................................................................................................................... 162
4.15.8. Custom SQL transformation............................................................................................................................................................. 163
4.15.8.1. Logging abbreviated bind values.................................................................................................................................................. 163
4.16. Zero-based vs one-based APIs............................................................................................................................................................ 164
4.17. SQL building in Scala.............................................................................................................................................................................. 165
5. SQL execution................................................................................................................................................................................................ 168
5.1. Comparison between jOOQ and JDBC................................................................................................................................................ 169
5.2. Query vs. ResultQuery.............................................................................................................................................................................. 169
5.3. Fetching........................................................................................................................................................................................................ 170
5.3.1. Record vs. TableRecord......................................................................................................................................................................... 172
5.3.2. Record1 to Record22............................................................................................................................................................................ 173
5.3.3. Arrays, Maps and Lists.......................................................................................................................................................................... 173
5.3.4. RecordHandler........................................................................................................................................................................................ 174
5.3.5. RecordMapper......................................................................................................................................................................................... 174
5.3.6. POJOs......................................................................................................................................................................................................... 175
5.3.7. POJOs with RecordMappers................................................................................................................................................................ 178
5.3.8. Lazy fetching............................................................................................................................................................................................ 179
5.3.9. Lazy fetching with Streams.................................................................................................................................................................. 179
5.3.10. Many fetching........................................................................................................................................................................................ 180
5.3.11. Later fetching........................................................................................................................................................................................ 181
5.3.12. ResultSet fetching................................................................................................................................................................................ 183
5.3.13. Data type conversion.......................................................................................................................................................................... 184
5.3.14. Interning data........................................................................................................................................................................................ 185
5.4. Static statements vs. Prepared Statements........................................................................................................................................ 186
5.5. Reusing a Query's PreparedStatement................................................................................................................................................ 187
5.6. JDBC flags..................................................................................................................................................................................................... 187
5.7. Using JDBC batch operations................................................................................................................................................................. 189
5.8. Sequence execution.................................................................................................................................................................................. 190
5.9. Stored procedures and functions......................................................................................................................................................... 190
5.9.1. Oracle Packages...................................................................................................................................................................................... 192
5.9.2. Oracle member procedures................................................................................................................................................................ 193
5.10. Exporting to XML, CSV, JSON, HTML, Text......................................................................................................................................... 193
5.10.1. Exporting XML....................................................................................................................................................................................... 193
5.10.2. Exporting CSV........................................................................................................................................................................................ 194
5.10.3. Exporting JSON..................................................................................................................................................................................... 194
5.10.4. Exporting HTML.................................................................................................................................................................................... 195
5.10.5. Exporting Text....................................................................................................................................................................................... 195
5.11. Importing data.......................................................................................................................................................................................... 196
5.11.1. Importing CSV....................................................................................................................................................................................... 196
5.11.2. Importing JSON..................................................................................................................................................................................... 197
5.11.3. Importing Records............................................................................................................................................................................... 198
5.11.4. Importing Arrays................................................................................................................................................................................... 198
The jOOQ User Manual
© 2009 - 2018 by Data Geekery™ GmbH. Page 7 / 300
5.11.5. Importing XML...................................................................................................................................................................................... 199
5.12. CRUD with UpdatableRecords............................................................................................................................................................. 199
5.12.1. Simple CRUD......................................................................................................................................................................................... 199
5.12.2. Records' internal flags........................................................................................................................................................................ 201
5.12.3. IDENTITY values.................................................................................................................................................................................... 201
5.12.4. Navigation methods............................................................................................................................................................................ 202
5.12.5. Non-updatable records...................................................................................................................................................................... 203
5.12.6. Optimistic locking................................................................................................................................................................................. 203
5.12.7. Batch execution.................................................................................................................................................................................... 204
5.12.8. CRUD SPI: RecordListener................................................................................................................................................................. 205
5.13. DAOs........................................................................................................................................................................................................... 205
5.14. Transaction management...................................................................................................................................................................... 206
5.15. Exception handling.................................................................................................................................................................................. 209
5.16. ExecuteListeners...................................................................................................................................................................................... 210
5.17. Database meta data............................................................................................................................................................................... 212
5.18. Parsing Connection................................................................................................................................................................................. 213
5.19. Logging....................................................................................................................................................................................................... 213
5.20. Performance considerations................................................................................................................................................................ 214
5.21. Alternative execution models............................................................................................................................................................... 215
5.21.1. Using jOOQ with Spring's JdbcTemplate........................................................................................................................................ 215
5.21.2. Using jOOQ with JPA........................................................................................................................................................................... 216
5.21.2.1. Using jOOQ with JPA Native Query.............................................................................................................................................. 216
5.21.2.2. Using jOOQ with JPA entities......................................................................................................................................................... 217
5.21.2.3. Using jOOQ with JPA EntityResult................................................................................................................................................ 218
6. Code generation............................................................................................................................................................................................ 220
6.1. Configuration and setup of the generator......................................................................................................................................... 220
6.2. Advanced generator configuration....................................................................................................................................................... 226
6.2.1. Logging...................................................................................................................................................................................................... 227
6.2.2. Jdbc............................................................................................................................................................................................................. 227
6.2.3. Generator................................................................................................................................................................................................. 229
6.2.4. Database, name, and properties....................................................................................................................................................... 230
6.2.5. RegexFlags................................................................................................................................................................................................ 232
6.2.6. Includes and Excludes.......................................................................................................................................................................... 233
6.2.7. Include object types.............................................................................................................................................................................. 235
6.2.8. Record Version and Timestamp Fields............................................................................................................................................. 235
6.2.9. Synthetic identities................................................................................................................................................................................. 236
6.2.10. Synthetic primary keys........................................................................................................................................................................ 237
6.2.11. Override primary keys........................................................................................................................................................................ 237
6.2.12. Date as timestamp.............................................................................................................................................................................. 238
6.2.13. Ignore procedure return values (deprecated).............................................................................................................................. 239
6.2.14. Unsigned types..................................................................................................................................................................................... 239
6.2.15. Catalog and schema mapping.......................................................................................................................................................... 240
6.2.16. Catalog and schema version providers.......................................................................................................................................... 244
6.2.17. Custom ordering of generated code.............................................................................................................................................. 245
6.2.18. Forced types.......................................................................................................................................................................................... 246
6.2.19. Table valued functions........................................................................................................................................................................ 249
6.3. Programmatic generator configuration................................................................................................................................................ 250
6.4. Custom generator strategies.................................................................................................................................................................. 251
6.5. Matcher strategies..................................................................................................................................................................................... 254
6.6. Custom code sections.............................................................................................................................................................................. 256
6.7. Generated global artefacts..................................................................................................................................................................... 258
6.8. Generated tables....................................................................................................................................................................................... 259
6.9. Generated records.................................................................................................................................................................................... 260
6.10. Generated POJOs.................................................................................................................................................................................... 261
The jOOQ User Manual
© 2009 - 2018 by Data Geekery™ GmbH. Page 8 / 300
6.11. Generated Interfaces.............................................................................................................................................................................. 262
6.12. Generated DAOs...................................................................................................................................................................................... 262
6.13. Generated sequences............................................................................................................................................................................ 263
6.14. Generated procedures........................................................................................................................................................................... 263
6.15. Generated UDTs...................................................................................................................................................................................... 264
6.16. Data type rewrites................................................................................................................................................................................... 265
6.17. Custom data types and type conversion.......................................................................................................................................... 265
6.18. Custom data type binding.................................................................................................................................................................... 267
6.19. Mapping generated catalogs and schemas..................................................................................................................................... 269
6.20. Code generation for large schemas................................................................................................................................................... 270
6.21. Code generation and version control................................................................................................................................................ 270
6.22. JPADatabase: Code generation from entities................................................................................................................................... 271
6.23. XMLDatabase: Code generation from XML files.............................................................................................................................. 273
6.24. DDLDatabase: Code generation from SQL files.............................................................................................................................. 275
6.25. XMLGenerator: Generating XML.......................................................................................................................................................... 276
6.26. Running the code generator with Maven......................................................................................................................................... 277
6.27. Running the code generator with Ant............................................................................................................................................... 277
6.28. Running the code generator with Gradle......................................................................................................................................... 278
7. Tools.................................................................................................................................................................................................................. 280
7.1. JDBC mocking for unit testing................................................................................................................................................................ 280
7.2. API validation using the Checker Framework..................................................................................................................................... 282
7.3. SQL 2 jOOQ Parser................................................................................................................................................................................... 284
7.4. jOOQ Console............................................................................................................................................................................................. 285
8. Reference......................................................................................................................................................................................................... 286
8.1. Supported RDBMS..................................................................................................................................................................................... 286
8.2. Data types.................................................................................................................................................................................................... 286
8.2.1. BLOBs and CLOBs.................................................................................................................................................................................. 287
8.2.2. Unsigned integer types......................................................................................................................................................................... 287
8.2.3. INTERVAL data types............................................................................................................................................................................. 288
8.2.4. XML data types....................................................................................................................................................................................... 288
8.2.5. Geospacial data types........................................................................................................................................................................... 288
8.2.6. CURSOR data types............................................................................................................................................................................... 289
8.2.7. ARRAY and TABLE data types.............................................................................................................................................................. 289
8.2.8. Oracle DATE data type.......................................................................................................................................................................... 289
8.3. SQL to DSL mapping rules...................................................................................................................................................................... 290
8.4. jOOQ's BNF pseudo-notation................................................................................................................................................................. 293
8.5. Quality Assurance...................................................................................................................................................................................... 293
8.6. Migrating to jOOQ 3.0.............................................................................................................................................................................. 295
8.7. Credits........................................................................................................................................................................................................... 300