Home

The jOOQ™ User Manual

1. Intervals in JOOQ jOOQ fills a gap opened by JDBC which neglects an important SQL data type as defined by the SQL standards INTERVAL types See the manual s section about INTERVAL data types for more details 4 5 11 System functions This is a list of system functions supported by jOOQ s DSL CURRENT_USER Get current user 4 5 12 Aggregate functions Aggregate functions work just like functions even if they have a slightly different semantics Here are some example aggregate functions from the DSL 2009 2014 by Data Geekery GmbH All rights reserved Page 72 174 The jOOQ User Manual 4 5 12 Aggregate functions Every day SQL standard aggregate functions AggregateFunction lt Integer gt count AggregateFunction lt Integer gt count Field lt gt field AggregateFunction lt T gt max Field lt T gt field AggregateFunction lt T gt min Field lt T gt field AggregateFunction lt BigDecimal gt sum Field lt extends Number gt field AggregateFunction lt BigDecimal gt avg Field lt extends Number gt field DISTINCT keyword in aggregate functions AggregateFunction lt Integer gt countDistinct Field lt gt field AggregateFunction lt T gt maxDistinct Field lt T gt field AggregateFunction lt T gt minDistinct Field lt T gt field AggregateFunction lt BigDecimal gt sumDistinct Field lt extends Number gt field
2. Conditions or conditional expressions are widely used in SQL and in the jOOQ API They can be used in The CASE expression The JOIN clause or JOIN ON clause to be precise of a SELECT statement UPDATE statement DELETE statement The WHERE clause of a SELECT statement UPDATE statement DELETE statement The CONNECT BY clause of a SELECT statement The HAVING clause of a SELECT statement The MERGE statement s ON clause Boolean types in SQL Before SQL 1999 boolean types did not really exist in SQL They were modelled by O and 1 numeric char values With SQL 1999 true booleans were introduced and are now supported by most databases In short these are possible boolean values 1 or TRUE O or FALSE NULL or UNKNOWN It is important to know that SQL differs from many other languages in the way it interprets the NULL boolean value Most importantly the following facts are to be remembered ANY NULL yields NULL not FALSE ANY NULL yields NULL not TRUE NULL NULL yields NULL not TRUE ULL NULL yields NULL not FALSE T For simplified NULL handling please refer to the section about the DISTINCT predicate Note that jOOQ does not model these values as actual column expression compatible 4 6 1 Condition building With OO
3. RN TN 7 2 Copyright License and TAS A ta eos aula eee aed 9 3 Getting Stared A a A a a aa a AAA A EA AA A 3 A NN 3 3 2 The sample database Usd ti Ma iii di aiii 4 3 3 Different use cases for JOOQ csesessussessssssessssessssessesssssssssessecsessssssssesssssssussssessenecsussnssseesessecsssseaestessesussnsenessecsussssaseeneeseensensenseneess 5 3351 000 asa SOLE DUI A A dd dd ee da es 6 3 3 2 JOOQ as a SQL builder with code Zemerationan cccceccsscsssseeesessssessnessstssseesessecssssssesssessesssesesseessessssnssneeneesecsisssessesseeseesenseneenes 7 3 3 3 OOQ as a SQL executor 7 SA OOO RD a a a 0 8 A A O E 9 Ol 9 3 4 1 JOOQ in 7 Easy SUCDS isc sccestessccenstesscetaaduascedadisscetaccea cede a A EE EEE a ENEE SEASSA SESS lb eSa a Sa SEa SEa ia Saa S 9 era A E 9 34 12 Step A A AN A SS Code generation anien miia e A A A a A A 34 1 4 Step A Connect to YOUR data DASE iii ron ci er ON SAM A NN SAR Using OOO in O DE bedi a dis awe ata dis 3 4 3 Using jOOQ with Spring ven a8 3 44 A simple web application With JOOOQ sie ai T A IT ereo OORO hea EEA EA RCN 3 0 000 3N0 NOSO linia aia 37 DO O e e aa A O ANS 39 OOO AA DAI NA dali lada 4 SOL Pull rd Al Me query DS Pei dado as A DE Ud a a ed AA A SE O A SOLD AG CU cosas na a tenant lala lid A 22 COMMEGUON WS DAS UE A A Neon 4 2 3 CUSTOM Gata ccc 4 2 4 Custom ExecuteListeners 4 225 CUSTOM A AAN 4 2 6 Runtime schema and table mapping 74 is
4. Loop over records returned from a SELECT statement for BookRecord book create fetch BOOK BOOK PUBLISHED_IN equal 1948 Perform actions on BookRecords depending on some conditions if Orwell equals book fetchParent Keys FK_BOOK_AUTHOR getLastName book delete 5 11 2 Records internal flags All of jOOQ s Record types and subtypes maintain an internal state for every column value This state is composed of three elements The value itself The original value i e the value as it was originally fetched from the database or null if the record was never in the database The changed flag indicating if the value was ever changed through the Record API The purpose of the above information is for OOQ s CRUD operations to know which values need to be stored to the database and which values have been left untouched 5 11 3 IDENTITY values Many databases support the concept of IDENTITY values or SEQUENCE generated key values This is reflected by JDBC s getGeneratedKeys method jOOQ abstracts using this method as many databases and JDBC drivers behave differently with respect to generated keys Let s assume the following SQL Server BOOK table CREATE TABLE book ID INTEGER IDENTITY 1 1 NOT NULL lesoll CONSTRAINT pk_book PRIMARY KEY id If you re using OOQ s code generator the above table will generate a org joog UpdatableRe
5. This column indicates when each book record was modified for the last time MODIFIED TIMESTAMP NOT NULL fleas The MODIFIED column will contain a timestamp indicating the last modification timestamp for any book in the BOOK table If you re using jOOQ and it s store methods on UpdatableRecords jOOQ will then generate this TIMESTAMP value for you automatically However instead of running an additional with an example Properly configure the DSLContext DSLContext optimistic DSL using connection SQLDialect ORACLE new Settings withExecuteWithOptimisticLocking true Fetch a book two times BookRecord bookl optimistic fetch BOOK BOOK ID equal 5 BookRecord book2 optimistic fetch BOOK BOOK ID equal 5 Change the title and store this book The MODIFIED value has not been changed since the book was fetched It can be safely updated book1 setTitle Animal Farm bookl store Book2 still references the original MODIFIED value but the database holds a new value from bookl store a store wikl thus fail book2 setTitle 1984 book2 store As before without the added TIMESTAMP column optimistic locking is transparent to the API Optimised optimistic locking using VERSION fields SELECT FOR UPDATE statement prior to an UPDATE or DELETE statement jOOQ adds a WHERE cla
6. Use any String literal to identify your custom data configuration data com example my namespace no inserts true Try to execute an INSERT statement try DSL using configuration insertInto AUTHOR AUTHOR ID AUTHOR LAST_NAME values 1 Orwell execute You shouldn t get here Assert fail Your NoInsertListener should be throwing this exception here catch DataAccessException expected Assert assertEquals No INSERT statements allowed expected getMessage Using the data methods you can store and retrieve custom data in your Configurations 4 2 4 Custom ExecuteListeners ExecuteListeners are a useful tool to implement custom logging apply triggers written in Java collect query execution statistics integrate with the OOQ Console ExecuteListeners are hooked into your Configuration org joog ExecuteListenerProvider Create your Configuration Configuration configuration new DefaultConfiguration set connection set dialect Hook your listener providers into the configuration configuration set new DefaultExecuteListenerProvider new MyFirstListener new DefaultExecuteListenerProvider new PerformanceLoggingListener new DefaultExecuteListenerProvider new NoInsertListener O 2009 2014 by Data Geekery GmbH All rights reserved returning them Now the above listener can be added to your Configuration but you wi
7. 2 Page 98 174 The jOOQ User Manual 4 9 3 Variable binding The section about Executel isteners shows an example of how such pretty printing can be used to log readable SQL to the stdout 4 9 3 Variable binding Every org joog QueryPart must implement the bind BindContext method This BindContext has two purposes It provides some information about the state of the variable binding in process It provides a common API for binding values to the context s internal java sql PreparedStatement An overview of the org joog RenderContext API is given here This method provides access to the PreparedStatement to which bind values are bound PreparedStatement statement These methods provide convenience to delegate variable binding BindContext bind QueryPart part throws DataAccessException BindContext bind Collection lt extends QueryPart gt parts throws DataAccessException BindContext bind QueryPart parts throws DataAccessException These methods perform the actual variable binding BindContext bindValue Object value Class lt gt type throws DataAccessException BindContext bindValues Object values throws DataAccessException Some additional methods are inherited from a common org joog Context which is shared among org joog RenderContext and org joog BindContext Details are documented in the previous chapter about SQL rendering An example of binding valu
8. fetch AUTHOR DATE_OF_BIRTH 6 13 Mapping generated schemata and tables We ve seen previously in the chapter about runtime schema mapping that schemata and tables can be mapped at runtime to other names But you can also hard wire schema mapping in generated artefacts at code generation time e g when you have 5 developers with their own dedicated developer databases and a common integration database In the code generation configuration you would then write lt schemata gt lt schema gt US Sci as Che developeris schemat lt gt lt inputSchema gt LUKAS_DEV_SCHEMA lt inputSchema gt lt Use this as the integration production database gt lt outputSchema gt PROD lt outputSchema gt lt schema gt lt schemata gt 6 14 Code generation for large schemas Databases can become very large in real world applications This is not a problem for jOOQ s code generator but it can be for the Java compiler OOQ generates some classes for global access These classes can hit two sorts of limits ofthe compiler JVM Methods including static instance initialisers are allowed to contain only 64kb of bytecode Classes are allowed to contain at most 64k of constant literals While there exist workarounds for the above two limitations delegating initialisations to nested classes inheriting constant literals from implemented interfaces the preferred approach is either one of these
9. 4 2 Brida A Now if you have millions of records with only few distinct values for AUTHOR_ID you may not want to hold references to distinct but equal java lang Integer objects This is specifically true for IDs of type java util UUID or string representations thereof OOQ allows you to intern those values Interning data after fetching Result lt gt rl create select BOOK ID BOOK AUTHOR_ID BOOK TITLE from BOOK join AUTHOR on BOOK AUTHOR_ID eq AUTHOR ID fetch intern BOOK AUTHOR_ID Interning data while fetching Result lt gt rl create select BOOK ID BOOK AUTHOR_ID BOOK TITLE from BOOK Join AUTHOR on BOOK AUTHOR_ID eq AUTHOR ID intern BOOK AUTHOR_ID sfetoh i7 You can specify as many fields as you want for interning The above has the following effect If the interned Field is of type java lang String then String intern is called upon each string Ifthe interned Field is of any other type then the call is ignored Future versions of jOOQ will implement interning of data for non String data types by collecting values in java util Set removing duplicate instances Note that jOOQ will not use interned data for identity comparisons string1 string2 Interning is used only to reduce the memory footprint of org joog Result objects 5 4 Static statements vs Prepared Statements With JDBC you have full control ove
10. As previously discussed in the chapter about differences between jOOQ and JDBC jOOQ does not rely on an internal state of any JDBC object which is externalised by Javadoc Instead it has a straight forward API allowing you to do the above in a one liner Get some information about the author table its columns keys indexes etc List lt Result lt Record gt gt results create fetchMany sp_help author Using generics the resulting structure is immediately clear O 2009 2014 by Data Geekery GmbH All rights reserved Page 114 174 The jOOQ User Manual 5 3 9 Later fetching Using Java 8 CompletableFutures Java 8 has introduced the new java util concurrent CompletableFuture type 5 3 9 Later fetching which allows for functional composition of asynchronous execution units When applying this to SQL and jOOQ you might be writing code as follows Initiate an asynchronous call chain CompletableFuture This lambda will supply an int value indicating the number of inserted rows supplyAsync gt DSL using configuration insertInto AUTHOR AUTHOR ID AUTHOR LAST_NAME values 3 Hitchcock execute This will supply an AuthorRecord value for the newly inserted author handleAsync rows throwable gt DSL using configuration fetchOne AUTHOR AUTHOR ID eq 3 This should supply an int value indicating the number of rows but in fact it ll throw a const
11. Combine conditions with AND Convenience for adding an exists predicate to the rhs andNot Condition Combine conditions with AND Convenience for adding an inverted condition to the rhs andNotExists Select lt gt Combine conditions with AND Convenience for adding an inverted exists predicate to the rhs or Condition Combine conditions with OR or String Combine conditions with OR Convenience for adding plain SQL to the right hand side or String ODJECE Combine conditions with OR Convenience for adding plain SQL to the right hand side or String QueryPart Combine conditions with OR Convenience for adding plain SOL to the right hand side orExists Select lt gt Combine conditions with OR Convenience for adding an exists predicate to the rhs orNot Condition Combine conditions with OR Convenience for adding an inverted condition to the rhs orNotExists Select lt gt Combine conditions with OR Convenience for adding an inverted exists predicate to the rhs not Invert a condition synonym for DSL not Condition 4 6 3 Comparison predicate In SQL comparison predicates are formed using common comparison operators to test for equality lt or to test for non equality gt to test for being strictly greater gt to test for being greater or equal lt to test for being strictly less lt to test for being less or equal Unfortunately Java does not support op
12. 2009 2014 by Data Geekery GmbH All rights reserved Page 159 174 The jOOQ User Manual 6 15 Code generation and version control Distribute your database objects in several schemas That is probably a good idea anyway for such large databases Configure jOOQ s code generator to exclude excess database objects Configure OOQ s code generator to avoid generating global objects using lt globalObjectReferences gt Remove uncompilable classes after code generation 6 15 Code generation and version control When using jOOQ s code generation capabilities you will need to make a strategic decision about whether you consider your generated code as Part of your code base Derived artefacts In this section we ll see that both approaches have their merits and that none of them is clearly better Part of your code base When you consider generated code as part of your code base you will want to Check in generated sources in your version control system Use manual source code generation Possibly use even partial source code generation This approach is particularly useful when your Java developers are not in full control of or do not have full access to your database schema or if you have many developers that work simultaneously on the same database schema which changes all the time It is also useful to be able to track side effects of database changes as your checked in database schema
13. O 2009 2014 by Data Geekery GmbH All rights reserved Page 23 174 The jOOQ User Manual 3 4 1 7 Step 7 Explore package test For convenience always static import your generated tables and 3000 functions to decrease verbosity import static test generated Tables import static org jooq impl DSL import java sql import org jooq import org jooq impl public class Main pr param args cif public static void main String args Connection conn null String userName root String password String url jdbc mysql localhost 3306 guestbook try Class forName com mysql jdbc Driver newInstance conn DriverManager getConnection url userName password DSLContext create DSL using conn SQLDialect MYSQL Result lt Record gt result create select from POSTS fetch for Record r result Long id r getValue POSTS ID String title r getValue POSTS TITLE String description r getValue POSTS BODY System out printen SID dy T tities f title desciption A description catch Exception e For the sake of this tutorial let s keep exception handling simple e printStackTrace finally iE Conn t nuri try conn close catch SQLException ignore 3 4 1 7 Step 7 Explore jOOQ has grown to be a comprehensive SQL library For more information please consider the documentation http www joog org
14. The INSERT statement is used to insert new records into a database table Records can either be supplied using a VALUES constructor or a SELECT statement jOOQ supports both types of INSERT statements An example of an INSERT statement using a VALUES constructor is given here INSERT INTO AUTHOR create insertInto AUTHOR ID FIRST_NAME LAST_NAME AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME VALUES 100 Hermann Hesse values 100 Hermann Hesse Note that for explicit degrees up to 22 the VALUES constructor provides additional typesafety The following example illustrates this InsertValuesStep3 lt AuthorRecord Integer String String gt step create insertInto AUTHOR AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME ESO ae Ce CME Miche Doesn t compile the expected type is Integer INSERT multiple rows with the VALUES constructor The SQL standard specifies that multiple rows can be supplied to the VALUES constructor in an INSERT statement Here s an example of a multi record INSERT INSERT INTO AUTHOR create insertInto AUTHOR ID FIRST_NAME LAST_NAME AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME VALUES 100 Hermann Hesse values 100 Hermann Hesse TOW UAE ne diy ae Dob de mus values 101 Alfred D blin jOOQ tries to stay close to actual SQL In detail however Java s expressiveness is limited That s why the val
15. This may be useful if you do not want to repeat a lengthy expression by which you want to order although most databases also allow for referencing aliased column references in the ORDER BY clause An example of this is given here SELECT AUTHOR_ID TITLE create select BOOK AUTHOR_ID BOOK TITLE FROM BOOK from BOOK ORDER BY 1 ASC 2 DESC orderBy one asc inline 2 desc Note how one is used as a convenience short cut for inline 1 O 2009 2014 by Data Geekery GmbH All rights reserved Page 47 174 The jOOQ User Manual 4 3 2 8 The ORDER BY clause Ordering and NULLS A few databases support the SQL standard null ordering clause in sort specification lists to define whether NULL values should come first or last in an ordered result SELECT create select AUTHOR FIRST_NAME AUTHOR FIRST_NAME AUTHOR LAST_NAME AUTHOR LAST_NAME FROM AUTHOR from AUTHOR ORDER BY LAST_NAME ASC orderBy AUTHOR LAST_NAME asc FIRST_NAME ASC NULLS LAST AUTHOR FIRST_NAME asc nullsLast If your database doesn t support this syntax OOQ emulates it using a CASE expression as follows SELECT AUTHOR FIRST_NAME AUTHOR LAST_NAME FROM AUTHOR ORDER BY LAST_NAME ASC CASE WHEN FIRST_NAME IS NULL THEN 1 ELSE 0 END ASC FIRST_NAME ASC Ordering using CASE expressions Using CASE expressions in SQL ORDER BY clauses is a common pattern if you want to introduce some sort indirection sort mappin
16. impl JooqLogger 5 5 included impl JooqLogger info info info 0 excluded info 0 excluded info C workspace MySQLTest src test generated tables impl JooqLogger 0 0 included impl JooqLogger 0 0 included impl JooqLogger 0 0 included impl JooqLogger Posts java impl JooqLogger info 0 excluded info 0 excluded info 0 excluded info info Total 680 464ms 558 284ms impl JooqLogger info C workspace MySQLTest src test generated tables impl JooqLogger info MORAS OLAS t383157ms impl JooqLogger info C workspace MySQLTest src test generated tables records impl JooqLogger info PostsRecord java impl JooqLogger info Total 782 545ms 63 924ms impl JooqLogger 0 0 included impl JooqLogger 0 0 included impl JooqLogger info 0 excluded info 0 excluded info Total 791 688ms 9 143ms 3 4 1 4 Step 4 Connect to your database Let s just write a vanilla main class in the project containing the generated classes 2009 2014 by Data Geekery GmbH All rights reserved Page 22 174 The jOOQ User Manual For import import import public 3 4 1 5 Step 5 Querying convenience always static import your generated tables and j00Q functions to decrease verbosity Stata cucest generated lab lest static org jooq impl DSL av aS quis class Main public static void main String args This is 3 4 Connection conn nul
17. jooq 3 0 1 Jjar The main library that you will include in your application to run jOOQ joog meta 3 0 1 jar The utility that you will include in your build to navigate your database schema for code generation This can be used as a schema crawler as well joog codegen 3 0 1 jar The utility that you will include in your build to generate your database schema Configure JOOQ s code generator You need to tell OOQ some things about your database connection Here s an example of how to do it for an Oracle database 2009 2014 by Data Geekery GmbH All rights reserved Page 142 174 The jOOQ User Manual lt xml version 1 0 encoding UTF 8 standalone yes gt lt configuration gt lt Configure the database connection here gt lt jdbe gt lt driver gt oracle jdbc OracleDriver lt driver gt lt url gt jdbc oracle thin your jdbc connection parameters lt url gt lt user gt your database user lt user gt lt password gt your database password lt password gt 6 1 Configuration and setup of the generator lt You can also pass user password and other JDBC properties in the optional properties tag gt lt properties gt lt property gt lt key gt user lt key gt lt value gt db user lt value gt lt property gt lt property gt lt key gt password lt key gt lt value gt db password lt value gt lt property gt lt properties gt lt jdbc gt lt generat
18. lt GROUP BY 4 4 4 CUBE explained in SQL CUBE is different from ROLLUP in the way that it doesn t just create N 1 groupings it creates all 24N possible combinations between all group fields in the CUBE function argument list Let s re consider our second query from before O 2009 2014 by Data Geekery GmbH All rights reserved Page 76 174 The jOOQ User Manual 4 5 14 Grouping functions ON ALL CUBE with two arguments The same query using UNI SELECT AUTHOR_ID PUBLISHED_IN COUNT SELECT AUTHOR_ID PUBLISHED_IN COUNT FROM BOOK FROM BOOK GROUP BY UNION ALL SELECT AUTHOR_ID FROM BOOK GROUP BY UNION ALL GROUP BY CUBE AUTHOR_ID PUBLISHED_IN AUTHOR_ID PUBLISHED_IN NULL COUNT AUTHOR_ID SELECT NULL PUBLISHED_IN COUNT FROM BOOK GROUP BY UNION ALL SELECT NULL NULL COUNT FROM BOOK GROUP BY ORDER BY 1 NULLS FIRST PUBLI The results would then hold 4 4 4 AUTHOR_ID PUBLISHED_IN COUNT 4 4 4 NULL NULL 2 lt GROUP BY NULL 1945 1 lt GROUP BY PUBLISHED_IN NULL 1948 1 lt GROUP BY PUBLISHED_IN NULL 1988 1 lt GROUP BY PUBLISHED_IN NULL 1990 1 lt GROUP BY PUBLISHED_IN T NULL 2 lt GROUP BY AUTHOR_ID i 1945 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN 1 1948 1 lt GROUP BY AUT
19. 2 4 will yield true in jOOQ This is emulated as such gt This predicate A B OVERLAPS C D can be emulated as such C lt B AND A lt D 4 7 Plain SQL A DSL is a nice thing to have it feels fluent and natural especially if it models a well known language such as SQL But a DSL is always expressed in a host language Java in this case which was not made for exactly the same purposes as its hosted DSL If it were then jOOQ would be implemented on a compiler level similar to LINQ in NET But it s not and so the DSL is limited by language constraints of its host language We have seen many functionalities where the DSL becomes a bit verbose This can be especially true for 2009 2014 by Data Geekery GmbH All rights reserved Page 91 174 The jOOQ User Manual 4 7 Plain SQL aliasing nested selects arithmetic expressions casting You ll probably find other examples If verbosity scares you off don t worry The verbose use cases for jOOQ are rather rare and when they come up you do have an option Just write SQL the way you re used to jOOQ allows you to embed SQL as a String into any supported statement in these contexts Plain SQL as a conditional expression Plain SQL as a column expression Plain SQL as a function Plain SQL as a table expression Plain SQL as a query The DSL plain SQL API Plain SQL API methods are usual
20. AUTHOR LEFT OUTER JOIN BOOK leftOuterJoin BOOK PARTITION BY PUBLISHED_IN partitionBy BOOK PUBLISHED_IN ON BOOK AUTHOR_ID AUTHOR ID on BOOK AUTHOR_ID equal AUTHOR ID 4 3 2 4 The WHERE clause The WHERE clause can be used for JOIN or filter predicates in order to restrict the data returned by the table expressions supplied to the previously specified from clause and join clause Here is an example SELECT create select FROM BOOK from BOOK WHERE AUTHOR_ID 1 where BOOK AUTHOR_ID equal 1 AND TITLE 1984 and BOOK TITLE equal 1984 The above syntax is convenience provided by jOOQ allowing you to connect the org jooq Condition supplied in the WHERE clause with another condition using an AND operator You can of course also create a more complex condition and supply that to the WHERE clause directly observe the different placing of parentheses The results will be the same SELECT create select FROM BOOK from BOOK WHERE AUTHOR_ID 1 where BOOK AUTHOR_ID equal 1 and AND TITLE 1984 BOOK TITLE equal 1984 You will find more information about creating conditional expressions later in the manual 2009 2014 by Data Geekery GmbH All rights reserved Page 44 174 The jOOQ User Manual 4 3 2 5 The CONNECT BY clause 4 3 2 5 The CONNECT BY clause The Oracle database knows a very succinct synta
21. AggregateFunction lt BigDecimal gt avgDistinct Field lt extends Number gt field String aggregate functions AggregateFunction lt String gt groupConcat Field lt gt field AggregateFunction lt String gt groupConcatDistinct Field lt gt field OrderedAggregateFunction lt String gt listAgg Field lt gt field OrderedAggregateFunction lt String gt listAgg Field lt gt field String separator Statistical functions AggregateFunction lt BigDecimal gt median Field lt extends Number gt field AggregateFunction lt BigDecimal gt stddevPop Field lt extends Number gt field AggregateFunction lt BigDecimal gt stddevSamp Field lt extends Number gt field AggregateFunction lt BigDecimal gt varPop Field lt extends Number gt field AggregateFunction lt BigDecimal gt varSamp Field lt extends Number gt field Linear regression functions AggregateFunction lt BigDecimal gt regrAvgX AggregateFunction lt BigDecimal gt regrAvgY Field lt extends Number gt y Field lt extends Number gt Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigDecimal gt regrCount Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigDecimal gt regriIntercept Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigDecimal gt regrR2 Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigD
22. Enabling DEBUG logging of all executed SOL Please refer to the jOOQ runtime configuration XSD for more details http www joog org xsd joog runtime 3 0 0 xsd 4 2 6 Runtime schema and table mapping Mapping your DEV schema to a productive environment You may wish to design your database in a way that you have several instances of your schema This is useful when you want to cleanly separate data belonging to several customers organisation units branches users and put each of those entities data in a separate database or schema In our AUTHOR example this would mean that you provide a book reference database to several companies such as My Book World and Books R Us In that case you ll probably have a schema setup like this 2009 2014 by Data Geekery GmbH All rights reserved Page 35 174 The jOOQ User Manual 4 2 6 Runtime schema and table mapping DEV Your development schema This will be the schema that you base code generation upon with jOOQ MY_BOOK_WORLD The schema instance for My Book World BOOKS_R_US The schema instance for Books R Us Mapping DEV to MY_BOOK_WORLD with jOOQ When a user from My Book World logs in you want them to access the MY_BOOK_WORLD schema using classes generated from DEV This can be achieved with the org joog conf RenderMapping class that you can equip your Configuration s settings with Take the following example Settings settings
23. Often with jOOQ it is even more useful to compare SQL code with its corresponding Java jOOQ code When this is done the blocks are aligned side by side with SQL usually being on the left and an equivalent jOOQ DSL query in Java usually being on the right In SQL 44 Using JOO SELECT 1 FROM DUAL create selectOne Code block contents The contents of code blocks follow conventions too If nothing else is mentioned next to any given code block then the following can be assumed SQL assumptions BE nothing Slse is specified assume that idas Oracle syntax is used SELECT 1 FROM DUAL O 2009 2014 by Data Geekery GmbH All rights reserved Page 13 174 The jOOQ User Manual 3 2 The sample database used in this manual Java assumptions _ Whenever you see standalone functions assume they were static imported from org jooq impl DSL DSL is the entry point of the static query DSL exists max min val inline correspond to DSL exists DSL max DSL min etc Whenever you see BOOK Book AUTHOR Author and similar entities assume they were static imported from the generated schema BOOK TITLE AUTHOR LAST_NAME correspond to com example generated Tables BOOK TITLE com example generated Tables BOOK TITLE FK_BOOK_AUTHOR corresponds to com example generated Keys FK_BOOK_AUTHOR Whenever you see create being used in Java code assume that this is an instance o
24. Then create a guestbook xml that looks like this O 2009 2014 by Data Geekery GmbH All rights reserved Page 20 174 The jOOQ User Manual 3 4 1 3 Step 3 Code generation lt xml version 1 0 encoding UTF 8 standalone yes gt lt configuration xmlns http www jooq org xsd joog codegen 3 0 0 xsd gt ICON ta qurestue database connection here gt lt jdbc gt lt driver gt com mysql jdbc Driver lt driver gt lt url gt jdbc mysql localhost 3306 guestbook lt url gt lt user gt root lt user gt lt password gt lt password gt lt jdbc gt lt generator gt lt The default code generator You can override this one to generate your own code style Defaults to org jooq util JavaGenerator gt lt name gt org jooq util JavaGenerator lt name gt lt database gt lt The database type The format here is org util database database Database gt lt name gt org jooq util mysql MySQLDatabase lt name gt lt The database schema or in the absence of schema support in your RDBMS this can be the owner user database name to be generated gt lt inputSchema gt guestbook lt inputSchema gt lt t AlI elements that are generated from your schema A Java regular expression Use the pipe to separate several expressions Watch out for case sensitivity Depending on your database this might be important gt lt includes gt lt includes gt lt All elements that are exclude
25. data types from their database tables OOQ s enum mapping and converter features sufficiently cover interacting with such user defined types The DSL subtypes are no longer instanciable As DSL now only contains static methods subclassing is no longer useful There are still dialect specific DSL types providing static methods for dialect specific functions But the code generator no longer generates a schema specific DSL The concept of a main key is no longer supported The code generator produces UpdatableRecords only if the underlying table has a PRIMARY KEY The reason for this removal is the fact that main keys are not reliable enough They were chosen arbitrarily among UNIQUE EYS he UpdatableTable type has been removed While adding significant complexity to the type ierarchy this type adds not much value over a simple Table getPrimaryKey null check he USE statement support has been removed from jOOQ Its behaviour was ill defined while it idn t work the same way or didn t work at all in some databases SM TDT AX 8 6 Credits jOOQ lives in a very challenging ecosystem The Java to SQL interface is still one of the most important system interfaces Yet there are still a lot of open questions best practices and no true standard has been established This situation gave way to a lot of tools APIs utilities which essentially tackle the same problem
26. java sql CallableStatement This statement type is used for SQL statements that are called rather than executed In particular this includes calls to stored procedures Callable statements can register OUT parameters Today the JDBC API may look weird to users being used to object oriented design While statements hide a lot of SQL dialect specific implementation details quite well they assume a lot of knowledge about the internal state of a statement For instance you can use the PreparedStatement addBatch method to adda the prepared statement being created to an internal list of batch statements Instead of returning a new type this method forces user to reflect on the prepared statement s internal state or mode jOOQ is wrapping JDBC These things are abstracted away by jOOQ which exposes such concepts in a more object oriented way For more details about jOOQ s batch query execution see the manual s section about batch execution The following sections of this manual will show how jOOQ is wrapping JDBC for SQL execution 5 1 Comparison between JOOQ and JDBC Similarities with JDBC Even if there are two general types of Query there are a lot of similarities between JDBC and jOOQ Just to name a few Both APIs return the number of affected records in non result queries JDBC Statement executeUpdate OOQ Query execute Both APIs return a scrollable resul
27. or the LIKE predicate Variable binding behaved unpredictably as IS NULL predicates don t bind any variables The generated SQL depended on the possible combinations of bind values which creates unnecessary hard parses every time a new unique SQL statement is rendered Here is an example how to check if a field has a given value without applying SQL s ternary NULL logic String possiblyNull null Or else 3000 2 6 Condition conditionl BOOK TITLE equal possiblyNull 3000 3 0 Condition condition2 Condition condition3 BOOK TITLE equal possiblyNull or BOOK TITLE isNull and val possiblyNull isNull BOOK TITLE isNotDistinctFrom possiblyNull Configuration DSLContext ExecuteContext RenderContext BindContext no longer extend Configuration for convenience From jOOQ 3 0 onwards composition is chosen over inheritance as these objects are not really configurations Most importantly DSLContext is only a DSL entry point for constructing attached QueryParts ExecuteContext has a well defined lifecycle tied to that of a single query execution RenderContext has a well defined lifecycle tied to that of a single rendering operation BindContext has a well defined lifecycle tied to that of a single variable binding operation In order to resolve confusion that used to arise because of different lifecycle durations these types are n
28. orderBy books AUTHOR ID 4 4 6 The Oracle 11g PIVOT clause If you are closely coupling your application to an Oracle database you can take advantage of some Oracle specific features such as the PIVOT clause used for statistical analyses The formal syntax definition is as follows A e e FROM table PIVOT aggregateFunction aggregateFunction FOR column IN expression expression WAEBE e The PIVOT clause is available from the org jooq Table type as pivoting is done directly on a table Currently only Oracle s PIVOT clause is supported Support for SQL Server s slightly different PIVOT clause will be added later Also OOQ may emulate PIVOT for other dialects in the future 4 4 7 jOOQ s relational division syntax There is one operation in relational algebra that is not given a lot of attention because it is rarely used in real world applications It is the relational division the opposite operation of the cross product or relational multiplication The following is an approximate definition of a relational division Assume the following cross join cartesian product G AxB Then it can be said that A EE B E Q With jOOQ you can simplify using relational divisions by using the following syntax C dividegy B Omi ID equal B C ID returning C TEXT The above roughly translates to SELECT DISTINCT C TEXT FROM C cl WHERE NOT EXISTS SELECT 1 FROM B WHERE NOT EXISTS SELECT 1 FROM C
29. r1 ROUTINE_NAME r1 SPECIFIC_NAME Ignore the data type when there is at least one out parameter decode when exists selectOne from PARAMETERS where PARAMETERS SPECIFIC_SCHEMA equal r1 SPECIFIC_SCHEMA and PARAMETERS SPECIFIC_NAME equal r1 SPECIFIC_NAME and upper PARAMETERS PARAMETER_MODE notEqual IN val void otherwise r1 DATA_TYPE as data_type r1 CHARACTER_MAXIMUM_LENGTH r1 NUMERIC_PRECISION r1 NUMERIC_SCALE r1 TYPE_UDT_NAME Calculate overload index if applicable decode when exists selectOne from r2 where r2 ROUTINE_SCHEMA in getInputSchemata and r2 ROUTINE_SCHEMA equal r1 ROUTINE_SCHEMA and r2 ROUTINE_NAME equal r1 ROUTINE_NAME and r2 SPECIFIC_NAME notEqual r1 SPECIFIC_NAME select count RETOM TZ where r2 ROUTINE_SCHEMA in getInputSchemata and r2 ROUTINE_SCHEMA equal r1 ROUTINE_SCHEMA and r2 ROUTINE_NAME equal r1 ROUTINE_NAME and r2 SPECIFIC_NAME lessOrEqual r1 SPECIFIC_NAME asField as overload Erom r1 where r1 ROUTINE_SCHEMA in getInputSchemata OrderBy r1 ROUTINE_SCHEMA asc r1 ROUTINE_NAME asc clica g result add new PostgresRoutineDefinition this record These rather complex queries show that the OOQ API is fit for advanced SQL use cases compared to the rather simple often unrealistic queries in the integration test suite Clean API and implementatio
30. super AUTHOR_EXISTS TEST addInParameter AUTHOR_NAME addOutParameter RESULT Every IN and IN OUT parameter generates a setter public void setAuthorName String value setValue AUTHOR_NAME value Every IN OUT OUT and RETURN_VALUE generates a getter public BigDecimal getResult return getValue RESULT WH Weal Package and member procedures or functions Every procedure or function routine in your database will generate a org joog Routine implementation Procedures or functions contained in packages or UDTs are generated in a sub package that corresponds to the package or UDT name Flags controlling routine generation Routine generation cannot be deactivated 6 11 Generated UDTs Every UDT in your database will generate a org joog UDT implementation that looks like this O 2009 2014 by Data Geekery GmbH All rights reserved Page 157 174 The jOOQ User Manual 6 12 Custom data types and type conversion public class AddressType extends UDTImpl lt AddressTypeRecord gt The singleton UDT instance public static final UAddressType U_ADDRESS_TYPE new UAddressType Every UDT attribute generates a static member public static final UDTField lt AddressTypeRecord String gt ZIP createField ZIP SQLDataType VARCHAR U_ADDRESS_TYPE public static final UDTField lt AddressTypeRecord String gt CITY createField CITY SQLDataType VARCHAR U_ADDRESS_TYPE pub
31. 70 A NON 74 O 2009 2014 by Data Geekery GmbH All rights reserved Page 6 174 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 n more advanced querying use cases more th
32. API The term fetch is always reused in jOOQ when you can fetch data from the database An org joog ResultQuery provides many overloaded means of fetching data Various modes of fetching These modes of fetching are also documented in subsequent sections of the manual The standard fetch Result lt R gt fetch The standard fetch when you know your query returns only one record R fetchOne The standard fetch when you only want to fetch the first record R fetchAny Create a lazy Cursor that keeps an open underlying JDBC ResultSet Cursor lt R gt fetchLazy Cursor lt R gt fetchLazy int fetchSize Create a java util concurrent Future to handle asynchronous execution of the ResultQuery FutureResult lt R gt fetchLater FutureResult lt R gt fetchLater ExecutorService executor Fetch several results at once List lt Result lt Record gt gt fetchMany Fetch records into a custom callback lt H extends RecordHandler lt R gt gt H fetchInto H handler Map records using a custom callback lt E gt List lt E gt fetch RecordMapper lt super R E gt mapper Execute a ResultQuery with j000 but return a JDBC ResultSet not a j000 object ResultSet fetchResultSet Fetch convenience These means of fetching are also available from org jooq Result and org joog Record APIs 2009 2014 by Data Geekery GmbH All rights reserved Page 106 174 The jOOQ
33. Condition Condition Condition Condition Condition Condition Condition Condition Condition Condition adding overloaded The following depicts a trait which wraps numeric fields A Scala esque representation of link org jooq Field operators for common j00Q operations to numeric fields Fi trait SNumberField T lt Arithmetic operations def def def def def def def def def def def def def def def def def def def def def der An example query using such overloaded operators would then look like this unary_ value value value value value value value value unary_ amp value amp value value value value value lt lt value lt lt value gt gt value gt gt value Number Field _ lt Number Field _ lt Number Field _ lt Number ECN Number Field _ lt 1 Field T 1 Field T T Field T T Field T T Field T Number Number Number Number Number Number extends SAnyField T Dio lip FierafT Ii ae PielaiT Field T n io lip Pietat Fietda T Fristat FierafT EirstarT FPrSLOFT Dio ab ialzssilrod ue Fielat Dilo pde FierafT Fielat Eield T Bi io n FistalT Ere rapi O 2009 2014 by Data Geekery GmbH All rights reserved 1 adding overloaded
34. Define your converter public class YNMConverter extends EnumConverter lt String YNM gt public YNMConverter super String class YNM class And you re all set for converting records to your custom Enum for BookRecord book create selectFrom BOOK fetch switch book getValue BOOK I_LIKE new YNMConverter case YES System out println I like this book book getTitle break case NO System out println I didn t like this book book getTitle break case MAYBE System out println I m not sure about this book book getTitle break Using Converters in generated source code jOOQ also allows for generated source code to reference your own custom converters in order to permanently replace a table column s lt T gt type by your own custom lt U gt type See the manual s section about custom data types for details 5 3 12 Interning data SQL result tables are not optimal in terms of used memory as they are not designed to represent hierarchical data as produced by JOIN operations Specifically FOREIGN KEY values may repeat themselves unnecessarily 2009 2014 by Data Geekery GmbH All rights reserved Page 118 174 The jOOQ User Manual 5 4 Static statements vs Prepared Statements ID AUTHOR ID C TITLE Pl 1 1984 ae 1 Animal Farm 3h Il 2 O Alquimista
35. Firebird 2 5 21 3 SQLDB 2 2 ngres 10 1 ariaDB 5 2 ySQL 5 5 Oracle 11g PostgreSQL 9 0 SQLite with Xerial JDBC driver SQL Azure SQL Server 2008 R8 Sybase Adaptive Server Enterprise 15 5 Sybase SQL Anywhere 12 For an up to date list of currently supported RDBMS please refer to http www jooq org legal icensing databases 8 2 Data types There is always a small mismatch between SQL data types and Java data types This is for two reasons SQL data types are insufficiently covered by the JDBC API Java data types are often less expressive than SQL data types This chapter should document the most important notes about SQL JDBC and jOOQ data types O 2009 2014 by Data Geekery GmbH All rights reserved Page 165 174 The jOOQ User Manual 8 2 1 BLOBs and CLOBs 8 2 1 BLOBs and CLOBs jOOQ currently doesn t explicitly support JDBC BLOB and CLOB data types If you use any of these data types in your database jOOQ will map them to byte and String instead In simple cases small data this simplification is sufficient In more sophisticated cases you may have to bypass OOQ in order to deal with these data types and their respective resources True support for LOBs is on the roadmap though 8 2 2 Unsigned integer types Some databases explicitly support unsigned integer data types In most normal JDBC based applications they would just be
36. GenerationTool jooq config xml 2009 2014 by Data Geekery GmbH All rights reserved Page 143 174 The jOOQ User Manual 6 1 Configuration and setup of the generator Be sure that these elements are located on the classpath The XML configuration file jooq 3 0 1 jar jooq meta 3 0 1 jar jooq codegen 3 0 1 jar The JDBC driver you configured A command line example For Windows unix linux etc will be similar Put the property file jooq jar and the JDBC driver into a directory e g C temp jooq z Go to CMempljooq Run java cp jooq 3 0 1 jar joog meta 3 0 1 jar joog codegen 3 0 1 jar DBC driver jar org jooq utilGenerationTool XML file Note that the property file must be passed as a classpath resource Run code generation from Eclipse Of course you can also run code generation from your IDE In Eclipse set up a project like this Note that this example uses jOOQ s log4j support by adding log4j xml and log4j jar to the project classpath the actual joog 3 0 1 jar jooq meta 3 0 1 jar jooq codegen 3 0 1 jar artefacts may contain version numbers in the file names se example a src X library xml Xx log4j xml E JRE System Library JavaSE 1 7 4 E Referenced Libraries lud jooq codegen jar on jooq meta jar ua jooq jar ud log4j 1 2 16 jar ora mysql connector java 5 1 15 bin jar Once the project is set up correctly with all required artefacts on the
37. Here are some examples supported by jOOQ TITLE ANY Animal Farm 1982 BOOK TITLE equal any Animal Farm 1982 PUBLISHED_IN gt ALL 1920 1940 BOOK PUBLISHED_IN greaterThan al1 1920 1940 For the example the right hand side of the quantified comparison predicates were filled with argument lists But it is easy to imagine that the source of values results from a subselect 2009 2014 by Data Geekery GmbH All rights reserved Page 85 174 The jOOQ User Manual 4 6 6 NULL predicate ANY and the IN predicate It is interesting to note that the SQL standard defines the IN predicate in terms of the ANY quantified predicate The following two expressions are equivalent ROW VALUE EXPRESSION IN IN PREDICATE VALUE ROW VALUE EXPRESSION ANY IN PREDICATE VALUE Typically the IN predicate is more readable than the quantified comparison predicate 4 6 6 NULL predicate In SQL you cannot compare NULL with any value using comparison predicates as the result would yield NULL again which is neither TRUE nor FALSE see also the manual s section about conditional expressions In order to test a column expression for NULL use the NULL predicate as such TITLE IS NULL BOOK TITLE isNull TITLE IS NOT NULL BOOK TITLE isNotNul1 4 6 7 NULL predicate degree gt 1 The SQL NULL predicate also works well for row value expressions although it has some subtle counter intuitive f
38. Most databases only support a few bitwise operations while others ship with the full set of operators jOOQ s API includes most bitwise operations as listed below In order to avoid ambiguities with conditional operators all bitwise functions are prefixed with bit T_COUNT Count the number of bits set to 1 in a number T_AND Set only those bits that are set in two numbers T_OR Set all bits that are set in at least one number T_NAND Set only those bits that are set in two numbers and inverse the result T_NOR Set all bits that are set in at least one number and inverse the result T_NOT Inverse the bits in a number T_XOR Set all bits that are set in at exactly one number T_XNOR Set all bits that are set in at exactly one number and inverse the result L Shift bits to the left R Shift bits to the right i NNDWDWDWWWWOWOW 2009 2014 by Data Geekery GmbH All rights reserved Page 70 174 The jOOQ User Manual 4 5 9 String functions Some background about bitwise operation emulation As stated before not all databases support all of these bitwise operations OOQ emulates them wherever this is possible More details can be seen in this blog post http blog joog org 201 1 10 30 the comprehensive sql bitwise operations compatibility list 4 5 9 String functions String formatting can be done efficiently in the database before returning results to your Java application As discussed in t
39. POJOs MyBook1 myBook create select from BOOK fetchAny into MyBookl class List lt MyBook1 gt myBooks create select from BOOK fetch into MyBook1 class List lt MyBook1 gt myBooks create select from BOOK fetchInto MyBookl class Please refer to the Record into Javadoc for more details Using immutable POJOs If OOQ does not find any default constructor columns are mapped to the best matching constructor This allows for using immutable POJOs with jOOQ An example illustrates this An immutable POJO class public class MyBook2 public final ant ad public final String title public MyBook2 int id String title tbis id id this title title With immutable POJO classes there must be an exact match between projected fields and available constructors MyBook2 myBook create select BOOK ID BOOK TITLE from BOOK fetchAny into MyBook2 class List lt MyBook2 gt myBooks create select BOOK ID BOOK TITLE from BOOK fetch into MyBook2 class List lt MyBook2 gt myBooks create select BOOK ID BOOK TITLE from BOOK fetchInto MyBook2 class An immutable POJO class with a java beans ConstructorProperties annotation public class MyBook3 public final String title public final int id ConstructorProperties title id public MyBook2 String title int id this title title this id id With annotated immutable
40. ResultSet to jOOQ in apropriate methods For convenience and better backwards compatibility consider extending org joog impl DefaultExecuteListener instead of implementing this interface Here is asample implementation of an ExecuteListener that is simply counting the number of queries per type that are being executed using jOOQ package com example Extending DefaultExecuteListener which provides empty implementations for all methods public class StatisticsListener extends DefaultExecutelistener public static Map lt ExecuteType Integer gt STATISTICS new HashMap lt ExecuteType Integer gt Count start events for every type of query executed by jO0Q Override public void start ExecuteContext ctx synchronized STATISTICS Integer count STATISTICS get ctx type if count null count 0 STATISTICS put SEAS lly count t 17 Now configure jOOQ s runtime to load your listener Create a configuration with an appropriate listener provider Configuration configuration new DefaultConfiguration set connection set dialect configuration set new DefaultExecuteListenerProvider new StatisticslListener Create a DSLContext from the above configuration DSLContext create DSL using configuration O 2009 2014 by Data Geekery GmbH All rights reserved Page 138 174 The jOOQ User Manual 5 15 Database meta data And log results any time with a
41. SELECT FROM SELECT DISTINCT BOOK ID BOOK TITLE DENSE_RANK OVER ORDER BY ID ASC TITLE ASC AS RN FROM BOOK AS X WHERE RN gt 1 AND RN lt 3 Oracle Ilg and less SELECT FROM SELECT b ROWNUM RN FROM SELECT FROM BOOK ORDER BY ID ASC b WHERE ROWNUM lt 3 WHERE RN gt 1 As you can see jOOQ will take care of the incredibly painful ROW_NUMBER OVER or ROWNUM for Oracle filtering in subselects for you you ll just have to write limit 1 offset 2 in any dialect Side note If you re interested in understanding why we chose ROWNUM for Oracle please refer to this very interesting benchmark comparing the different approaches of doing pagination in Oracle http www inf unideb hu gabora pagination results html SQL Server s ORDER BY TOP and subqueries As can be seen in the above example writing correct SQL can be quite tricky depending on the SQL dialect For instance with SQL Server you cannot have an ORDER BY clause in a subquery unless you also have a TOP clause This is illustrated by the fact that jOOQ renders a TOP 100 PERCENT clause for you The same applies to the fact that ROW_NUMBER OVER needs an ORDER BY windowing clause even if you don t provide one to the jOOQ query By default OOQ adds ordering by the first column of your projection 4 3 2 10 The FOR UPDATE clause For inter process synchronisati
42. SS ARTE publie static lt T T2 T3 U4 RON T2 T3 TA ON UE T2 t2 T3 E3 TA EA f zi idem for Row5 Row6 Row7 Row22 Degrees of more than 22 are supported without type safety public static RowN row Object values Using row value expressions in predicates Row value expressions are incompatible with most other QueryParts but they can be used as a basis for constructing various conditional expressions such as comparison predicates NULL predicates BETWEEN predicates IN predicates OVERLAPS predicate for degree 2 row value expressions only See the relevant sections for more details about how to use row value expressions in predicates Using row value expressions in UPDATE statements The UPDATE statement also supports a variant where row value expressions are updated rather than single columns See the relevant section for more details O 2009 2014 by Data Geekery GmbH All rights reserved Page 81 174 The jOOQ User Manual 4 6 Conditional expressions Higher degree row value expressions jOOQ chose to explicitly support degrees up to 22 to match Scala s typesafe tuple function and product support Unlike Scala however jOOQ also supports higher degrees without the additional typesafety 4 6 Conditional expressions
43. The result should be ready now Result lt BookRecord gt result future get O 2009 2014 by Data Geekery GmbH All rights reserved Page 115 174 The jOOQ User Manual 5 3 10 ResultSet fetching Note that instead of letting OOQ spawn a new thread you can also provide jOOQ with your own java util concurrent ExecutorService Spawn off this query in a separate process ExecutorService service FutureResult lt BookRecord gt future create selectFrom BOOK where complex predicates fetchLater service 5 3 10 ResultSet fetching When interacting with legacy applications you may prefer to have jOOQ return a java sql ResultSet rather than jOOQ s own org joog Result types This can be done simply in two ways j00Q s Cursor type exposes the underlying ResultSet ResultSet rsl create selectFrom BOOK fetchLazy resultSet But you can also directly access that ResultSet from ResultQuery ResultSet rs2 create selectFrom BOOK fetchResultSet Don t forget to close these though rsl close rs2 close Transform jOOQ s Result into a JDBC ResultSet Instead of operating on a JDBC ResultSet holding an open resource from your database you can also let jOOQ s org jooq Result wrap itself in a java sql ResultSet The advantage of this is that the so created ResultSet has no open connection to the database It is a completely in memory ResultSet Transform a j
44. User Manual 5 3 1 Record vs TableRecord These methods are convenience for fetching only a single field possibly converting results to another type lt gt List lt T gt fetch Field lt T gt field Sus List lt T gt fetch Field lt gt field Class lt extends T gt type lt T U gt List lt U gt fetch Field lt T gt field Converter lt super T U gt converter List lt gt fetch int fieldindex SIS List lt T gt fetch int fieldIndex Class lt extends T gt type lt U gt List lt U gt fetch int fieldIndex Converter lt U gt converter List lt gt fetch String fieldName TS List lt T gt fetch String fieldName Class lt extends T gt type lt U gt List lt U gt fetch String fieldName Converter lt U gt converter These methods are convenience for fetching only a single field possibly converting results to another type Instead of returning lists these return arrays lt gt acta fetchArray Field lt T gt field lt T gt Al fetchArray Field lt gt field Class lt extends T gt type o WES uii fetchArray Field lt T gt field Converter lt super T U gt converter Object fetchArray int fieldIndex lt gt P fetchArray int fieldIndex Class lt extends T gt type lt U gt U fetchArray int fieldIndex Converter lt U gt converter Object fetchArray String fieldName TS Al fetchArray String fieldName Class lt extends T gt type lt U gt U f
45. Z greater than A gt X OR A X AND B gt Y A gt X OR A X AND B gt Y OR A X AND B Y AND C gt Z greater or equal A gt X OR A X AND B gt Y OR A X AND B Y A gt X OR A X AND B gt Y OR A X AND B Y AND C gt Z OR A X AND B Y AND C Z For simplicity these predicates are shown in terms of their negated counter parts NOT A B X Y NOT A B gt X Y NOT A B gt X Y jOOQ supports all of the above row value expression comparison predicates both with column expression lists and scalar subselects at the right hand side With regular column expressions BOOK AUTHOR_ID BOOK TITLE 1 gt With scalar subselects BOOK AUTHOR_ID BOOK TITLE SELECT PERSON ID Animal Farm FROM PERSON WHERE PERSON ID 1 Column expressions row BOOK AUTHOR_ID BOOK TITLE equal 1 Animal Farm Subselects row BOOK AUTHOR_ID BOOK TITLE equal select PERSON ID val Animal Farm from PERSON where PERSON ID equal 1 Y 4 6 5 Quantified comparison predicate If the right hand side of a comparison predicate turns out to be a non scalar table subquery you can wrap that subquery in a quantifier such as ALL ANY or SOME Note that the SQL standard defines ANY and SOME to be equivalent jOOQ settled for the more intuitive ANY and doesn t support SOME
46. be no type safety provided by jOOQ 5 7 Sequence execution Most databases support sequences of some sort to provide you with unique values to be used for primary keys and other enumerations If you re using jOOQ s code generator it will generate a sequence object per sequence for you There are two ways of using such a sequence object Standalone calls to sequences Instead of actually phrasing a select statement you can also use the DSLContext s convenience methods Fetch the next value from a sequence BigInteger nextID create nextval S_AUTHOR_ID Fetch the current value from a sequence BigInteger currID create currval S_AUTHOR_ID Inlining sequence references in SQL You can inline sequence references in jOOQ SQL statements The following are examples of how to do that Reference the sequence in a SELECT statement BigInteger nextID create select s fetchOne S_AUTHOR_ID nextval Reference the sequence in an INSERT statement create insertInto AUTHOR AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME values S_AUTHOR_ID nextval val William val Shakespeare For more info about inlining sequence references in SQL statements please refer to the manual s section about sequences and Serials 2009 2014 by Data Geekery GmbH All rights reserved Page 122 174 The jOOQ User Manual 5 8 Stored procedures and functions 5 8 Stored procedures and function
47. boolean renderNamedParams CastMode castMode RenderContext castMode CastMode mode Boolean Cast RenderContext castModeSome SQLDialect dialects 2009 2014 by Data Geekery GmbH All rights reserved Page 97 174 The jOOQ User Manual 4 9 2 Pretty printing SQL The following additional methods are inherited from a common org joog Context which is shared among org joog RenderContext and org joog BindContext These methods indicate whether fields or tables are being declared MY_TABLE AS MY_ALIAS or referenced MY_ALIAS boolean declareFields Context declareFields boolean declareFields boolean declareTables Context declareTables boolean declareTables These methods indicate whether a top level query is being rendered or a subquery boolean subquery Context subquery boolean subquery These methods provide the bind value indices within the scope of the whole Context and thus of the whole Query int nextIndex int peekIndex An example of rendering SQL A simple example can be provided by checking out jOOQ s internal representation of a simplified CompareCondition lt is used for any org joog Condition comparing two fields as for example the AUTHOR ID BOOK AUTHOR_ID condition here Se llos oil FROM AUTHOR JOIN BOOK ON AUTHOR ID BOOK AUTHOR_ID Ihooodl This is how jOOQ renders such a condition simplified example Override public final v
48. can access table columns by dereferencing the relevant Java attributes of their tables See the manual s section about generated tables for more information about what is really generated by the code generator 4 4 2 Allased Tables The strength of jOOQ s code generator becomes more obvious when you perform table aliasing and dereference fields from generated aliased tables This can best be shown by example Select all books by authors born after 1920 Declare your aliases before using them in SQL named Paulo from a catalogue Author a AUTHOR as a Book b BOOK as b Use aliased tables in your statement SELECT create select FROM author a from a JOIN book b ON a id b author_id join b on a ID equal b AUTHOR_ID WHERE a year_of_ birth gt 1920 where a YEAR_OF_BIRTH greaterThan 1920 AND a first_name Paulo and a FIRST_NAME equal Paulo ORDER BY b title orderBy b TITLE As you can see in the above example calling as on generated tables returns an object of the same type as the table This means that the resulting object can be used to dereference fields from the aliased table This is quite powerful in terms of having your Java compiler check the syntax of your SQL statements If you remove a column from a table dereferencing that column from that table alias will cause compilation errors Dereferencing columns from other table expressions Only fe
49. classpath you can configure an Eclipse Run Configuration for org jooq util GenerationTool O 2009 2014 by Data Geekery GmbH All rights reserved Page 144 174 The jOOQ User Manual 6 1 Configuration and setup of the generator example Main class org jooq util GenerationTool C Include system libraries when searching for a main class C Include inherited mains when searching for a main class C Stop in main With the XML file as an argument Name GenerationTool MySQL Example Working directory Default S workspace_loc example Other Workspace File System Variables And the classpath set up correctly 2009 2014 by Data Geekery GmbH All rights reserved Page 145 174 The jOOQ User Manual 6 1 Configuration and setup of the generator GenerationTool MySQL Example Bl JRE System Library JavaSE 1 7 a User Entries gt example jooq codegen jar example lib jooq meta jar example lib Add Projects jooq jar example lib log4j 1 2 16 jar example lib Add JARs mysql connector java 5 1 15 bin jar example lib Remove Finally run the code generation and see your generated artefacts a amp example a 3 src gt EB org jooq test mysql generatedclasses gt EY org jooq test mysql generatedclasses enums gt EB org jooq test mysql generatedclasses routines b EB org jooq te
50. equal 3 forUpdate nowait create select from BOOK where BOOK ID equal 3 forUpdate wait 5 create select from BOOK where BOOK ID equal 3 forUpdate skipLockead FOR UPDATE in CUBRID and SQL Server The SQL standard specifies a FOR UPDATE clause to be applicable for cursors Most databases interpret this as being applicable for all SELECT statements An exception to this rule are the CUBRID and SQL Server databases that do not allow for any FOR UPDATE clause in a regular SQL SELECT statement jOOQ emulates the FOR UPDATE behaviour by locking record by record with JDBC JDBC allows for specifying the flags TYPE_SCROLL_SENSITIVE CONCUR_UPDATABLE for any statement and then using ResultSet updateXXX methods to produce a cell lock row lock Here s a simplified example in JDBC try PreparedStatement stmt connection prepareStatement SELECT FROM author WHERE IGA ENS Cbs O ResultSet TYPE_SCROLL_SENSITIVE ResultSet CONCUR_UPDATABLE ResultSet rs stmt executeQuery eed while es next 0 UPDATE the primary key for row locks or any other columns for cell locks rs updateObject 1 rs getObject 1 rs updateRow Do more stuff with this record The main drawback of this approach is the fact that the database has to maintain a scrollable cursor whose records are locked one by one This ca
51. even with ON DELETE CASCADE clauses in place Please consider your database manual to learn more about its TRUNCATE implementation The TRUNCATE syntax is trivial TRUNCATE TABLE AUTHOR create truncate AUTHOR execute TRUNCATE is not supported by Ingres and SQLite jOOQ will execute a D statement instead ETE FROM AUTHOR 4 4 Table expressions The following sections explain the various types of table expressions supported by jOOQ 2009 2014 by Data Geekery GmbH All rights reserved Page 59 174 The jOOQ User Manual 4 4 1 Generated Tables 4 4 1 Generated Tables Most of the times when thinking about a table expression you re probably thinking about an actual table in your database schema If you re using jOOQ s code generator you will have all tables from your database schema available to you as type safe Java objects You can then use these tables in SQL FROM clauses JOIN clauses or in other SOL statements just like any other table expression An example is given here SELECT create select FROM AUTHOR Table expression AUTHOR from AUTHOR Table expression AUTHOR JOIN BOOK Table expression BOOK join BOOK Table expression BOOK ON AUTHOR ID BOOK AUTHOR_ID on AUTHOR ID equal BOOK AUTHOR_ID The above example shows how AUTHOR and BOOK tables are joined in a SELECT statement It also shows how you
52. extends Record gt SelectWhereStep lt R gt selectFrom Table lt R gt table As you can see there is no way to further restrict project the selected fields This just selects all known TableFields in the supplied Table and it also binds lt R extends Record gt to your Table s associated Record An example of such a Query would then be BookRecord book create selectFrom BOOK where BOOK LANGUAGE equal DE orderBy BOOK TITLE fetchAny The reduced S CT API is limited in the way that it skips DSL access to any of these clauses The SELECT clause The JOIN clause In most parts of this manual it is assumed that you do not use the reduced SELECT API For more information about the simple SELECT API see the manual s section about fetching strongly or weakly typed records 4 3 2 1 The SELECT clause elds functions arithmetic ECT clause The SELECT clause lets you project your own record types referencing table expressions etc The DSL type provides several methods for expressing a SE O 2009 2014 by Data Geekery GmbH All rights reserved Page 40 174 The jOOQ User Manual 4 3 2 1 The SELECT clause The SELECT clause Provide a varargs Fields list to the SELECT clause SELECT BOOK ID BOOK TITLE Select lt 7 gt s1 create select BOOR ID BOOKTITLE gt SELECT BOOK ID TRIM BOOK TITLE Se
53. generation This section contains the necessary information to run jOOQ s code generator against your developer database SQL execution This section contains a lot of information about executing SQL statements using the jOOQ API Fetching This section contains some useful information about the various ways of fetching data with jOOQ 3 3 4 JOOQ for CRUD This is probably the most complete use case for jOOQ Use all of jOOQ s features Apart from jOOQ s fluent API for query construction OOQ can also help you execute everyday CRUD operations An example is given here Fetch all authors for AuthorRecord author create fetch AUTHOR Skip previously distinguished authors if int author getDistinguished 1 continue Check if the author has written more than 5 books if author fetchChildren Keys FK_BOOK_AUTHOR size gt 5 Mark the author as a distinguished author author setDistinguished 1 author store If you wish to use all of jOOQ s features the following sections of the manual will be of interest to you including all sub sections SQL building This section contains a lot of information about creating SQL statements using the jOOQ API Code generation This section contains the necessary information to run jOOQ s code generator against your developer database SQL execution This section contains a lot of information about executing SQL statements using the jO
54. has two purposes It provides some information about the state of SQL rendering It provides a common API for constructing SQL strings on the context s internal java lang StringBuilder An overview of the org joog RenderContext API is given here These methods are useful for generating unique aliases within a RenderContext and thus within a Query String peekAlias String nextAlias These methods return rendered SQL String render String render QueryPart part These methods allow for fluent appending of SQL to the RenderContext s internal StringBuilder RenderContext keyword String keyword RenderContext literal String literal RenderContext sql String sql RenderContext sql char sql RenderContext sql int sql RenderContext sql QueryPart part These methods allow for controlling formatting of SQL if the relevant Setting is active RenderContext formatNewLine RenderContext formatSeparator RenderContext formatIndentStart RenderContext formatIndentStart int indent RenderContext formatIndentLockStart RenderContext formatIndentEnd RenderContext formatIndentEnd int indent RenderContext formatIndentLockEnd These methods control the RenderContext s internal state boolean inline RenderContext inline boolean inline boolean qualify 5 RenderContext qualify boolean qualify boolean namedParanms RenderContext namedParams
55. interfaces in Record into Class lt gt and similar methods to let jOOQ return proxy objects for them Defaults tofalse gt lt interfaces gt false lt interfaces gt lt Generate DAOS in addition to POJO classes Decio t false gt lt daos gt false lt daos gt lt Annotate POJOs and Records with JPA annotations for increased compatibility and better integration with JPA Hibernate etc Defautte to farse ses lt jpaAnnotations gt false lt jpaAnnotations gt lt Annotate POJOs and Records with JSR 303 validation annotations Dera smc Onc Som lt validationAnnotations gt false lt validationAnnotations gt lt Allow to turn off the generation of global object references which include Tables java Sequences java UDTs java Turning off the generation of the above files may be necessary for very large schemas which exceed the amount of allowed constants in a class s constant pool 64k or whose static initialiser would exceed 64k of byte code Der tul Lo nia tie gt lt globalO0bjectReferences gt true lt globalO0bjectReferences gt lt generate gt Property interdependencies Some of the above properties depend on other properties to work correctly For instance when generating immutable pojos pojos must be generated jOOQ will enforce such properties even if you tell it otherwise Here is a list of property interdependencies When daos true then jOOQ will set relations true
56. is often called a derived table Apart from many convenience methods accepting org jooq Select objects directly a SELECT statement can always be transformed into a org joog Table object using the asTable method Example Scalar subquery SELECT create select FROM BOOK from BOOK WHERE BOOK AUTHOR_ID where BOOK AUTHOR_ID equal create SELECT ID select AUTHOR ID FROM AUTHOR WHERE LAST_NAME from AUTHOR Orwell where AUTHOR LAST_NAME equal Orwell Example Derived table SELECT nested FROM SELECT AUTHOR_ID FROM BOOK GROUP BY AUTHOR_ID nested ORDER BY nested books DESC count books O 2009 2014 by Data Geekery GmbH All rights reserved Table lt Record gt nested create select BOOK AUTHOR_ID count from BOOK groupBy BOOK AUTHOR_ID asTable nested as books create select nested fields from nested orderBy nested field books Page 63 174 The jOOQ User Manual 4 4 6 The Oracle 11g PIVOT clause Example Correlated subquery SELECT LAST_NAME The type of books cannot be inferred from the Select lt gt SELECT COUNT Field lt Object gt books FROM BOOK create selectCount WHERE BOOK AUTHOR_ID AUTHOR ID books from BOOK FROM AUTHOR where BOOK AUTHOR_ID equal AUTHOR ID ORDER BY books DESC asField books create select AUTHOR ID books from AUTHOR
57. learn explore the Javadoc ttp www joog org javadoc latest F or join the news group httos groups google com forum forum joog user This tutorial is the courtesy of Ikai Lan See the original source here http ikaisays com 201 1 1 1 01 getting started with joog a tutorial 3 4 2 Using JOOQ in modern IDEs Feel free to contribute a tutorial 2009 2014 by Data Geekery GmbH All rights reserved Page 24 174 The jOOQ User Manual 3 4 3 Using jOOQ with Spring 3 4 3 Using JOOQ with Spring Feel free to contribute a tutorial 3 4 4 A simple web application with OOQ Feel free to contribute a tutorial 3 5 JOOQ and Scala As any other library OOQ can be easily used in Scala taking advantage of the many Scala language features such as for example Optional to dereference methods from expressions Optional and to delimit method argument lists Optioanl at the end of a Scala statement Type inference using var and val keywords But jOOQ also leverages other useful Scala features such as implicit defs for operator overloading Scala Macros soon to come All of the above heavily improve jOOQ s querying DSL API experience for Scala developers A short example jOOQ application in Scala might look like this 2009 2014 by Data Geekery GmbH All rights reserved Page 25 174 The jOOQ User Manual 3 6 OOQ and NoSQL import coll
58. least one fetch method public List lt Book gt tetenByld Imteger values 4 return fetch BOOK ID values public Book fetchOneByld Integer value return fetchOne BOOK ID value public List lt Book gt fetchByAuthorId Integer values return fetch BOOK AUTHOR_ID values We Wool Flags controlling DAO generation DAO generation can be activated using the daos flag 6 9 Generated sequences Every sequence in your database will generate a org joog Sequence implementation that looks like this 2009 2014 by Data Geekery GmbH All rights reserved Page 156 174 The jOOQ User Manual 6 10 Generated procedures public final class Sequences Every sequence generates a member public static final Sequence lt Integer gt S_AUTHOR_ID new SequenceImpl lt Integer gt S_AUTHOR_ID TEST SQLDataType INTEGER Flags controlling sequence generation Sequence generation cannot be deactivated 6 10 Generated procedures that looks like this public class AuthorExists extends AbstractRoutine lt java lang Void gt All IN IN OUT OUT parameters and function return values generate a static member public static final Parameter lt String gt AUTHOR_NAME createParameter AUTHOR_NAME SQLDataType VARCHAR public static final Parameter lt BigDecimal gt RESULT createParameter RESULT SQLDataType NUMERIC A constructor for a new empty procedure call public AuthorExists
59. load of SQL statements jOOQ simplifies these operations compared to JDBC Exporting and Importing jOOQ ships with an API to easily export import data in various formats If you re a power user of your favourite feature rich database jOOQ will help you access all of your database s vendor specific features such as OLAP features stored procedures user defined types vendor specific SQL functions etc Examples are given throughout this manual 3 4 Tutorials Don t have time to read the full manual Here are a couple of tutorials that will get you into the most essential parts of jOOQ as quick as possible 3 4 1 JOOQ in 7 easy steps This manual section is intended for new users to help them get a running application with jOOQ quickly 3 4 1 1 Step 1 Preparation If you haven t already downloaded it download jOOQ http www joog org download Alternatively you can create a Maven dependency to download jOOQ artefacts 2009 2014 by Data Geekery GmbH All rights reserved Page 19 174 The jOOQ User Manual 3 4 1 2 Step 2 Your database lt dependency gt lt groupId gt org jooq lt groupid gt lt artifactId gt jooq lt artifactId gt lt version gt 3 0 1 lt version gt lt dependency gt lt dependency gt lt groupId gt org jooq lt groupid gt lt artifactId gt jooq meta lt artifactId gt lt version gt 3 0 1 lt version gt lt dependency gt lt depe
60. mean time The record is successfully stored deleted if the record had not been modified in the mean time The above changes to jOOQ s behaviour are transparent to the API the only thing you need to do for it to be activated is to set the Settings flag Here is an example illustrating optimistic locking 2009 2014 by Data Geekery GmbH All rights reserved Page 134 174 The jOOQ User Manual 5 11 6 Optimistic locking Properly configure the DSLContext DSLContext optimistic DSLContext using connection SOLDialect ORACLE new Settings withExecuteWithOptimisticLocking true Fetch a book two times BookRecord bookl optimistic fetch BOOK BOOK ID equal 5 BookRecord book2 optimistic fetch BOOK BOOK ID equal 5 Change the title and store this book The underlying database record has not been modified it can be safely updated book1 setTitle Animal Farm bookl store Book2 still references the original TITLE value but the database holds a new value from bookl store Lp This stores fails book2 setTitle 1984 book2 store Optimised optimistic locking using TIMESTAMP fields If you re using jOOQ s code generator you can take indicate TIMESTAMP or UPDATE COUNTER fields for every generated table in the code generation configuration Let s say we have this table CREATE TABLE book
61. means any of these actions Adding methods to the interface Overloading methods for convenience Changing the type hierarchy of interfaces It becomes obvious that it would be impossible to add new language elements e g new SQL functions new SELECT clauses to the API without breaking any client code that actually implements those interfaces Hence the following rule should be observed jOOQ s DSL interfaces should not be implemented by client code Extend only those extension points that are explicitly documented as extendable e g custom QueryParts 2009 2014 by Data Geekery GmbH All rights reserved Page 28 174 The jOOQ User Manual 4 SQL building 4 SQL building SQL is a declarative language that is hard to integrate into procedural object oriented functional or any other type of programming languages jOOQ s philosophy is to give SQL the credit it deserves and integrate SQL itself as an internal domain specific language directly into Java With this philosophy in mind SQL building is the main feature of jOOQ All other features such as SQL execution and code generation are mere convenience built on top of jOOQ s SQL building capabilities This section explains all about the various syntax elements involved with jOOQ s SQL building capabilities For a complete overview of all syntax elements please refer to the manual s section about jOOQ s BNF pseudo no
62. prevent that and scroll over an open underlying database cursor Optimise wisely Don t be put off by the above paragraphs You should optimise wisely i e only in places where you really need very high throughput to your database jOOQ s overhead compared to plain JDBC is typically less than 1ms per query 2009 2014 by Data Geekery GmbH All rights reserved Page 141 174 The jOOQ User Manual 6 Code generation 6 Code generation While optional source code generation is one of jOOQ s main assets if you wish to increase developer productivity OOQ s code generator takes your database schema and reverse engineers it into a set of Java classes modelling tables records sequences POJOs DAOs stored procedures user defined types and many more The essential ideas behind source code generation are these Increased IDE support Type your Java code directly against your database schema with all type information available Type safety When your database schema changes your generated code will change as well Removing columns will lead to compilation errors which you can detect early The following chapters will show how to configure the code generator and how to generate various artefacts 6 1 Configuration and setup of the generator There are three binaries available with jOOQ to be downloaded from http www joog org download or from Maven central
63. snippet like this log info STATISTICS Tog bars S gt gt gt 2 gt gt for Executelype type Executelype values log info type name StatisticsListener STATISTICS get type executions This may result in the following log output 15 16 52 982 INFO TEST STATISTICS 15 16 52 982 INFO 15 16 52 983 INFO READ 919 executions 15 16 52 983 INFO WRITE 117 executions 15 16 52 983 INFO DDL 2 executions 15 16 52 983 INFO BATCH 4 executions 15 16 52 983 INFO ROUTINE 21 executions 15 16 52 983 INFO OTHER 30 executions Please read the ExecuteListener Javadoc for more details Writing a custom ExecuteListener for logging The following depicts an example of a custom ExecuteListener which pretty prints all queries being executed by jOOQ to stdout import org jooq DSLContext import org jooq ExecuteContext import org jooq conf Settings import org jooq impl DefaultExecuteListener import org jooq tools StringUtils public class PrettyPrinter extends DefaultExecuteListener pr Hook into the query execution lifecycle before executing queries ey Override public void executeStart ExecuteContext ctx Create a new DSLContext for logging rendering purposes This DSLContext doesn t need a connection only the SQLDialect DSLContext create DSL using ctx configuration dialect and the flag for pretty printing new Settings withRe
64. the LOAD procedure author setId 1 author load The record is now updated with the LOAD implementation s content assertNotNull author getFirstName assertNotNull author getLastName For more details about code generation for UDTs see the manual s section about user defined types and code generation 5 9 Exporting to XML CSV JSON HTML Text If you are using jOOQ for scripting purposes or in a slim unlayered application server you might be interested in using jOOQ s exporting functionality see also the importing functionality You can export any Result lt Record gt into the formats discussed in the subsequent chapters of the manual O 2009 2014 by Data Geekery GmbH All rights reserved Page 125 174 The jOOQ User Manual 5 9 1 Exporting XML 5 9 1 Exporting XML Fetch books and format them as XML String xml create selectFrom BOOK fetch formatXML The above query will result in an XML document looking like the following one lt result xmlns http www jooq org xsd jooq export 2 6 0 xsd gt lt fields gt lt field name ID type INTEGER gt lt field name AUTHOR_ID type INTEGER gt lt field name TITLE type VARCHAR gt lt fields gt lt records gt lt record gt lt value field ID gt 1 lt value gt lt value field AUTHOR_ID gt 1 lt value gt lt value field TITLE gt 1984 lt value gt lt record gt lt record gt lt value field ID gt 2 lt
65. trademark of Oracle Corp Firebird is a registered trademark of Firebird Foundation Inc Oracle database is a registered trademark of Oracle Corp PostgreSQL is a registered trademark of The PostgreSQL Global Development Group Postgres Plus is a registered trademark of EnterpriseDB software SQL Anywhere is a registered trademark of Sybase Inc SQL Server is a registered trademark of Microsoft Inc SQLite is a trademark of Hipp Wyrick amp Company Inc Other trademarks by vendors with no affiliation to Data Geekery GmbH Java is a registered trademark by Oracle Corp and or its affiliates Scala is a trademark of EPFL Other trademark remarks Other names may be trademarks of their respective owners Throughout the manual the above trademarks are referenced without a formal R or TM symbol It is believed that referencing third party trademarks in this manual or on the jOOQ website constitutes fair use Please contact us if you think that your trademark s are not properly attributed Contributions The following are authors and contributors of jOOQ or parts of jOOQ in alphabetical order 2009 2014 by Data Geekery GmbH All rights reserved Page 11 174 The jOOQ User Manual Aaron Digulla Arnaud Roger Art O Cathain Artur Dryomov Ben Manes Brent Douglas Brett M
66. will hence attempt to INSERT it When you activate optimistic locking storing a record may fail if the underlying database record has been changed in the mean time Deleting Deleting a record will remove it from the database Here s how you delete records Get a previously inserted book BookRecord book create fetchOne BOOK BOOK ID equal 5 Delete the book book delete Refreshing Refreshing a record from the database means that jOOQ will issue a SELECT statement to refresh all record values that are not the primary key This is particularly useful when you use jOOQ s optimistic locking feature in case a modified record is stale and cannot be stored to the database because the underlying database record has changed in the mean time In order to perform a refresh use the following Java code 2009 2014 by Data Geekery GmbH All rights reserved Page 131 174 The jOOQ User Manual 5 11 2 Records internal flags Fetch an updatable record from the database BookRecord book create fetchOne BOOK BOOK ID equal 5 Refresh the record book refresh CRUD and SELECT statements CRUD operations can be combined with regular querying if you select records from single database tables as explained in the manual s section about SELECT statements For this you will need to use the selectFrom method from the DSL Context
67. 0 and 4 1 which are needed to mock JDBC for jOOQ In order to write mock tests provide the jOOQ Configuration with a MockConnection and implement the MockDataProvider Initialise your data provider implementation further down MockDataProvider provider new MyProvider MockConnection connection new MockConnection provider Pass the mock connection to a j00Q DSLContext DSLContext create DSL using connection SQLDialect ORACLE Execute queries transparently with the above DSLContext Result lt BookRecord gt result create selectFrom BOOK where BOOK ID equal 5 fetch As you can see the configuration setup is simple Now the MockDataProvider acts as your single point of contact with JDBC jOOQ It unifies any of these execution modes transparently 2009 2014 by Data Geekery GmbH All rights reserved Page 162 174 The jOOQ User Manual 7 1 JDBC mocking for unit testing Statements without results Statements without results but with generated keys Statements with results Statements with several results Batch statements with single queries and multiple bind value sets Batch statements with multiple queries and no bind values The above are the execution modes supported by jOOQ Whether you re using any of jOOQ s various fetching modes e g pojo fetching lazy fetching many fetching later fetching is irrelevant as those modes are all built on top of the standard JD
68. 11 1 Simple CRUD See the manual s section about serializability for some more insight on attached objects Storing Storing a record will perform an INSERT statement or an UPDATE statement In general new records are always inserted whereas records loaded from the database are always updated This is best visualised in code Create a new record BookRecord bookl create newRecord BOOK Insert the record INSERT INTO BOOK TITLE VALUES 1984 booki setTitile 1954 bookstore 00 Update the record UPDATE BOOK SET PUBLISHED_IN 1984 WHERE ID id bookl setPublishedIn 1948 bookl store Get the possibly auto generated ID from the record Integer id bookl getId Get another instance of the same book BookRecord book2 create fetchOne BOOK BOOK ID equal id Update the record UPDATE BOOK SET TITLE Animal Farm WHERE ID id book2 setTitle Animal Farm book2 store Some remarks about storing jOOQ sets only modified values in INSERT statements or UPDATE statements This allows for default values to be applied to inserted records as specified in CREATE TABLE DDL statements When store performs an INSERT statement jOOQ attempts to load any generated keys from the database back into the record For more details see the manual s section about IDENTITY values When loading records from POJOSs jOOQ will assume the record is a new record It
69. 174 The jOOQ User Manual 8 3 OOQ s BNF pseudo notation 2 SQLDB Postgres Oracle typed arrays Oracle has strongly typed arrays and table types as opposed to the previously seen anonymously typed arrays These arrays are wrapped by org jooq ArrayRecord types 8 3 JOOQ s BNF pseudo notation This chapter will soon contain an overview over jOOQ s API using a pseudo BNF notation 8 4 Quality Assurance jOOQ is running some of your most mission critical logic the interface layer between your Java Scala application and the database You have probably chosen OOQ for any of the following reasons To evade JDBC s verbosity and error proneness due to string concatenation and index based variable binding To add lots of type safety to your inline SQL To increase productivity when writing inline SQL using your favourite IDE s autocompletion capabilities With jOOQ being in the core of your application you want to be sure that you can trust jOOQ That is why jOOQ is heavily unit and integration tested with a strong focus on integration tests Unit tests Unit tests are performed against dummy JDBC interfaces using http mock org These tests verify that various org joog QueryPart implementations render correct SQL and bind variables correctly Integration tests This is the most important part of the jOOQ test suites Some 1500 queries are currently run against a Standard integration
70. 2 IN predicate In SQL apart from comparing a value against several values the IN predicate can be used to create semi joins or anti joins OOQ knows the following methods on the org joog Field interface to construct such IN predicates in Collection lt T gt Construct an IN predicate from a collection of bind values A Construct an IN predicate from bind values al So o Construct an IN predicate from column expressions in Select lt extends Recordl lt T gt gt Construct an IN predicate from a subselect notIn Collection lt T gt Construct a NOT IN predicate from a collection of bind values malo ao Construct a NOT IN predicate from bind values notin rueld lt 5 Construct a NOT IN predicate from column expressions notIn Select lt extends Record1 lt T gt gt Construct a NOT IN predicate from a subselect A sample IN predicate might look like this TITLE IN Animal Farm 1984 BOOK TITLE in Animal Farm 1984 TITLE NOT IN Animal Farm 1984 BOOK TITLE notIn Animal Farm 1984 NOT IN and NULL values Beware that you should probably not have any NULL values in the right hand side of a NOT IN predicate as the whole expression would evaluate to NULL which is rarely desired This can be shown informally using the following reasoning The following conditional expressions are formally or informally equivalent A NOT IN B C A ANY B C A B AND A C Substitute C for
71. 4 10 SQL building in Scala Page 102 174 The jOOQ User Manual 4 10 SQL building in Scala select BOOK ID BOOK AUTHOR_ID BOOK ID BOOK AUTHOR_ID 3 4 OJOS IS A ZA from BOOK leftOuterJoin STE CEMSA R YEAR OE BERTH from x Amare asTable x getName on BOOK AUTHOR_ID x ID where BOOK ID lt gt 2 or BOOK TITLE in 0 Alquimista Brida Teteh Scala 2 10 Macros This feature is still being experimented with With Scala Macros it might be possible to inline a true SQL dialect into the Scala syntax backed by the jOOQ API Stay tuned O 2009 2014 by Data Geekery GmbH All rights reserved Page 103 174 The jOOQ User Manual 5 SQL execution 5 SQL execution Ina previous section of the manual we ve seen how jOOQ can be used to build SOL that can be executed with any API including JDBC or OOQ This section of the manual deals with various means of actually executing SQL with jOOQ SQL execution with JDBC JDBC calls executable objects java sal Statement It distinguishes between three types of statements java sql Statement or static statement This statement type is used for any arbitrary type of SQL statement It is particularly useful with inlined parameters java sql PreparedStatement This statement type is used for any arbitrary type of SQL statement t is particularly useful with indexed parameters note that JDBC does not support named parameters
72. 62 WHERE e2 TEXT oI TEXT AND c2 ID B C_ID 2009 2014 by Data Geekery GmbH All rights reserved Page 64 174 The jOOQ User Manual 4 4 8 Array and cursor unnesting Or in plain text Find those TEXT values in C whose ID s correspond to all ID s in B Note that from the above SQL statement it is immediately clear that proper indexing is of the essence Be sure to have indexes on all columns referenced from the on and returning clauses For more information about relational division and some nice real life examples see http en wikipedia org wiki Relational_algebra Division http www simple talk com sal t sal programming divided we stand the sal of relational division 4 4 8 Array and cursor unnesting The SQL standard specifies how SQL databases should implement ARRAY and TABLE types as well as CURSOR types Put simply a CURSOR is a pointer to any materialised table expression Depending on the cursor s features this table expression can be scrolled through in both directions records can be locked updated removed inserted etc Often CURSOR types contain s whereas ARRAY and TABLE types contain simple scalar values although that is not a requirement ARRAY types in SQL are similar to Java s array types They contain a component type or element type and a dimension This sort of ARRAY type is implemented in H2 HSQLDB and Postgres and supp
73. 74 The jOOQ User Manual 4 5 Column expressions With jOOQ you don t have to worry about the above distinction of SQL dialects OOQ never requires a FROM clause but renders the necessary DUAL table if needed The following program shows how jOOQ renders DUAL tables SELECT 1 DSL using SQLDialect ASE selectOne getSQL SELECT 1 FROM db_root DSL using SQLDialect CUBRID esellectoOne jmgeesoin i SELECT 1 FROM SYSIBM DUAL DSL using SQLDialect DB2 selectOne getSOL SELECT 1 FROM SYSIBM SYSDUMMY1 DSL using SQLDialect DERBY Aselectone ceso SELECT 1 FROM RDBSDATABASE DSL using SQLDialect FIREBIRD selectOne getSQL SELECT 1 FROM dual DSL using SQLDialect H2 jesellectOne jr gersoin SELECT 1 FROM INFORMATION_SCHEMA SYSTEM_USERS DSL using SQLDialect HSQLDB selectOne getSOL SELECT 1 FROM select 1 as dual as dual DSL using SQLDialect INGRES JeselieccOne i geesoin SELECT 1 FROM dual DSL using SQLDialect MYSQL selectOne getSQL SELECT 1 FROM dual DSL using SQLDialect ORACLE esellectone J geesoin SELECT 1 DSL using SQLDialect POSTGRES selectOne getSQL SELECT 1 DSL using SQLDialect SQLITE j setectone get sSgL SELECT 1 DSL using SQLDialect SQLSERVER selectOne getSQL SELECT 1 FROM SYS DUMMY DSL using SQLDialect SYBASE jsellectOne ji geresoi 7 Note that some databases H2 MySQL can normally do without DUAL H
74. AGG TITLE pe create select listAgg BOOK TITLE WITHIN GROUP ORDER BY TITLE withinGroupOrderBy BOOK TITLE OVER PARTITION BY BOOK AUTHOR_ID Over partitionBy BOOK AUTHOR_ID FROM BOOK from BOOK Window functions created from Oracle s FIRST and LAST aggregate functions In the previous chapter about aggregate functions we have seen the concept of FIRST and LAST aggregate functions These functions have a window function analytical function variant as well For example Tl O 2009 2014 by Data Geekery GmbH All rights reserved Page 75 174 The jOOQ User Manual 4 5 14 Grouping functions SUM BOOK AMOUNT_SOLD sum BOOK AMOUNT_SOLD KEEP DENSE_RANK FIRST ORDER BY BOOK AUTHOR_ID keepDenseRankFirstOrderBy BOOK AUTHOR_ID OVER PARTITION BY 1 Over partitionByOne Window functions created from user defined aggregate functions User defined aggregate functions also implement org joog AggregateFunction hence they can also be transformed into window functions using over This is supported by Oracle in particular See the manual s section about user defined aggregate functions for more details 4 5 14 Grouping functions ROLLUP explained in SQL The SQL standard defines special functions that can be used in the GROUP BY clause the grouping functions These functions can be used to generate several groupings in a single clause This can best be explained in SQL Let s tak
75. AS FADO EU A a tato AS a e e A e a AS A APIthIMEtic pS ii a A E a iO adi a a 68 doo SUING CONCALEM AL OR io Ae acid 69 4 5 0 General TUNCH ONS eet ahh Sided calla Ia Sali Ma aa 69 ALS 27 NUPMORIG TUMEE OMS abia 69 AS 8 BIWI SE TUM CUIOPS coseseretste O O NN NA 70 A NN 71 4 5 10 Date and time functions Bl As ASYStEMITUNETOMSS ti e e e a dos shouted 12 Aggregate functions Se VINO WA UNC Ss adidas MA GOUPINS TUCU Snte tias cdi couse Do CES DCI avy aaa ib da iio ed SS A iia 78 OSEA UNC taa 78 7 TheCASEEXDrESS O an a ten deni anantaneniiaesnitieaeieriis A a A 79 8 Sequences and SETIAIS ceesseessssessseesessecssssssscssecsessessssssssscssesuessessscsscssssusesseuscsessecsusasessessessessecnseseessssesissneesessecsssusaseeneenecaieaseneenneeses 80 9 TUPIESSOF LOW Vell UO express Surco i a AE NE E avadaunele tienes 81 AS Sione nean orra oaa aA AEA A TEA EAA oust neds TA TA A A E E els 4 6 b Condition DUGINE Srn adds 4 6 2 AND OR NOT Boolean opera Sucesiones a a an 463 Comparison predicates mniam a TE T E A ARE 40A COMPALISOMI Predicate degree diia dosis i annann 4 6 5 Quantified comparison predicate His APG OA NUE OSC ICAL esos ceshesasessasestvves ev snisceanseb tages est todos aeehiegesscshietscatshasascatetsssucndui E E E AT A67 NULG predicate deste sAN i echcce thee aa D B 4 6 3 DISTINCT Pride A dee oe nan nba aia 46 91 BETWEEN DROCIGALCS rise sues ta rd didas A 6 10 BETWE
76. BC API Implementing MockDataProvider Now here s how to implement MockDataProvider public class MyProvider implements MockDataProvider Override public MockResult execute MockExecuteContext ctx throws SQLException You might need a DSLContext to create org jooq Result and org jooq Record objects DSLContext create DSL using SQLDialect ORACLE MockResult mock new MockResult 1 The execute context contains SOL string s bind values and other meta data String sql ctx sql Exceptions are propagated through the JDBC and j00Q APIs if sql toUpperCase startsWith DROP throw new SQLException Statement not supported sql You decide whether any given statement returns results and how many else if sql toUpperCase startsWith SELECT Always return one author record Result lt AuthorRecord gt result create newResult AUTHOR result add create newRecord AUTHOR result get 0 setValue AUTHOR ID 1 result get 0 setValue AUTHOR LAST_NAME Orwell mock 0 new MockResult 1 result You can detect batch statements easily else if ctx batch WW local return mock Essentially the MockExecuteContext contains all the necessary information for you to decide what kind of data you should return The MockResult wraps up two pieces of information Statement getUpdateCount The number of affected rows Statement getR
77. BOOK ID equal subselect FieldProvider The FieldProvider marker interface was removed Its methods still exist on FieldProvider subtypes Note they have changed names from getField to field and from getindex to indexOf GroupField GroupField has been introduced as a DSL marker interface to denote fields that can be passed to GROUP BY clauses This includes all org jooq Field types However fields obtained from ROLLUP CUBE and GROUPING SETS functions no longer implement Field Instead they only implement GroupField An example 2009 2014 by Data Geekery GmbH All rights reserved Page 171 174 The jOOQ User Manual 8 5 Migrating to jOOQ 3 0 3000 2 6 Field lt gt fieldla Factory rollup OK Field lt gt field2a Factory one OK j00Q 3 0 GroupField fieldlb DSL rollup OK Field lt gt fieldlc DSL rollup Compilation error GroupField field2b DSL one OK Field lt gt field2c DSL one OK NULL predicate Beware Previously Field equal null was translated internally to an IS NULL predicate This is no longer the case Binding Java null to a comparison predicate will result in a regular comparison predicate which never returns true This was changed for several reasons To most users this was a Surprising feature Other predicates didn t behave in such a way e g the IN predicate the BETWEEN predicate
78. C s LINQ or Scala s SLICK try to inverse the lexical order of SELECT clauses to what appears to be closer to the logical order The obvious advantage of moving the SELECT clause to the end is the fact that the projection type which is the record type returned by the SELECT statement can be re used more easily in the target environment of the internal domain specific language A LINQ example LINQ to SQL looks somewhat similar to SQL AS clause FROM clause From p In db Products WHERE clause Where p UnitsInStock lt p ReorderLevel AndAlso Not p Discontinued SELECT clause Select p A SLICK example O 2009 2014 by Data Geekery GmbH All rights reserved Page 54 174 The jOOQ User Manual 4 3 3 The INSERT statement for is the entry point to the DSL val q for FROM clause WHERE clause CS Coffees if c supID 101 SELECT clause and projection to a tuple yield te name mas While this looks like a good idea at first it only complicates translation to more advanced SQL statements while impairing readability for those users that are used to writing SQL jOOQ is designed to look just ike SQL This is specifically true for SLICK which not only changed the SELECT clause order but also heavily integrated SQL clauses with the Scala language For these reasons the jOOQ DSL API is modelled in SQLs lexical order 4 3 3 The INSERT statement
79. Context create DSL using connection SQLDialect ORACLE settings Run queries with the mapped configuration create selectFrom AUTHOR fetch The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement SELECT FROM MY_BOOK_WORLD MY_APP__ AUTHOR Table mapping and schema mapping can be applied independently by specifying several MappedSchema entries in the above configuration jOOQ will process them in order of appearance and map at first match Note that you can always omit a MappedSchema s output value in case of which only the table mapping is applied If you omit a MappedSchema s input value the table mapping is applied to all schemata Hard wiring mappings at code generation time Note that the manual s section about code generation schema mapping explains how you can hard wire your schema mappings at code generation time 4 3 SQL Statements jOOQ currently supports 6 types of SQL statements All of these statements are constructed from a DSLContext instance with an optional JDBC Connection or DataSource If supplied with a Connection or DataSource they can be executed Depending on the query type executed queries can return results 2009 2014 by Data Geekery GmbH All rights reserved Page 37 174 The jOOQ User Manual 4 3 1 OOQ s DSL and model API 4 3 1 JOOQ s DSL and model API jOOQ ships with its own DSL or Domain Speci
80. E E A NT 7 AS A RNA 8 5 4 Static statements vs Prepared Statements 9 5 5 Reusing a QUES Prepare date Med taa 20 56 Using JDBC DAPR Si A diodo 21 O E 22 58 StOred Procedures and UNC io 23 5 8417 Oracle PA aia 24 5 8 2 Oracle Member ProCeCUrreS ccecccsssssessesseesssssssessecsesssssssessnessessssseeseesecssssssvssuessessessssseesnsesessesseeseeneessesusesseneenecsessusseesesseeseaeeseeneenees 25 5 SMEXpontine to AMI CSV ISON HTM MAS 25 5 O NN 26 A O ONO 26 IMEI SON rad al e NE aN 27 594 EXPO me TAM Laia iaa io Na IAS 27 DiS EXPO aida 27 5 10 Importing data 28 5 10 1 Importing CSV 28 5 10 2 Importing X 29 STI CRUD With UpdatableRECOTO Scipion aia 30 SMA Mp CD EEN A EE A 30 S1127 Records internal Nafs NN 32 SRS DENHATY Well IGS iaa A A A a A N Cae pags a A A TA A A A A AAN 32 AE To EEA EE EE EAEE E EE EE ET 33 SAA ll A 34 MO A Aus states A aten be Meum Sucub ta AA 34 2009 2014 by Data Geekery GmbH All rights reserved Page 5 174 The jOOQ User Manual O e ed 36 SL DAS a Oia iaa aer an apa tids 36 5 13 Exception handling 37 De VA Execution ies 38 5 15 Database meta NO 39 5 16 Loggin 40 5 17 Performance considerations 40 OS Code generati N ee anna aa ia da ened 42 61 Configuration and Setupior the PaRa E ean 42 6 2 AdVan Ged SEneralor COMM SUPATION sereis ean bebeadils euler tabelss elvethoh cided wise tidheeteatllh hah bere eiatinete bes 47 6 3 Generated global a
81. EN predicate degree ed AG Aili UREA e a ea O cles Ma A O need ed lua ieee 4 613 IN predicate Ceerees gt Dai ii tis clea Gil de dean E un eater eee AOSTA SEXISTS PGC ICACC ss sic A A A 2009 2014 by Data Geekery GmbH All rights reserved Page 4 174 The jOOQ User Manual 4 6 15 4 7 Pl 4 8 Bi 4 8 1 4 8 2 4 8 3 4 8 4 SQL injection and plain SQL QueryParts OVR RA O A td Ona 91 MIES a E NNE NE ANNAE A DE ONN nd values and parameters eSEE NN amed parameters ias nlined parameters 4 9 QUELY NS MI A o a LED PSP MUNE SQ ao e leg e a a o ed E ON NO ASA EXEN OOO MITAEUSOMN Des ias 4 9 5 Plain SOL QUEYP Mis Addis AO SSP AI ZA Bity a a aaEs 01 ANO SOL p ilding dai a e aa eats cally a pe a 01 O A A A E E A A EN 04 5i Comparison between DONA IB aa a a eMC MUA Nh aa 04 5 2 QUELY VS ARESUILQ UE RY s i0ue wit cen sesh coh nee Mawes erates A olan o do cerdo ae ed 05 5 3 Fetching cece ie ee 05 5 3 1 Record vs TableRecord 07 5 3 2 O A 2 2 acces csiaysisfingyeess cvvay eta Svences tov qcee cush qiivecavh cars seni cots dank a cats citi a a a i aaa i iiai eiia 08 533 Arrays Maps and iaa ual A a a O O a 09 DiS Ay Record andina Rita inania a ee a e o sith eh o o ea 09 53 Sy Recor O MP PRE iros e o na O A a AAA E Ea a ET A 0 O POJOS A A A A A A Aaa 0 A A a A e e e ad 3 A a a NA 4 E Later fetching airaa NN 5 5310 Res ltSet fetehiNE ap A NO 6 elds Dala eco
82. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures functions implementations especially when advanced data types such as cursors UDT s arrays are involved 2009 2014 by Data Geekery GmbH All rights reserved Page 123 174 The jOOQ User Manual 5 8 1 Oracle Packages To simplify things a little bit OOQ handles both procedures and functions the same way using a more general org joog Routine type Using jOOQ for standalone calls to stored procedures and functions If you re using jOOQ s code generator it will generate org ooq Routine objects for you Let s consider the following example Check whether there is an author in AUTHOR by that name and get his ID CREATE OR REPLACE PROCEDURE author_exists author_name VARCHAR2 result OUT NUMBER id OUT NUMBER The generated artefacts can then be used as follows Make an explicit call to the generated procedure object AuthorExists procedure new AuthorExists All IN and IN OUT parameters generate setters procedure setAuthorName Paulo procedure execute configuration All OUT and IN OUT parameters generate getters assertEquals new BigDecimal 1 procedure getResult assertEquals new BigDecimal 2 procedure getId But you can also call the procedure using a generated convenience method in a global Routines class The generated Routines class contains stati
83. Get the arc tangent of a value ATAN2 Get the atan2 function of two values CEIL Get the smalles integer value larger than a given numeric value COS Get the cosine of a value COSH Get the hyperbolic cosine of a value COT Get the cotangent of a value COTH Get the hyperbolic cotangent of a value DEG Transform radians into degrees EXP Calculate e value FLOOR Get the largest integer value smaller than a given numeric value GREATEST Finds the greatest among all argument values can also be used with non numeric values EAST Finds the least among all argument values can also be used with non numeric values LN Get the natural logarithm of a value LOG Get the logarithm of a value given a base POWER Calculate value exponent RAD Transform degrees into radians RAND Get a random number ROUND Rounds a value to the nearest integer SIGN Get the sign of a value 1 O 1 SIN Get the sine of a value SINH Get the hyperbolic sine of a value SQRT Calculate the square root of a value TAN Get the tangent of a value TANH Get the hyperbolic tangent of a value TRUNC Truncate the decimals off a given value Please refer to the DSL Javadoc for more details 4 5 8 Bitwise functions Interestingly bitwise functions and bitwise arithmetic is not very popular among SQL databases
84. HOR_ID PUBLISHED_IN 2 NULL 2 lt GROUP BY AUTHOR_ID 2 1988 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN 2 1990 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN 4 4 4 GROUPING SETS GROUPING SETS are the generalised way to create multiple groupings RO PU CU PU UP AUTHOR_ID PU ISHED_IN AUTHO AUTHOR_ID PUBL ISHED_IN AUTHO BLISHED_IN corresponds to GROUPING S R_ID UY R_ID PUBLISHED_IN This is n functions http msdn microsoft com en us library bb51042 7 v sql 105 SHED_IN ey 2 NULLS FIRST From our previous examples ETS AUTHOR_ID SHED_IN corresponds to GROUPING SETS AUTHOR_ID icely explained in the SQL Server manual pages about GROUPING SETS and other grouping OOQ s support for ROLLUP CUBE GROUPING SETS jOOQ fully supports all of these functions as well as the utility functions GROUPING and GROUPING _ID used for identifying the grouping set ID of a record The DSL API thus includes The various grouping function constructors GroupField rollup Field lt gt fields GroupField cube Field lt gt fields GroupField groupingSets Field lt gt fields GroupField groupingSets Field lt gt fields GroupField groupingSets Collection lt extends Field lt gt gt fields The utility functions genera
85. K ID BOOK TITLE BOOK ID BOOK TITLE SQLDataType VARCHAR SQLDataType VARCHAR If supplied the additional information is used to override the information obtained from the ResultSet s java sal ResultSetMetaData information 5 3 11 Data type conversion Apart from a few extra features user defined types OOQ only supports basic types as supported by the JDBC API In your application you may choose to transform these data types into your own ones without writing too much boiler plate code This can be done using jOOQ s org joog Converter types A converter essentially allows for two way conversion between two Java data types lt T gt and lt U gt By convention the lt T gt type corresponds to the type in your database whereas the gt U gt type corresponds to your own user type The Converter API is given here public interface Converter lt T U gt extends Serializable Jer Convert a database object to a user object at U from T databaseObject Jer Convert a user object to a database object e T to U userObject The database type yf Class lt T gt fromType pex The user type e Class lt U gt toType Such a converter can be used in many parts of the jOOQ API Some examples have been illustrated in the manual s section about fetching A Converter for GregorianCalendar Here is a some more elaborate example involving a Converter for java util GregorianCalend
86. L BETWEEN predicate also works well for row value expressions Much like the BETWEEN predicate for degree 1 it is defined in terms of a pair of regular comparison predicates A BETWEEN B AND C A A BETWEEN SYMMETRIC B AND C A C OR A gt C AND A lt B The above can be factored out according to the rules listed in the manual s section about row value expression comparison predicates jOOQ supports the BETWEEN SYMMETRIC predicate and emulates it in all SQL dialects where necessary An example is given here row BOOK ID BOOK TITLE between 1 A and 10 Z 4 6 11 LIKE predicate LIKE predicates are popular for simple wildcard enabled pattern matching Supported wildcards in all SQL databases are _ single character wildcard multi character wildcard With jOOQ the LIKE predicate can be created from any column expression as such TITLE LIKE abc BOOK TITLE like abc TITLE NOT LIKE abc BOOK TITLE notLike abcs Escaping operands with the LIKE predicate Often your pattern may contain any of the wildcard characters _ and in case of which you may want to escape them jOOQ does not automatically escape patterns in like and notLike methods Instead you can explicitly define an escape character as such TITLE LIKE the Sign Books ESCAPE rI BOOK TITLE like The Sign Book TITLE NOT
87. LIKE SThe Sign Book ESCAPE BOOK TITLE notLike The Sign Book O 2009 2014 by Data Geekery GmbH All rights reserved Page 88 174 The jOOQ User Manual 4 6 12 IN predicate In the above predicate expressions the exclamation mark character is passed as the escape character to escape wildcard characters _ and as well as to escape the escape character itself Please refer to your database manual for more details about escaping patterns with the LIKE predicate jOOQ s convenience methods using the LIKE predicate In addition to the above jOOQ provides a few convenience methods for common operations performed on strings using the LIKE predicate Typical operations are contains predicates starts with predicates ends with predicates etc Here is the full convenience API wrapping LIKE predicates case insensitivity case insensitivity LOWER TITLE LIKE LOWER abc BOOK TITLE likeIgnoreCase abc LOWER TITLE NOT LIKE LOWER abc BOOK TITLE notLikeIgnoreCase Sabc contains and similar methods contains and similar methods ARIE a Mee as Marl BOOK TITLE contains abc TITLE LIKE Mabel i 7 BOOK TITLE startsWith abc TITLE LIKE abc BOOK TITLE endsWith abc Note that jOOQ escapes and _ characters in value in some of the above predicate implementations For simplicity this has been omitted in this manual 4 6 1
88. NULL you ll get A NOT IN B NULL Substitute for NULL A B AND A NULL From the above rules A B AND NULL ANY NULL yields NULL NULL ANY AND NULL yields NULL 2009 2014 by Data Geekery GmbH All rights reserved Page 89 174 The jOOQ User Manual 4 6 13 IN predicate degree gt 1 A good way to prevent this from happening is to use the EXISTS predicate for anti joins which is NULL value insensitive See the manual s section about conditional expressions to see a boolean truth table 4 6 13 IN predicate degree gt 1 The SQL IN predicate also works well for row value expressions Much like the IN predicate for degree 1 it is defined in terms of a quantified comparison predicate The two expressions are equivalent R IN IN predicate value R ANY IN predicate value jOOQ supports the IN predicate Emulation of the IN predicate where row value expressions aren t well supported is currently only available for IN predicates that do not take a subselect as an IN predicate value An example is given here row BOOK ID BOOKTITLE ini rowll ATI cowl 2 Bis 4 6 14 EXISTS predicate Slightly less intuitive yet more powerful than the previously discussed IN predicate is the EXISTS predicate that can be used to form semi joins or anti joins With jOOQ the EXISTS predicate can be formed in various ways From the DSL using static methods This is
89. O0Q Result into a ResultSet Result lt BookRecord gt result create selectFrom BOOK fetch ResultSet rs result intoResultSet The inverse Fetch data from a legacy ResultSet using JOOQ The inverse of the above is possible too Maybe a legacy part of your application produces JDBC java sql ResultSet and you want to turn them into a org joog Result Transform a JDBC ResultSet into a j000 Result ResultSet rs connection createStatement executeQuery SELECT FROM BOOK As a Result Result lt Record gt result create fetch rs As a Cursor Cursor lt Record gt cursor create fetchLazy rs You can also tighten the interaction with jOOQ s data type system and data type conversion features by passing the record type to the above fetch methods 2009 2014 by Data Geekery GmbH All rights reserved Page 116 174 The jOOQ User Manual Pass an array of types Resullt lt Record gt result create Cursor lt Record gt result create Pass an array of data types Resullt lt Record gt result create Cursor lt rRecord gt result create Pass an array of fields Result lt Record gt result create Cursor lt Record gt result create fetch a fetchLazy rs fetch SA fetchLazy rs fetch Mo fetchLazy rs 5 3 11 Data type conversion imteger class ot sing classi integer class String class SQLDataType INTEGER SQLDataType INTEGER BOO
90. OIN BOOK ON AUTHOR ID AUTHOR_ID GROUP BY FIRST_NAME LAST_NAME Here is how it s done with jOOQ Record record create select Whe concat AUTHOR FIRST_NAME val AUTHOR LAST_NAME as author count as books from AUTHOR Join BOOK on AUTHOR ID equal BOOK AUTHOR_ID groupBy AUTHOR FIRST_NAME AUTHOR LAST_NAME fetchAny n you alias Fields like above you can access those Fields values using the alias name System out println Author record getValue author System out println Books record getValue books ti 4 5 3 Cast expressions jOOQ s source code generator tries to find the most acc specific data types and a matching Java type For instan to String Most BINARY BYTEA BLOB types will map to byte NUM math BigDecimal but can also be any of java math Biginteger java lang Long java lang Integer map java java ce most VARCHA urate type mapping between your vendor R CHAR CLOB types will ERIC types will default to ang Short java lang Byte java lang Double java lang loat Som etimes this automatic mapping might not be what yo a fie 2009 2014 by Data Geekery GmbH All rights reserved u needed or jOOQ cannot know the type of d In those cases you would write SQL type CAST like this Page 67 174 The jOOQ User Manual 4 5 4 Arithmetic expressions Let s say your Postgres
91. OQ API O 2009 2014 by Data Geekery GmbH All rights reserved Page 18 174 The jOOQ User Manual 3 3 5 jOOQ for PROs 3 3 5 JOOQ for PROs jOOQ isn t just a library that helps you build and execute SQL against your generated compilable schema jOOQ ships with a lot of tools Here are some of the most important tools shipped with jOOQ jOOQ Console This small application hooks into jOOQ s execute listener support to allow for tracing debugging and introspecting any SQL statement executed through the jOOQ API This includes setting breakpoints introspecting bind values running probe SQL statements ad hoc patching of SQL measuring execution times exporting stack traces Use this tool to better know your SQL jOOQ s Execute Listeners jOOQ allows you to hook your custom execute listeners into jOOQ s SQL statement execution lifecycle in order to centrally coordinate any arbitrary operation performed on SQL being executed Use this for logging identity generation SQL tracing performance measurements etc Logging jOOQ has a standard DEBUG logger built in for logging and tracing all your executed SQL statements and fetched result sets Stored Procedures jOOQ supports stored procedures and functions of your favourite database All routines and user defined types are generated and can be included in jOOQ s SQL building API as function references Batch execution Batch execution is important when executing a big
92. Override public void setId Integer value setValue BOOK ID value Id Column name ID unique true nullable false precision 7 Override public Integer getId return getValue BOOK ID More setters and getters public void setAuthorId Integer value public Integer getAuthorld Convenience methods for foreign key methods public void setAuthorld AuthorRecord value if value null setValue BOOK AUTHOR_ID null erse if setValue BOOK AUTHOR_ID value getValue AUTHOR ID Navigation methods public AuthorRecord fetchAuthor return create selectFrom AUTHOR where AUTHOR ID equal getValue BOOK AUTHOR_ID fetchOne Mes all Flags influencing generated records These flags from the code generation configuration influence generated records dateAsTimestamp This influences all relevant getters and setters unsignedTypes This influences all relevant getters and setters relations This is needed as a prerequisite for navigation methods daos Records are a pre requisite for DAOs If DAOs are generated records are generated as well interfaces If interfaces are generated records will implement them jpaAnnotations JPA annotations are used on generated records Flags controlling record generation Record generation can be deactivated using the records flag 6 6 Generated POJOs Every table in you
93. POJO classes there doesn t need to be an exact match between fields and constructor arguments In the below cases only BOOK ID is really set onto the POJO BOOK TITLE remains null and BOOK AUTHOR_ID is ignored MyBook3 myBook create select BOOK ID BOOK AUTHOR_ID from BOOK fetchAny into MyBook3 class List lt MyBook3 gt myBooks create select BOOK ID BOOK AUTHOR_ID from BOOK fetch into MyBook3 class List lt MyBook3 gt myBooks create select BOOK ID BOOK AUTHOR_ID from BOOK fetchInto MyBook3 class 2009 2014 by Data Geekery GmbH All rights reserved Page 111 174 The jOOQ User Manual 5 3 6 POJOs Please refer to the Record into Javadoc for more details Using proxyable types jOOQ also allows for fetching data into abstract classes or interfaces or in other words proxyable types This means that jOOQ will return a java util HashMap wrapped in a java lang reflect Proxy implementing your custom type An example of this is given here A proxyable type public interface MyBook3 int getId void setId int id String getTitle vord setIitle string title The various into methods allow for fetching records into your custom POJOs MyBook3 myBook create select BOOK ID BOOK TITLE from BOOK fetchAny into MyBook3 class List lt MyBook3 gt myBooks create select BOOK ID BOOK TITLE from BOOK fetch into MyBook3 class List lt MyBook3 gt myBo
94. Q most conditional expressions are built from column expressions calling various methods on them For instance to build a comparison predicate you can write the following expression O 2009 2014 by Data Geekery GmbH All rights reserved Page 82 174 The jOOQ User Manual 4 6 2 AND OR NOT boolean operators TITLE Animal Farm BOOK TITLE equal Animal Farm TITLE Animal Farm BOOK TITLE notEqual Animal Farm Create conditions from the DSL There are a few types of conditions that can be created statically from the DSL These are plain SQL conditions that allow you to phrase your own SQL string conditional expression The EXISTS predicate a standalone predicate that creates a conditional expression Constant TRUE and FALSE conditional expressions Connect conditions using boolean operators Conditions can also be connected using boolean operators as will be discussed in a subsequent chapter 4 6 2 AND OR NOT boolean operators In SQL as in most other languages conditional expressions can be connected using the AND and OR binary operators as well as the NOT unary operator to form new conditional expressions In jOOQ this is modelled as such A simple conditional expression A simple boolean connection TITLE Animal Farm OR TITLE 1984 BOOK TITLE equal Animal Farm or BOOK TITLE equal 1984 A more complex conditional expression A more
95. Q User Manual 4 6 8 DISTINCT predicate 4 6 8 DISTINCT predicate Some databases supportthe DISTINCT predicate which serves as a convenient NULL safe comparison predicate With the DISTINCT predicate the following truth table can be assumed ANY IS DISTINCT FROM NULL yields TRUE ANY IS NOT DISTINCT FROM NULL yields FALSE NULL IS DISTINCT FROM NULL yields FALSE ULL IS NOT DISTINCT FROM NULL yields TRUE For instance you can compare two fields for distinctness ignoring the fact that any of the two could be NULL which would lead to funny results This is supported by jOOQ as such TITLE IS DISTINCT FROM SUB TITLE BOOK TITLE isDistinctFrom BOOK SUB_TITLE TITLE IS NOT DISTINCT FROM SUB_TITLE BOOK TITLE isNotDistinctFrom BOOK SUB_TITLE If your database does not natively support the DISTINCT predicate OOQ emulates it with an equivalent CASE expression modelling the above truth table A 1S DISTINCT FROM B A IS NOT DISTINCT FROM B CASE WHEN A IS NULL AND B IS NULL THEN FALSE CASE WHEN A IS NULL AND B IS NULL THEN TRUE WHEN A IS NULL AND B IS NOT NULL THEN TRUE WHEN A IS NULL AND B IS NOT NULL THEN FALSE WHEN A IS NOT NULL AND B IS NULL THEN TRUE WHEN A IS NOT NULL AND B IS NULL THEN FALSE WHEN A B THEN FALSE WHEN A B THEN TRUE ELSE TRUE ELSE FALSE END END 4 6 9 BETWEEN predicate T
96. QL jOOQ users often get excited about jOOQ s intuitive API and would then wish for NoSQL support There are a variety of NOSQL databases that implement some sort of proprietary query language Some of these query languages even look like SQL Examples are JCR SQL2 CQL Cassandra Query Language Cypher Neo4j s Query Language SOQL Salesforce Query Language and many more Mapping the jOOQ API onto these alternative query languages would be a very poor fit and a leaky abstraction We believe in the power and expressivity of the SQL standard and its various dialects Databases that extend this standard too much or implement it not thoroughly enough are often not suitable targets for jOOQ It would be better to build a new dedicated API for just that one particular query language jOOQ is about SQL and about SQL alone Read more about our visions in the manual s preface 3 7 Dependencies Dependencies are a big hassle in modern software Many libraries depend on other non JDK library parts that come in different incompatible versions potentially causing trouble in your runtime environment jOOQ has no external dependencies on any third party libraries However the above rule has some exceptions 2009 2014 by Data Geekery GmbH All rights reserved Page 26 174 The jOOQ User Manual 3 8 Build your own logging APIs are referenced as optional dependencies jOOQ tries to find slf4j or log4j on
97. QL is much more expressive than many other SQL dialects It features many unique keywords clauses and functions that are out of scope for the SQL standard Some examples for this are The CONNECT BY clause for hierarchical queries The PIVOT keyword for creating PIVOT tables Packages object oriented user defined types member procedures as described in the section about stored procedures and functions Advanced analytical functions as described in the section about window functions jOOQ has a historic affinity to Oracle s SQL extensions If something is supported in Oracle SQL it has a high probability of making it into the jOOQ API 4 2 2 Connection vs DataSource Interact with JDBC Connections While you can use jOOQ for SQL building only you can also run queries against a JDBC java sql Connection Internally OOQ creates java sql Statement or java sql PreparedStatement objects O 2009 2014 by Data Geekery GmbH All rights reserved Page 32 174 The jOOQ User Manual 4 2 3 Custom data from such a Connection in order to execute statements The normal operation mode is to provide a Configuration with a JDBC Connection whose lifecycle you will control yourself This means that jOOQ will not actively close connections rollback or commit transactions Note in this case jOOQ will internally use a org joog impl DefaultConnectionProvider which you can reference directly if you prefer tha
98. R JOIN CROSS JOIN NATURAL JOIN a NATURAL LEFT OUTER JOIN E NATURAL RIGHT OUTER JOIN O 2009 2014 by Data Geekery GmbH All rights reserved Page 42 174 The jOOQ User Manual 4 3 2 3 The JOIN clause FROM clause for All of these JOIN methods can be called on org joog Table types or directly after the convenience The following example joins AUTHOR and BOOK DSLContext create DSL using connection dialect Call join directly on the AUTHOR table Result lt gt result create select from AUTHOR join BOOK on BOOK AUTHOR_ID equal AUTHOR ID fetch Call join on the type returned by from Result lt gt result create select from AUTHOR Join BOOK on BOOK AUTHOR_ID equal AUTHOR ID fetechi The two syntaxes will produce the same SQL statement However calling join on org jooq Table objects allows for more powerful nested JOIN expressions if you can handle the parentheses SELECT FROM AUTHOR LEFT OUTER JOIN Nest joins and provide JOIN conditions only at the end create select from AUTHOR BOOK JOIN BOOK_TO_BOOK_STORE ON BOOK_TO_BOOK_STORE BOOK_ID BOOK ID ON BOOK AUTHOR_ID AUTHOR ID leftOuterJoin BOOK join BOOK_TO_BOOK_STORE on BOOK_TO_BOOK_STORE BOOK_ID equal BOOK ID on BOOK AUTHOR_ID equal AUTHOR ID See the section about conditional expressions to lea
99. ROCUREMENT OF SUBSTITUTE GOODS OR SERVICES LOSS OF USE DATA OR PROFITS OR BUSINESS INTERRUPTION HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY WHETHER IN CONTRACT STRICT LIABILITY OR TORT INCLUDING NEGLIGENCE OR OTHERWISE ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE 2009 2014 by Data Geekery GmbH All rights reserved Page 9 174 The jOOQ User Manual License for jOOQ 3 2 and later Copyright c 2009 2015 Data Geekery GmbH http www datageekery com All rights reserved This work is dual licensed under the Apache Software License 2 0 the ASL You may choose which license applies to you If you re using this work with Open Source databases you may choose either ASL or j00Q License If you re using this work with at least one commercial database you must choose j00Q License For more information please visit http www jooq org licenses Apache Software License 2 0 Licensed under the Apache License Version 2 0 the License you may not use this file except in compliance with the License You may obtain a copy of the License at http www apache org licenses LICENSE 2 0 Unless required by applicable law or agreed to in writing software distributed under the License is distributed on an AS IS BASIS WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND either express or implied See the License for the specific language governing permissio
100. ROM SELECT null a null b FROM DUAL WHERE 1 0 UNION ALL SELECT 1 2 FROM DUAL VE In jOOQ you would simply specify a varargs list of column aliases as such Unnested tables cereate select from unnest myTableFunction as t a ber VALUES constructor create select from values OZ row 3 4 as t a b 4 4 3 Joined tables The JOIN operators that can be used in SQL SELECT statements are the most powerful and best supported means of creating new table expressions in SQL Informally the following can be said Ia aar COLAR Jorn a oleh ys GOLEM produces CIcorlnl r SOLR OLB r SOLBM SQL and relational algebra distinguish between at least the following JOIN types upper case SQL lower case relational algebra 2009 2014 by Data Geekery GmbH All rights reserved Page 61 174 The jOOQ User Manual 4 4 4 The VALUES table constructor CROSS JOIN or cartesian product The basic JOIN in SQL producing a relational cross product combining every record of table A with every record of table B Note that cartesian products can so be produced by listing comma separated table expressions in the FROM clause of a SELECT statement ATURAL JOIN The basic JOIN in relational algebra yet a rarely used JOIN in databases with everyday degree of normalisation This JOIN type unconditionally equi joins two tables by all columns with the same name requ
101. Records not for Tables public class AsInDatabaseStrategy extends DefaultGeneratorStrategy Override this to specifiy what identifiers in Java should look like This will just take th id ntifier as defined in the databa g wa Override public String getJavaIdentifier Definition definition return definition getOutputName Override these to specify what a setter in Java should look like Setters are used in TableRecords UDTRecords and POJOs This example will name setters set NAME_IN_DATABASE wy Override public String getJavaSetterName Definition definition Mode mode return set definition getOutputName gust like setters we Override public String getJavaGetterName Definition definition Mode mode return get definition getOutputName Override this method to define what a Java method generated from a database Definition should look like This is used mostly for convenience methods when calling stored procedures and functions This example shows how to s t a prefix to a CamelCase version of your procedure Es Override public String getJavaMethodName Definition definition Mode mode return call org jooqg tools StringUtils toCamelCase definition getOutputName Override this method to define how your Java classes and Java files should be named This example applies no custom setting and uses CamelCase versions instead 2 Overrid
102. S book id language id name ON DELETE CASCADE ON DELETE CASCADE 3 3 Different use cases for jOOQ More entities types e g UDT s ARRAY types ENUM types etc stored procedures and packages are introduced for specific e xamples In addition to the above you may assume the following sample data INSERT INSERT INSERT INSERT INTO INTO INTO INTO language language language language felch xel tol A CE fel lol ol INSERT INTO VALUES INSERT INTO VALUES PILS I e George apabiatshes aint Batido INSERT INTO VALUES INSERT INTO VALUES INSERT INTO VALUES INSERT INTO VALUES book author_id pede author_id els author_id po author_id 7 book book book INSERT INSERT INSERT INTO INTO INTO book_store VALUES book_store VALUES book_store VALUES INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO book_to_book_store book_to_book_store book_to_book_store book_to_book_store book_to_book_store book_to_book_store VALUES VALUES VALUES VALUES escription escription escription escription ame last_name da E FOWLERI y ame last_name COSO da ETS E AO K pe EIES Animal Farm pe ETELS O Alquimista pe titie y BRA Orell Fiissli ESC BASA VALUES Orell F s VALUES Orell F s VALUES Orell E s VALUES Ex Libris VALUES Ex Libris VALUES Buchhandl DATE DATE p
103. Se S 1 Gf 1 Sn ARAN A431 jOOQ S DSL And mod AP Ms crisisen ovat eds a a A dea 4 3 2 The SELECT State mente minorare aa a A E A aos 422 h Me SELECT da US DS o a a aa bee a 40 AB D2 eS FROM adas ta a a a 42 Az MO O INES is A A id a ATA 42 4 324 THO A eesscvassttenadusag EEA E AA aA E TOTA wish eds evba devs CEARA TOTA EEE A TTA AA eaten eibateds 44 43A The CONNECT BY Claus a 45 4320 ME GROUP BY a o a 46 AS D7 MS A MING CAUSE a A N A A A a da 47 AIRE INE ORDER BY ie lat n a O E sus ds toevearta tenable a nett iterate a tea 47 AFJ The LIMIT x OFESET US da clive a ad ad dos 0 49 O 2009 2014 by Data Geekery GmbH All rights reserved Page 3 174 The jOOQ User Manual AB 210 Me FOR UPDATE Cla USCr cists a a E a cana 43 211 UNION INTERSECTION AN GHEXGEP racista psu tte decstesiaseons deta ia 4 3 2 12 Oracle Style NINtS cece te teseeeeeeeee 4 3 2 13 Lexical and logical SELECT clause order Aa INe INSER Rac ee ll i A 4 3 4 The UPDATE statement ma Aa The DELETE Stadt ara it AEA a AO NS MERCES racista As Ine TRUNCATE Statement nine A4 Table expres aaa AAN Generated Table ainia aca EI ON AAS ANC ta i 61 AAAS TAG VALUESO table GCOMSTEU CLO alado 62 4 45 Nested SELECT Si ass 63 446 The Orce E PIVOT AUS ii aaa o da 64 AAT OGOS relational AiVision Sy AA ld 64 4AB Array and cursor UNNESTNE neninn a E A E E eee es ead A an Nees 65 4 4 9 The DUAL table 4 5 Column expressions
104. T in other SQL dialects The following query groups by authors and concatenates their books titles SELECT LISTAGG TITLE create select listAgg BOOK TITLE WITHIN GROUP ORDER BY TITLE withinGroupOrderBy BOOK TITLE FROM BOOK from BOOK GROUP BY AUTHOR_ID groupBy BOOK AUTHOR_ID The above query might yield 1984 Animal Farm O Alquimista Brida O 2009 2014 by Data Geekery GmbH All rights reserved Page 73 174 The jOOQ User Manual 4 5 13 Window functions FIRST and LAST Oracle s ranked aggregate functions Oracle allows for restricting aggregate functions using the KEEP clause which is supported by jOOQ In Oracle some aggregate functions MIN MAX SUM AVG COUNT VARIANCE or STDDEV can be restricted by this clause hence org joog AggregateFunction also allows for specifying it Here are a couple of examples using this clause SUM BOOK AMOUNT_SOLD sum BOOK AMOUNT_SOLD KEEP DENSE_RANK FIRST ORDER BY BOOK AUTHOR_ID keepDenseRankFirstOrderBy BOOK AUTHOR_ID User defined aggregate functions jOOQ also supports using your own user defined aggregate functions See the manual s section about user defined aggregate functions for more details Window functions analytical functions In those databases that support window functions jOOQ s orgjoog AggregateFunction can be transformed into a window function analytical function by calling
105. 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 OOQ 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 5 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 console Reference This section is a reference for elements in this manual O 2009 2014 by Data Geekery GmbH All rights reserved Page 2 174 The jOOQ User Manual Table of contents
106. VALUE END IF RETURN ODCIConst Success END MEMBER FUNCTION ODCIAggregateTerminate self IN U_SECOND_MAX returnValue OUT NUMBER flags IN NUMBER RETURN NUMBER IS BEGIN RETURNVALUE SELF SECMAX RETURN ODCIConst Success END MEMBER FUNCTION ODCIAggregateMerge self IN OUT U_SECOND_MAX ctx2 IN U_SECOND_MAX RETURN NUMBER IS BEGIN IF CTX2 MAX gt SELF MAX THEN IF CTX2 SECMAX gt SELF SECMAX THEN SELF SECMAX CTX2 SECMAX ELSE SELF SECMAX SELF MAX END IF SELF MAX CTX2 MAX ELSIF CTX2 MAX gt SELF SECMAX THEN SELF SECMAX CTX2 MAX END IF RETURN ODCIConst Success END END The above OBJECT type is then available to function declarations as such CREATE FUNCTION SECOND_MAX input NUMBER RETURN NUMBER PARALLEL ENABLE AGGREGATE USING U_SECOND_MAX Using the generated aggregate function jOOQ s code generator will detect such aggregate functions and generate them differently from regular user defined functions They implement the org jooq AggregateFunction type as mentioned in the manual s section about aggregate functions Here s how you can use the SECOND_MAX aggregate function with OOQ Get the second latest publishing date by author Routines secondMax can be static imported SELECT SECOND_MAX PUBLISHED_IN create select secondMax BOOK PUBLISHED_IN FROM BOOK from BOOK GROUP BY AUTHOR_ID groupBy BOOK AUTHOR_ID 4 5 17 The CASE expression The CASE expre
107. When daos true then jOOQ will set records true When daos true then jOOQ will set pojos true When immutablePojos true then jOOQ will set pojos true 2009 2014 by Data Geekery GmbH All rights reserved Page 151 174 The jOOQ User Manual 6 3 Generated global artefacts 6 3 Generated global artefacts For increased convenience at the use site OOQ generates global artefacts at the code generation root location referencing tables routines sequences etc In detail these global artefacts include the following Keys java This file contains all of the required primary key unique key foreign key and identity references in the form of static members of type org joog Key Routines java This file contains all standalone routines not in packages in the form of static factory methods for org joog Routine types Sequences java This file contains all sequence objects in the form of static members of type org joog Sequence Tables java This file contains all table objects in the form of static member references to the actual singleton org jooq Table object UDTs java This file contains all UDT objects in the form of static member references to the actual singleton org jooq UDT object Referencing global artefacts When referencing global artefacts from your client application you would typically static import them as such Static imports for all global
108. Y DATE_OF_BIRTH ASC LIMIT 1 orderBy AUTHOR DATE_OF_BIRTH asc limit 1 UNION Ron SELECT FROM AUTHOR create selectFrom AUTHOR ORDER BY DATE_OF_BIRTH DESC LIMIT 1 OrderBy AUTHOR DATE_OF_BIRTH desc limit 1 O 2009 2014 by Data Geekery GmbH All rights reserved Page 52 4 74 The jOOQ User Manual 4 3 2 12 Oracle style hints Projection typesafety for degrees between 1 and 22 Two subselects that are combined by a set operator are required to be of the same degree and in most databases also of the same type jOOQ 3 0 s introduction of Typesafe Record N types helps compile checking these constraints Some sample SELECT statements select lt Record2 lt Integer String gt gt sl select BOOK ID BOOK TITLE from BOOK Select lt Recordl lt Integer gt gt s2 selectOne select lt Record2 lt Integer Integer gt gt s3 select one zexo Select lt Record2 lt Integer String gt gt s4 select one inline abc Let s try to combine them sl union s2 Doesn t compile because of a degree mismatch Expected Record2 lt gt got Recordl lt gt sl union s3 Doesn t compile because of a type mismatch Expected lt Integer String gt got lt Integer Integer gt sl union s4 OK The two Record N types match 4 3 2 12 Oracle style hints If you are closely coupling your application to an Oracle or CUBRID database you might need to be able to pass hints of
109. Y_PATH E PRIOR Note that this syntax is also supported in the CUBRID database and might be emulated in other dialects supporting common table expressions in the future O 2009 2014 by Data Geekery GmbH All rights reserved Page 45 174 The jOOQ User Manual 4 3 2 6 The GROUP BY clause ORDER SIBLINGS The Oracle database allows for specifying a SIBLINGS keyword in the ORDER BY clause Instead of ordering the overall result this will only order siblings among each other keeping the hierarchy intact An example is given here SELECT DIRECTORY NAME select DIRECTORY NAME FROM DIRECTORY from DIRECTORY CONNECT BY CconnectBy PRIOR DIRECTORY ID DIRECTORY PARENT_ID prior DIRECTORY ID equal DIRECTORY PARENT_ID START WITH DIRECTORY PARENT_ID IS NULL StartWith DIRECTORY PARENT_ID isNull ORDER SIBLINGS BY 1 orderSiblingsBy 1 4 3 2 6 The GROUP BY clause GROUP BY can be used to create unique groups of data to form aggregations to remove duplicates and for other reasons It will transform your previously defined set of table expressions and return only one record per unique group as specified in this clause For instance you can group books by BOOK AUTHOR_ID SELECT AUTHOR_ID COUNT create select BOOK AUTHOR_ID count FROM BOOK from BOOK GROUP BY AUTHOR_ID groupBy BOOK AUTHOR_ID The above example counts all books per author Note as defi
110. _ID ORDER BY BOOK ID rowNumber over partitionBy BOOK AUTHOR_ID orderBy BOOK ID Sample uses of FIRST_VALUE Sample uses of firstValue FIRST_VALUE BOOK ID OVER firstValue BOOK ID over FIRST_VALUE BOOK ID IGNORE NULLS OVER firstValue BOOK ID ignoreNulls over FIRST_VALUE BOOK ID RESPECT NULLS OVER firstValue BOOK ID respectNulls over An advanced window function example Window functions can be used for things like calculating a running total The following example fetches transactions and the running total for every transaction going back to the beginning of the transaction table ordered by booked_at Window functions are accessible from the previously seen org joog AggregateFunction type using the over method SELECT booked_at amount create select t BOOKED_AT t AMOUNT SUM amount OVER PARTITION BY 1 sum t AMOUNT over partitionByOne ORDER BY booked_at OrderBy t BOOKED_AT ROWS BETWEEN UNBOUNDED PRECEDING rowsBet weenUnboundedPreceding AND CURRENT ROW AS total andCurrentRow as total FROM transactions from TRANSACTIONS as t Window functions created from ordered set aggregate functions In the previous chapter about aggregate functions we have seen the concept of ordered set aggregate functions such as Oracle s LISTAGG These functions have a window function analytical function variant as well For example SELECT LIST
111. a E Page a he English Deutsch Francais Portugu s te_of_birth e AUS te_of birth 1947 08 24 year_of_birth 1903 year_of_birth 1947 published_in language_id 1948 o E Ve published_in language_id 1945 ek published_in language_id 1988 rn Ne published_in language_id t990 a Ve Buchhandlung im Volkshaus Sita Hala ON Sula Y 2 10 Silat En LD Alo dde PR AA YA ung im Volkshaus 3 1 di di 3 3 Different use cases for JOOQ jOOQ has originally been created as a library for complete abstraction of JDBC and all database interaction Various best practices that are frequently encountered in pre existing software products are applied to this library This includes 2009 2014 by Data Geekery GmbH All rights reserved Page 15 174 The jOOQ User Manual 3 3 1 jOOQ as a SQL builder Typesafe database object referencing through generated schema table column record procedure type dao pojo artefacts see the chapter about code generation Typesafe SQL construction SQL building through a complete querying DSL API modelling SQL as a domain specific language in Java see the chapter about the query DSL API Convenient query execution through an improved API for result fetching see the chapters about the various types of data fetching SQL dialect abstraction and SQL clause emulation to improve cross database compatibility and to enable missing fe
112. ability for some more insight on attached objects 5 11 5 Non updatable records Tables without a PRIMARY KEY are considered non updatable by jOOQ as jOOQ has no way of uniquely identifying such a record within the database If you re using jOOQ s code generator such tables will generate org joog lableRecord classes instead of org joog UpdatableRecord classes When you fetch typed records from such a table the returned records will not allow for calling any of the store refresh delete methods Note that some databases use internal rowid or object id values to identify such records jOOQ does not support these vendor specific record meta data 5 11 6 Optimistic locking jOOQ allows you to perform CRUD operations using optimistic locking You can immediately take advantage of this feature by activating the relevant executeWithOptimisticLocking Setting Without any further knowledge of the underlying data semantics this will have the following impact on store and delete methods INSERT statements are not affected by this Setting flag Prior to UPDATE or DELETE statements OOQ will run a S CT FOR UPDATE statement pessimistically locking the record for the subsequent UPDATE DELET The data fetched with the previous SELECT will be compared against the data in the record being stored or deleted An org joog exception DataChangedException is thrown if the record had been modified in the
113. an 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 tis very hard to formally express and validate joins and the ad hoc table expression types they create t 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 n 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 1 999 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 2014 by Data Geekery GmbH A
114. anual 6 2 Advanced generator configuration i These properties canbe added to the geherate element gt lt generate gt lt Primary key foreign key relations should be generated and used This is a prerequisite for various advanced features Defaite to CNA TSS lt relations gt true lt relations gt lt Generate deprecated code for backwards compatibility Defauttr a to TARE lt deprecated gt true lt deprecated gt lt Generate instance fields in your tables as opposed to static fields This simplifies aliasing Defaults to true gt lt instanceFields gt true lt instanceFields gt lt Generate the javax annotation Generated annotation to indicate jOOQ version used for source code Detail seto either s lt generatedAnnotation gt true lt generatedAnnotation gt xh Generate 1000 Record classes for type sare querying You can turn this off if you don t need active records for CRUD Defaults ote true gt lt records gt true lt records gt ql Generate POv0s in addition to R cord classes for usage of the ResultQuery fetchInto Class API Defaults to farges lt pojos gt false lt pojos gt lt Generate immutable POJOs for usage of the ResultQuery fetchInto Class API This overrides any value set in lt pojos gt Defaults to falge gt lt immutablePojos gt false lt immutablePojos gt lt Generate interfaces that will be implemented by records and or pojos You can also use these
115. ar O 2009 2014 by Data Geekery GmbH All rights reserved Page 117 174 The jOOQ User Manual 5 3 12 Interning data You may prefer Java Calendars over JDBC Timestamps public class CalendarConverter implements Converter lt Timestamp GregorianCalendar gt Override public GregorianCalendar from Timestamp databaseObject GregorianCalendar calendar GregorianCalendar Calendar getInstance calendar setTimeInMillis databaseObject getTime return calendar Override public Timestamp to GregorianCalendar userObject return new Timestamp userObject getTime getTime Override public Class lt Timestamp gt fromType return Timestamp class Override public Class lt GregorianCalendar gt toType return GregorianCalendar class Now you can fetch calendar values from jO0OQ s API List lt GregorianCalendar gt datesl create selectFrom BOOK fetch getValues BOOK PUBLISHING_DATE new CalendarConverter List lt GregorianCalendar gt dates2 create selectFrom BOOK fetch BOOK PUBLISHING_DATE new CalendarConverter Enum Converters jOOQ ships with a built in default org joog impl EnumConverter that you can use to map VARCHAR values to enum literals or NUMBER values to enum ordinals both modes are supported Let s say you want to map a YES NO MAYBE column to a custom Enum Define your Enum public enum YNM YES NO MAYBE
116. arameter You can then use that name for accessing the parameter again Query queryl create select from AUTHOR where LAST_NAME equal param lastName Poe Param lt gt paraml query getParam lastName Or keep a reference to the typed parameter in order not to lose the lt T gt type information Param lt String gt param2 param lastName Poe Query query2 create select from AUTHOR where LAST_NAME equal param2 You can now change the bind value directly on the Param reference param2 setValue Orwell The org joog Query interface also allows for setting new bind values directly without accessing the Param type Query queryl create select from AUTHOR where LAST_NAME equal Poe queryl bind 1 Orwell Or with named parameters Query query2 create select from AUTHOR where LAST_NAME equal param lastName Poe query2 bind lastName Orwell In order to actually render named parameter names in generated SQL use the DSLContext renderNamedParams method 2009 2014 by Data Geekery GmbH All rights reserved Page 95 174 The jOOQ User Manual 4 8 3 Inlined parameters create renderNamedParams The named bind variable can be rendered create select from AUTHOR SELECT where LAST_NAME equal FROM AUTHOR param lastName Poe WHERE LAST_NAME lastName 4 8 3 Inlined parameters Sometimes you may wish to avoid renderi
117. arget package AQ SAS Of ALL impl JooqLogger MYSQL impl JooqLogger guestbook impl JooqLogger info info C workspace MySQLTest src impl JooqLogger test generated impl JooqLogger info info Ay SAAS Oil AIL Emptying AN ASOT PM org Joog Generating classes in AO AS ONE Generating schema ZO ae 2 5 10 TEM Schema generated ZO AO ONE Sequences fetched PAOLA AS OTCEM Tables fetched AA O AEM 2011 7 t2507 PM ARRAYs fetched ZOLIA EAS O TEBA Enums fetched Ze Ab OT PM UDTs fetched Adal SSeS S O RED Generating table 20L AS AO REM Tables generated ZOTIT EASO TPM Generating Keys ZOTLI OSOS PM Keys generated AE AS OPM org org org org org org org org org org org org org Generating records ZOD AS OEPM Generating record ZOTINI ZOSO PM org org INFO Table records generated Nov 1 INFO Nov 1 INFO Nov 1 AACA SS OEPM Routines fetched ANAL SHAS OR 1904 Packages fetched AMAL OZ SOs ALE org org org INFO GENERATION FINISHED ESOO POO EOS jooq jooq jooq joog jooq jooq jooq jooq jooq jooq jooq jooq jooq jooq jooq imp1 JooqLogger info C workspace MySQLTest src test generated impl JooqLogger info C workspace MySQLTest src test generated impl JooqLogger Guestbook java impl JooqLogger Total 122 18ms impl JooqLogger O 0 included
118. artefacts if they exist import static com example generated Keys import static com example generated Routines import static com example generated Sequences import static com example generated Tables You could then reference your artefacts as follows create insertInto MY_TABLE values MY_SEQUENCE nextval myFunction as a more concise form of this create insertInto com example generated Tables MY_TABLE values com example generated Sequences MY SEQUENCE nextval com example generated Routines myFunction 6 4 Generated tables Every table in your database will generate a org jooq Table implementation that looks like this public class Book extends Tablelmpl lt BookRecord gt The singleton instance public static final Book BOOK new Book Generated columns public final TableField lt BookRecord Integer gt ID createField ID SQOLDataType INTEGER this public final TableField lt BookRecord Integer gt AUTHOR_ID createField AUTHOR_ID SQLDataType INTEGER this public final TableField lt BookRecord String gt ITLE createField TITLE SQLDataType VARCHAR this Covariant aliasing method returning a table of the same type as BOOK Override public Book as java lang String alias return new Book alias Ii es all O 2009 2014 by Data Geekery GmbH All rights reserved Page 152 174 The jOOQ User Manual 6 5 Generated records Flags influencing gener
119. ated tables These flags from the code generation configuration influence generated tables recordVersionFields Relevant methods from super classes are overridden to return the VERSION field recordlimestampFields Relevant methods from super classes are overridden to return the TIMESTAMP field dateAsTimestamp This influences all relevant columns unsignedTypes This influences all relevant columns relations Relevant methods from super classes are overridden to provide primary key unique key foreign key and identity information instanceFields This flag controls the static keyword on table columns as well as aliasing convenience records The generated record type is referenced from tables allowing for type safe single table record fetching Flags controlling table generation Table generation cannot be deactivated 6 5 Generated records Every table in your database will generate an org joog Record implementation that looks like this O 2009 2014 by Data Geekery GmbH All rights reserved Page 153 174 The jOOQ User Manual 6 6 Generated POJOs JPA annotations can be generated optionally Entity GTable name BOOK schema TEST public class BookRecord extends UpdatableRecordImp1 lt BookRecord gt An interface common to records and pojos can be generated optionally implements IBook Every column generates a setter and a getter
120. ation directly if context inline Context sq BOOK UD Ss qua In non inline mode render a bind value else Context asgl BDOR AUD Sql 7 Override public void bind BindContext context try Manually bind the value 2 context statement setInt context nextIndex 2 Alternatively you could also write context bind DSL val 2 catch SQLException e throw new DataAccessException Bind error e y Use the above field in a SOL statement create select IDx2 from BOOK 4 9 5 Plain SQL QueryParts If you don t need the integration of rather complex QueryParts into jOOQ then you might be safer using simple Plain SQL functionality where you can provide jOOQ with a simple String representation of your embedded SQL Plain SQL methods in jOOQ s API come in two flavours O 2009 2014 by Data Geekery GmbH All rights reserved Page 100 174 The jOOQ User Manual 4 9 6 Serializability method String Object This is a method that accepts a SQL string and a list of bind values that are to be bound to the variables contained in the SQL string methoq String QueryPart This is a method that accepts a SQL string and a list of QueryParts that are injected at the position of their respective placeholders in the SQL string The above distinction is best explained using an example Plain SOL using bind values The value 5 is bound to the first varia
121. ation tests When you extend these classes you will have to provide your own implementations for the QueryParts toSQL and bind methods as discussed before This method must produce valid SQL If your QueryPart contains other parts you may delegate SQL generation to them Ji in the correct order passing the render context Life If context inline is true you must inline all bind variables If context inline is false you must generate for your bind variables public void toSQL RenderContext context This method must bind all bind variables to a PreparedStatement If your QueryPart contains other QueryParts you may delegate variable binding to them in the correct order passing the bind context Hf Every QueryPart must ensure that it starts binding its variables at context nextIndex public void bind BindContext context throws DataAccessException The above contract may be a bit tricky to understand at first The best thing is to check out jOOQ source code and have a look at a couple of QueryParts to see how it s done Here s an example org jooq impl CustomField showing how to create a field multiplying another field by 2 Create an anonymous CustomField initialised with BOOK ID arguments final Field lt Integer gt IDx2 new CustomField lt Integer gt BOOK ID getName BOOK ID getDataType Override public void toSQL RenderContext context In inline mode render the multiplic
122. atures in simpler databases see the chapter about SQL dialects SQL logging and debugging using jOOQ as an integral part of your development process see the chapters about logging and about the OOQ Console Effectively OOQ was originally designed to replace any other database abstraction framework short of the ones handling connection pooling and transaction management see also the credits for other database abstraction libraries Use jOOQ the way you prefer but open source is community driven And the community has shown various ways of using jOOQ that diverge from its original intent Some use cases encountered are Using Hibernate for 70 of the queries i e CRUD and jOOQ for the remaining 30 where SQL is really needed Using jOOQ for SQL building and JDBC for SQL execution Using jOOQ for SQL building and Spring Data for SQL execution Using jOOQ without the source code generator to build the basis of a framework for dynamic SQL execution The following sections explain about various use cases for using OOQ in your application 3 3 1 JOOQ as a SQL builder This is the most simple of all use cases allowing for construction of valid SQL for any database In this use case you will not use jOOQ s code generator and probably not even jOOQ s query execution facilities Instead you ll use jOOQ s query DSL API to wrap strings literals and other user defined objects into an obje
123. ause is commonly used to further restrict data resulting from a previously issued GROUP BY clause An example selecting only those authors that have written at least two books SELECT AUTHOR_ID COUNT create select BOOK AUTHOR_ID count FROM BOOK from BOOK GROUP BY AUTHOR_ID groupBy AUTHOR_ID HAVING COUNT gt 2 having count greaterOrEqual 2 According to the SQL standard you may omit the GROUP BY clause and still issue a HAVING clause This will implicitly GROUP BY jOOQ also supports this syntax The following example selects one record only if there are at least 4 books in the books table SELECT COUNT create select count FROM BOOK from BOOK HAVING COUNT gt 4 having count greaterOrEqual 4 4 3 2 8 The ORDER BY clause Databases are allowed to return data in any arbitrary order unless you explicitly declare that order in the ORDER BY clause In jOOQ this is straight forward SELECT AUTHOR_ID TITLE create select BOOK AUTHOR_ID BOOK TITLE FROM BOOK from BOOK ORDER BY AUTHOR_ID ASC TITLE DESC orderBy BOOK AUTHOR_ID asc BOOK TITLE desc Any OOQ column expression or field can be transformed into an org jooq SortField by calling the asc and desc methods Ordering by field index The SQL standard allows for specifying integer literals literals not bind values to reference column indexes from the projection SELECT clause
124. ble Animal Farm to the second variable create selectFrom BOOK where BOOK ID AND TITLE 5 Animal Farm Plain SOL using placeholders counting from zero The QueryPart id is substituted for the placeholder 0 the QueryPart title for 1 Field lt Integer gt id val 5 Field lt String gt title val Animal Farm create selectFrom BOOK where BOOK ID 0 AND TITLE 1 id title The above technique allows for creating rather complex SQL clauses that are currently not supported by jOOQ without extending any of the custom QueryParts as indicated in the previous chapter 4 9 6 Serializability The only transient non serializable element in any jOOQ object is the Configuration s underlying java sql Connection When you want to execute queries after de serialisation or when you want to store refresh delete Updatable Records you may have to re attach them to a Configuration Deserialise a SELECT statement ObjectInputStream in new ObjectInputStream Select lt gt select Select lt gt in readObject This will throw a DetachedException select execute In order to execute the above select attach it first DSLContext create DSL using connection SQLDialect ORACLE create attach select Automatically attaching QueryParts Another way of attaching QueryParts automatically or rather providing them with a new java sql Connection at will is
125. ble on single tables Support for multi table updates will be implemented in the near future An example update query is given here UPDATE AUTHOR create update AUTHOR SET FIRST_NAME Hermann set AUTHOR FIRST_NAME Hermann LAST_NAME Hesse set AUTHOR LAST_NAME Hesse WHERE ID 3 where AUTHOR ID equal 3 Most databases allow for using scalar subselects in UPDATE statements in one way or another jOOQ models this through a set Field lt T gt Select lt extends Record1 lt T gt gt method in the UPDATE DSL API UPDATE AUTHOR create update AUTHOR SET FIRST_NAME set AUTHOR FIRST_NAME SELECT FIRST_NAME select PERSON FIRST_NAME FROM PERSON from PERSON WHERE PERSON ID AUTHOR ID where PERSON ID equal AUTHOR ID he WHERE ID 3 where AUTHOR ID equal 3 Using row value expressions in an UPDATE statement jOOQ supports formal row value expressions in various contexts among which the UPDATE statement Only one row value expression can be updated at a time Here s an example 2009 2014 by Data Geekery GmbH All rights reserved Page 57 174 The jOOQ User Manual UPDATE AUTHOR SET FIRST_NAME LAST_NAME Hermann Hesse WHERE ID 3 4 3 5 The DELETE statement create update AUTHOR set row AUTHOR FIRST_NAME AUTHOR LAST_NAME row Herman Hesse where AUTHOR ID equal 3 This can be particularly useful when usi
126. ble to run the jOOQ meta code generator against all supported databases 2009 2014 by Data Geekery GmbH All rights reserved Page 161 174 The jOOQ User Manual 7 Tools 7 Tools These chapters hold some information about tools to be used with jOOQ 7 1 JDBC mocking for unit testing When writing unit tests for your data access layer you have probably used some generic mocking tool offered by popular providers like Mockito mock mockrunner or even DBUnit With jOOQ you can take advantage of the built in JDBC mock API that allows you to emulate a database on the JDBC level for precisely those SQL JDBC use cases supported by jOOQ Mocking the JDBC API JDBC is a very complex API It takes a lot of time to write a useful and correct mock implementation implementing at least these interfaces java sql Connection Java sql Statement java sal PreparedStatement java sqlCallableStatement java sql ResultSet java sql ResultSetMetaData Optionally you may even want to implement interfaces such as java sql Array java sql Blob java sql Clob and many others In addition to the above you might need to find a way to simultaneously support incompatible JDBC minor versions such as 4 0 4 1 Using JOOQ s own mock API This work is greatly simplified when using jOOQ s own mock API The org joog tools jdoc package contains all the essential implementations for both JDBC 4
127. c methods for every procedure Results are also returned in a generated object holding getters for every OUT or IN OUT parameter AuthorExists procedure Routines authorExists configuration Paulo All OUT and IN OUT parameters generate getters assertEquals new BigDecimal 1 procedure getResult assertEquals new BigDecimal 2 procedure getId For more details about code generation for procedures see the manual s section about procedures and code generation Inlining stored function references in SQL Unlike procedures functions can be inlined in SQL statements to generate column expressions or table expressions if you re using unnesting operators Assume you have a function like this Check whether there is an author in AUTHOR by that name and get his ID CREATE OR REPLACE FUNCTION author_exists author_name VARCHAR2 RETURN NUMBER The generated artefacts can then be used as follows This is the rendered SQL Use the static imported method from Routines boolean exists SELECT AUTHOR_EXISTS Paulo FROM DUAL create select authorExists Paulo fetchOne 0 boolean class For more info about inlining stored function references in SQL statements please refer to the manual s section about user defined functions 5 8 1 Oracle Packages Oracle uses the concept of a PACKAGE to group several procedures functions into a sort of namespace The SQL 92 standard talks about m
128. can be considered when you want to analyse the history of your schema With this approach you can also keep track of the change of behaviour in the jOOQ code generator e g when upgrading jOOQ or when modifying the code generation configuration The drawback of this approach is that it is more error prone as the actual schema may go out of sync with the generated schema Derived artefacts When you consider generated code to be derived artefacts you will want to Check in only the actual DDL Regenerate jOOQ code every time the schema changes Regenerate jOOQ code on every machine including continuous integration 2009 2014 by Data Geekery GmbH All rights reserved Page 160 174 The jOOQ User Manual 6 15 Code generation and version control This approach is particularly useful when you have a smaller database schema that is under full control by your Java developers who want to profit from the increased quality of being able to regenerate all derived artefacts in every step of your build The drawback of this approach is that the build may break in perfectly acceptable situations when parts of your database are temporarily unavailable Pragmatic combination In some situations you may want to choose a pragmatic combination where you put only some parts of the generated code under version control For instance OOQ meta s generated sources are put under version control as few contributors will be a
129. cateKeyError the default loadCsV inputstream neretas tin nu TEELS execute Specify behaviour when errors occur create loadInto AUTHOR choose any of these methods onErrorignore onErrorAbort the default LloadCSV inputstream fields 1D null TITLE execute Specify transactional behaviour where this is possible e g not in container managed transactions create loadInto AUTHOR choose any of these methods commitEach commitAfter 10 commitAll commitNone the default LloadCSV inputstream ETS TAS AE execute 5 10 2 Importing XML Any of the above configuration methods can be combined to achieve the type of load you need Please refer to the API s Javadoc to learn about more details Errors that occur during the load are reported by the execute method s result Loader lt Author gt loader execute The number of processed rows int processed loader processed The number of stored rows INSERT or UPDATE int stored loader stored The number of ignored rows due to errors or duplicate rule int ignored loader ignored The errors that may have occurred during loading List lt LoaderError gt errors loader errors LoaderError error errors get 0 The exception that caused the error DataAccessException exception error exception The row that caused the error int rowIndex error r
130. ce object directly in a SQL statement as such Reference the sequence in a SELECT statement BigInteger nextID create select s fetchOne S_AUTHOR_ID nextval Reference the sequence in an INSERT statement create insertInto AUTHOR AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME values S_AUTHOR_ID nextval val William val Shakespeare For more information about generated sequences refer to the manual s section about generated seguences For more information about executing standalone calls to sequences refer to the manual s section about sequence execution 4 5 19 Tuples or row value expressions According to the SQL standard row value expressions can have a degree of more than one This is commonly used in the INSERT statement where the VALUES row value constructor allows for providing a row value expression as a source for INSERT data Row value expressions can appear in various other places though They are supported by jOOQ as records rows jOOQ s DSL allows for the construction of type safe records up to the degree of 22 Higher degree Rows are supported as well but without any type safety Row types are modelled as follows The DSL provides overloaded row value expression constructor methods public static lt T1 gt Row1 lt T1 gt row T1 t1 a publie static lt T 12 gt Row2 lt T1 T2 gt LOWE jell T2 E2 Ee public static lt T T27 ee ROWS cTIS T2 b LOMILI Ery T2
131. cific DSL For instance if you re only using the MySQL dialect you can choose to reference the MySQLDSL instead of the standard DSL The advantage of referencing a dialect specific DSL lies in the fact that you have access to more proprietary RDMBS functionality This may include MySQL s encryption functions PL SQL constructs pgplsql or any other dialect s ROUTINE language maybe in the future 4 2 The DSLContext class DSLContext references a org jooq Configuration an object that configures jOOQ s behaviour when executing queries see SQL execution for more details Unlike the static DSL the DSLContext allow for creating SQL statements that are already configured and ready for execution Fluent creation of a DSLContext object The DSLContext object can be created fluently from the DSL type Create it from a pre existing configuration DSLContext create DSL using configuration Create it from ad hoc arguments DSLContext create DSL using connection dialect If you do not have a reference to a pre existing Configuration object e g created from org joog impl DefaultConfiguration the various overloaded DSL using methods will create one for you Contents of a Configuration object A Configuration can be supplied with these objects O 2009 2014 by Data Geekery GmbH All rights reserved Page 30 174 The jOOQ User Manual 4 2 1 SQL Dialect org oog SQLDialect The dialect
132. column LAST_NAME was VARCHAR 30 Then you could do this SELECT CAST AUTHOR LAST_NAME AS TEXT FROM DUAL in jOOQ you can write something like that create select TAuthor LAST_NAME cast PostgresDataType TEXT The same thing can be achieved by casting a Field directly to String class as TEXT is the default data type in Postgres to map to Java s String create select TAuthor LAST_NAME cast String class The complete CAST API in org jooq Field consists of these three methods public interface Field lt T gt Cast this field to the type of another field lt Z gt Field lt Z gt cast Field lt Z gt field Cast this field to a given DataType lt Z gt Field lt Z gt cast DataType lt Z gt type Cast this field to the default DataType for a given Class lt Z gt Field lt Z gt cast Class lt extends Z gt type And additional convenience methods in the DSL public class DSL lt T gt Field lt T gt cast Object object Field lt T gt field lt T gt Field lt T gt cast Object object DataType lt T gt type lt T gt Field lt T gt cast Object object Class lt extends T gt type lt T gt Field lt T gt castNull Field lt T gt field lt T gt Field lt T gt castNull DataType lt T gt type lt T gt Field lt T gt castNull Class lt extends T gt type 4 5 4 Arithmetic expressions Numeric arithmetic expressions Your database can do the math for you Arithmetic operati
133. complex conditional expression TITLE Animal Farm OR TITLE 1984 BOOK TITLE equal Animal Farm or BOOK TITLE equal 1984 AND NOT AUTHOR LAST_NAME Orwell andNot AUTHOR LAST_NAME equal Orwell The above example shows that the number of parentheses in Java can quickly explode Proper indentation may become crucial in making such code readable In order to understand how jOOQ composes combined conditional expressions let s assign component expressions first Condition a BOOK TITLE equal Animal Farm Condition b BOOK TITLE equal 1984 Condition c AUTHOR LAST_NAME equal Orwell Condition combinedl a or b These OR connected conditions form a new condition wrapped in parentheses Condition combined2 combinedl andNot c The left hand side of the AND NOT operator is already wrapped in parentheses Here are all boolean operators on the org joog Condition interface O 2009 2014 by Data Geekery GmbH All rights reserved Page 83 174 The jOOQ User Manual 4 6 3 Comparison predicate and Condition Combine conditions with AND and String Combine conditions with AND Convenience for adding plain SQL to the right hand side and String Object Combine conditions with AND Convenience for adding plain SQL to the right hand side and String QueryPart Combine conditions with AND Convenience for adding plain SOL to the right hand side andExists Select lt gt
134. conditional expressions column expressions such as functions assumes immutable behaviour creating SQL statements does not In other words the following can be said Conditional expressions immutable O Condition a BOOK TITLE equal 1984 Condition b BOOK TITLE equal Animal Farm The following can be said a a or b or does not modify a a or b a or b or always creates new objects Statements mutable NE ES SelectFromStep lt gt sl SelectJoinStep lt gt s2 sl from BOOK SelectJoinStep lt gt s3 sl from AUTHOR The following can be said sl 2 The internal object is always the same s2 3 The internal object is always the same s g On the other hand beware that you can always extract and modify bind values from any QueryPart 4 3 2 The SELECT statement When you don t just perform CRUD i e SELECT FROM your_table WHERE ID you re usually generating new record types using custom projections With jOOQ this is as intuitive as if using SQL O 2009 2014 by Data Geekery GmbH All rights reserved Page 39 174 The jOOQ User Manual 4 3 2 1 The SELECT clause directly A more or less complete example of the standard SQL syntax plus some extensions is provided by a query like this SELECT from a complex table expression get all authors first and last names and the number And with j00OQ of boo
135. cord with an IDENTITY column This information is used by jOOQ internally to update IDs after calling store O 2009 2014 by Data Geekery GmbH All rights reserved Page 132 174 The jOOQ User Manual 5 11 4 Navigation methods BookRecord book create newRecord BOOK book setTitle 1984 book store The generated ID value is fetched after the above INSERT statement System out printin book getId Database compatibility DB2 Derby HSQLDB Ingres These SQL dialects implement the standard very neatly id INTEGER GENERATED BY DEFAULT AS IDENTITY id INTEGER GENERATED BY DEFAULT AS IDENTITY START WITH 1 H2 MySQL Postgres SQL Server Sybase ASE Sybase SQL Anywhere These SQL dialects implement identites but the DDL syntax doesn t follow the standard H2 mimicks MySQL s and SQL Server s syntax ID INTEGER IDENTITY 1 1 ID INTEGER AUTO_INCREMENT MySQL and SQLite ID INTEGER NOT NULL AUTO_INCREMENT Postgres serials implicitly create a sequence Postgres also allows for selecting from custom sequences That way sequences can be shared among tables id SERIAL NOT NULL SQL Server ID INTEGER IDENTITY 1 1 NOT NULL Sybase ASE id INTEGER IDENTITY NOT NULL Sybase SQL Anywhere id INTEGER NOT NULL IDENTITY Oracle Oracle does not know any identity columns at all Instead you will have to use a trigger and update the ID column yourself using a custom sequence Som
136. ct oriented type safe AST modelling your SQL statements An example is given here Fetch a SOL string from a jO0Q Query in order to manually execute it with another tool String sql create select field BOOK TITLE field AUTHOR FIRST_NAME field AUTHOR LAST_NAME from table BOOK join table AUTHOR on field BOOK AUTHOR_ID equal field AUTHOR ID where field BOOK PUBLISHED_IN equal 1948 getSOL The SQL string built with the jOOQ query DSL can then be executed using JDBC directly using Spring s JdbcTemplate using Apache DbUtils and many other tools If you wish to use jOOQ only as a SQL builder the following sections of the manual will be of interest to you 2009 2014 by Data Geekery GmbH All rights reserved Page 16 174 The jOOQ User Manual 3 3 2 jOOQ as a SQL builder with code generation SQL building This section contains a lot of information about creating SQL statements using the jOOQ API Plain SQL This section contains information useful in particular to those that want to supply table expressions column expressions etc as plain SQL to jOOQ rather than through generated artefacts 3 3 2 000 as a SQL builder with code generation In addition to using jOOQ as a standalone SQL builder you can also use jOOQ s code generation features in order to compile your SQL statements using a Java compiler against an actual database schema This add
137. custom POJO types 5 3 8 Many fetching Many databases support returning several result sets or cursors from single queries An example for this is Sybase ASE s sp_help command gt sp _belp author Name Owner Object_type Object_status Create_date authorjadbo user table none lisa 22 2011 dl sei Column_name Type Length Prec Scale id lint 4 NULL NULL o first_name Ivarchar SO NULL NULL wi last_name Ivarchar 50 NULL NULL ol date_of_birth date 4 NULL NULL 1 year_of_birth int 4 NULL NULL 1 The correct and verbose way to do this with JDBC is as follows ResultSet rs statement executeQuery Repeat until there are no more result sets for 75 Empty the current result set while rs next 4 if l se do something with it Get the next result set if available if statement getMoreResults rs statement getResultSet else break ES su ure that alt result sets are closed statement getMoreResults Statement CLOSE_ALL_ RESULTS statement close
138. d from your schema A Java regular expression Use the pipe to separate several expressions nxeludes match before includes lt gt lt excludes gt lt excludes gt lt database gt lt target gt lt The destination package of your generated classes within the destination directory gt lt packageName gt test generated lt packageName gt lt The destination directory of your generated classes gt lt directory gt C workspace MySQLTest src lt directory gt lt target gt lt generator gt lt configuration gt Replace the username with whatever user has the appropriate privileges to query the database meta data You ll also want to look at the other values and replace as necessary Here are the two interesting properties generator target package set this to the parent package you want to create for the generated classes The setting of test generated will cause the test generated Posts and test generated PostsRecord to be created generator target directory the directory to output to Once you have the JAR files and library xml in your temp directory type this on a Windows machine java classpath jooq 3 0 1 jar jooq meta 3 0 1 jar joogq codegen 3 0 1 jar mysql connector java 5 1 18 bin jJar org jooq util GenerationTool library xml or type this on a UNIX Linux Mac system colons instead of semi colons java classpath jooq 3 0 1 jar jooq meta 3 0 1 jar joogq codegen 3 0 1 ja
139. data from The following are examples of how to form normal FROM clauses SELECT 1 FROM BOOK create selectOne from BOOK SELECT 1 FROM BOOK AUTHOR create selectOne from BOOK AUTHOR SELECT 1 FROM BOOK b AUTHOR a create selectOne from BOOK as b AUTHOR as a Read more about aliasing in the manual s section about aliased tables More advanced table expressions Apart from simple tables you can pass any arbitrary table expression to the jOOQ FROM clause This may include unnested cursors in Oracle SELECT create select FROM TABLE DBMS_XPLAN DISPLAY_CURSOR null null ALLSTATS from table DbmsXplan displayCursor null null ALLSTATS Note in order to access the DbmsXplan package you can use the code generator to generate Oracle s SYS schema Selecting FROM DUAL with OOQ In many SQL dialects FROM is a mandatory clause in some it isn t OOQ allows you to omit the FROM clause returning just one record An example SELECT 1 FROM DUAL DSL using SQLDialect ORACLE selectOne getSQL SELECT 1 DSL using SQLDialect POSTGRES selectOne getSQL Read more about dual or dummy tables in the manual s section about the DUAL table The following are examples of how to form normal FROM clauses 4 3 2 3 The JOIN clause jOOQ supports many different types of standard SQL JOIN operations INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTE
140. domain as jOOQ jOOQ has gotten great inspiration from pre existing tools and this section should give them some credit Here is a list of inspirational tools in alphabetical order Avaj EBean Play Framework s preferred ORM has a feature called asynchronous query execution This idea made it into jOOQ as org joog ResultQuery Hibernate The de facto standard JPA with its useful table to POJO mapping features have influenced jOOQ s org joog ResultQuery facilities JaQu H2 s own fluent API for querying databases PA The de facto standard in the javax persistence packages supplied by Oracle Its annotations are useful to jOOQ as well QOneWebSQL A commercial SQL abstraction API with support for DAO source code generation which was integrated also in j OOQ QueryDSL A LINQ port to Java It has a similar fluent API a similar code generation facility yet quite a different purpose While jOOQ is all about SQL QueryDSL like LINQ is mostly about querying SLICK A LINQ like database abstraction layer for Scala Unlike LINQ its API doesn t really remind of SQL Instead it makes SQL look like Scala Spring Data Spring s JdbcTemplate knows RowMappers which are reflected by jOOQ s RecordHandler or RecordMapper 2009 2014 by Data Geekery GmbH All rights reserved Page 174 174
141. dsl create select from BOOK fetch getValues BOOK ID Long class List lt Long gt ids2 create select from BOOK fetch BOOK ID Long class Long ids3 create select from BOOK fetchArray BOOK ID Long class Fetching book IDs and mapping each ID to their records or titles Map lt Integer BookRecord gt mapl create selectFrom BOOK fetch intoMap BOOK ID Map lt Integer BookRecord gt map2 create selectFrom BOOK fetchMap BOOK ID Map lt Integer String gt map3 create selectFrom BOOK fetch intoMap BOOK ID BOOK TITLE Map lt Integer String gt map4 create selectFrom BOOK fetchMap BOOK ID BOOK TITLE Group by AUTHOR_ID and list all books written by any author Map lt Integer Result lt BookRecord gt gt groupl create selectFrom BOOK fetch intoGroups BOOK AUTHOR_1D Map lt Integer Result lt BookRecord gt gt group2 create selectFrom BOOK fetchGroups BOOK AUTHOR_ID Map lt Integer List lt String gt gt group3 create selectFrom BOOK fetch intoGroups BOOK AUTHOR_ID BOOK TITLE Map lt Integer List lt String gt gt group4 create selectFrom BOOK fetchGroups BOOK AUTHOR_ID BOOK TITLE Note that most of these convenience methods are available both through org joog ResultQuery and org joog Result some are even available through org joog Record as well 5 3 4 RecordHandler In a more functional operating mode you might want to write callbacks that recei
142. e public String getJavaClassName Definition definition Mode mode return super getJavaClassName definition mode Override this method to re define the package names of your generated Tarteracts lt we Override public String getJavaPackageName Definition definition Mode mode return super getJavaPackageName definition mode per Override this method to define how Java members should be named This is used for POJOs and method arguments E Override public String getJavaMemberName Definition definition Mode mode return definition getOutputName Override this method to define the base chasse for those artefacts that allow for custom base classes sl Override public String getJavaClassExtends Definition definition Mode mode return Object class getName Override this method to define the interfaces to be implemented by those artefacts that allow for custom interface implementation va Override public List lt String gt getJavaClassImplements Definition definition Mode mode return Arrays asList Serializable class getName Cloneable class getName Override this method to define the suffix to apply to routines when they are overloaded H EE tabs E Use this to resolve compile time conflicts in generated source code in case you make heavy use of procedure overloading val Override public String getOverloadSuffix Definition definition Mode mode String o
143. e ROLLUP for instance ROLLUP with one argument The same query using UNION ALL SELECT AUTHOR_ID COUNT SELECT AUTHOR_ID COUNT FROM BOOK GROUP BY AUTHOR_ID FROM BOOK UNION ALL GROUP BY ROLLUP AUTHOR_ID SELECT NULL COUNT FROM BOOK GROUP BY ORDER BY 1 NULLS LAST ROLLUP with two arguments The same query using UNION ALL SELECT AUTHOR_ID PUBLISHED_IN COUNT SELECT AUTHOR_ID PUBLISHED_IN COUNT FROM BOOK FROM BOOK GROUP BY AUTHOR_ID PUBLISHED_IN GROUP BY ROLLUP AUTHOR_ID PUBLISHED_IN UNION ALL SELECT AUTHOR_ID NULL COUNT FROM BOOK GROUP BY AUTHOR_ID UNION ALL SELECT NULL NULL COUNT FROM BOOK GROUP BY ORDER BY 1 NULLS LAST 2 NULLS LAST In English the ROLLUP grouping function provides N 1 groupings when N is the number of arguments to the ROLLUP function Each grouping has an additional group field from the ROLLUP argument field list The results of the second query might look something like this 4 4 4 AUTHOR_ID PUBLISHED_IN COUNT 4 4 4 ii 1945 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN gi 1948 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN ny NULL 2 lt GROUP BY AUTHOR_ID 2m 1988 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN 2m 1990 1 lt GROUP BY AUTHOR_ID PUBLISHED_IN 2 NULL 2 lt GROUP BY AUTHOR_ID NULL NULL 4
144. e callbacks to receive records from select statements List lt Integer gt ids create selectFrom BOOK orderBy BOOK ID fetch map new RecordMapper lt BookRecord Integer gt Override public Integer map BookRecord book return book getId We Or more concisely create selectFrom BOOK OrderBy BOOK ID fetch new RecordMapper lt BookRecord Integer gt Or even more concisely with Java 8 s lambda expressions create selectFrom BOOK orderBy BOOK ID fetch book gt book getId See also the manual s section about the RecordHandler which provides similar features 0 30 POJOS Fetching data in records is fine as long as your application is not really layered or as long as you re still writing code in the DAO layer But if you have a more advanced application architecture you may not want to allow for jOOQ artefacts to leak into other layers You may choose to write POJOs Plain Old Java Objects as your primary DTOs Data Transfer Objects without any dependencies on jOOQ s org joog Record types which may even potentially hold a reference to a Configuration and thus a JDBC java sql Connection Like Hibernate JPA jOOQ allows you to operate with POJOs Unlike Hibernate JPA jOOQ does not attach those POJOs or create proxies with any magic in them If you re using jOOQ s code generator you can configure it to generate POJOs for you but you re not required to use those ge
145. eatures when it comes to inversing predicates with the NOT operator Here are some examples Row value expressions Equivalent factored out predicates A B IS NULL A IS NULL AND B IS NULL A B IS NOT NULL A IS NOT NULL AND B IS NOT NULL Inverse of the above Inverse NOT A B IS NULL A IS NOT NULL OR B IS NOT NULL NOT A B IS NOT NULL A IS NULL OR BES NULL The SQL standard contains a nice truth table for the above rules 2 4 Expression RAE SENTI R ITSFNOT NUL NOT R IS NULL NOT R IS NOT NULL 2 4 degree 1 nur true false false true degree 1 not mari false true true false degrees L alk nari true false false true degree gt 17 some nuli false false true trues degree gt ile none nuli farse true true false 4 In jOOQ you would simply use the isNull and isNotNull methods on row value expressions Again as with the row value expression comparison predicate the row value expression NULL predicate is emulated by jOOQ if your database does not natively support it row BOOK ID BOOK TITLE isNull row BOOK ID BOOK TITLE isNotNull 2009 2014 by Data Geekery GmbH All rights reserved Page 86 174 The jOO
146. ecimal gt regrSlope Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigDecimal gt regrSXX Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigDecimal gt regrSXY Field lt extends Number gt y Field lt extends Number gt AggregateFunction lt BigDecimal gt regrSYY Field lt extends Number gt y Field lt extends Number gt XXX OOO Here s an example counting the number of books any author has written SELECT AUTHOR_ID COUNT create select BOOK AUTHOR_ID count FROM BOOK from BOOK GROUP BY AUTHOR_ID groupBy BOOK AUTHOR_ID Aggregate functions have strong limitations about when they may be used and when not For instance you can use aggregate functions in scalar queries Typically this means you only select aggregate functions no regular columns or other column expressions Another use case is to use them along with a GROUP BY clause as seen in the previous example Note that jOOQ does not check whether your using of aggregate functions is correct according to the SQL standards or according to your database s behaviour Ordered set aggregate functions Oracle and some other databases support ordered set aggregate functions This means you can provide an ORDER BY clause to an aggregate function which will be taken into consideration when aggregating The best example for this is Oracle s LISTAGG also known as GROUP_CONCA
147. ection JavaConversions _ Import implicit defs for iteration over org jooq Result A import java sql DriverManager Hae ig import org jooq _ eh import org jooq impl _ ey import org jooq impl DSL _ He import org jooq scala example h2 Tables _ iy import org jooq scala Conversions _ Import implicit defs for overloaded j000 SQL operators object Test Hse def main args Array String Unit if val c DriverManager getConnection jdbc h2 test sa Standard JDBC connection val e DSL using c SOLDialect H2 val x AUTHOR as x SQL esque table aliasing for r lt e Iteration over Result r is an org jooq Record3 select te BOOK ID BOOK AUTHOR_ID Using the overloaded operator BOOK ID BOOK AUTHOR_ID 3 4 Using the overloaded operator BOOR TITLE are Using the overloaded operator lil from BOOK No need to use parentheses or here leftOuterJoin Tis select x ID x YEAR_OF_BIRTH Dereference fields from aliased table from x ti limit 1 asTable x getName Hi on BOOK AUTHOR_ID x ID Using the overloaded operator where BOOK ID lt gt 2 Using the olerloaded lt gt operator or BOOK TITLE in O Alquimista Brida Neat IN predicate expression fetch ti T print1n r lid For more details about jOOQ s Scala integration please refer to the manual s section about SQL building with Scala 3 6 OOQ and NoS
148. ement H2 specific syntax The H2 database ships with a somewhat less powerful but a little more intuitive syntax for its own version of the MERGE statement An example more or less equivalent to the previous one can be seen here Check it there 19 already an author called Hitchcock create mergelnto AUTHOR If there is rename him to John If there isn t add him AUTHOR FIRST_NAME AUTHOR LAST_NAME MERGE INTO AUTHOR FIRST_NAME LAST_NAME key AUTHOR LAST_NAME KEY LAST_NAME values John Hitchcock VALUES John Hitchcock execute This syntax can be fully emulated by jOOQ for all other databases that support the SQL standard MERG statement For more information about the H2 MERGE syntax see the documentation here http www h2database com html grammar html merge Typesafety of VALUES for degrees up to 22 Much like the INSERT statement the MERGE statement s VALUES clause provides typesafety for degrees up to 22 in both the standard syntax variant as well as the H2 variant 4 3 7 The TRUNCATE statement The TRUNCATE statement is the only DDL statement supported by jOOQ so far It is popular in many databases when you want to bypass constraints for table truncation Databases may behave differently when a truncated table is referenced by other tables For instance they may fail if records from a truncated table are referenced
149. en if they are already consumed With JDBC there is a lot of verbosity around safely closing resources In jOOQ resources are closed after consumption by default If you want to keep them open after consumption you have to explicitly say so 5 2 Query vs ResultQuery Unlike JDBC jOOQ has a lot of knowledge about a SQL query s structure and internals see the manual s section about SQL building Hence jOOQ distinguishes between these two fundamental types of queries While every org joog Query can be executed only org joog ResultQuery can return results see the manual s section about fetching to learn more about fetching results With plain SQL the distinction can be made clear most easily Create a Query object and execute it Query query create query DELETE FROM BOOK query execute Create a ResultQuery object and execute it fetching results ResultQuery lt Record gt resultQuery create resultQuery SELECT FROM BOOK Result lt Record gt resultQuery fetch 5 3 Fetching Fetching is something that has been completely neglegted by JDBC and also by various other database abstraction libraries Fetching is much more than just looping or listing records or mapped objects There are so many ways you may wart to fetch data from a database it should be considered a first class feature of any database abstraction API Just
150. ense Please contact legal datageekery com should you have any questions regarding licensing License for jOOQ 1 x 2 x 3 0 3 1 Copyright c 2009 2015 Lukas Eder lukas eder gmail com All rights reserved This software is licensed to you under the Apache License Version 2 0 the License You may obtain a copy of the License at http www apache org licenses LICENSE 2 0 Redistribution and use in source and binary forms with or without modification are permitted provided that the following conditions are met Redistributions of source code must retain the above copyright notice this list of conditions and the following disclaimer Redistributions in binary form must reproduce the above copyright notice this list of conditions and the following disclaimer in the documentation and or other materials provided with the distribution Neither the name jOOQ nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS AS IS AND ANY EXPRESS OR IMPLIED WARRANTIES INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT INDIRECT INCIDENTAL SPECIAL EXEMPLARY OR CONSEQUENTIAL DAMAGES INCLUDING BUT NOT LIMITED TO P
151. ependency gt lt dependencies gt lt Specify the plugin configuration The configuration format is the same as for the standalone lt configuration gt US JDBC connection parameters gt gt gt lt jdbe gt lt driver gt org postgresql Driver lt driver gt lt url gt jdbc postgresql postgres lt url gt lt user gt postgres lt user gt lt password gt test lt password gt lt jdbe gt lt gt Generator parameters gt lt generator gt lt database gt lt name gt org jooq util postgres PostgresDatabase lt name gt lt includes gt lt includes gt lt excludes gt lt excludes gt lt input Schema gt public lt inputSchema gt lt database gt lt target gt lt packageName gt org joog util maven example lt packageName gt lt directory gt target generated sources jooq lt directory gt lt target gt lt generator gt lt configuration gt lt plugin gt a PostgreSQL database gt code generator gt Use jOOQ generated classes in your application Be sure both jooq 3 0 1 jar and your generated package see configuration are located on your classpath Once this is done you can execute SQL statements with your generated classes 6 2 Advanced generator configuration In the previous section we have seen how jOOQ s source code generator is configured and run within a few steps In this chapter we ll cover some advanced settings O 2009 2014 by Data Geekery GmbH All rights res
152. erator overloading hence these operators are also implemented as methods in jOOQ like any other SQL syntax elements The relevant parts of the org jooq Field interface are these eq or equal T TS some bind value eq or equal Field lt T gt AS some column expression eq or equal Select lt extends Recordi lt T gt gt Hy some scalar SELECT statement ne or notEqual T lt gt some bind value Field lt T gt ES Select lt extends Record1 lt T gt gt T ces ne or notEqual ne or notEqual some column expression some scalar SELECT statement it or lessThan T i some bind value lt or lessThan Field lt T gt Well some column expression lt or lessThan Select lt extends Recordl lt T gt gt Wil some scalar SELECT statement le or lessOrEqual T lt some bind value le or lessOrEqual Field lt T gt lt some column expression le or lessOrEqual Select lt extends Recordi lt T gt gt lt some scalar SELECT statement gt or greaterThan T Hi some bind value gt or greaterThan Field lt T gt Vk some column expression gt or greaterThan Select lt extends Recordl lt T gt gt Lf some scalar SELECT statement ge or greaterOrEqual T gt some bind value ge or greaterOrEqual Field lt T gt gt some column expression ge or greaterOrEqual Select lt extends Recordl lt T gt gt gt some scalar SELECT statement Note t
153. ers jOOQ doesn t know what you re doing You re on your own again You have to provide something that will be syntactically correct If it s not then OOQ won t know Only your JDBC driver or your RDBMS will detect the syntax error You have to provide consistency when you use variable binding The number of must match the number of variables Your SQL is inserted into jOOQ queries without further checks Hence OOQ can t prevent SQL injection 4 8 Bind values and parameters Bind values are used in SQL JDBC for various reasons Among the most obvious ones are Protection against SQL injection Instead of inlining values possibly originating from user input you bind those values to your prepared statement and let the JDBC driver database take care of handling security aspects Increased speed Advanced databases such as Oracle can keep execution plans of similar queries in a dedicated cache to prevent hard parsing your query again and again In many cases the actual value of a bind variable does not influence the execution plan hence it can be reused Preparing a statement will thus be faster OnaJDBC level you can also reuse the SQL string and prepared statement object instead of constructing it again as you can bind new values to the prepared statement jOOQ currently does not cache prepared statements internally The following sections explain how you can introduce bind values
154. erter Note a custom type can only have one converter in j00Q gt lt converter gt com example CalendarConverter lt converter gt lt customType gt lt customTypes gt lt Then associate custom types with database columns gt lt forcedTypes gt lt forcedType gt lt Specify again he fully qualified class name of your custom type gt lt name gt java util GregorianCalendar lt name gt lt Add a Java regular expression matching columns Use the pipe to separate several expressions gt lt expressions gt DATE_OF_ lt expressions gt lt forcedType gt lt forcedTypes gt lt database gt The above configuration will lead to AUTHOR DATE_OF_BIRTH being generated like this 2009 2014 by Data Geekery GmbH All rights reserved Page 158 174 The jOOQ User Manual 6 13 Mapping generated schemata and tables public class TAuthor extends TableImpl lt TAuthorRecord gt i Necell public final TableField lt TAuthorRecord GregorianCalendar gt DATE_OF_BIRTH ERE i Meoel This means that the bound type of lt T gt will be GregorianCalendar wherever you reference DATE_OF_BIRTH jOOQ will use your custom converter when binding variables and when fetching data from java util ResultSet Get all date of births of authors born after 1980 List lt GregorianCalendar gt result create selectFrom AUTHOR where AUTHOR DATE_OF_BIRTH greaterThan new GregorianCalendar 1980 0 1
155. erved Page 147 174 The jOOQ User Manual A these properties can be added directly to the generator element gt lt generator gt lt The default code generator You can override this one to generate your own code style Defaults to org jooq util JavaGenerator gt lt name gt org jooq util JavaGenerator lt name gt lt The naming strategy used for class and field names You may override this with your custom naming strategy Some examples follow Defaults to org jooq util DefaultGeneratorStrategy gt lt strategy gt lt name gt org jooq util DefaultGeneratorStrategy lt name gt lt strategy gt lt generator gt 6 2 Advanced generator configuration The following example shows how you can override the DefaultGeneratorStrategy to render table and column names the way they are defined in the database rather than switching them to camel case O 2009 2014 by Data Geekery GmbH All rights reserved Page 148 174 The jOOQ User Manual abs E Paty oh Patek ot oe It is recommended that you extend the DefaultGeneratorStrategy Most of the GeneratorStrategy API is already declared final You only need to override any of the following methods for whatever generation behaviour you d like to achieve Beware that most methods also receive a Mode object to tell you whether a TableDefinition is being rendered as a Table Record POJO etc Depending on that information you can add a suffix only for Table
156. es An example is given here Typesafely execute the SQL statement directly with jO0Q Result lt Record3 lt String String String gt gt result create select BOOK TITLE AUTHOR FIRST_NAME AUTHOR LAST_NAME from BOOK join AUTHOR on BOOK AUTHOR_ID equal AUTHOR ID where BOOK PUBLISHED_IN equal 1948 fetch By having jOOQ execute your SQL the jOOQ query DSL becomes truly embedded SQL jOOQ doesn t stop here though You can execute any SQL with jOOQ In other words you can use any other SQL building tool and run the SQL statements with jOOQ An example is given here O 2009 2014 by Data Geekery GmbH All rights reserved Page 17 174 The jOOQ User Manual 3 3 4 OOQ for CRUD Use your favourite tool to construct SQL strings String sql SELECT title first_name last_name FROM book JOIN author ON book author_id author id WHERE book published_in 1984 Fetch results using jO0Q Result lt Record gt result create fetch sql Or execute that SOL with JDBC fetching the ResultSet with jOOQ ResultSet rs connection createStatement executeQuery sql Result lt Record gt result create fetch rs If you wish to use jOOQ as a SQL executor with or without code generation the following sections of the manual will be of interest to you SQL building This section contains a lot of information about creating SQL statements using the jOOQ API Code
157. es to SQL A simple example can be provided by checking out jOOQ s internal representation of a simplified CompareCondition It is used for any org joog Condition comparing two fields as for example the AUTHOR ID BOOK AUTHOR_ID condition here lesa WHERE AUTHOR ID MA This is how jOOQ binds values on such a condition Override public final void bind BindContext context throws DataAccessException The CompareCondition itself does not bind any variables But the two fields involved in the condition might do so context bind field1 bind field2 See the manual s sections about custom QueryParts and plain SOL QueryParts to learn about how to write your own query parts in order to extend jOOQ 4 9 4 Extend JOOQ with custom types If a SQL clause is too complex to express with jOOQ you can extend either one of the following types for use directly in a jOOQ query O 2009 2014 by Data Geekery GmbH All rights reserved Page 99 174 The jOOQ User Manual 4 9 5 Plain SQL QueryParts public abstract class CustomField lt T gt extends AbstractField lt T gt public abstract class CustomCondition extends AbstractCondition public abstract class CustomTable lt R extends TableRecord lt R gt gt extends Tablelmpl lt R gt public abstract class CustomRecord lt R extends TableRecord lt R gt gt extends TableRecordImpl lt R gt These classes are declared public and covered by jOOQ s integr
158. esultSet The result set You should return as many MockResult objects as there were query executions in batch mode or results in fetch many mode Instead of an awkward JDBC ResultSet however you can construct a friendlier org joog Result with your own record types The OOQ mock API will use meta data provided with this Result in order to create the necessary JDBC java sql ResultSetMetaData See the MockDataProvider Javadoc for a list of rules that you should follow 2009 2014 by Data Geekery GmbH All rights reserved Page 163 174 The jOOQ User Manual 7 2 jOOQ Console 7 2 j00Q Console The jOOQ Console is no longer supported or shipped with jOOQ 3 2 You may still use the jOOQ 3 1 Console with new versions of jOOQ at your own risk 2009 2014 by Data Geekery GmbH All rights reserved Page 164 174 The jOOQ User Manual 8 Reference 8 Reference These chapters hold some general jOOQ reference information 8 1 Supported RDBMS A list of supported databases Every RDMBS out there has its own little specialties OOQ considers those specialties as much as possible while trying to standardise the behaviour in jOOQ In order to increase the quality of OOQ some 70 unit tests are run for syntax and variable binding verification as well as some 180 integration tests with an overall of around 1200 queries for any of these databases CUBRID 8 4 DB29 7 Derby 10 10
159. etchArray String fieldName Converter lt U gt converter These methods are convenience for fetching only a single field from a single record possibly converting results to another type TS T fetchOne Field lt T gt field Sus T fetchOne Field lt gt field Class lt extends T gt type sD Usp fetchOne Field lt T gt field Converter lt super T U gt converter Object fetchOne int fieldIndex lt gt T fetchOne int fieldIndex Class lt extends T gt type lt U gt U fetchOne int fieldIndex Converter lt U gt converter Object fetchOne String fieldName SSS T fetchOne String fieldName Class lt extends T gt type lt U gt U fetchOne String fieldName Converter lt U gt converter Fetch transformations These means of fetching are also available from org joog Result and org joog Record APIs Transform your Records into arrays Results into matrices Object fetchArrays Object 1 fetchOneArray Reduce your Result object into maps lt K gt Map lt K R gt fetchMap Field lt K gt key lt K V gt Map lt K V gt fetchMap Field lt K gt key Field lt V gt value lt K E gt Map lt K E gt fetchMap Field lt K gt key Class lt E gt value Map lt Record R gt fetchMap Field lt gt key lt E gt Map lt Record E gt fetchMap Field lt gt key Class lt E gt value Transform your Result object into maps List lt Map lt String Object gt gt fetchMap
160. ething along these lines CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT ON my_table REFERENCING NEW AS new FOR EACH ROW BEGIN SELECT my_sequence nextval INTO new id FROM dual END my_trigger Note that this approach can be employed in most databases supporting sequences and triggers It is a lot more flexible than standard identities 5 11 4 Navigation methods org jooq TableRecord and org joog UpdatableRecord contain foreign key navigation methods These navigation methods allow for navigating inbound or outbound foreign key references by executing an appropriate query An example is given here 2009 2014 by Data Geekery GmbH All rights reserved Page 133 174 The jOOQ User Manual 5 11 5 Non updatable records CREATE TABLE book BookRecord book create fetch BOOK BOOK ID equal 5 AUTHOR_ID NUMBER 7 NOT NULL Find the author of a book static imported from Keys hosel AuthorRecord author book fetchParent FK_BOOK_AUTHOR FOREIGN KEY AUTHOR_ID REFERENCES author ID Find other books by that author Result lt BookRecord gt books author fetchChildren FK_BOOK_AUTHOR Note that unlike in Hibernate jOOQ s navigation methods will always lazy fetch relevant records without caching any results In other words every time you run such a fetch method a new query will be issued These fetch methods only work on attached records See the manual s section about serializ
161. eyer Christopher Deckers Ed Schaller E Espen Stromsnes Gonzalo Ortiz Jaureguizar Gregory Hlavac Henrik Sjostrand van Dugic Javier Durante Johannes Buhler Joseph B Phillips e Laurent Pireyn ukas Eder ichael Doberenz ichat Kotodziejski Peter Ertl Robin Stocker Sander Plas Sean Wellington Sergey Epik Stanislas Nanchen Sugiharto Lim Sven Jacobs Thomas Darimont Tsukasa Kitachi Vladimir Kulev Vladimir Vinogradov Zoltan Tamasi See the following website for details about contributing to jOOQ http Awww joog org legal contributions 2009 2014 by Data Geekery GmbH All rights reserved 2 Copyright License and Trademarks The jOOQ User Manual 3 Getting started with jOOQ 3 Getting started with jOOQ These chapters contain a quick overview of how to get started with this manual and with jOOQ While the subsequent chapters contain a lot of reference information this chapter here just wraps up the essentials 3 1 How to read this manual This section helps you correctly interpret this manual in the context of jOOQ Code blocks The following are code blocks A SQL code block SELECT 1 FROM DUAL A Java code block amon iene mak OSO ieee Kea AN RML CIG DALOK gt lt hello what world gt lt hello gt A config file code block org jooq property value These are useful to provide examples in code
162. f org jooq DSLContext The reason why it is called create is the fact that a j00Q QueryPart is being created from the DSL object create is thus the entry point of the non static query DSL DSLContext create DSL using connection SQLDialect ORACLE Your naming may differ of course For instance you could name the create instance db instead Execution When you re coding PL SQL T SQL or some other procedural SQL language SQL statements are always executed immediately at the semi colon This is not the case in OOQ because as an internal DSL jOOQ can never be sure that your statement is complete until you call fetch or execute The manual tries to apply fetch and execute as thoroughly as possible If not it is implied SELECT 1 FROM DUAL create selectOne fetch UPDATE t SET v 1 create upgate T set T V 1 execute Degree arity jOOQ records and many other API elements have a degree N between 1 and 22 The variable degree of an API element is denoted as N e g Row N or Record N The term degree is preferred over arity as degree is the term used in the SQL standard whereas arity is used more often in mathematics and relational theory Settings jOOQ allows to override runtime behaviour using org joog conf Settings If nothing is specified the default runtime settings are assumed Sample database jOOQ query examples run against the sample database See
163. fic Language that emulates SQL in Java This means that you can write SQL statements almost as if Java natively supported it just like NET s C does with LINQ to SQL Here is an example to illustrate what that means Select all books by authors born after T920 Result lt Record gt result named Paulo from a catalogue create select SELECT from AUTHOR as a FROM author a join BOOK as b on a ID equal b AUTHOR_ID JOIN book b ON a id b author_id where a YEAR_OF_BIRTH greaterThan 1920 WHERE a year_of_birth gt 1920 and a FIRST_NAME equal Paulo AND a first_name Paulo OrderBy b TITLE ORDER BY b title fetch We ll see how the aliasing works later in the section about aliased tables jOOQ as an internal domain specific language in Java a k a the DSL API F Many other frameworks have similar APIs with similar feature sets Yet what makes jOOQ special is its informal BNF notation modelling a unified SQL dialect suitable for many vendor specific dialects and implementing that BNF notation as a hierarchy of interfaces in Java This concept is extremely powerful when using jOOQ in modern IDEs with syntax completion Not only can you code much faster your SQL code will be compile checked to a certain extent An example of a DSL query equivalent to the previous one is given here DSLContext create DSL using connection dialect Result lt gt result c
164. g into your queries As with SQL you can add any type of column expression into your ORDER BY clause For instance if you have two favourite books that you always want to appear on top you could write SELECT create select FROM BOOK from BOOK ORDER BY CASE TITLE OrderBy decode value BOOK TITLE WHEN 1984 THEN 0 when 1984 0 WHEN Animal Farm THEN 1 when Animal Farm 1 ELSE 2 END ASC otherwise 2 asc But writing these things can become quite verbose jOOQ supports a convenient syntax for specifying sort mappings The same query can be written in OOQ as such create select from BOOK orderBy BOOK TITLE sortAsc 1984 Animal Farm More complex sort indirections can be provided using a Map create select from BOOK OrderBy BOOK TITLE sort new HashMap lt String Integer gt pene MICELI SE put Animal Farm 13 put The j00Q book 10 PEREG Of course you can combine this feature with the previously discussed NULLS FIRST NULLS LAST feature So if in fact these two books are the ones you like least you can put all NULLS FIRST all the other books create select from BOOK OrderBy BOOK TITLE sortAsc 1984 Animal Farm nullsFirst O 2009 2014 by Data Geekery GmbH All rights reserved Page 48 174 The jOOQ User Manual 4 3 2 9 The LIMIT OFFSET clause jOOQ s understanding of SELECT ORDER BY The SQL standard define
165. hat every operator is represented by two methods A verbose one such as equal and a two character one such as eq Both methods are the same You may choose either one depending on your taste The manual will always use the more verbose one jOOQ s convenience methods using comparison operators In addition to the above OOQ provides a few convenience methods for common operations performed on strings using comparison predicates 2009 2014 by Data Geekery GmbH All rights reserved Page 84 174 The jOOQ User Manual Case insensitivity LOWER TITLE LOWER animal farm LOWER TITLE lt gt LOWER animal farm 4 6 4 Comparison predicate degree gt 1 case insensitivity BOOK TITLE equaliIgnoreCase animal farm BOOK TITLE notEqualIgnoreCase animal farm 4 6 4 Comparison predicate degree gt 1 All variants of the comparison predicate that we ve seen in the previous chapter also work for row value expressions If your database does not support row value expression comparison predicates OOQ emulates them the way they are defined in the SQL standard Row value expressions equal A B X Y Ay By E Exp Mi A greater than A B gt X Y greater or equal A B gt X Y A B lt gt 05 O A B lt p 0 A B lt X Y Equivalent factored out predicates equal A X AND B Y A X AND B Y AND C
166. he BETWEEN predicate can be seen as syntactic sugar for a pair of comparison predicates According to the SQL standard the following two predicates are equivalent A BETWEEN B AND C A gt B AND A lt C Note the inclusiveness of range boundaries in the definition of the BETWEEN predicate Intuitively this is supported in jOOQ as such PUBLISHED_IN BETWEEN 1920 AND 1940 BOOK PUBLISHED_IN between 1920 and 1940 PUBLISHED_IN NOT BETWEEN 1920 AND 1940 BOOK PUBLISHED_IN notBetween 1920 and 1940 BETWEEN SYMMETRIC The SQL standard defines the SYMMETRIC keyword to be used along with BETWEEN to indicate that you do not care which bound of the range is larger than the other A database system should simply swap range bounds in case the first bound is greater than the second one jOOQ supports this keyword as well emulating it if necessary 2009 2014 by Data Geekery GmbH All rights reserved Page 87 174 The jOOQ User Manual 4 6 10 BETWEEN predicate degree gt 1 PUBLISHED_IN BETWEEN SYMMETRIC 1940 AND 1920 BOOK PUBLISHED_IN betweenSymmetric 1940 and 1920 PUBLISHED_IN NOT BETWEEN SYMMETRIC 1940 AND 1920 BOOK PUBLISHED_IN notBetweenSymmetric 1940 and 1920 The emulation is done trivially A BETWEEN SYMMETRIC B AND C A BETWEEN B AND C OR A BETWEEN C AND B 4 6 10 BETWEEN predicate degree gt 1 The SQ
167. he chapter about SQL dialects string functions as any function type are mostly emulated in your database in case they are not natively supported This is a list of numeric functions supported by jOOQ s DSL ASCII Get the ASCII code of a character BIT_LENGTH Get the length of a string in bits CHAR_LENGTH Get the length of a string in characters CONCAT Concatenate several strings SCAPE Escape a string for use with the LIKE predicate ENGTH Get the length of a string OWER Get a string in lower case letters LPAD Pad a string on the left side LTRIM Trim a string on the left side OCTET_LENGTH Get the length of a string in octets POSITION Find a string within another string REPEAT Repeat a string a given number of times REPLACE Replace a string within another string RPAD Pad a string on the right side RTRIM Trim a string on the right side SUBSTRING Get a substring of a string TRIM Trim a string on both sides 5 UPPER Get a string in upper case letters Please r oO fer to the DSL Javadoc for more details Regular expressions REGEXP REGEXP_LIKE etc Various databases have some means of searching through columns using regular expressions if the LIKE predicate does not provide sufficient pattern matching power While there are many different Aico and operato
168. here Obtain a Cursor reference Cursor lt BookRecord gt cursor null try cursor create selectFrom BOOK fetchhazy Cursor has similar methods as Iterator lt R gt while cursor hasNext BookRecord book cursor fetchOne Util doThingsWithBook book Close the cursor and the cursor s underlying JDBC ResultSet finally if cursor null cursor close As a org joog Cursor holds an internal reference to an open java sql ResultSet it may need to be closed at the end of iteration If a cursor is completely scrolled through it will conveniently close the underlying ResultSet However you should not rely on that Cursors ship with all the other fetch features Like org jooq ResultQuery or org jooq Result org joog Cursor gives access to all of the other fetch features that we ve seen so far i e 2009 2014 by Data Geekery GmbH All rights reserved Page 113 174 The jOOQ User Manual 5 3 8 Many fetching Strongly or weakly typed records Cursors are also typed with the lt R gt type allowing to fetch custom generated org jooq TableRecord or plain org jooq Record types RecordHandler callbacks You can use your own org joog RecordHandler callbacks to receive lazily fetched records RecordMapper callbacks You can use your own org joog RecordMapper callbacks to map lazily fetched records POJOs You can fetch data into your own
169. in jOOQ and how you can control the way they are rendered and bound to SQL 4 8 1 Indexed parameters JDBC only knows indexed bind values A typical example for using bind values with JDBC is this try PreparedStatement stmt connection prepareStatement SELECT FROM BOOK WHERE ID AND TITLE bind values to the above statement for appropriate indexes stmt setInt 1 5 stmt setString 2 Animal Farm stmt executeQuery With dynamic SQL keeping track of the number of question marks and their corresponding index may turn out to be hard jOOQ abstracts this and lets you provide the bind value right where it is needed A trivial example is this create select from BOOK where BOOK ID equal 5 and BOOK TITLE equal Animal Farm This notation is in fact a short form for the equivalent create select from BOOK where BOOK ID equal val 5 and BOOK TITLE equal val Animal Farm Note the using of DSL val to explicitly create an indexed bind value You don t have to worry about that index When the query is rendered each bind value will render a question mark When the query binds its variables each bind value will generate the appropriate bind value index 2009 2014 by Data Geekery GmbH All rights reserved Page 94 174 The jOOQ User Manual 4 8 2 Named parameters Extract bind values from a query Should you decide to run the above query outside of jOOQ
170. ing MySQL SELECT concatk A BY ICI FROM DUAL Create selectiiconeak Am BM Met 4 5 6 General functions There are a variety of general functions supported by jOOQ As discussed in the chapter about SQL dialects functions are mostly emulated in your database in case they are not natively supported This is a list of general functions supported by jOOQ s DSL COALESCE Get the first non null value in a list of arguments NULLIF Return NULL if both arguments are equal or the first argument otherwise NVL Get the first non null value among two arguments NVL2 Get the second argument if the first is null or the third argument otherwise Please refer to the DSL Javadoc for more details 4 5 7 Numeric functions Math can be done efficiently in the database before returning results to your Java application In addition to the arithmetic expressions discussed previously OOQ also supports a variety of numeric functions As discussed in the chapter about SQL dialects numeric functions as any function type are mostly emulated in your database in case they are not natively supported 2009 2014 by Data Geekery GmbH All rights reserved Page 69 174 The jOOQ User Manual 4 5 8 Bitwise functions This is a list of numeric functions supported by jOOQ s DSL ABS Get the absolute value of a value ACOS Get the arc cosine of a value ASIN Get the arc sine of a value ATAN
171. ions can be used if they are unnested 4 5 16 User defined aggregate functions Some databases support user defined aggregate functions which can then be used along with GROUP BY clauses or as window functions An example for such a database is Oracle With Oracle you can define the following OBJECT type the example was taken from the Oracle 11g documentation 2009 2014 by Data Geekery GmbH All rights reserved Page 78 174 The jOOQ User Manual 4 5 17 The CASE expression CREATE TYPE U_SECOND_MAX AS OBJECT MAX NUMBER highest value seen so far SECMAX NUMBER second highest value seen so far STATIC FUNCTION ODCIAggregateInitialize sctx IN OUT U_SECOND_MAX RETURN NUMBER MEMBER FUNCTION ODCIAggregatelterate self IN OUT U_SECOND_MAX value IN NUMBER RETURN NUMBER MEMBER FUNCTION ODCIAggregateTerminate self IN U_SECOND_MAX returnValue OUT NUMBER flags IN NUMBER RETURN NUMBER MEMBER FUNCTION ODCIAggregateMerge self IN OUT U_SECOND_MAX ctx2 IN U_SECOND_MAX RETURN NUMBER i CREATE OR REPLACE TYPE BODY U_SECOND_MAX IS STATIC FUNCTION ODCIAggregateInitialize sctx IN OUT U_SECOND_MAX RETURN NUMBER IS BEGIN SCTX U_SECOND_MAX 0 0 RETURN ODCIConst Success END MEMBER FUNCTION ODCIAggregatelIterate self IN OUT U_SECOND_MAX value IN NUMBER RETURN NUMBER IS BEGIN IF VALUE gt SELF MAX THEN SELF SECMAX SELF MAX SELF MAX VALUE ELSIF VALUE gt SELF SECMAX THEN SELF SECMAX
172. iring foreign keys and primary keys to share the same name Note that the JOIN columns will only figure once in the resulting table expression INNER JOIN or equi join This JOIN operation performs a cartesian product CROSS JOIN with a filtering predicate being applied to the resulting table expression Most often a equal comparison predicate comparing foreign keys and primary keys will be applied as a filter but any other predicate will work too OUTER JOIN This JOIN operation performs a cartesian product CROSS JOIN with a filtering predicate being applied to the resulting table expression Most often a equal comparison predicate comparing foreign keys and primary keys will be applied as a filter but any other predicate will work too Unlike the INNER JOIN an OUTER JOIN will add empty records to the eft table A or right table B or both tables in case the conditional expression fails to produce a semi join In SQL this JOIN operation can only be expressed implicitly using IN predicates or EXISTS predicates The table expression resulting from a semi join will only contain the left hand side table A anti join In SQL this JOIN operation can only be expressed implicitly using NOT IN predicates or NOT EXISTS predicates The table expression resulting from a semi join will only contain the left hand side table A division This JOIN operation is hard to express at all in SQL See the manual s chapter about relatio
173. ison predicates taking subselects changed incompatibly INSERT and MERGE statements now take typesafe VALUES clauses Some hints related to row value expressions SELECT statements are now more typesafe Record2 lt String Integer gt record create select BOOK TITLE BOOK ID from BOOK where ID eq 1 fetchOne Result lt Record2 lt String Integer gt gt result create select BOOK TITLE BOOK ID from BOOK fetch But Record2 extends Record You don t have to use the additional typesafety Record record create select BOOK TITLE BOOK ID from BOOK where ID eq 1 fetchOne Result lt gt result create select BOOK TITLE BOOK ID from BOOK fetch SelectQuery and SelectXXXStep are now generic In order to support type safe row value expressions and type safe Record N types SelectQuery is now generic SelectQuery lt R gt SimpleSelectQuery and SimpleSelectxXXStep API were removed The duplication of the SELECT API is no longer useful now that SelectQuery and SelectXxxXStep are generic 2009 2014 by Data Geekery GmbH All rights reserved Page 170 174 The jOOQ User Manual 8 5 Migrating to jOOQ 3 0 Factory was split into DSL query building and DSLContext query execution The pre existing Factory class has been split into two parts o Th th im DSL This class contains only static factory methods All QueryParts constructed from cla
174. key Generated DAOs implement a common jOOQ type called org joog DAO This type contains the following methods lt R gt corresponds to the DAO s related table lt P gt corresponds to the DAO s related generated POJO type lt T gt corresponds to the DAO s related table s primary key type Note that multi column primary keys are not yet supported by DAOs public interface DAO lt R extends TableRecord lt R gt P T gt These methods allow for inserting POJOs void insert P object throws DataAccessException void insert P objects throws DataAccessException void insert Collection lt P gt objects throws DataAccessException These methods allow for updating POJOs based on their primary key void update P object throws DataAccessException void update P objects throws DataAccessException void update Collection lt P gt objects throws DataAccessException These methods allow for deleting POJOs based on their primary key void delete P objects throws DataAccessException void delete Collection lt P gt objects throws DataAccessException void deleteById T ids throws DataAccessException void deleteById Collection lt T gt ids throws DataAccessException These methods allow for checking record existence boolean exists P object throws DataAccessException boolean existsById T id throws DataAccessException long count throws DataAccessException These methods allow for
175. ks they ve written in German if they have written more than five books in German in the last three years from 2011 and sort those authors by last names limiting results to the second and third row locking the rows for a subsequent update whew DSLContext create DSL using connection dialect SELECT AUTHOR FIRST_NAME AUTHOR LAST_NAME COUNT create select AUTHOR FIRST_NAME AUTHOR LAST_NAME count FROM AUTHOR from AUTHOR JOIN BOOK ON AUTHOR ID BOOK AUTHOR_ID join BOOK on BOOK AUTHOR_ID equal AUTHOR ID WHERE BOOK LANGUAGE DE where BOOK LANGUAGE equal DE AND BOOK PUBLISHED gt 2008 01 01 and BOOK PUBLISHED greaterThan 2008 01 01 GROUP BY AUTHOR FIRST_NAME AUTHOR LAST_NAME groupBy AUTHOR FIRST_NAME AUTHOR LAST_NAME HAVING COUNT gt 5 having count greaterThan 5 ORDER BY AUTHOR LAST_NAME ASC NULLS FIRST orderBy AUTHOR LAST_NAME asc nullsFirst LIMIT 2 limit 2 OFFSET 1 offset 1 FOR UPDATE A EOrUPdA ES Details about the various clauses of this query will be provided in subsequent sections SELECT from single tables A very similar but limited API is available if you want to select from single tables in order to retrieve TableRecords or even UpdatableRecords The decision which type of select to create is already made at the very first step when you create the SELECT statement with the DSL or DSLContext types public lt R
176. l String userName root String password String url jdbc mysql localhost 3306 guestbook try Class forName com mysql jdbc Driver newInstance conn DriverManager getConnection url userName password catch Exception e For the sake of this tutorial let s keep exception handling simple e printStackTrace finally TE oa Se puh 46 try conn close catch SQLException ignore pretty standard code for establishing a MySQL connection 1 5 Step 5 Querying Let s add a simple query constructed with jOOQ s query DSL DSLContext create DSL using conn SQLDialect MYSQL Result lt Record gt result create select from POSTS fetch First get an instance of DSLContext so we can write a simple S CT query We pass an instance of the MySQL connection to DSL Note that the DSLContext doesn t close the connection We ll have to do that ourselves We then use jOOQ s query DS 3 4 1 6 Step 6 Iterating Lto return an instance of Result We ll be using this result in the next step After the line where we retrieve the results let s iterate over the results and print out the data for Record r result Long id r getValue POSTS ID String title gertVvalue POSTS TITLE String description r getValue POSTS BODY Sytem OUt princim ED ay Gleb se W ELELE a CA OS Upton dos CrIPELONI The full program should now look like this
177. le to re use execution plans for identical SQL statements regardless of actual bind values This heavily improves the time it takes for soft parsing a SQL statement In other situations assuming that bind values are irrelevant for SQL execution plans may be a bad idea as you might run into bind value peeking issues You may be better off spending the extra cost for a new hard parse of your SQL statement and instead having the database fine tune the new plan to the concrete bind values Whichever aproach is more optimal for you cannot be decided by jOOQ In most cases prepared statements are probably better But you always have the option of forcing OOQ to render inlined bind values Inlining bind values on a per bind value basis Note that you don t have to inline all your bind values at once If you know that a bind value is not really a variable and should be inlined explicitly you can do so by using DSL inline as documented in the manual s section about inlined parameters 5 5 Reusing a Querys PreparedStatement As previously discussed in the chapter about differences between OOQ and JDBC reusing PreparedStatements is handled a bit differently in OOQ from how it is handled in JDBC Keeping open PreparedStatements with JDBC With JDBC you can easily reuse a java sal PreparedStatement by not closing it between subsequent executions An example is given here Execute the statement try PreparedSta
178. lect lt gt s2 create select BOOK ID trim BOOK TITLE Some commonly used projections can be easily created using convenience methods Simple SELECTs Select commonly used values SELECT COUNT Select lt gt selectl create selectCount SELECT 0 Not a bind variable Select lt gt select2 create selectZero SELECT 1 Not a bind variable Select lt gt select2 create selectOne See more details about functions and expressions in the manual s section about Column expressions The SELECT DISTINCT clause CT clause The DISTINCT keyword can be included in the method name constructing a S SELECT DISTINCT BOOK TITLE Select lt gt selectl create selectDistinct BOOK TITLE SELECTA jOOQ does not explicitly support the asterisk operator in projections However you can omit the projection as in these examples Explicitly selects all columns available from BOOK create select from BOOK Explicitly selects all columns available from BOOK and AUTHOR create select from BOOK AUTHOR create select from BOOK crossJoin AUTHOR Renders a SELECT statement as columns are unknown to j00Q create select from tableByName BOOK Typesafe projections with degree up to 22 Since jOOQ 3 0 records and row value expressions up to degree 22 are now generically typesafe This is reflected by an overloaded SELECT and SELECT DISTINCT API in both DSL and DSLCon
179. lic static final UDTField lt AddressTypeRecord String gt COUNTRY createField COUNTRY SQLDataType VARCHAR U_ADDRESS_TYPE I less Besides the org joog UDT implementation a org joog UDTRecord implementation is also generated public class AddressTypeRecord extends UDTRecordImpl lt AddressTypeRecord gt Every attribute generates a getter and a setter public void setZip String value public String getZip 1 public void setCity String value public String getCityi t lt public void setCountry String value public String getCountry L Flags controlling UDT generation UDT generation cannot be deactivated 6 12 Custom data types and type conversion When using a custom type in jOOQ you need to let jOOQ know about its associated org ooq Converter Ad hoc usages of such converters has been discussed in the chapter about data type conversion A more common use case however is to let OOQ know about custom types at code generation time Use the following configuration elements to specify that you d like to use GregorianCalendar for all database fields that start with DATE_OF lt database gt lt First register your custom types here gt lt customTypes gt lt customType gt lt Specify the fully qualified class name of your custom type gt lt name gt java util GregorianCalendar lt name gt lt Associate that custom type with your conv
180. ll also need to pass the flag to from an Page 34 174 The jOOQ User Manual 4 2 5 Custom Settings See the manual s section about ExecuteListeners to see examples of such listener implementations 4 2 5 Custom Settings The jOOQ Configuration allows for some optional configuration elements to be used by advanced users The org jooq conf Settings class is a JAXB annotated type that can be provided to a Configuration in several ways In the DSLContext constructor DSL using This will override default settings below in the org joog impl DefaultConfiguration constructor This will override default settings below From a location specified by a JVM parameter Dorg jooq settings From the classpath at joog settings xml From the settings defaults as specified in http www joog org xsd joog runtime 3 0 0 xsd Example For example if you want to indicate to jOOQ that it should inline all bind variables and execute static java sql Statement instead of binding its variables to java sal PreparedStatement you can do so by creating the following DSLContext Settings settings new Settings settings setStatementType StatementType STATIC_STATEMENT DSLContext create DSL using connection dialect settings Subsequent sections of the manual contain some more in depth explanations about these settings Runtime schema and table mapping Execute CRUD with optimistic locking enabled
181. ll rights reserved Page 137 174 The jOOQ User Manual 5 14 ExecuteListeners DataAccessException General exception usually originating from a java sq SQLException DataChangedException An exception indicating that the database s underlying record has been changed in the mean time see optimistic locking DataTypeException Something went wrong during type conversion DetachedException A SQL statement was executed on a detached UpdatableRecord or a detached SQL statement nvalidResultException An operation was performed expecting only one result but several results were returned appingException Something went wrong when loading a record from a POJO or when mapping a record into a POJO Override OOQ s exception handling The following section about execute listeners documents means of overriding jOOQ s exception handling if you wish to deal separately with some types of constraint violations or if you raise business errors from your database etc 5 14 ExecuteListeners The Executor class lets you specify a list of org joog ExecuteListener instances The ExecuteListener is essentially an event listener for Query Routine or ResultSet render prepare bind execute fetch steps It is a base type for loggers debuggers profilers data collectors triggers etc Advanced ExecuteListeners can also provide custom implementations of Connection PreparedStatement and
182. ll rights reserved Page 7 174 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 Avery 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 2014 by Data Geekery GmbH All rights reserved Page 8 174 The jOOQ User Manual 2 Copyright License and Trademarks 2 Copyright License and Trademarks This section lists the various licenses that apply to different versions of jOOQ Prior to version 3 2 OOQ was shipped for free under the terms of the Apache Software License 2 0 With jOOQ 3 2 jOOQ became dual licensed Apache Software License 2 0 for use with Open Source databases and commercial for use with commercial databases This manual itself as well as the www joog org public website is licensed to you under the terms of the CC BY SA 4 0 lic
183. lsStep lt T gt lag Field lt T gt field lt T gt WindowIgnoreNullsStep lt T gt TagEreldsio field ant offset lt T gt WindowIgnoreNullsStep lt T gt lag Field lt T gt field ant offset T defaultValue lt T gt WindowIgnoreNullsStep lt T gt lag Field lt T gt field int offset Field lt T gt defaultValue Statistical functions WindowOverStep lt BigDecimal gt cumeDist WindowOverStep lt Integer gt ntile int number SQL distinguishes between various window function types e g ranking functions Depending on the function SQL expects mandatory PARTITION BY or ORDER BY clauses within the OVER clause jOOQ does not enforce those rules for two reasons 2009 2014 by Data Geekery GmbH All rights reserved Page 74 174 The jOOQ User Manual 4 5 13 Window functions Your JDBC driver or database already checks SQL syntax semantics Not all databases behave correctly according to the SQL standard If possible however jOOQ tries to render missing clauses for you if a given SQL dialect is more restrictive Some examples Here are some simple examples of window functions with jOOQ Sample uses of ROW_NUMBER Sample uses of rowNumber ROW_NUMBER OVER rowNumber over ROW_NUMBER OVER PARTITION BY 1 rowNumber over partitionByOne ROW_NUMBER OVER ORDER BY BOOK ID rowNumber over partitionBy BOOK AUTHOR_ID ROW_NUMBER OVER PARTITION BY BOOK AUTHOR
184. ly overloaded in three ways Let s look at the condition query part constructor Construct a condition without bind values Example condition a b Condition condition String sql Construct a condition with bind values Example condition a 1 Condition conditrontSstring sgl Object bindings Construct a condition taking other j00Q object arguments Example condition a 0 val 1 Condition conditironistring sal Querybart parts Please refer to the org jooq impl DSL Javadoc for more details The following is a more complete listing of plain SQL construction methods from the DSL O 2009 2014 by Data Geekery GmbH All rights reserved Page 92 174 The jOOQ User Manual A condition Condition condition String sql Condition condition String sql Object bindings Condition condition String sql QueryPart parts A field with an unknown data type Field lt Object gt field String sql Field lt Object gt field String sql Object bindings Field lt Object gt field String sql QueryPart parts A field with a known data type lt T gt Field lt T gt field String sql Class lt T gt type MC freld string sql Class SES MODEST cos lt T gt Field lt T gt field String sql Class lt T gt type QueryPart parts lt T gt Field lt T gt field String sql DataType lt T gt type lt T gt Field lt T gt field String sql DataType lt T gt type Objec
185. mapped to their signed counterparts letting bit wise shifting and tweaking to the user jOOQ ships with a set of unsigned java lang Number implementations modelling the following types org oog types UByte Unsigned byte an 8 bit unsigned integer org joog types UShort Unsigned short a 16 bit unsigned integer org joog types UInteger Unsigned int a 32 bit unsigned integer org joog types ULong Unsigned long a 64 bit unsigned integer T Each o these wrapper types extends java lang Number wrapping a higher level integer type internally Byte wraps java lang Short Short wraps java lang Integer nteger wraps java lang Long Long wraps java math Biglnteger U U U U 8 2 3 INTERVAL data types jOOQ fills a gap opened by JDBC which neglects an important SQL data type as defined by the SQL standards INTERVAL types SQL knows two different types of intervals YEAR TO MONTH This interval type models a number of months and years DAY TO SECOND This interval type models a number of days hours minutes seconds and milliseconds Both interval types ship with a variant of subtypes such as DAY TO HOUR HOUR TO SECOND etc jOOQ models these types as Java objects extending javalang Number org jooq types YeartoMonth where Number intValue corresponds to the absolute number of months and org joog types DayToSecond where Number intValue corresponds to the absolute numbe
186. n Code is kept DRY As a general rule of thumb throughout the jOOQ code everything is kept DRY Some examples O 2009 2014 by Data Geekery GmbH All rights reserved Page 169 174 The jOOQ User Manual 8 5 Migrating to OOQ 3 0 There is only one place in the entire code base which consumes values from a JDBC ResultSet There is only one place in the entire code base which transforms jOOQ Records into custom POJOs Keeping things DRY leads to longer stack traces but in turn also increases the relevance of highly reusable code blocks Chances that some parts of the jOOQ code base slips by integration test coverage decrease significantly 8 5 Migrating to JOOQ 3 0 This section is for all users of jOOQ 2 x who wish to upgrade to the next major release In the next sub sections the most important changes are explained Some code hints are also added to help you fix compilation errors Type safe row value expressions Support for row value expressions has been added in OOQ 2 6 In jOOQ 3 0 many API parts were thoroughly but often incompatibly changed in order to provide you with even more type safety Here are some affected API parts N in Row N has been raised from 8 to 22 This means that existing row value expressions with degree gt 9 are now type safe Subqueries returned from DSL select now implement Select lt Record N gt not Select lt Record gt IN predicates and compar
187. n cause a major risk of deadlocks or race conditions if the JDBC driver can recover from the unsuccessful locking if two Java threads execute the following statements jelmhereyeh di SELECT FROM author ORDER BY id ASC AECI SELECT FROM author ORDER BY id DESC So use this technique with care possibly only ever locking single rows Pessimistic shared locking with the FOR SHARE clause Some databases MySQL Postgres also allow to issue a non exclusive lock explicitly using a FOR SHAR clause This is also supported by jOOQ 2009 2014 by Data Geekery GmbH All rights reserved Page 51 174 The jOOQ User Manual 4 3 2 11 UNION INTERSECTION and EXCEPT Optimistic locking in jOOQ Note that jOOQ also supports optimistic locking if you re doing simple CRUD This is documented in the section s manual about optimistic locking 4 3 2 11 UNION INTERSECTION and EXCEPT SQL allows to perform set operations as understood in standard set theory on result sets These operations include unions intersections subtractions For two subselects to be combinable by such a set operator each subselect must return a table expression of the same degree and type UNION and UNION ALL These operators combine two results into one While UNION removes all duplicate records resulting from this combination UNION ALL leaves subselect results as they are Typically you should prefer UNION ALL over UNION if you d
188. nal division for details on how jOOQ emulates this operation 0 jOOQ supports all of these JOIN types except semi join and anti join directly on any table expression jO0Q s relational division convenience syntax DivideByOnStep divideBy Table lt gt table Various overloaded INNER JOINs TableOnStep join TableLike lt gt TableOnStep join String TableOnStep join String Object TableOnStep join String QueryPart Various overloaded OUTER JOINs supporting Oracle s partitioned OUTER JOIN Overloading is similar to that of INNER JOIN TablePartitionByStep leftOuterJoin TableLike lt gt TablePartitionByStep rightOuterJoin TableLike lt gt Various overloaded FULL OUTER JOINs TableOnStep fullOuterJoin TableLike lt gt Various overloaded CROSS JOINs Table lt Record gt crossJoin TableLike lt gt Various overloaded NATURAL JOINs Table lt Record gt naturalJoin TableLike lt gt Table lt Record gt naturalLeftOuterJoin TableLike lt gt Table lt Record gt naturalRightOuterJoin TableLike lt gt Note that most of jOOQ s JOIN operations give way to a similar DSL API hierarchy as previously seen in the manual s section about the JOIN clause 4 4 4 The VALUES table constructor Some databases allow for expressing in memory temporary tables using a VALUES constructor This const
189. nced use cases of integrating your application with jOOQ you may want to put custom data into your Configuration which you can then access from your Custom ExecuteListeners Custom QueryParts Here is an example of how to use the custom data API Let s assume that you have written an ExecuteListener that prevents INSERT statements when a given flag is set to true 2009 2014 by Data Geekery GmbH All rights reserved Page 33 1 74 The jOOQ User Manual Implement an ExecuteListener public class NoInsertListener extends DefaultExecuteListener Override public void start ExecuteContext ctx This listener is active only when your custom flag is set to true 4 2 4 Custom ExecuteListeners if Boolean TRUE equals ctx configuration data com example my namespace no inserts If active fail this execution if an INSERT statement is being executed if ctx query instanceof Insert throw new DataAccessException No INSERT statements allowed See the manual s section about ExecuteListeners to learn more about how to implement an ExecuteListener the Configuration in order for the listener to work Create your Configuration Configuration configuration new DefaultConfiguration set connection set dialect Set a new execute listener provider onto the configuration configuration set new DefaultExecuteListenerProvider new NoInsertListener
190. ndency gt lt groupId gt org jooq lt groupid gt lt artifactId gt jooq codegen lt artifactId gt lt version gt 3 0 1 lt version gt lt dependency gt Please refer to the manual s section about Code generation configuration to learn how to use jOOQ s code generator with Maven For this example we ll be using MySQL If you haven t already downloaded MySQL Connector J download it here http dev mysql com downloads connector j If you don t have a MySQL instance up and running yet get XAMPP now XAMPP is a simple installation bundle for Apache MySQL PHP and Perl 3 4 1 2 Step 2 Your database We re going to create a database called guestbook and a corresponding posts table Connect to MySQL via your command line client and type the following CREATE DATABASE guestbook CREATE TABLE posts id bigint 20 NOT NULL body varchar 255 DEFAULT NULL timestamp datetime DEFAULT NULL title varchar 255 DEFAULT NULL PRIMARY KEY id i 3 4 1 3 Step 3 Code generation In this step we re going to use jOOQ s command line tools to generate classes that map to the Posts table we just created More detailed information about how to set up the jOOQ code generator can be found here jOOQ manual pages about setting up the code generator The easiest way to generate a schema is to copy the jOOQ jar files there should be 3 and the MySQL Connector jar file to a temporary directory
191. nderFormatted true If we re executing a query if ctz query nuli i System out printin create renderInlined ctx query If we re executing a routine else if ctx routine null System out printin create renderInlined ctx routine If we re executing anything else e g plain SQL else if StringUtils isBlank ctx sql System out printin ctx sql See also the manual s sections about logging and the OOQ Console for more sample implementations of actual ExecuteListeners 5 15 Database meta data Since jOOQ 3 0 a simple wrapping API has been added to wrap JDBC s rather awkward java sqlDatabaseMetaData This API is still experimental as the calls to the underlying JDBC type are not always available for all SQL dialects 2009 2014 by Data Geekery GmbH All rights reserved Page 139 174 The jOOQ User Manual 5 16 Logging 5 16 Logging jOOQ logs all SQL queries and fetched result sets to its internal DEBUG logger which is implemented as an execute listener By default execute logging is activated in the OOQ Settings In order to see any DEBUG log output put either log4j or slf4j on jOOQ s classpath along with their respective configuration A sample log4j configuration can be seen here lt xml version 1 0 encoding UTF 8 gt lt log4j configuration gt lt appender name stdout class org apache log4j ConsoleAppender gt lt layout class o
192. ned in the SQL standard when grouping you may no longer project any columns that are not a formal part of the GROUP BY clause or aggregate functions MySQL s deviation from the SQL standard MySQL has a peculiar way of not adhering to this standard behaviour This is documented in the MySQL manual In short with MySQL you can also project any other field that is not part of the GROUP BY clause The projected values will just be arbitrary values from within the group You cannot rely on any ordering For example SELECT AUTHOR_ID TITLE create select BOOK AUTHOR_ID BOOK TITLE FROM BOOK from BOOK GROUP BY AUTHOR_ID groupBy AUTHOR_ID This will return an arbitrary title per author OOQ supports this syntax as jOOQ is not doing any checks internally about the consistence of tables fields functions that you provide it Empty GROUP BY clauses jOOQ supports empty GROUP BY clause as well This will result in SELECT statements that return only one record SELECT COUNT create selectCount FROM BOOK from BOOK GROUP BY groupBy 2009 2014 by Data Geekery GmbH All rights reserved Page 46 174 The jOOQ User Manual 4 3 2 7 The HAVING clause ROLLUP CUBE and GROUPING SETS Some databases support the SQL standard grouping functions and some extensions thereof See the manual s section about grouping functions for more details 4 3 2 7 The HAVING clause The HAVING cl
193. nerated POJOs You can use your own Using JPA annotated POJOs jOOQ tries to find JPA annotations on your POJO types If it finds any they are used as the primary source for mapping meta information Only the javax persistence Column annotation is used and understood by jOOQ An example 2009 2014 by Data Geekery GmbH All rights reserved Page 110 174 The jOOQ User Manual 5 3 6 POJOs A JPA annotated POJO class public class MyBook Column name ID public int myld Column name TITLE public String myTitle The various into methods allow for fetching records into your custom POJOs MyBook myBook create select from BOOK fetchAny into MyBook class List lt MyBook gt myBooks create select from BOOK fetch into MyBook class List lt MyBook gt myBooks create select from BOOK fetchInto MyBook class Just as with any other JPA implementation you can put the javax persistence Column annotation on any class member including attributes setters and getters Please refer to the Record into Javadoc for more details Using simple POJOs If OOQ does not find any JPA annotations columns are mapped to the best matching constructor attribute or setter An example illustrates this A mutable POJO class public class MyBookl public int id public String title The various into methods allow for fetching records into your custom
194. new BookDao configuration Start using the DAO Book book bookDao findById 5 Modify and update the POJO book setTitle 1984 book setPublishedIn 1948 bookDao update book Delete it again bookDao delete book 5 13 Exception handling Checked vs unchecked exceptions This is an eternal and religious debate Pros and cons have been discussed time and again and it still is a matter of taste today In this case OOQ clearly takes a side OOQ s exception strategy is simple All system exceptions are unchecked If in the middle of a transaction involving business logic there is no way that you can recover sensibly from a lost database connection or a constraint violation that indicates a bug in your understanding of your database model All business exceptions are checked Business exceptions are true exceptions that you should handle e g not enough funds to complete a transaction With jOOQ it s simple All of OOQ s exceptions are system exceptions hence they are all unchecked jOOQ s DataAccessException jOOQ uses its own org joog exception DataAccessException to wrap any underlying java sql SQLException that might have occurred Note that all methods in jOOQ that may cause such a DataAccessException document this both in the Javadoc as well as in their method signature DataAccessException is subtyped several times as follows 2009 2014 by Data Geekery GmbH A
195. new Settings withRenderMapping new RenderMapping withSchemata new MappedSchema withInput DEV withOutput MY_BOOK_WORLD Add the settings to the DSLContext DSLContext create DSL using connection SQLDialect ORACLE settings Run queries with the mapped Configuration create selectFrom AUTHOR fetch The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement SELECT FROM MY_BOOK_WORLD AUTHOR EV Even if AUTHOR was generated from D Mapping several schemata Your development database may not be restricted to hold only one DEV schema You may also have a LOG schema and a MASTER schema Let s say the MASTER schema is shared among all customers but each customer has their own LOG schema instance Then you can enhance your RenderMapping like this e g using an XML configuration file lt settings xmlns http www jooq org xsd joogqg runtime 3 0 0 xsd gt lt renderMapping gt lt schemata gt lt schema gt lt input gt DEV lt input gt lt output gt MY_BOOK_WORLD lt output gt lt schema gt lt schema gt lt input gt LOG lt input gt lt output gt MY_BOOK_WORLD_LOG lt output gt lt schema gt lt schemata gt lt renderMapping gt lt settings gt Note you can load the above XML file like this Settings settings JAXB unmarshal new File jooq runtime xml Settings class This will map genera
196. ng bind variables while still using custom values in SQL jOOQ refers to that as inlined bind values When bind values are inlined they render the actual value in SQL rather than a JDBC question mark Bind value inlining can be achieved in two ways By using the Settings and setting the org joog conf StatementType to STATIC_STATEMENT This will inline all bind values for SQL statements rendered from such a Configuration By using DSL inline methods In both cases your inlined bind values will be properly escaped to avoid SQL syntax errors and SQL injection Some examples Use dedicated calls to inline in order to specify single bind values to be rendered as inline values _ create select from AUTHOR where LAST_NAME equal inline Poe Or render the whole query with inlined values wanna nnn nnn nnn nnn nnn Settings settings new Settings withStatementType StatementType STATIC_STATEMENT Add the settings to the Configuration DSLContext create DSL using connection SQLDialect ORACLE settings Run queries that omit rendering schema names create select from AUTHOR where LAST_NAME equal Poe 4 8 4 SQL injection and plain SQL QueryParts Special care needs to be taken when using plain SOL QueryParts While OOQ s API allows you to specify bind values for use with plain SQL you re
197. ng subselects UPDATE AUTHOR SET FIRST_NAME LAST_NAME SELECT PERSON FIRST_NAME PERSON LAST_NAME FROM PERSON WHERE PERSON ID AUTHOR ID WHERE ID 3 create update AUTHOR set row AUTHOR FIRST_NAME AUTHOR LAST_NAME select PERSON FIRST_NAME PERSON LAST_NAME from PERSON where PERSON ID equal AUTHOR ID where AUTHOR ID equal 3 The above row value expressions usages are completely typesafe UPDATE RETURNING The Firebird and Postgres databases support aR as the RETURNING clause in INSERT statements go An example is given here Fetch a trigger generated value UPDATE BOOK SET TITLE Animal Farm WHERE ID 5 RETURNING TITLE ETURNING clause on their UPDATE statements similar This is useful to fetch trigger generated values in one String title create update BOOK Set BOOK TITLE where BOOK ID equal 5 returning BOOK TITLE fetchOne get Value BOOK TITLE Animal Farm The UPDATE RETURNI E G clause is currently not emulated for other databases Future versions might execute an additional S ECT statement to fetch results 4 3 5 The DELETE statement The DELETE statement removes records from a database table DEI on single tables Support for multi table deletes will be implemen delete query is given here ETE statements are only possible ted in
198. ng to http semver org quite strictly Those rules impose a versioning scheme X Y Z that can be summarised as follows E fa patch release includes bug Z is incremented by one fa minor release includes backwards compatible API relevant new features Y is incremented by one and Z is reset to zero fa major release includes backwards incompatible API relevant new features X is incremented by one and Y Z are reset to zero ixes performance improvements and APl irrelevant new features 2009 2014 by Data Geekery GmbH All rights reserved Page 27 174 The jOOQ User Manual 3 9 OOQ and backwards compatibility jOOQ s understanding of backwards compatibility Backwards compatibility is important to jOOQ You ve chosen jOOQ as a strategic SQL engine and you don t want your SQL to break That is why there is at most one major release per year which changes only those parts of OOQ s API and functionality which were agreed upon on the user group During the year only minor releases are shipped adding new features in a backwards compatible way However there are some elements of API evolution that would be considered backwards incompatible in other APIs but not in jOOQ As discussed later on in the section about OOQ s query DSL API much of jOOQ s API is indeed an internal domain specific language implemented mostly using Java interfaces Adding language elements to these interfaces
199. non standard extension to the SQL syntax which is supported by jOOQ and emulated in other RDBMS where this is possible i e if they support the SQL standard MERGE statement Here is an example how to use the ON DUPLICATE KEY UPDATE clause Add a new author called Koontz with ID 3 If that ID is already present update the author s name create insertInto AUTHOR AUTHOR ID AUTHOR LAST_NAME values 3 Koontz onDuplicateKeyUpdate set AUTHOR LAST_NAME Koontz The synthetic ON DUPLICATE KEY IGNORE clause The MySQL database also supports an INSERT IGNORE INTO clause This is supported by jOOQ using the more convenient SQL syntax variant of ON DUPLICATE KEY IGNORE which can be equally emulated in other databases using a MERGE statement Add a new author called Koontz with ID 3 If that ID is already present ignore the INSERT statement create insertInto AUTHOR AUTHOR ID AUTHOR LAST_NAME values 3 Koontz onDuplicateKeyIgnore Postgres s INSERT RETURNING The Postgres database has native support for an INSERT RETURNING clause This is a very powerful concept that is emulated for all other dialects using JDBC s getGeneratedKeys method Take this example O 2009 2014 by Data Geekery GmbH All rights reserved Page 56 174 The jOOQ User Manual 4 3 4 The UPDATE statement Add another auth
200. not forced to do that For instance both of the following queries will lead to the same valid result This query will use bind values internally create fetch SELECT FROM BOOK WHERE ID AND TITLE 5 Animal Farm This query will not use bind values internally create fetch SELECT FROM BOOK WHERE ID 5 AND TITLE Animal Farm All methods in the jOOQ API that allow for plain unescaped untreated SQL contain a warning message in their relevant Javadoc to remind you of the risk of SQL injection in what is otherwise a SQL injection safe API 2009 2014 by Data Geekery GmbH All rights reserved Page 96 174 The jOOQ User Manual 4 9 QueryParts 4 9 QueryParts A org joog Query and all its contained objects is a org ooq QueryPart QueryParts essentially provide this functionality they can render SQL using the toSQL RenderContext method they can bind variables using the bind BindContext method Both of these methods are contained in jOOQ s internal API s org ooq QueryPartInternal which is internally implemented by every QueryPart The following sections explain some more details about SQL rendering and variable binding as well as other implementation details about QueryParts in general 4 9 1 SQL rendering Every org joog QueryPart must implement the toSQL RenderContext method to render its SQL string to a org joog RenderContext This RenderContext
201. notation as such O 2009 2014 by Data Geekery GmbH All rights reserved Page 31 174 The jOOQ User Manual 4 2 2 Connection vs DataSource pex Add an Oracle specific lt code gt CONNECT BY lt code gt clause to the query El Support SQLDialect CUBRID SQLDialect ORACLE SelectConnectByConditionStep lt R gt connectBy Condition condition jOOQ API methods which are not annotated with the org joog Support annotation or which are annotated with the Support annotation but without any SQL dialects can be safely used in all SQL dialects An example for this is the SELECT statement factory method t Create a new DSL select statement Support selectselectstepsk gt select Field lt gt fields jOOQ s SQL clause emulation capabilities The aforementioned Support annotation does not only designate which databases natively support a feature It also indicates that a feature is emulated by jOOQ for some databases lacking this feature An example of this is the DISTINCT predicate a predicate syntax defined by SQL 1999 and implemented only by H2 HSQLDB and Postgres A IS DISTINCT FROM B Nevertheless the IS DISTINCT FROM predicate is supported by jOOQ in all dialects as its semantics can be expressed with an equivalent CASE expression For more details see the manual s section about the DISTINCT predicate jOOQ and the Oracle SQL dialect Oracle S
202. ns and limitations under the License j00Q License and Maintenance Agreement Data Geekery grants the Customer the non exclusive timely limited and non transferable license to install and use the Software under the terms of the j00Q License and Maintenance Agreement This library is distributed with a LIMITED WARRANTY See the j00Q License and Maintenance Agreement for more details http www jooq org licensing Trademarks owned by Data Geekery GmbH jOOA is a trademark by Data Geekery GmbH jOOQ is a trademark by Data Geekery GmbH jOOR is a trademark by Data Geekery GmbH j J OOU is a trademark by Data Geekery GmbH OOX is a trademark by Data Geekery GmbH O 2009 2014 by Data Geekery GmbH All rights reserved 2 Copyright License and Trademarks Page 10 174 The jOOQ User Manual 2 Copyright License and Trademarks Trademarks owned by database vendors with no affiliation to Data Geekery GmbH Access is a registered trademark of Microsoft Inc Adaptive Server Enterprise is a registered trademark of Sybase Inc CUBRID is a trademark of NHN Corp 7 DB20 is a registered trademark of IBM Corp E Derby is a trademark of the Apache Software Foundation 2 is a trademark of the H2 Group SQLDB is a trademark of The hsql Development Group ngres is a trademark of Actian Corp Z ariaDB is a trademark of Monty Program Ab ySQLO is a registered
203. nted SQL builder library like any other This meant that all queries and their syntactic components were modeled as so called QueryParts which delegate SQL rendering and variable binding to child components This part of the API will be referred to as the model API or non DSL API which is still maintained and used internally by jOOQ for incremental query building An example of incremental query building is given here DSLContext create DSL using connection dialect selectpuery eRecord gt query Greate se lestquesy 0 query addFrom AUTHOR Join books only under certain circumstances LE ela query addJoin BOOK BOOK AUTHOR_ID equal AUTHOR ID Result lt gt result query fetch This query is equivalent to the one shown before using the DSL syntax In fact internally the DSL API constructs precisely this SelectQuery object Note that you can always access the SelectQuery object to switch between DSL and model APIs DSLContext create DSL using connection dialect SelectFinalStep lt gt select create select from AUTHOR Add the JOIN clause on the internal QueryObject representation SelectQuery lt gt query select getQuery query addJoin BOOK BOOK AUTHOR_ID equal AUTHOR ID Mutability Note that for historic reasons the DSL API mixes mutable and immutable behaviour with respect to the internal representation ofthe QueryPart being constructed While creating
204. odules to represent this concept even if this is rarely implemented O 2009 2014 by Data Geekery GmbH All rights reserved Page 124 174 The jOOQ User Manual 5 8 2 Oracle member procedures as such This is reflected in jOOQ by the use of Java sub packages in the source code generation destination package Every Oracle package will be reflected by A Java package holding classes for formal Java representations of the procedure function in that package AJava class holding convenience methods to facilitate calling those procedures functions Apart from this the generated source code looks exactly like the one for standalone procedures functions For more details about code generation for procedures and packages see the manual s section about procedures and code generation 5 8 2 Oracle member procedures Oracle UDTs can have object oriented structures including member functions and procedures With Oracle you can do things like this CREATE OR REPLACE TYPE u_author_type AS OBJECT id NUMBER 7 first_name VARCHAR2 50 last_name VARCHAR2 50 MEMBER PROCEDURE LOAD MEMBER FUNCTION counBOOKs RETURN NUMBER The type body is omitted for the example These member functions and procedures can simply be mapped to Java methods Create an empty attached UDT record from the DSLContext UAuthorType author create newRecord U_AUTHOR_TYPE Set the author ID and load the record using
205. of your database This may be any of the currently supported database types see SQL Dialect for more details org joog conf Settings An optional runtime configuration see Custom Settings for more details org joog ExecuteListenerProvider An optional reference to a provider class that can provide execute listeners to jOOQ see ExecuteListeners for more details Any of these java sql Connection An optional JDBC Connection that will be re used for the whole lifecycle of your Configuration see Connection vs DataSource for more details For simplicity this is the use case referenced from this manual most of the time java sql DataSource An optional JDBC DataSource that will be re used for the whole lifecycle of your Configuration If you prefer using DataSources over Connections jOOQ will internally fetch new Connections from your DataSource conveniently closing them again after query execution This is particularly useful in J2EE or Spring contexts see Connection vs DataSource for more details org joog ConnectionProvider A custom abstraction that is used by jOOQ to acquire and release connections jOOQ will internally acquire new Connections from your ConnectionProvider conveniently releasing them again after query execution see Connection vs DataSource for more details Wrapping a Configuration object a DSLContext can construct statement
206. oid toSQL RenderContext context The CompareCondition delegates rendering of the Fields to the Fields themselves and connects them using the Condition s comparator operator context sql fieldl og sql comparator toSQL eai o sql field2 See the manual s sections about custom QueryParts and plain SQL QueryParts to learn about how to write your own query parts in order to extend jOOQ 4 9 2 Pretty printing SQL As mentioned in the previous chapter about SQL rendering there are some elements in the org joog RenderContext that are used for formatting pretty printing rendered SQL In order to obtain pretty printed SQL just use the following custom settings Create a DSLContext that will render formatted SOL DSLContext pretty DSL using dialect new Settings withRenderFormatted true And then use the above DSLContext to render pretty printed SQL String sql pretty select select AUTHOR LAST_NAME count as c TEST AUTHOR LAST_NAME from BOOK coumt er join AUTHOR on BOOK AUTHOR_ID equal AUTHOR ID where BOOK TITLE notEqual 1984 groupBy AUTHOR LAST_NAME having count equal 2 getSQL 2009 2014 by Data Geekery GmbH All rights reserved from TEST BOOK join YTEST AAUTHORY on PIESTA C BOOKT VAUTHOR IDM ES AUTOR NB where HALE Sia ea OO Kile UTIILEM ASAS group by TEST AUTHOR LAST_NAME having count
207. oks create select BOOK ID BOOK TITLE from BOOK fetchInto MyBook3 class Please refer to the Record into Javadoc for more details Loading POJOs back into Records to store them The above examples show how to fetch data into your own custom POJOs DTOs When you have modified the data contained in POJOs you probably want to store those modifications back to the database An example of this is given here A mutable POJO class public class MyBook public int id public String title Create a new POJO instance MyBook myBook new MyBook myBook id 10 myBook title Animal Farm Load a j00Q generated BookRecord from your POJO BookRecord book create newRecord BOOK myBook Insert it implicitly book store Insert it explicitly create executelnsert book or update it ID 10 create executeUpdate book Note Because of your manual setting of ID 10 OOQ s store method will asume that you want to insert a new record See the manual s section about CRUD with UpdatableRecords for more details on this Interaction with DAOs If you re using jOOQ s code generator you can configure it to generate DAOs for you Those DAOs operate on generated POJOs An example of using such a DAO is given here 2009 2014 by Data Geekery GmbH All rights reserved Page 112 174 The jOOQ User Manual 5 3 7 Lazy fetching Initialise a Configuration Configu
208. on and other reasons you may choose to use the SELECT FOR UPDATE clause to indicate to the database that a set of cells or records should be locked by a given transaction for subsequent updates With jOOQ this can be achieved as such SELECT create select FROM BOOK from BOOK WHERE ID 3 where BOOK ID equal 3 FOR UPDATE porUpdacel i The above example will produce a record lock locking the whole record for updates Some databases also support cell locks using FOR UPDATE OF SELECT create select FROM BOOK from BOOK WHERE ID 3 where BOOK ID equal 3 FOR UPDATE OF TITLE forUpdate of BOOK TITLE 2009 2014 by Data Geekery GmbH All rights reserved Page 50 174 The jOOQ User Manual 4 3 2 10 The FOR UPDATE clause Oracle goes a bit further and also allows to specify the actual locking behaviour It features these additional clauses which are all supported by jOOQ a FOR UPDATE NOWAIT This is the default behaviour If the lock cannot be acquired the query fails immediately FOR UPDATE WAIT n Try to wait for n seconds for the lock acquisition The query will fail only afterwards FOR UPDATE SKIP LOCKED This peculiar syntax will skip all locked records This is particularly useful when implementing queue tables with multiple consumers With jOOQ you can use those Oracle extensions as such create select from BOOK where BOOK ID
209. on t really need to remove duplicates The following example shows how to use such a UNION operation in jOOQ SELECT FROM BOOK WHERE ID 3 create selectFrom BOOK where BOOK ID equal 3 UNION ALL unionAll SELECT FROM BOOK WHERE ID 5 create selectFrom BOOK where BOOK ID equal 5 INTERSECT ALL and EXCEPT ALL NTERSECT is the operation that produces only those values that are returned by both subselects EXCEPT is the operation that returns only those values that are returned exclusively in the first subselect Both operators will remove duplicates from their results The SQL standard allows to specify the ALL eyword for both of these operators as well but this is hardly supported in any database jOOQ does not support INTERSECT ALL EXEPT ALL operations either jOOQ s set operators and how they re different from standard SQL As previously mentioned in the manual s section about the ORDER BY clause jOOQ has slightly changed the semantics of these set operators While in SQL a subselect may not contain any ORDER BY clause or LIMIT clause unless you wrap the subselect into a nested SELECT jOOQ allows you to do so In order to select both the youngest and the oldest author from the database you can issue the following statement with jOOQ rendered to the MySQL dialect SELECT FROM AUTHOR create selectFrom AUTHOR ORDER B
210. one Pear ICS a Ee Merete 2 orale SS a Sirus yok SN veer nams a Ml e iesiqotey inv My il TESSA a SA Lie e value 1 n NAS oo value 2 n Note This format has changed in jOOQ 2 6 0 5 9 4 Exporting HTML Fetch books and format them as HTML String html create selectFrom BOOK fetch formatHTML The above query will result in an HTML document looking like the following one lt table gt lt thead gt Estates lt th gt ID lt th gt lt th gt AUTHOR_ID lt th gt lt th gt TITLE lt th gt lt tr gt lt thead gt lt tbody gt mae lt td gt 1 lt td gt lt td gt 1 lt td gt lt td gt 1984 lt td gt lt tr gt lt b gt lt td gt 2 lt td gt lt td gt 1 lt td gt lt td gt Animal Farm lt td gt lt tr gt lt tbody gt lt table gt 5 9 5 Exporting Text Fetch books and format them as text String text create selectFrom BOOK fetch format The above query will result in a text document looking like the following one 2009 2014 by Data Geekery GmbH All rights reserved 5 9 3 Exporting JSON Page 127 174 The jOOQ User Manual 5 10 Importing data ID AUTHOR_ID TITLE il 1 1984 i 1 Animal Farm 4 4 A simple text representation can also be obtained by calling toString on a Result object See also the manual s section about DEBUG logging 5 10 Importing da
211. ons are implemented just like numeric functions with similar limitations as far as type restrictions are concerned You can use any of these operators In order to express a SQL query like this one SELECT IL 27 SS 27 10 FROM DUAL You can write something like this in jOOQ create select val 1 add 2 mul val 5 sub 3 div 2 mod 10 O 2009 2014 by Data Geekery GmbH All rights reserved Page 68 174 The jOOQ User Manual 4 5 5 String concatenation Datetime arithmetic expressions jOOQ also supports the Oracle style syntax for adding days to a Field lt extends java util Date gt SELECT SYSDATE 3 FROM DUAL create select currentTimestamp add 3 For more advanced datetime arithmetic use the DSL s timestampbDiff and dateDiff functions as well as jOOQ s built in SQL standard INTERVAL data type support INTERVAL YEAR TO MONTH org jooq types YearToMonth INTERVAL DAY TO SECOND org jooq types DayToSecond 4 5 5 String concatenation The SQL standard defines the concatenation operator to be an infix operator similar to the ones we ve seen in the chapter about arithmetic expressions This operator looks like this Some other dialects do not support this operator but expect a concat function instead OOQ renders the right operator function depending on your SQL dialect SELECT A B C FROM DUAL Om in MySoL For all RDBMS includ
212. or with a generated ID Record lt gt record create insertInto AUTHOR AUTHOR FIRST_NAME AUTHOR LAST_NAME values Charlotte Roche returning AUTHOR ID fetchOne System out println record getValue AUTHOR ID For some RDBMS this also works when inserting several values The following should return a 2x2 table Result lt gt result create insertInto AUTHOR AUTHOR FIRST_NAME AUTHOR LAST_NAME values Johann Wolfgang von Goethe values Friedrich Schiller You can request any field Also trigger generated values returning AUTHOR ID AUTHOR CREATION_DATE fetch Some databases have poor support for returning generated keys after INSERTs In those cases jOOQ might need to issue another SELECT statement in order to fetch an identity value Be aware that this can lead to race conditions in those databases that cannot properly return generated ID values For more information please consider the jOOQ Javadoc for the returning clause The INSERT SELECT statement In some occasions you may prefer the INSERT SELECT syntax for instance when you copy records from one table to another create insertInto AUTHOR_ARCHIVE select create selectFrom AUTHOR where AUTHOR DECEASED isTrue 4 3 4 The UPDATE statement The UPDATE statement is used to modify one or several pre existing records in a database table UPDATE statements are only possi
213. or gt lt database gt lt The database dialect from jooq meta Available dialects are named org util database database Database org jooq org jooq org jooq org jooq org jooq org jooq org jooq org jooq org jooq org jooq org jooq org jooq org jooq uer util QELI util UELI util QELI util util util QELI util QELI Known values are ase ASEDatabase to be used with Sybase ASE cubrid CUBRIDDatabase db2 DB2Database derby DerbyDatabase h2 H2Database hsqldb HSQLDBDatabase ingres IngresDatabase mysql MySQLDatabase oracle OracleDatabase postgres PostgresDatabase sqlite SQLiteDatabase sqlserver SQLServerDatabase sybase SybaseDatabase to be used with Sybase SQL Anywhere You can also provide your own org jooq util Database implementation here if your database is currently not supported or if you wish to read the database schema from a file lt name gt org jooq util oracle OracleDatabase lt name gt such as a Hibernate hbm xml file gt lt All elements that are generated from your schema A Java regular expression Use the pipe to separate several expressions Watch out for case sensitivity Depending on your database important using this syntax 7i expr gt lt includes gt lt includes gt this might be You can create case insensitive regular expressions lt All elements that are excluded from your schema A Java regula
214. ormance considerations Many users may have switched from higher level abstractions such as Hibernate to jOOQ because of Hibernate s difficult to manage performance when it comes to large database schemas and complex second level caching strategies However jOOQ itself is not a lightweight database abstraction framework and it comes with its own overhead Please be sure to consider the following points 2009 2014 by Data Geekery GmbH All rights reserved Page 140 174 The jOOQ User Manual 5 17 Performance considerations t takes some time to construct jOOQ queries If you can reuse the same queries you might cache them Beware of thread safety issues though as jOOQ s Configuration is not necessarily threadsafe and queries are attached to their creating DSLContext t takes some time to render SQL strings Internally OOQ reuses the same java lang StringBuilder for the complete query but some rendering elements may take their time You could of course cache SQL generated by jOOQ and prepare your own java sql PreparedStatement objects t takes some time to bind values to prepared statements OOQ does not keep any open prepared statements internally Use a sophisticated connection pool that will cache prepared statements and inject them into jOOQ through the standard JDBC API t takes some time to fetch results By default OOQ will always fetch the complete java sql ResultSet into memory Use lazy fetching to
215. orted by jOOQ as such Oracle uses strongly typed arrays which means that an ARRAY type VARRAY or TABLE type has aname and possibly a maximum capacity associated with it Unnesting array and cursor types The real power of these types become more obvious when you fetch them from stored procedures to unnest them as table expressions and use them in your FROM clause An example is given here where Oracle s DBMS_XPLAN package is used to fetch a cursor containing data about the most recent execution plan SELECT create select FROM TABLE DBMS_XPLAN DISPLAY_CURSOR null null ALLSTATS from table DbmsXplan displayCursor null null ALLSTATS Note in order to access the DbmsXplan package you can use the code generator to generate Oracle s SYS schema 4 4 9 The DUAL table The SQL standard specifies that the FROM clause is optional in a SELECT statement However according to the standard you may then no longer use some other clauses such as the WHERE clause In the real world there exist three types of databases The ones that always require a FROM clause as required by the SQL standard The ones that never require a FROM clause and still allow a WHERE clause The ones that require a FROM clause only with a WHERE clause GROUP BY clause or HAVING clause 2009 2014 by Data Geekery GmbH All rights reserved Page 65 1
216. os ELO to tmt SStStringta Gamma yy stmt addBatch EAD Wee baie dike ANS stmt setString 2 Richard Sousa 3y SHST NS stmt addBatch SEME GETING hD SE Beemt SetString da Ralph SENS e ESE OS O stmt addBatch stmt setInt 1 4 SENS e Esti as O semt vsetsString 3 TVILISSideSs T stmt addBatch int result stmt executeBatch Using jOOQ jOOQ supports executing queries in batch mode as follows 2009 2014 by Data Geekery GmbH All rights reserved Page 121 174 The jOOQ User Manual 5 7 Sequence execution 1 several queries create batch create insertinto AUTHOR ID FIRST_NAME LAST_NAME values 1 Erich Gamma Mo create insertinto AUTHOR ID FIRST_NAME LAST_NAME values 2 Richard Helm Mo create insertInto AUTHOR ID FIRST_NAME LAST_NAME values 3 Ralph A A Mo create insertinto AUTHOR ID FIRST_NAME LAST_NAME values 4 John Vlissides execute 2 a single query _ create batch create insertInto AUTHOR ID FIRST_NAME LAST_NAME values Integer null null null bind IEA Gamma bind a cr o oi bind E Ralph Johnson bind 4 UOH Vlissides execute When creating a batch execution with a single query and multiple bind values you will still have to provide jOOQ with dummy bind values for the original query In the above example these are set to null For subsequent calls to bind there will
217. oth tables to the left and right of a JOIN operation This can be useful when your database schema has a high degree of relational normalisation An example 2009 2014 by Data Geekery GmbH All rights reserved Page 43 174 The jOOQ User Manual 4 3 2 4 The WHERE clause Assuming that both tables contain AUTHOR_ID columns P JOrn ES SELECT create select FROM AUTHOR from AUTHOR JOIN BOOK USING AUTHOR_ID join BOOK using AUTHOR AUTHOR_ID In schemas with high degrees of normalisation you may also choose to use NATURAL JOIN which takes no JOIN arguments as it joins using all fields that are common to the table expressions to the left and to the right of the JOIN operator An example Assuming that both tables contain AUTHOR_ID columns matara TO el SELECT create select FROM AUTHOR from AUTHOR NATURAL JOIN BOOK naturalJoin BOOK Oracle s partitioned OUTER JOIN Oracle SQL ships with a special syntax available for OUTER JOIN clauses According to the Oracle documentation about partitioned outer joins this can be used to fill gaps for simplified analytical calculations jOOQ only supports putting the PARTITION BY clause to the right of the OUTER JOIN clause The following example will create at least one record per AUTHOR and per existing value in BOOK PUBLISHED_IN regardless if an AUTHOR has actually published a book in that year SELECT create select FROM AUTHOR from
218. over on it See the manual s section about window functions for more details 4 5 13 Window functions Most major RDBMS support the concept of window functions jOOQ knows of implementations in DB2 Oracle Postgres SQL Server and Sybase SQL Anywhere and supports most of their specific syntaxes Note that H2 and HSQLDB have implemented ROW_NUMBER functions without true windowing support As previously discussed any org joog AggregateFunction can be transformed into a window function using the over method See the chapter about aggregate functions for details In addition to those there are also some more window functions supported by jOOQ as declared in the DSL Ranking functions WindowOverStep lt Integer gt rowNumber WindowOverStep lt Integer gt rank WindowOverStep lt Integer gt denseRank WindowOverStep lt BigDecimal gt percentRank Windowing functions lt T gt WindowIgnoreNullsStep lt T gt firstValue Field lt T gt field lt T gt WindowIgnoreNullsStep lt T gt lastValue Field lt T gt field lt T gt WindowIgnoreNullsStep lt T gt lead Field lt T gt field lt T gt WindowIgnoreNullsStep lt T gt lead Field lt T gt field int offset lt T gt WindowIgnoreNullsStep lt T gt lead Field lt T gt field int offset T defaultValue lt T gt WindowIgnoreNullsStep lt T gt lead Field lt T gt field int offset Field lt T gt defaultValue lt T gt WindowIgnoreNul
219. ow no longer formally connected through inheritance ConnectionProvider In order to allow for simpler connection data source management jOOQ externalised connection handling in a new ConnectionProvider type The previous two connection modes are maintained backwards compatibly JDBC standalone connection mode pooled DataSource mode Other connection modes can be injected using 2009 2014 by Data Geekery GmbH All rights reserved Page 172 174 The jOOQ User Manual 8 5 Migrating to jOOQ 3 0 public interface ConnectionProvider Provide j00Q with a connection Connection acquire throws DataAccessException Get a connection back from jO0Q void release Connection connection throws DataAccessException These are some side effects of the above change Connection related JDBC wrapper utility methods commit rollback etc have been moved to the new DefaultConnectionProvider They re no longer available from the DSLContext This had been confusing to some users who called upon these methods while operating in pool DataSource mode ExecuteListeners ExecuteListeners can no longer be configured via Settings Instead they have to be injected into the Configuration This resolves many class loader issues that were encountered before It also helps istener implementations control their lifecycles themselves Data type API The data type API has been changed drastically in order to enable some ne
220. owIndex String row error row The query that caused the error Query query error query 5 10 2 Importing XML This is not yet supported 2009 2014 by Data Geekery GmbH All rights reserved Page 129 174 The jOOQ User Manual 5 11 CRUD with UpdatableRecords 5 11 CRUD with UpdatableRecords Your database application probably consists of 50 80 CRUD whereas only the remaining 20 50 of querying is actual querying Most often you will operate on records of tables without using any advanced relational concepts This is called CRUD for Create INSERT Read SELECT Update UPDATE Delete DELETE CRUD always uses the same patterns regardless of the nature of underlying tables This again leads to a lot of boilerplate code if you have to issue your statements yourself Like Hibernate JPA and other ORMs jOOQ facilitates CRUD using a specific API involving org joog UpdatableRecord types Primary keys and updatability In normalised databases every table has a primary key by which a tuple record within that table can be uniquely identified In simple cases this is a possibly auto generated number called ID But in many cases primary keys include several non numeric columns An important feature of such keys is the fact that in most databases they are enforced using an index that allows for very fast random access to the table A typical wa
221. owever there exist some corner cases with complex nested SELECT statements where this will cause syntax errors or parser bugs To stay on the safe side jOOQ will always render dual in those dialects 4 5 Column expressions Column expressions can be used in various SQL clauses in order to refer to one or several columns This chapter explains how to form various types of column expressions with jOOQ A particular type of column expression is given in the section about tuples or row value expressions where an expression may have a degree of more than one Using column expressions in JOOQ jOOQ allows you to freely create arbitrary column expressions using a fluent expression construction API Many expressions can be formed as functions from DSL methods other expressions can be formed based on a pre existing column expression For example A regular table column expression Field lt String gt fieldl BOOK TITLE A function created from the DSL using prefix notation Field lt String gt field2 trim BOOK TITLE The same function created from a pre existing Field using postfix notation Field lt String gt field3 BOOK TITLE trim More complex function with advanced DSL syntax Field lt String gt field4 listAgg BOOK TITLE withinGroupOrderBy BOOK ID asc Over partitionBy AUTHOR ID In general it is up to you whether you want to use the prefix notation or the postfix no
222. pes gt true lt unsignedTypes gt lt The schema that is used in generated source code This will be the production schema Use this to override your local development schema name for source code generation If not specified this will be the same as the input schema gt lt outputSchema gt your database schema owner name lt outputSchema gt lt A configuration element to configure several input and or output schemata for jooq meta in case you re using jooq meta in a multi schema environment This cannot be combined with the above inputSchema outputSchema gt lt schemata gt lt schema gt lt inputSchema gt lt inputSchema gt lt outputSchema gt lt outputSchema gt lt schema gt lt schema gt lt schema gt lt schemata gt lt A configuration element to configure custom data types gt lt customTypes gt lt customTypes gt lt A configuration element to configure type overrides for generated artefacts e g in combination with customTypes gt lt forcedTypes gt lt forcedTypes gt lt database gt Check out the some of the manual s advanced sections to find out more about the advanced configuration parameters Schema mapping Custom types joog codegen configuration Also you can add some optional advanced configuration parameters for the generator O 2009 2014 by Data Geekery GmbH All rights reserved Page 150 174 The jOOQ User M
223. predicate 4 6 15 OVERLAPS predicate When comparing dates the SQL standard allows for using a special OVERLAPS predicate which checks whether two date ranges overlap each other The following can be said This yields true DATE 2010 01 01 DATE 2010 01 03 OVERLAPS DATE 2010 01 02 DATE 2010 01 04 INTERVAL data types are also supported This is equivalent to the above DATE 2010 01 01 CAST 2 00 00 00 AS INTERVAL DAY TO SECOND OVERLAPS DATE 2010 01 02 CAST 2 00 00 00 AS INTERVAL DAY TO SECOND The OVERLAPS predicate in JOOQ jOOQ supports the OVERLAPS predicate on row value expressions of degree 2 The following methods are contained in org joog Row2 Condition overlaps T1 tl T2 t2 Condition overlaps Field lt T1 gt tl Field lt T2 gt t2 Condition overlaps Row2 lt T1 T2 gt row This allows for expressing the above predicates as such The date range tuples version row Date valueOf 2010 01 01 Date value0f 2010 01 03 overlaps Date valueOf 2010 01 02 Date valueOf 2010 01 04 The INTERVAL tuples version row Date valueOf 2010 01 01 new DayToSecond 2 overlaps Date valueOf 2010 01 02 new DayToSecond 2 jOOQ s extensions to the standard Unlike the standard or any database implementing the standard OOQ also supports the OVERLAPS predicate for comparing arbitrary row vlaue expressions of degree 2 For instance 1 3 OVERLAPS
224. probably the most used case From a conditional expression using convenience methods attached to boolean operators From a SELECT statement using convenience methods attached to the where clause and from other clauses An example of an EXISTS predicate can be seen here EXISTS SELECT 1 FROM BOOK exists create selectOne from BOOK WHERE AUTHOR_ID 3 where BOOK AUTHOR_ID equal 3 NOT EXISTS SELECT 1 FROM BOOK notExists create selectOne from BOOK WHERE AUTHOR_ID 3 where BOOK AUTHOR_ID equal 3 Note that in SQL the projection of a subselect in an EXISTS predicate is irrelevant To help you write queries like the above you can use jOOQ s selectZero or selectOne DSL methods Performance of IN vs EXISTS In theory the two types of predicates can perform equally well If your database system ships with a sophisticated cost based optimiser it will be able to transform one predicate into the other if you have all necessary constraints set e g referential constraints not null constraints However in reality performance between the two might differ substantially An interesting blog post investigating this topic on the MySQL database can be seen here http blog joog org 201 2 07 27 not in vs not exists vs left join is null mysql 2009 2014 by Data Geekery GmbH All rights reserved Page 90 174 The jOOQ User Manual 4 6 15 OVERLAPS
225. r database will generate a POJO implementation that looks like this 2009 2014 by Data Geekery GmbH All rights reserved Page 154 174 The jOOQ User Manual JPA annotations can be generated optionally javax persistence Entity javax persistence Table name BOOK schema TEST public class Book implements java io Serializable An interface common to records and pojos can be generated optionally IBook JSR 303 annotations can be generated optionally NotNull private Integer id NotNull private Integer authorld NotNull Size max 400 private String title Every column generates a getter and a setter Id Column name ID Override public Integer getId return this id Override public void setId Integer id this id id Mes all unique true nullable false precision 7 Flags influencing generated POJOs These flags from the code generation con figuration influence generated POJOs dateAsTimestamp This influences al relevant getters and setters unsignedTypes This influences all re evant getters and setters interfaces If interfaces are generated POJOs will implement them 6 7 Generated Interfaces immutablePojos Immutable POJOs have final members and no setters All members must be passed to the constructor daos POJOs are a pre requisite for DAOs If DAOs are generated POJOs a
226. r expression Use the pipe to separate several expressions includes gt lt excludes gt lt excludes gt Excludes match before lt The schema that is used locally as a source for meta information This could be your development schema or the production schema etc This cannot be combined with the schemata element If left empty jOOQ will generate all available schemata See the manual s next section to learn how to generate several schemata gt lt inputSchema gt your database schema owner name lt inputSchema gt lt database gt lt generate gt lt Generation flags See advanced configuration properties gt lt generate gt lt target gt lt The destination package of your generated classes within the destination directory gt lt packageName gt org joog your packagename lt packageName gt lt The destination directory of your generated classes gt lt directory gt path to your dir lt directory gt lt target gt lt generator gt lt configuration gt There are also lots of advanced configuration parameters which will be treated in the manual s section about advanced code generation features Note you can find the official XSD file for a formal specification at http www jooq org xsd jooq codegen 3 0 0 xsd Run jJOOQ code generation Code generation works by calling this class with the above property file as argument org jooqg util
227. r mysql connector java 5 1 18 bin jar org jooq util GenerationTool library xml There are two things to note o The prefix slash before the library xml Even though it s in our working directory we need to prepend a slash as the configuration file is loaded from the classpath o The trailing period in the classpath We need this because we want the current directory on the classpath in order to find the above library xml file at the root of your classpath Replace the filenames with your actual filenames In this example OOQ 3 0 1 is being used If everything has worked you should see this in your console output 2009 2014 by Data Geekery GmbH All rights reserved Page 21 174 The jOOQ User Manual Nov 1 INFO Nov 1 ELA EAS NS PM halo ajolole le Initialising properties A TEOS OTCEM org INFO Database parameters Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Generating tables Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 INFO Nov 1 AA Oe Srg TOON impl JooqLogger guestbook xml impl JooqLogger impl JooqLogger 3 4 1 4 Step 4 Connect to your database 20TI 7225707 PM dialect AQUA TAASI ON al schema PANE SS 07 PM target dir Alay AO EA t
228. r of milliseconds 2009 2014 by Data Geekery GmbH All rights reserved Page 166 174 The jOOQ User Manual 8 2 4 XML data types Interval arithmetic In addition to the arithmetic expressions documented previously interval arithmetic is also supported by jOOQ Essentially the following operations are supported DATETIME DATETIME gt INTERVA s DATETIME or INTERVAL gt DATETIM a NTERVAL DATETIME gt DATETI NTERVAL INTERVAL gt INTERVAL NTERVAL or NUMERIC gt INTERVAL UMERIC INTERVAL gt INTERVA 8 2 4 XML data types XML data types are currently not supported 8 2 5 Geospacial data types Geospacial data types Geospacial data types are currently not supported 8 2 6 CURSOR data types Some databases support cursors returned from stored procedures They are mapped to the following jOOQ data type Field lt Result lt Record gt gt cursor In fact such a cursor will be fetched immediately by OOQ and wrapped in an org joog Result object 8 2 7 ARRAY and TABLE data types The SQL standard specifies ARRAY data types that can be mapped to Java arrays as such Fielid lt Integer gt intArray The above array type is supported by these SQL dialects 2009 2014 by Data Geekery GmbH All rights reserved Page 167
229. r your SQL statements You can decide yourself if you want to execute a static java sal Statement without bind values or a java sal PreparedStatement with or without bind values But you have to decide early which way to go And you ll have to prevent SQL injection and syntax errors manually when inlining your bind variables With OOQ this is easier As a matter of fact it is plain simple With jOOQ you can just set a flag in your Configuration s Settings and all queries produced by that configuration will be executed as static statements with all bind values inlined An example is given here This DSLContext executes PreparedStatements DSLContext prepare DSL using connection SQLDialect ORACLE This DSLContext executes static Statements DSLContext inlined DSL using connection SQLDialect ORACLE new Settings withStatementType StatementType STATIC_STATEMENT These statements are rendered by the two factories SELECT FROM DUAL WHERE prepare select val 1 where walt equal y fetchi SELECT 1 FROM DUAL WHERE 1 1 inlined select val 1 where val 1 equal 1 fetch O 2009 2014 by Data Geekery GmbH All rights reserved Page 119 174 The jOOQ User Manual 5 5 Reusing a Query s PreparedStatement Reasons for choosing one or the other Not all databases are equal Some databases show improved performance if you use java sql PreparedStatement as the database will then be ab
230. raint violation exception handleAsync record throwable gt record changed true return record insert This will supply an int value indicating the number of deleted rows handleAsync rows throwable gt DSL using configuration delete AUTHOR where AUTHOR ID eq 3 execute GOONS The above example will execute four actions one after the other but asynchronously in the JDK s default or common java util concurrent ForkjoinPool For more information please refer to the java util concurrent completable Future Javadoc and official documentation Using deprecated API Some queries take very long to execute yet they are not crucial for the continuation of the main program For instance you could be generating a complicated report in a Swing application and while this report is being calculated in your database you want to display a background progress bar allowing the user to pursue some other work This can be achived simply with jOOQ by creating a org joog FutureResult a type that extends java util concurrent Future An example is given here Spawn off this query in a separate process FutureResult lt BookRecord gt future create selectFrom BOOK where complex predicates fetchLater This example actively waits for the result to be done while future isDone progressBar increment 1 Thread sleep 50
231. ration configuration new DefaultConfiguration set connection set SQLDialect ORACLE Initialise the DAO with the Configuration BookDao bookDao new BookDao configuration Start using the DAO Book book bookDao findById 5 Modify and update the POJO Book settitle ise book setPublishedIn 1948 bookDao update book Delete it again bookDao delete book More complex data structures jOOQ currently doesn t support more complex data structures the way Hibernate JPA attempt to map relational data onto POJOs While future developments in this direction are not excluded jOOQ claims that generic mapping strategies lead to an enormous additional complexity that only serves very few use cases You are likely to find a solution using any of jOOQ s various fetching modes with only little boiler plate code on the client side 5 3 7 Lazy fetching Unlike JDBC s java sql ResultSet OOQ s org joog Result does not represent an open database cursor with various fetch modes and scroll modes that needs to be closed after usage jOOQ s results are simple in memory Java java util List objects containing all of the result values If your result sets are large or if you have a lot of network latency you may wish to fetch records one by one or in small chunks jOOQ supports a org joog Cursor type for that purpose In order to obtain such a reference use the ResultQuery fetchLazy method An example is given
232. rd2 for instance public interface Record2 lt T1 T2 gt extends Record Access fields and values as row value expressions Row2 lt T1 T2 gt fieldsRow Row2 lt T1 T2 gt valuesRow Access fields by index FietasTIi ilu Pieiid lt i2 gt ed iy Access values by index T1 valuel T2 value2 Higher degree records jOOQ chose to explicitly support degrees up to 22 to match Scala s typesafe tuple function and product support Unlike Scala however jOOQ also supports higher degrees without the additional typesafety O 2009 2014 by Data Geekery GmbH All rights reserved Page 108 174 The jOOQ User Manual 5 3 3 Arrays Maps and Lists 5 3 3 Arrays Maps and Lists By default OOQ returns an org joog Result object which is essentially a java util List of org joog Record Often you will find yourself wanting to transform this result object into a type that corresponds more to your specific needs Or you just want to list all values of one specific column Here are some examples to illustrate those use cases Fetching only book titles the two calls are equivalent List lt String gt titlesl create select from BOOK fetch getValues BOOK TITLE List lt String gt titles2 create select from BOOK fetch BOOK TITLE String titles3 create select from BOOK fetchArray BOOK TITLE Fetching only book IDs converted to Long List lt Long gt i
233. re generated as well jpaAnnotations JPA annotations are used on generated records validationAnnotations JSR 303 validation annotations are used on generated records Flags controlling POJO generation POJO generation can be activated using the pojos flag 6 7 Generated Interfaces an interface that looks like this Every table in your database will generate public interface IBook extends java io Serializable Every column generates a getter and a setter public void setid Integer value public Integer getId WH Ee O 2009 2014 by Data Geekery GmbH All rights reserved Page 155 174 The jOOQ User Manual 6 8 Generated DAOs Flags influencing generated interfaces These flags from the code generation configuration influence generated interfaces dateAsTimestamp This influences all relevant getters and setters unsignedTypes This influences all relevant getters and setters Flags controlling POJO generation POJO generation can be activated using the interfaces flag 6 8 Generated DAOs Generated DAOs Every table in your database will generate a org joog DAO implementation that looks like this public class BookDao extends DAOImpl1 lt BookRecord Book Integer gt Generated constructors public BookDao super BOOK Book class public BookDao Configuration configuration super BOOK Book class configuration Every column generates at
234. reate select from AUTHOR Join BOOK on BOOK AUTHOR_ID equal AUTHOR ID Seetoniry Unlike other simpler frameworks that use fluent APIs or method chaining OOQ s BNF based interface hierarchy will not allow bad query syntax The following will not compile for instance DSLContext create DSL using connection dialect Result lt gt result create select Join BOOK on BOOK AUTHOR_ID equal AUTHOR ID join is not possible here from AUTHOR teta Result lt gt result create select from AUTHOR Join BOOK fetch Kk SARGEN on is missing here Result lt gt result as Used alate Ube SP Ree See ste art over is missing here from AUTHOR ztetch Result lt gt result create select from AUTHOR where AUTHOR ID in select BOOK E from BOOK A E eS NS AUTHOR ID is of type Field lt Integer gt but subselect returns Recordl lt String gt Scetcohi ey Result lt gt result create select from AUTHOR e e TOR veers ae sO AUTHOR_ID BOOK ID from BOOK Jf KARA AAAAAAAAAAAAAAAAAAAAAAAAAA AUTHOR ID is of degree 1 but subselect returns Record2 lt Integer Integer gt reten O 2009 2014 by Data Geekery GmbH All rights reserved Page 38 174 The jOOQ User Manual 4 3 2 The SELECT statement History of SQL building and incremental query building a k a the model API Historically OOQ started out as an object orie
235. retrieving POJOs by primary key or by some other field List lt P gt findAll throws DataAccessException P findById T id throws DataAccessException lt Z gt List lt P gt fetch Field lt Z gt field Z values throws DataAccessException lt Z gt P fetchOne Field lt Z gt field Z value throws DataAccessException These methods provide DAO meta information Table lt R gt getTable Class lt P gt getType Besides these base methods generated DAO classes implement various useful fetch methods An incomplete example is given here for the BOOK table 2009 2014 by Data Geekery GmbH All rights reserved Page 136 174 The jOOQ User Manual 5 13 Exception handling An example generated BookDao class public class BookDao extends DAOImpl lt BookRecord Book Integer gt Columns with primary unique keys produce fetchOne methods public Book fetchOneByld Integer value Other columns produce fetch methods returning several records public List lt Book gt fetchByAuthorld Integer values public List lt Book gt fetchByTitle String values Note that you can further subtype those pre generated DAO classes to add more useful DAO methods to them Using such a DAO is simple Initialise an Configuration Configuration configuration new DefaultConfiguration set connection set SQLDialect ORACLE Initialise the DAO with the Configuration BookDao bookDao
236. rg apache 1log4j PatternLayout gt lt param name ConversionPattern value m n gt lt layout gt lt appender gt lt root gt lt priority value debug gt lt appender ref ref stdout gt lt root gt lt log4j configuration gt With the above configuration let s fetch some data with jOOQ Using H2 this time create select BOOK ID BOOK TITLE from BOOK orderBy BOOK ID 1limit 1 2 fetch The above query may result in the following log output Executing query select BOOK DY BOOKS ih from BOOK order by BOOK ID asc Limit offset T gt with bind values gt select BOOK ID BOOR TITLE from BOOK order by BOOK ID asc Limit 2 offset 1 Query executed Total 1 439ms Fetched result SS ID TITLE 4 2 Animal Farm 2 3 0 Alquimistal 2 Finishing Total 4 814ms 3 375ms Essentially OOQ will log The SQL statement as rendered to the prepared statement The SQL statement with inlined bind values for improved debugging The query execution time The first 5 records of the result This is formatted using jOOQ s text export The total execution fetching time If you wish to use your own logger e g avoiding printing out sensitive data you can deactivate jOOQ s logger using your custom settings and implement your own execute listener logger 5 17 Perf
237. rn more about the many ways to create org joog Condition objects in OOQ See the section about table expressions to learn about the various ways of referencing org jooq Table objects in jOOQ JOIN ON KEY convenience provided by jOOQ Surprisingly the SQL standard does not allow to formally JOIN on well known foreign key relationship information Naturally when you join BOOK to AUTHOR you will want to do that based on the BOOK AUTHOR_ID foreign key to AUTHOR ID primary key relation Not being able to do this in SQL leads to a lot of repetitive code re writing the same JOIN predicate again and again especially when your foreign keys contain more than one column With jOOQ when you use code generation you can use foreign key constraint information in JOIN expressions as such SELECT FROM AUTHOR JOIN BOOK ON BOOK AUTHOR_ID AUTHOR ID create select from AUTHOR join BOOK onKey In case of ambiguity you can also supply field references for your foreign keys or the generated foreign key reference to the onKey method Note that formal support for the Sybase JOIN ON K EY syntax is on the roadmap The JOIN USING syntax Most often you will provide jOOQ with JOIN conditions in the JOIN ON clause SQL supports a different means of specifying how two tables are to be joined This is the JOIN USING clause Instead of a condition you supply a set of fields whose names are common to b
238. rs in the various databases jOOQ settled for the SQL 2008 standard REGEX_LIKE operator Being an operator and not a function you should use the corresponding method on org joog Field create selectFrom BOOK where TITLE likeRegex SQL Note that the SQL standard specifies that patterns should follow the XQuery standards In the real world the POSIX regular expression standard is the most used one some use Java regular expressions and only a few ones use Perl regular expressions OOQ does not make any assumptions about regular expression syntax For cross database compatibility please read the relevant database manuals carefully to learn about the appropriate syntax Please refer to the DSL Javadoc for more details 2009 2014 by Data Geekery GmbH All rights reserved Page 71 174 The jOOQ User Manual 4 5 10 Date and time functions 4 5 10 Date and time functions This is a list of date and time functions supported by jOOQ s DSL CURRENT_DATE Get current date as a DATE object CURRENT_TIME Get current time as a TIME object CURRENT_TIMESTAMP Get current date as a TIMESTAMP object DATE_ADD Add a number of days or an interval to a date DATE_DIFF Get the difference in days between two dates TIMESTAMP_ADD Add a number of days or an interval to a timestamp TIMESTAMP_DIFF Get the difference as an INTERVAL DAY TO SECOND between two dates
239. rtefact cccecsesssessessessssssesseesecssssssssssessessscssesscsnesssssssesseesecsussssveesessessessvsseessessesuessesseseeuesssaveeneeseeseesseaeenseeses 52 64 Generated tables ia il tod 52 6 5 Generated PECOPS coesione iia iiiad iienaa iaaa iaiia a iaaa E ii a a 53 66 Generated ODE s 54 SR O NA 55 6 8 Generated DAOS iio abocado 56 6 9 Generated Se qUe nais daoa enap aaan ia A a a A aE a A A A a A a ai aaa ete 56 6 10 Generated proced re Snai a an NA 57 6 11 Generated UDTs 57 6 12 Custom data types and type conversion 58 6 13 Mapping generated schemata and tal cc ii 59 6 14 Code generation for large SCNEMAS cccsssssessesessssssssessecsessssssssssssessesussneesessecsussssseesessecsssssasesnscsessesnseseenssesesisenseneeneeaeeneaseaneeneeses 59 6 15 Code generation aa VEO cise eau A a a 60 Te O NN 62 A O ON 62 FALIOO S CON lo a e a A a naaa 64 oo e e o 65 SU RMS a 65 A NN 65 SAB UO BS RAEO E E A EO E sos saad hte ASA Is a Ae ls A Nets Hts oe de a ee 66 82 2 WIASISMED INLE a a MN oneal tale e 66 8 2 3 INTERVAL data types 66 824 KML sata PES daa a 67 ERE TA AN 67 8 26 CURSOR data type S avs cidessassescassashesedsssshscosstsatssesassuhseesasehiesssassbicracauahicsovesasiscasenabasessscasassesavsussssczseuhsaucasedusascassbistecauabisnscnvabasgeanrabadeauess 67 322 ARRAY and TABEE ata ty DOS tds 67 8 3 OOO S BNF PSECU OMOTATIO M iia 68 SA Gali tyYASSUPAIAC lalalala llenaba a Roots tiahantharenden 68 O O O do eo
240. ructor usually works the same way as the VALUES clause known from the INSERT statement or 2009 2014 by Data Geekery GmbH All rights reserved Page 62 174 The jOOQ User Manual from the MERGE statement With jOOQ you can also use the VALUES table constructor to create tables that can be used in a SELECT statement s FROM clause 4 4 5 Nested SELECTS SELECT a b create select FROM VALUES 1 a from values row 1 a 2 TB la B rowa Day ye Note that it is usually quite useful to provide column aliases derived column lists along with the table alias for the VALUES constructor The above statement is emulated by jOOQ for those databases that do not support the VALUES constructor natively actual emulations may vary If derived column SELECT a b FROM SELECT 1 1a SELECT 2 Ie I eta b expressions are supported FROM FROM DUAL UNION ALL DUAL If derived column SELECT a b FROM expressions are not supported An empty dummy record is added to provide column names for the emulated derived column expression SELECT NULL a NULL b FROM DUAL WHERE 1 0 UNION ALL Then the actual VALUES constructor is emulated SELECT 1 san FROM DUAL UNION ALL SELECT 2 Bb FROM DUAL I Ae 4 4 5 Nested SELECTs A SEI ECT statement can appear almost anywhere a table expression can Such a nested SELECT
241. s Map lt String Object gt fetchOneMap Transform your Result object into groups lt K gt Map lt K Result lt R gt gt fetchGroups Field lt K gt key lt K V gt Map lt K List lt V gt gt fetchGroups Field lt K gt key Field lt V gt value lt K E gt Map lt K List lt E gt gt fetchGroups Field lt K gt key Class lt E gt value Map lt Record Result lt R gt gt fetchGroups Field lt gt key lt E gt Map lt Record List lt E gt gt fetchGroups Field lt gt key Class lt E gt value Transform your Records into custom POJOs lt E gt List lt E gt fetchInto Class lt extends E gt type Transform your records into another table type lt Z extends Record gt Result lt Z gt fetchInto Table lt Z gt table Note that apart from the fetchLazy methods all fetch methods will immediately close underlying JDBC result sets 5 3 1 Record vs TableRecord jOOQ understands that SQL is much more expressive than Java when it comes to the declarative typing of table expressions As a declarative language SQL allows for creating ad hoc row value expressions records with indexed columns or tuples and records records with named columns In Java this is 2009 2014 by Data Geekery GmbH All rights reserved Page 107 174 The jOOQ User Manual 5 3 2 Record1 to Record22 not possible to the same extent Yet still sometimes you wish to use strongly typed records when you know
242. s any RDBMS support the concept of routines usually calling them procedures and or functions These concepts have been around in programming languages for a while also outside of databases Famous languages distinguishing procedures from functions are Ada BASIC Pascal etc The general distinction between stored procedures and stored functions can be summarised like this Procedures Are called using JDBC CallableStatement Have no return value Usually support OUT parameters Functions Can be used in SQL statements Have a return value Usually don t support OUT parameters Exceptions to these rules DB2 H2 and HSQLDB don t allow for JDBC escape syntax when calling functions Functions must be used in a SELECT statement H2 only knows functions without OUT parameters Oracle functions may have OUT parameters Oracle knows functions that must not be used in SQL statements for transactional reasons Postgres only knows functions with all features combined OUT parameters can also be interpreted as return values which is quite elegant surprising depending on your taste The Sybase jconn3 JDBC driver doesn t handle null values correctly when using the JDBC escape syntax on functions In general it can be said that the field of routines procedures functions is far from being standardised in modern RDBMS even if the SQL 2008 standard specifies things quite well
243. s for later execution An example is given here The DSLContext is configured with a Connection and a SOLDialect DSLContext create DSL using connection dialect This select statement contains an internal reference to the DSLContext s Configuration Select lt gt select create selectOne Using the internally referenced Configuration the select statement can now be executed Result lt gt result select fetch Note that you do not need to keep a reference to a DSLContext You may as well inline your local variable and fluently execute a SQL statement as such Execute a statement from a single execution chain Result lt gt result DSL using connection dialect select from BOOK where BOOK TITLE like Animal fetch 4 2 1 SQL Dialect While jOOQ tries to represent the SQL standard as much as possible many features are vendor specific to a given database and to its SQL dialect OOQ models this using the org jooq SQLDialect enum type The SQL dialect is one of the main attributes of a Configuration Queries created from DSLContexts will assume dialect specific behaviour when rendering SQL and binding bind values Some parts of the jOOQ API are officially supported only by a given subset of the supported SQL dialects For instance the Oracle CONNECT BY clause which is supported by the Oracle and CUBRID databases is annotated with a org jooq Support an
244. s a lot of power and expressiveness to just simply constructing SQL using the query DSL and custom strings and literals as you can be sure that all database artefacts actually exist in the database and that their type is correct An example is given here Fetch a SQL string from a jO0Q Query in order to manually execute it with another tool String sql create select BOOK TITLE AUTHOR FIRST_NAME AUTHOR LAST_NAME from BOOK join AUTHOR on BOOK AUTHOR_ID equal AUTHOR ID where BOOK PUBLISHED_IN equal 1948 getSQL The SQL string that you can generate as such can then be executed using JDBC directly using Spring s JdbcTemplate using Apache DbUtils and many other tools If you wish to use jOOQ only as a SQL builder with code generation the following sections of the manual will be of interest to you SQL building This section contains a lot of information about creating SQL statements using the jOOQ API Code generation This section contains the necessary information to run jOOQ s code generator against your developer database 3 3 3 JOOQ as a SQL executor Instead of any tool mentioned in the previous chapters you can also use jOOQ directly to execute your jOOQ generated SQL statements This will add a lot of convenience on top of the previously discussed API for typesafe SQL construction when you can re use the information from generated classes to fetch records and custom data typ
245. s that a query expression can be ordered and that query expressions can contain UNION INTERSECT and EXCEPT clauses whose subqueries cannot be ordered While this is defined as such in the SQL standard many databases allowing for the non standard LIMIT clause in one way or another do not adhere to this part of the SQL standard Hence jOOQ allows for ordering all SELECT statements regardless whether they are constructed as a part of a UNION or not Corner cases are handled internally by jOOQ by introducing synthetic subselects to adhere to the correct syntax where this is needed Oracle s ORDER SIBLINGS BY clause jOOQ also supports Oracle s SIBLINGS keyword to be used with ORDER BY clauses for hierarchical queries using CONNECT BY A3 2 9 The LIMIT OFFSET clause While being extremely useful for every application that does paging or just to limit result sets to reasonable sizes this clause is not yet part of any SQL standard up until SQL 2008 Hence there exist a variety of possible implementations in various SQL dialects concerning this limit clause OOQ chose to implement the LIMIT OFFSET clause as understood and supported by MySQL H2 HSQLDB Postgres and SQLite Here is an example of how to apply limits with jOOQ create select from BOOK limit 1 offset 2 This will limit the result to 1 books starting with the 2nd book starting at offse
246. s will lazily create a new PreparedStatement Result lt Record gt result2 query fetch This will reuse the previous PreparedStatement but now you must not forget to close the query finally query close The above example shows how a query can be executed twice against the same underlying PreparedStatement Unlike in other execution scenarios you must not forget to close this query now 5 6 Using JDBC batch operations With JDBC you can easily execute several statements at once using the addBatch method Essentially there are two modes in JDBC Execute several queries without bind values Execute one query several times with bind values Using JDBC In code this looks like the following snippet 1 several queries _ try Statement stmt connection createStatement stmt addBatch INSERT INTO author id first_name last_name VALUES stmt addBatch INSERT INTO author id first_name last_name VALUES stmt addBatch INSERT INTO author id first_name last_name VALUES id D cn Samna EA a AN SA OA SOM stmt addBatch INSERT INTO author i first_name last_name VALUES 4 John Vlissides int result stmt executeBatch 2 a single query _ try PreparedStatement stmt connection prepareStatement INSERT INTO author id first_name last_name VALUES 2 4 SEM Sette lees Ey Entes Ss pel
247. sources are defined and joined The WHERE clause Then data is filtered as early as possible The CONNECT BY clause Then data is traversed iteratively or recursively to produce new tuples The GROUP BY clause Then data is reduced to groups possibly producing new tuples if grouping functions like ROLLUPO CUBE GROUPING SETS are used The HAVING clause Then data is filtered again The SELECT clause Only now the projection is evaluated In case of a SE statement data is further reduced to remove duplicates The UNION clause Optionally the above is repeated for several UNION connected subqueries Unless this is a UNION ALL clause data is further reduced to remove duplicates The ORDER BY clause Now all remaining tuples are ordered The LIMIT clause Then a paging view is created for the ordered tuples The FOR UPDATE clause Finally pessimistic locking is applied ECT DISTINCT The SQL Server documentation also explains this with slightly different clauses FROM WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP As can be seen databases have to logically reorder a SQL statement in order to determine the best execution plan Alternative syntaxes LINQ SLICK Some higher level abstractions such as
248. ss are unattached i e queries that are constructed through DSL cannot be executed ediately This is useful for subqueries The DSL class corresponds to the static part of the jOOQ 2 x Factory type o The DSLContext This type holds a reference to a Configuration and can construct executable attached QueryParts The DSLContext type corresponds to the non static part of the jOOQ 2 x Factory FactoryOperations type TOD AW O The FactoryOperations interface has been renamed to DSLContext An example jOOQ 2 6 check if there are any books Factory create new Factory connection dialect create selectOne whereExists create selectFrom BOOK Reuse the factory to create subselects fetch Execute the attached query j00Q 3 0 DSLContext create DSL using connection dialect create selectOne whereExists selectFrom BOOK Create a static subselect from the DSL fetch Execute the attached query Quantified comparison predicates Field equalAny and similar methods have been removed in favour of Field equal any This greatly simplified the Field API An example j00Q 2 6 Condition condition BOOK ID equalAny create select BOOK ID from BOOK j00Q 3 0 adds some typesafety to comparison predicates involving quantified selects QuantifiedSelect lt Recordl lt Integer gt gt subselect any select BOOK ID from BOOR Condition condition
249. ss readable DECODE function with a variable number of arguments This function roughly does the same as the second case expression syntax OOQ supports the DECODE function and emulates it using CASE expressions in all dialects other than Oracle Oael DECODE FIRST_NAME Paulo brazilian George english unknown Other SQL dialects Use the Oracle style DECODE function with j000 CASE AUTHOR FIRST_NAME WHEN Paulo THEN brazilian Note that you will not be able to rely on type safety WHEN George THEN english DSL decode AUTHOR FIRST_NAME ELSE unknown Paulo brazilian END George english unknown CASE clauses in an ORDER BY clause ECT s ORDER BY clause See the Sort indirection is often implemented with a CASE clause of a SE manual s section about the ORDER BY clause for more details 4 5 18 Sequences and serials Sequences implement the org joog Sequence interface providing essentially this functionality Get a field for the CURRVAL sequence property Field lt I gt currval Get a field for the NEXTVAL sequence property Field lt T gt nextval So if you have a sequence like this in Oracle CREATE SEQUENCE s_author_id O 2009 2014 by Data Geekery GmbH All rights reserved Page 80 174 The jOOQ User Manual 4 5 19 Tuples or row value expressions You can then use your generated sequen
250. ssion is part of the standard SQL syntax While some RDBMS also offer an IF expression or a DECODE function you can always rely on the two types of CASE syntax 2009 2014 by Data Geekery GmbH All rights reserved Page 79 174 The jOOQ User Manual 4 5 18 Sequences and serials CASE WHEN AUTHOR FIRST_NAME Paulo THEN brazilian DSL decode WHEN AUTHOR FIRST_NAME George THEN english when AUTHOR FIRST_NAME equal Paulo brazilian ELSE unknown when AUTHOR FIRST_NAME equal George english END otherwise unknown GRE OR CASE AUTHOR FIRST_NAME WHEN Paulo THEN brazilian DSL decode value AUTHOR FIRST_NAME WHEN George THEN english when Paulo brazilian ELSE unknown when George english END Otherwise unknown InjOOQ both syntaxes are supported The second one is emulated in Derby which only knows the first one Unfortunately both case and else are reserved words in Java OOQ chose to use decode from the Oracle DECODE function and otherwise which means the same as else A CASE expression can be used anywhere where you can place a column expression or Field For instance you can SELECT the above expression if you re selecting from AUTHOR SELECT AUTHOR FIRST_NAME CASE EXPR AS nationality FROM AUTHOR The Oracle DECODE function Oracle knows a more succinct but maybe le
251. st mysql generatedclasses tables b EB org jooq test mysql generatedclasses tables records Xx library xml R log4jxml gt mA JRE System Library JavaSE 1 7 gt BBA Referenced Libraries Run generation with ant When running code generation with ant s lt java gt task you may have to set fork true lt Run the code generation task gt lt target name generate test classes gt lt java fork true classname org joog util GenerationTool gt adel lt java gt lt target gt 2009 2014 by Data Geekery GmbH All rights reserved Page 146 174 The jOOQ User Manual Integrate generation with Maven 6 2 Advanced generator configuration Using the official OOQ codegen maven plugin you can integrate source code generation in your Maven build process lt plugin gt lt Specify the maven code generator plugin gt lt groupId gt org jooq lt groupid gt lt artifactId gt jooq codegen maven lt artifactId gt lt version gt 3 0 1 lt version gt lt The plugin should hook into the generate goal gt lt executions gt lt execution gt lt goals gt lt goal gt generate lt goal gt lt goals gt lt execution gt lt executions gt lt Manage the plugin s dependency In this example we ll use lt dependencies gt lt dependency gt lt groupId gt postgresql lt groupId gt lt artifactId gt postgresql lt artifactId gt lt version gt 8 4 702 jdbc4 lt version gt lt d
252. t bindings lt T gt Field lt T gt field String sql DataType lt T gt type QueryPart parts A field with a known name properly escaped Field lt Object gt fieldByName String fieldName lt T gt Field lt T gt fieldByName Class lt T gt type String fieldName lt I gt Field lt T gt fieldByName DataType lt T gt type String fieldName A function lt T gt Field lt T gt function String name Class lt T gt type Field lt gt arguments lt T gt Field lt T gt function String name DataType lt T gt type Field lt gt arguments A table Table lt gt table String sql Table lt gt table String sql Object bindings Table lt gt table String sql QueryPart parts A table with a known name properly escaped Table lt Record gt tableByName String fieldName A query without results update insert etc Query query String sql Query query String sql Object bindings Query query String sql QueryPart parts A query with results ResultQuery lt Record gt resultQuery String sql ResultQuery lt Record gt resultQuery String sql Object bindings ResultQuery lt Record gt resultQuery String sql QueryPart parts A query with results This is the same as resultQuery fetch Result lt Record gt fetch String sql Result lt Record gt fetch String sql Object bindings Result lt Record gt fetch String sql QueryPart par
253. t The DefaultConnectionProvider exposes various transaction control methods such as commit rollback etc Interact with JDBC DataSources If you re in a J2EE or Spring context however you may wish to use a javax sql DataSource instead Connections obtained from such a DataSource will be closed after query execution by jOOQ The semantics of such a close operation should be the returning of the connection into a connection pool not the actual closing of the underlying connection Typically this makes sense in an environment using distributed JTA transactions An example of using DataSources with jOOQ can be seen in the tutorial section about using jOOQ with Spring Note in this case jOOQ will internally use a org jooq impl DataSourceConnectionProvider which you can reference directly if you prefer that Inject custom behaviour If your specific environment works differently from any of the above approaches you can inject your own custom implementation of a ConnectionProvider into jOOQ This is the API contract you have to fulfil public interface ConnectionProvider Provide jO0Q with a connection Connection acquire throws DataAccessException Get a connection back from j000 void release Connection connection throws DataAccessException Note that acquire should always return the same Connection until this connection is returned via release 4 2 3 Custom data In adva
254. t 0 limit is supported in all dialects offset in all but Sybase ASE which has no reasonable means to emulate it This is how jOOQ trivially emulates the above query in various SQL dialects with native OFFSET pagination support MySQL H2 HSOLDB Postgres and SQLite SELECT FROM BOOK LIMIT 1 OFFSET 2 CUBRID supports a MySQL variant of the LIMIT OFFSET clause SELECT FROM BOOK LIMIT 2 1 Derby SQL Server 2012 Oracle 12c syntax not yet supported by j00Q the SQL 2008 standard SELECT FROM BOOK OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY Ingres almost the SQL 2008 standard SELECT FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY lucia SELECT FROM BOOK ROWS 2 TO 3 Sybase SQL Anywhere SELECT TOP 1 ROWS START AT 3 FROM BOOK DB2 almost the SQL 2008 standard without OFFSET SELECT FROM BOOK FETCH FIRST 1 ROWS ONLY Sybase ASE SQL Server 2008 without OFFSET SELECT TOP 1 FROM BOOK Things get a little more tricky in those databases that have no native idiom for OFFSET pagination actual queries may vary 2009 2014 by Data Geekery GmbH All rights reserved Page 49 174 The jOOQ User Manual 4 3 2 10 The FOR UPDATE clause DB2 with OFFSET SOL Server 2008 with OFFSET SELECT EROM SELECT BOOK ROW_NUMBER OVER ORDER BY ID ASC AS RN FROM BOOK AS X WHERE RN gt 1 AND RN lt 3 DB2 with OFESET SOL Server 2008 with OFFESET
255. t set type from result queries JDBC java sql ResultSet jOOQ org jooq Result 2009 2014 by Data Geekery GmbH All rights reserved Page 104 174 The jOOQ User Manual 5 2 Query vs ResultQuery Differences to JDBC Some of the most important differences between JDBC and jOOQ are listed here Query vs ResultQuery JDBC does not formally distinguish between queries that can return results and queries that cannot The same API is used for both This greatly reduces the possibility for fetching convenience methods Exception handling While SQL uses the checked java sal SQLException OOQ wraps all exceptions in an unchecked org jooq exception DataAccessException orgjooq Result Unlike its JDBC counter part this type implements java util List and is fully oaded into Java memory freeing resources as early as possible Just like statements this means that users don t have to deal with a weird internal result set state org joog Cursor If you want more fine grained control over how many records are fetched into memory at once you can still do that using jOOQ s lazy fetching feature Statement type OOQ does not formally distinguish between static statements and prepared statements By default all statements are prepared statements in jOOQ internally Executing a statement as a Static statement can be done simply using a custom settings flag Closing Statements JDBC keeps open resources ev
256. ta If you are using jOOQ for scripting purposes or in a slim unlayered application server you might be interested in using jOOQ s importing functionality see also exporting functionality You can import data directly into a table from the formats described in the subsequent sections of this manual 5 10 1 Importing CSV The below CSV data represents two author records that may have been exported previously by jOOQ s exporting functionality and then modified in Microsoft Excel or any other spreadsheet tool ID AUTHORS TITLE lt Note the CSV header By default the first line is ignored 1 1 1984 2 1 Animal Farm With jOOQ you can load this data using various parameters from the loader API A simple load may look like this DSLContext create DSL using connection dialect Load data into the AUTHOR table from an input stream holding the CSV data watch out for encoding create loadInto AUTHOR loadCSV inputstream ETS TAS MED AUTHOR_ID TITLE execute Here are various other examples 2009 2014 by Data Geekery GmbH All rights reserved Page 128 174 The jOOQ User Manual Ignore the AUTHOR_ID column from the CSV file when inserting create loadInto AUTHOR LloadCSV inputst ream PTS MAS EE execute Specify behaviour for duplicate records create loadInto AUTHOR choose any of these methods onDuplicateKeyUpdate onDuplicateKeylIlgnore onDupli
257. tation 4 1 The query DSL type jOOQ exposes a lot of interfaces and hides most implementation facts from client code The reasons for this are Interface driven design This allows for modelling queries in a fluent API most efficiently Reduction of complexity for client code API guarantee You only depend on the exposed interfaces not concrete potentially dialect specific implementations The org jooq impl DSL class is the main class from where you will create all OOQ objects It serves as a static factory for table expressions column expressions or fields conditional expressions and many other QueryParts The static query DSL API With jOOQ 2 0 static factory methods have been introduced in order to make client code look more like SQL Ideally when working with jOOQ you will simply static import all methods from the DSL class import static org jooq impl DSL Note that when working with Eclipse you could also add the DSL to your favourites This will allow to access functions even more fluently concat trim FIRST_NAME trim LAST_NAME which is in fact the same as DSL concat DSL trim FIRST_NAME DSL trim LAST_NAME O 2009 2014 by Data Geekery GmbH All rights reserved Page 29 174 The jOOQ User Manual 4 1 1 DSL subclasses 4 1 1 DSL subclasses There are a couple of subclasses for the general query DSL Each SQL dialect has its own dialect spe
258. tation to create new column expressions based on existing ones The SQL way would be to use the prefix notation with functions created from the DSL The Java way or object oriented way would be to use the postfix notation with functions created from org joog Field objects Both ways ultimately create the same query part though 2009 2014 by Data Geekery GmbH All rights reserved Page 66 174 The jOOQ User Manual 4 5 1 Table columns 4 5 1 Table columns Table columns are the most simple implementations of a column expression They are mainly produced by jOOQ s code generator and can be dereferenced from the generated tables This manual is full of examples involving table columns Another example is given in this query SELECT BOOK ID BOOK TITLE create select BOOK ID BOOK TITLE FROM BOOK from BOOK WHERE BOOK TITLE LIKE SQL where BOOK TITLE like SQL ORDER BY BOOK TITLE orderBy BOOK TITLE Table columns implement a more specific interface called org jooq Table with its associated lt R extends Record gt record type Field which is parameterised See the manual s section about generated tables for more information about what is really generated by the code generator 4 5 2 Allased columns Just like tables columns can be renamed using aliases Here is an example SELECT FIRST_NAME LAST_NAME author COUNT books FROM AUTHOR J
259. ted classes from DEV to MY_BOOK_WORLD from LOG to MY_BOOK_WORLD_LOG but leave the MASTER schema alone Whenever you want to change your mapping configuration you will have to create a new Configuration 2009 2014 by Data Geekery GmbH All rights reserved Page 36 174 The jOOQ User Manual 4 3 SQL Statements Using a default schema If you wish not to render any schema name at all use the following Settings property for this Settings settings new Settings withRenderSchema false Add the settings to the Configuration DSLContext create DSL using connection SQLDialect ORACLE settings Run queries that omit rendering schema names create selectFrom AUTHOR fetch Mapping of tables Not only schemata can be mapped but also tables If you are not the owner of the database your application connects to you might need to install your schema with some sort of prefix to every table In our examples this might mean that you will have to map DEV AUTHOR to something MY_BOOK_WORLD MY_APP_ AUTHOR where MY_APP__is a prefix applied to all of your tables This can be achieved by creating the following mapping Settings settings new Settings withRenderMapping new RenderMapping withSchemata new MappedSchema withInput DEV withOutput MY_BOOK_WORLD withTables new MappedTable withInput AUTHOR withOutput MY_APP__AUTHOR Add the settings to the Configuration DSL
260. tement stmt connection prepareStatement SELECT 1 FROM DUAL Fetch a first ResultSet try ResultSet rsl stmt executeQuery Without closing the statement execute it again to fetch another ResultSet try ResultSet rs2 stmt executeQuery The above technique can be quite useful when you want to reuse expensive database resources This can be the case when your statement is executed very frequently and your database would take non negligible time to soft parse the prepared statement and generate a new statement cursor resource Keeping open PreparedStatements with jOOQ This is also modeled in jOOQ However the difference to JDBC is that closing a statement is the default action whereas keeping it open has to be configured explicitly This is better than JDBC because the default action should be the one that is used most often Keeping open statements is rarely done in average applications Here s an example of how to keep open PreparedStatements with jOOQ O 2009 2014 by Data Geekery GmbH All rights reserved Page 120 174 The jOOQ User Manual 5 6 Using JDBC batch operations Create a query which is configured to keep its underlying PreparedStatement open ResultQuery lt Record gt query create selectOne keepStatement true Execute the query twice against the same underlying PreparedStatement try Result lt Record gt resultl query fetch Thi
261. test database Both the test database and the queries are translated into every one of the 14 supported SQL dialects to ensure that regressions are unlikely to be introduced into the code base For libraries like OOQ integration tests are much more expressive than unit tests as there are so many subtle differences in SQL dialects Simple mocks just don t give as much feedback as an actual database instance jOOQ integration tests run the weirdest and most unrealistic queries As a side effect of these extensive integration test suites many corner case bugs for JDBC drivers and or open source databases have 2009 2014 by Data Geekery GmbH All rights reserved Page 168 174 The jOOQ User Manual 8 4 Quality Assurance been discovered feature requests submitted through jOOQ and reported mainly to CUBRID Derby H2 HSQLDB Code generation tests For every one of the 14 supported integration test databases source code is generated and the tiniest differences in generated source code can be discovered In case of compilation errors in generated source code new test tables views columns are added to avoid regressions in this field API Usability tests and proofs of concept jOOQ is used in jOOQ meta as a proof of concept This includes complex queries such as the following Postgres query Routines rl ROUTINES as r1 Routines r2 ROUTINES as r2 for Record record create select r1 ROUTINE_SCHEMA
262. text An extract from the DSL type Non typesafe select methods public static SelectSelectStep lt Record gt select Collection lt extends Field lt gt gt fields public static SelectSelectStep lt Record gt select Field lt gt fields Typesafe select methods public static lt TI gt SelectSelectStep lt Record1 lt T1 gt gt select Field lt T1 gt fieldl publie static lt T 12 gt SelectSelectStep lt Record2 lt T1 T2 gt gt select Field lt T1 gt fieldl Field lt T2 gt field2 public static lt T1 T2 T3 gt SelectSelectStep lt Record3 lt T1 T2 T3 gt gt select Field lt T1 gt fieldl Field lt T2 gt field2 Field lt T3 gt field3 WW Mes all Since the generic R type is bound to some Record N the associated T type information can be used in various other contexts e g the IN predicate Such a SELECT statement can be assigned typesafely Select lt Record2 lt Integer String gt gt sl Select lt Record2 lt Integer String gt gt s2 create select BOOK ID BOOK TITLE create select BOOK ID trim BOOK TITLE For more information about typesafe record types with degree up to 22 see the manual s section about Record to Record22 2009 2014 by Data Geekery GmbH All rights reserved Page 41 174 The jOOQ User Manual 4 3 2 2 The FROM clause 4 3 2 2 The FROM clause The SQL FROM clause allows for specifying any number of table expressions to select
263. that you re selecting only from a single table Fetching strongly or weakly typed records When fetching data only from a single table the table expression s type is known to jOOQ if you use jOOQ s code generator to generate TableRecords for your database tables In order to fetch such strongly typed records you will have to use the simple select API Use the selectFrom method BookRecord book create selectFrom BOOK where BOOK ID equal 1 fetchOne Typesafe field access is now possible System out prantin litle a b ook getTitle yi System out println Published in book getPublishedIn When you use the DSLContext selectFrom method jOOQ will return the record type supplied with the argument table Beware though that you will no longer be able to use any clause that modifies the type of your table expression This includes The SELECT clause The JOIN clause 5 3 2 Record to Record22 jOOQ s row value expression or tuple support has been explained earlier in this manual It is useful for constructing row value expressions where they can be used in SQL The same typesafety is also applied to records for degrees up to 22 To express this fact org joog Record is extended by org joog Record to org joog Record22 Apart from the fact that these extensions of the R type can be used throughout the jOOQ DSL they also provide a useful API Here is org joog Reco
264. the classpath If it fails it will use the java util logging Logger Oracle ojdbc types used for array creation are loaded using reflection The same applies to Postgres PG types Small libraries with compatible licenses are incorporated into jOOQ These include jOOR jOOU parts of OpenCSV json simple parts of commons lang javax persistence and javax validation will be needed if you activate the relevant code generation flags 3 8 Build your own In order to build jOOQ yourself please download the sources from https github com jOOQ jOOQ and use Maven to build jOOQ preferably in Eclipse OOQ requires Java 6 to compile and run Some useful hints to build OOQ yourself Get the latest version of Git or EGit Get the latest version of Maven or M2E Check out the jOOQ sources from https github com 00Q 00Q Optionally import Maven artefacts into an Eclipse workspace using the following command see the maven eclipse plugin documentation for details mvn eclipse eclipse Build the joog parent artefact by using any of these commands mvn clean package create jar files in project build directory mvn clean install install the jar files in your local repository e g m2 mvn clean goal Dmaven test skip true don t run unit tests when building artefacts 3 9 JOOQ and backwards compatibility jOOQ follows the rules of semantic versioning accordi
265. the form HINT with your SQL statements to the Oracle database For example SELECT ALL_ROWS FIRST_NAME LAST_NAME FROM AUTHOR This can be done in jOOQ using the hint clause in your SELECT statement create select AUTHOR FIRST_NAME AUTHOR LAST_NAME hint ALL_ROWS from AUTHOR Note that you can pass any string in the hint clause If you use that clause the passed string will always be put in between the SELECT DISTINCT keywords and the actual projection list This can be useful in other databases too such as MySQL for instance SELECT SQL_CALC_FOUND_ROWS fieldl field2 create select fieldl field2 FROM tablel hint SQL_CALC_FOUND_ROWS from tablel 4 3 2 13 Lexical and logical SELECT clause order SQL has a lexical and a logical order of SELECT clauses The lexical order of SELECT clauses is inspired by the English language As SQL statements are commands for the database it is natural to express a statement in an imperative tense such as SELECT this and that Logical SELECT clause order The logical order of S order is this CT clauses however does not correspond to the syntax In fact the logical 2009 2014 by Data Geekery GmbH All rights reserved Page 53 174 The jOOQ User Manual 4 3 2 13 Lexical and logical SELECT clause order The FROM clause First all data
266. the manual s section about the sample database used in this manual to learn more about the sample database 3 2 The sample database used in this manual For the examples in this manual the same database will always be referred to It essentially consists of these entities created using the Oracle dialect O 2009 2014 by Data Geekery GmbH All rights reserved Page 14 174 The jOOQ User Manual CREATE TABLE language id NUMBER 7 cd CHAR 2 description VARCHAR2 50 di CREATE TABLE author id NUMBER 7 first_name VARCHAR2 50 last_name VARCHAR2 50 date_of_birth DATE year_of_birth NUMBER 7 distinguished NUMBER 1 di CREATE TABLE book id NUMBER 7 author_id NUMBER 7 title VARCHAR2 400 published_in NUMBER 7 language_id NUMBER 7 CONSTRAINT fk_book_author CONSTRAINT fk_book_language y CREATE TABLE book_store name VARCHAR2 400 Kf CREATE TABLE book_to_book_store name VARCHAR2 400 book_id INTEGER stock INTEGER PRIMARY KEY name book_id CONSTRAINT fk_b2bs_book_store CONSTRAINT fk_b2bs_book NOT NULL PRIMARY NOT NULL NOT NULL PRIMARY NOT NULL NOT NULL PRIMARY NOT NULL NOT NULL NOT NULL NOT NULL FOREIGN KEY auth FOREIGN KEY lang NOT NULL UNIQUE NOT NULL NOT NULL FOREIGN KEY FOREIGN KEY name book id KEY KEY KEY enel itch REFERENCES uage_id REFERENCES author id REFERENCES book_store REFERENCE
267. the near future An example DELETE AUTHOR WHERE ID 100 create delete AUTHOR where AUTHOR ID equal 100 4 3 6 The MERGE statement The MERGE statement is one of the most advanced standardised SQL constructs which is supported by DB2 HSQLDB Oracle SQL Server and Sybase MySQL has the similar INSERT ON DUPLICATE KEY UPDATE construct The point of the standard MERGE statement is to take a TARGET table and merge data from a SOURCE table into it DB2 Oracle SQL Server and Sybase also allow for data and for adding many additional clauses With jOOQ 3 0 1 only Oracle s MERG supported Here is an example ERT UPDATE ETING some extensions are NS DE 2009 2014 by Data Geekery GmbH All rights reserved Page 58 174 The jOOQ User Manual 4 3 7 The TRUNCATE statement Check if there 1s already an author called Hitchcock create mergeInto AUTHOR If there is rename him to John If there isn t add him using create selectOne MERGE INTO AUTHOR on AUTHOR LAST_NAME equal Hitchcock USING SELECT 1 FROM DUAL whenMatchedThenUpdate ON LAST_NAME Hitchcock set AUTHOR FIRST_NAME John WHEN MATCHED THEN UPDATE SET FIRST_NAME John whenNotMatchedThenInsert AUTHOR LAST_NAME WHEN NOT MATCHED THEN INSERT LAST_NAME VALUES Hitchcock values Hitchcock MERGE Stat
268. ting IDs per GROUPING SET Field lt Integer gt grouping Field lt gt Field lt Integer gt groupingld Field lt gt O 2009 2014 by Data Geekery GmbH All rights reserved Page 77 174 The jOOQ User Manual 4 5 15 User defined functions MySQL s and CUBRID s WITH ROLLUP syntax MySQL and CUBRID don t know any grouping functions but they support a WITH ROLLUP clause that is equivalent to simple ROLLUP grouping functions jOOQ emulates ROLLUP in MySQL and CUBRID by rendering this WITH ROLLUP clause The following two statements mean the same Statement 1 SQL standard Statement 1 MysOl GROUP BY ROLLUP A B C GROUP BY A B C WITH ROLLUP Statement 2 SQL standard Statement 2 MysOl GROUP BY A ROLLUP B C This is not supported in MySQL 4 5 15 User defined functions Some databases support user defined functions which can be embedded in any SQL statement if you re using jOOQ s code generator Let s say you have the following simple function in Oracle SQL CREATE OR REPLACE FUNCTION echo INPUT NUMBER RETURN NUMBER Is BEGIN RETURN INPUT END echo The above function will be made available from a generated Routines class You can use it like any other column expression SELECT echo 1 FROM DUAL WHERE echo 2 2 create select echo 1 where echo 2 equal Z Note that user defined functions returning CURSOR or ARRAY data types can also be used wherever table express
269. to hook into the Execute Listener support More details about this can be found in the manual s chapter about ExecuteListeners 4 10 SQL building in Scala jOOQ Scala is a maven module used for leveraging some advanced Scala features for those users that wish to use jOOQ with Scala Using Scala s implicit defs to allow for operator overloading The most obvious Scala feature to use in jOOQ are implicit defs for implicit conversions in order to enhance the org joog Field type with SQL esque operators The following depicts a trait which wraps all fields 2009 2014 by Data Geekery GmbH All rights reserved Page 101 174 The jOOQ User Manual Jer A Scala esque representation of link org jooq Field operators for common j00Q operations to arbitrary fields zy trait SAnyField T extends Field T String operations BYE value value String Into 11 Comparison predicates def def def def def def def def def dert der def def def def def lt gt value lt gt value gt value gt value gt value gt value lt value lt value lt value lt value lt gt value lt gt value Els Field T TI Field T N Field T T Field T T Field T T Field T T Field T T Field T Field String Field String Condition Condition Condition Condition Condition Condition
270. to name a few here are some of jOOQ s fetching modes O 2009 2014 by Data Geekery GmbH All rights reserved Page 105 174 The jOOQ User Manual 5 3 Fetching Untyped vs typed fetching Sometimes you care about the returned type of your records sometimes with arbitrary projections you don t Fetching arrays maps or lists Instead of letting you transform your result sets into any more suitable data type a library should do that work for you Fetching through handler callbacks This is an entirely different fetching paradigm With Java 8 s ambda expressions this will become even more powerful Fetching through mapper callbacks This is an entirely different fetching paradigm With Java 8 s ambda expressions this will become even more powerful Fetching custom POJOs This is what made Hibernate and JPA so strong Automatic mapping of tables to custom POJOs Lazy vs eager fetching It should be easy to distinguish these two fetch modes Fetching many results Some databases allow for returning many result sets from a single query JDBC can handle this but it s very verbose A list of results should be returned instead Fetching data asynchronously Some queries take too long to execute to wait for their results You should be able to spawn query execution in a separate process Convenience and how ResultQuery Result and Record share
271. ts 4 7 Plain SQL Apart from the general factory methods plain SQL is also available in various other contexts For instance when adding a where a b clause to a query Hence there exist several convenience methods where plain SQL can be inserted usefully This is an example displaying all various use cases in one single query You can use your table aliases in plain SQL fields As long as that will produce syntactically correct SQL Field lt gt LAST_NAME create field a LAST_ NAME You can alias your plain SQL fields Field lt gt COUNT1 create field count x If you know a reasonable Java type for your field you can also provide j00Q with that type Field lt Integer gt COUNT2 create field count y Integer class Use plain SOL as select fields create select LAST_NAME COUNT1 COUNT2 Use plain SQL as aliased tables be aware of syntax from author a AOD BOOR DAN Use plain SQL for conditions both in JOIN and WHERE clauses on a id b author_id Bind a variable in plain SQL where b title Brida Use plain SQL again as fields in GROUP BY and ORDER BY clauses YroupBy LAST_NAME OrderBy LAST_NAME Important things to note about plain SQL There are some important things to keep in mind when using plain SQL 2009 2014 by Data Geekery GmbH All rights reserved Page 93 174 The jOOQ User Manual 4 8 Bind values and paramet
272. ues clause is repeated for every record in multi record inserts Some RDBMS do not support inserting several records in a single statement In those cases jOOQ emulates multi record INSERTs using the following SQL INSERT INTO AUTHOR create insertInto AUTHOR ID FIRST_NAME LAST_NAME AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME SELECT 100 Hermann Hesse FROM DUAL UNION ALL values 100 Hermann Hesse SELECT 101 Alfred D blin FROM DUAL values 101 Alfred D blin O 2009 2014 by Data Geekery GmbH All rights reserved Page 55 174 The jOOQ User Manual 4 3 3 The INSERT statement INSERT using jOOQ s alternative syntax MySQL and some other RDBMS allow for using a non SQL standard UPDATE like syntax for INSERT statements This is also supported in jOOQ should you prefer that syntax The above INSERT statement can also be expressed as follows create insertInto AUTHOR set AUTHOR ID 100 set AUTHOR FIRST_NAME Hermann set AUTHOR LAST_NAME Hesse newRecord set AUTHOR ID 101 Set AUTHOR FIRST_NAME Alfred set AUTHOR LAST_NAME D blin As you can see this syntax is a bit more verbose but also more readable as every field can be matched with its value Internally the two syntaxes are strictly equivalent MySQL s INSERT ON DUPLICATE KEY UPDATE The MySQL database supports a very convenient way to INSERT or UPDATE a record This is a
273. use to the UPDATE or DELETE statement checking for TIMESTAMP s integrity This can be best illustrated Instead of using TIMESTAMPs you may also use numeric VERSION fields containing version numbers that are incremented by jOOQ upon store calls Note for explicit pessimistic locking please consider the manual s section about the FOR UPDAT clause For more details about how to configure TIMESTAMP or VERSION fields consider the manual s section about advanced code generator configuration 2009 2014 by Data Geekery GmbH All rights reserved Page 135 174 The jOOQ User Manual 5 11 7 Batch execution 5 11 7 Batch execution When inserting updating deleting a lot of records you may wish to profit from JDBC batch operations which can be performed by jOOQ These are available through jOOQ s DSLContext as shown in the following example Fetch a bunch of books Result lt BookRecord gt books create fetch BOOK Modify the above books and add some new ones modify books addMore books Batch update and or insert all of the above books create batchStore books Internally OOQ will render all the required SQL statements and execute them as a regular JDBC batch execution 9 12 DAOS If you re using jOOQ s code generator you can configure it to generate POJOs and DAOs for you jOOQ then generates one DAO per UpdatableRecord i e per table with a single column primary
274. using your own java sal PreparedStatement you can do so as follows Select lt gt select create select from BOOK where BOOK ID equal 5 and BOOK TITLE equal Animal Farm Render the SOL statement String sql select getson assertEquals SELECT FROM BOOK WHERE ID AND TITLE sql Get the bind values List lt Object gt values select getBindValues assertEquals 2 values size assertEquals 5 values get 0 assertEquals Animal Farm values get 1 You can also extract specific bind values by index from a query if you wish to modify their underlying value after creating a query This can be achieved as such Select lt gt select create select from BOOK where BOOK ID equal 5 and BOOK TITLE equal Animal Farm Param lt gt param select getParam 2 You could now modify the Query s underlying bind value if Animal Farm equals param getValue param setConverted 1984 For more details about jOOQ s internals see the manual s section about QueryParts 4 8 2 Named parameters Some SQL access abstractions that are built on top of JDBC or some that bypass JDBC may support named parameters jOOQ allows you to give names to your parameters as well although those names are not rendered to SQL strings by default Here is an example of how to create named parameters using the org jooq Param type Create a query with a named p
275. value gt lt value field AUTHOR_ID gt 1 lt value gt lt value field TITLE gt Animal Farm lt value gt lt record gt lt records gt lt result gt The same result as an org w3c dom Document can be obtained using the Result intoXML method Fetch books and format them as XML Document xml create selectFrom BOOK fetch intoXML See the XSD schema definition here for a formal definition of the XML export format http www jooq org xsd joog export 2 6 0 xsd 5 9 2 Exporting CSV Fetch books and format them as CSV String csv create selectFrom BOOK fetch formatCSV The above query will result in a CSV document looking like the following one ID AUTHOR_ID TITLE 1 11984 2 1 Animal Farm In addition to the standard behaviour you can also specify a separator character as well as a special string to represent NULL values which cannot be represented in standard CSV Use as the separator character String csv create selectFrom BOOK fetch formatCSV Specify null as a representation for NULL values String csv create selectFrom BOOK fetenhi tommabe svi a a mudi yy 2009 2014 by Data Geekery GmbH All rights reserved Page 126 174 The jOOQ User Manual 5 9 3 Exporting JSON Fetch books and format them as JSON String json create selectFrom BOOK fetch formatJSON The above query will result in a JSON document looking like the following
276. ve records from your select statement results in order to do some processing This is a common data access pattern in Spring s JdbcTemplate and it is also available in OOQ With jOOQ you can implement your own org joog RecordHandler classes and plug them into jOOQ s org joog ResultQuery Write callbacks to receive records from select statements create selectFrom BOOK OrderBy BOOK ID fetch into new RecordHandler lt BookRecord gt Override public void next BookRecord book Util doThingsWithBook book MW Or more concisely create selectFrom BOOK OrderBy BOOK ID fetchInto new RecordHandler lt BookRecord gt Or even more concisely with Java 8 s lambda expressions create selectFrom BOOK OrderBy BOOK ID fetchInto book gt Util doThingsWithBook book See also the manual s section about the RecordMapper which provides similar features 2009 2014 by Data Geekery GmbH All rights reserved Page 109 174 The jOOQ User Manual 5 3 5 RecordMapper 5 3 5 RecordMapper In a more functional operating mode you might want to write callbacks that map records from your select statement results in order to do some processing This is a common data access pattern in Spring s JdbcTemplate and it is also available in jOOQ With jOOQ you can implement your own org joog RecordMapper classes and plug them into jOOQ s org joog ResultQuery Writ
277. verloadIndex return _OverloadIndex_ overloadIndex 6 2 Advanced generator configuration The jOOQ User Manual 6 2 Advanced generator configuration More examples can be found here org joog util example JPrefixGeneratorStrategy org oog util example VMArgsGeneratorStrategy joog meta configuration Within the lt generator gt element there are other configuration elements lt These properties can be added to the database element gt lt database gt lt All table and view columns that are used as version fields for optimistic locking A Java regular expression Use the pipe to separate several expressions See UpdatableRecord store and UpdatableRecord delete for details gt lt recordVersionFields gt REC_VERSION lt recordVersionFields gt lt All table and view columns that are used as timestamp fields for optimistic locking A Java regular expression Use the pipe to separate several expressions See UpdatableRecord store and UpdatableRecord delete for details gt lt recordTimestampFields gt REC_TIMESTAMP lt recordTimestampFields gt lt Generate java sql Timestamp fields for DATE columns This is particularly useful for Oracle databases Defaults to false gt lt dateAsTimestamp gt false lt dateAsTimestamp gt lt Generate jOOU data types for your unsigned data types which are not natively supported in Java Defaults to true gt lt unsignedTy
278. w DataType related features These changes include SQLDialect DataType and SQLDataType no longer implement DataType They re mere constant containers Various minor API changes have been done Object renames These objects have been moved renamed jOOU a library used to represent unsigned integer types was moved from org jooq util unsigned to org jooq util types which already contained INTERVAL data types Feature removals Here are some minor features that have been removed in jOOQ 3 0 2009 2014 by Data Geekery GmbH All rights reserved Page 173 174 The jOOQ User Manual 8 6 Credits The ant task for code generation was removed as it was not up to date at all Code generation through ant can be performed easily by calling OOQ s GenerationTool through a lt java gt target The navigation methods and foreign key setters are no longer generated in Record classes as they are useful only to few users and the generated code is very collision prone The code generation configuration no longer accepts comma separated regular expressions Use the regex pipe instead The code generation configuration can no longer be loaded from properties files Only XML configurations are supported The master data type feature is no longer supported This feature was unlikely to behave exactly as users expected It is better if users write their own code generators to generate master enum
279. w table expressions provide the SQL syntax typesafety as shown above where generated tables are used Most tables however expose their fields through field methods Type unsafe aliased table Table lt gt a AUTHOR as a Get fields from a Huetar id a field TD Field lt gt firstName a field FIRST_NAME O 2009 2014 by Data Geekery GmbH All rights reserved Page 60 174 The jOOQ User Manual 4 4 3 Joined tables Derived column lists The SQL standard specifies how a table can be renamed aliased in one go along with its columns It references the term derived column list for the following syntax as supported by Postgres for instance SELECT t a t b FROM SELECT 1 2 rela D This feature is useful in various use cases where column names are not known in advance but the table s degree is An example for this are unnested tables or the VALUES table constructor Unnested tables SELECI C a b FROM unnest my_table_function t a b VALUES constructor SELECI t a b FROM VALUES 1 2 3 4 Ela b Only few databases really support such a syntax but fortunately OOQ can emulate it easily using UNION ALL and an empty dummy record specifying the new column names The two statements are equivalent Using derived column lists SELECT taa t p FROM SELECT 1 2 t a b Using UNION ALL and a dummy record SELECT taa E D F
280. x for creating hierarchical queries the CONNECT BY clause which is fully supported by jOOQ including all related functions and pseudo columns A more or less formal definition of this clause is given here SELEC S S FROM aa WHERE CONNECT BY NOCYCLE condition AND condition lt 2 I START WITH condition O EA ORDER SIBLINGS EN An example for an iterative query iterating through values between 1 and 5 is this SELECT LEVEL Get a table with elements 1 2 3 4 5 FROM DUAL create select level CONNECT BY LEVEL lt 5 connectBy level lessOrEqual 5 Here s a more complex example where you can recursively fetch directories in your database and concatenate them to a path SELECT select SUBSTR SYS_CONNECT_BY _PATH DIRECTORY NAME 2 sysConnectByPath DIRECTORY NAME substring 2 FROM DIRECTORY Erom DIRECTORY CONNECT BY CconnectBy PRIOR DIRECTORY ID DIRECTORY PARENT_ID prior DIRECTORY ID equal DIRECTORY PARENT_ID START WITH DIRECTORY PARENT_ID IS NULL StartwWith DIRECTORY PARENT_I1D isNull ORDER BY 1 orderBy 1 The output might then look like this lie C eclipse C eclipse configuration C eclipse dropins C eclipse eclipse exe 21 record s truncated Some of the supported functions and pseudo columns are these available from the DSL LEVEI CONNECT_BY_IS_CYCLE CONNECT_BY_IS_LEAF CONNECT_BY_ROOT SYS_CONNECT_B
281. y to access modify delete a book is this gt Inserting uses a previously generated key value or generates it afresh INSERT INTO BOOK ID TITLE VALUES 5 Animal Farm Other operations can use a previously generated key value SELECT FROM BOOK WHERE ID 5 UPDATE BOOK SET TITLE 1984 WHERE ID 5 DELETE FROM BOOK WHERE ID 5 Normalised databases assume that a primary key is unique forever i e that a key once inserted into a table will never be changed or re inserted after deletion In order to use jOOQ s CRUD operations correctly you should design your database accordingly 5 11 1 Simple CRUD If you re using jOOQ s code generator it will generate org jooq UpdatableRecord implementations for every table that has a primary key When fetching such a record form the database these records are attached to the Configuration that created them This means that they hold an internal reference to the same database connection that was used to fetch them This connection is used internally by any of the following methods of the UpdatableRecord Refresh a record from the database void refresh throws DataAccessException Store insert or update a record to the database int store throws DataAccessException Delete a record from the database int delete throws DataAccessException 2009 2014 by Data Geekery GmbH All rights reserved Page 130 174 The jOOQ User Manual 5

The jOOQ™ User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents

The jOOQ&trade; User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents

The jOOQ™ User Manual