Home

The jOOQ™ User Manual

Contents

1. 2009 2014 by Data Geekery GmbH All rights reserved Page 115 151 The jOOQ User Manual Deleting 5 11 2 IDENTITY values 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 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 Factory Loop over records returned from a SELECT statement for BookRecord book create fetch BOOK BOOK PUBLISHED_IN equal 1948 Perform actions on BookRecords depen
2. 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 retrieving POJOs by primary key or by some other field List lt P gt findAll throws DataAccessException P
3. If you re in a J2EE or Spring context however you may wish to use a javax sql DataSource instead Currently Connections obtained from such a DataSource will be closed after query execution by OOQ 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 OOQ with Spring O 2009 2014 by Data Geekery GmbH All rights reserved Page 26 151 The jOOQ User Manual 4 1 3 Custom Settings 4 1 3 Custom Settings The jOOQ Factory allows for some optional configuration elements to be used by advanced users The org joog conf Settings class is a JAXB annotated type that can be provided to a Factory in several ways In the 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 2 5 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 sql PreparedStatement you can do so by using the following Factory Se
4. The DELETE statement removes records from a database table DELETE statements are only possible on single tables Support for multi table deletes will be implemented in the near future An example delete query is given here DELETE AUTHOR create delete AUTHOR WHERE ID 100 where AUTHOR ID equal 100 2009 2014 by Data Geekery GmbH All rights reserved Page 47 151 The jOOQ User Manual 4 2 6 The MERGE statement 4 2 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 INSERT UPDATE data from a SOURCE table into it DB2 Oracle SQL Server and Sybase also allow for DELETING some data and for adding many additional clauses With jOOQ 2 6 4 only Oracle s MERGE extensions are supported Here is an example 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
5. 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 Factory 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 WindowIgnoreNullsStep lt T gt lag Field lt T gt field lt T gt WindowIgnoreNullsStep lt T gt TaglEreldsio field ant offset lt T gt WindowIgnoreNullsStep lt T gt lagiiPteld 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 Wi
6. 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 abc contains and similar methods contains and similar methods TITLE LIKE MA A pi Egt BOOK TITLE contains abc TITLE LIKE tabat FESI BOOK TITLE startsWith abc E Vel I tabe 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 5 9 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 LAL o Construct an IN predicate from bind values arica Construct an IN predicate from colum
7. 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 5 8 Stored procedures and functions Many 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 2009 2014 by Data Geekery GmbH All rights reserved Page 106 151 The jOOQ User Manual Procedures Are called using JDBC CallableStatement 2 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 5 8 Stored procedures and functions 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 witho
8. System out println ctx sql e g plain SOL l3tring tils isSlankictz sgl yy t See also the manual s sections about logging and the jOOQ Console for more sample implementations of actual E xecuteListeners 5 15 Logging jOOQ logs al as an execut to see any D configuration SQL queries and fetched result sets to its internal D 2009 2014 by Data Geekery GmbH All rights reserved EBUG logger which is implemented e listener By default execute logging is activated in the OOQ Factory Settings In order EBUG log output put either log4j or slf4j on jOOQ s classpath along with their respective Asample log4j configuration can be seen here Page 123 151 The jOOQ User Manual 5 16 Performance considerations 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 org apache log4j 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 exrsate select BOOK ID BOOK TITLE from BOOK orderBy BOOK 1D lamit l 2 fetch The above query may result in the following log output Executing query gt sele
9. 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 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 133 151 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 TableRecords 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
10. newInstance conn DriverManager getConnection url userName password Factory create new Factory 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 Systensoute princi Dd tte fi title t de sciption catch Exception e For the sake of this tutorial let s keep exception handling simple e printStackTrace t finally f iE conn G puriy try conn close catch SQLException ignore 3 4 1 7 Step 7 Explore Ue deseriptionj 3 4 1 7 Step 7 Explore jOOQ has grown to be a comprehensive SQL library For more information please consider the manual http www joog org manual explore the Javadoc http www joog org javadoc latest or join the news group https 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 11 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 21 151 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 F
11. 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 Object 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 Str
12. 2014 by Data Geekery GmbH All rights reserved Page 84 151 The jOOQ User Manual 4 8 3 Variable binding Create a factory that will render formatted SOL Factory pretty new Factory dialect new Settings withRenderFormatted true And then use the above factory to render pretty printed SQL String sql pretty select select AUTHOR LAST_NAME count as c TEST AUTHOR LAST_NAME from BOOK Counce wey Join AUTHOR from TEST BOOK on BOOK AUTHOR_ID equal AUTHOR ID jon YTEST AAUTHORY where BOOK TITLE notEqual 1984 on ES IAB ATEO AID IS TES AUTOR IN Bt groupBy AUTHOR LAST_NAME where TESEO E SAS having count equal 2 group by TEST AUTHOR LAST_NAME getSQL having count 2 The section about Executel isteners shows an example of how such pretty printing can be used to log readable SQL to the stdout 4 8 3 Variable binding Every org joog QueryPart must implement the bind BindContext method This BindContext has two purposes lt 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 t
13. GmbH All rights reserved Page 49 151 The jOOQ User Manual 4 3 3 Joined tables 4 3 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 ACCOLIRL asp Epia Jorn B CoIBI oan COLEmM produces Chcornl r COLD emba e r SOLBM SQL and relational algebra distinguish between at least the following JOIN types upper case SQL lower case relational algebra 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 also be produced by listing comma separated table expressions in the FROM clause of a SELECT statement NATURAL 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 requiring 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 w
14. 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 3 6 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 62 WHERE e2 TEXT oI TEXT AND c2 ID B C_ID 2009 2014 by Data Geekery GmbH All rights reserved Page 52 151 The jOOQ User Manual 4 3 7 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 fro
15. All rights reserved Page 81 151 The jOOQ User Manual 4 7 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 7 3 Inlined parameters Sometimes you may wish to avoid rendering 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 Factory By using Factory 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 So Settings settings new Settings withStatementType StatementType STATIC_STATEMENT Add the settings to the factory Factory create new Factory connection SQLDiale
16. 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 Factory s settings with Take the following example Settings settings new Settings withRenderMapping new RenderMapping withSchemata new MappedSchema withInput DEV withOutput MY_BOOK_WORLD Add the settings to the factory Factory create new Factory connection SQLDialect ORACLE settings Ran queries with the mapped factory create selectFrom AUTHOR fetch The query executed with a Factory 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 jooq runtime 2 5 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
17. 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 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 z TOER LO A da Ay Me Oe cin ys SHEI rys 37 bRalph y Johnson e stmt addBatch INSERT INTO author 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 4 Sens tinte stmt setString 2 Erich stmt setString 3 Gamma stmt addBatch Siembas Std AS peme sees Brno Van Bac Sens SES Ca stmt addBatch Sens epi L stmt setString 2 Ralph semt setString 3 dois on stmt addBatch stmt setInt 1 4 Sale SS e a stmt setString 3 Vlissides stmt addBatch int result stmt executeBatch This will also be supported by jOOQ jOOQ supports executing queries in batch mode as follows 1 several querie
18. With jOOQ you can emulate ENUM types by declaring a table as a master data table in the configuration At code generation time this table will be treated specially and a Java enum type is generated from its data Configure master data tables As previously discussed you can configure master data tables as follows SoS Thege properties canbe added to th database element gt lt database gt lt masterDataTables gt lt masterDataTable gt fhe nont fone a master data table lt name gt a table name lt name gt lt I The column used for en m literals lt gt lt literal gt a column name lt literal gt oho Son uscd for documentation lt description gt a column name lt description gt lt masterDataTable gt lt masterDataTable gt lt masterDataTable gt lt masterDataTables gt lt database gt The results of this will be a Java enum that looks similar to this public enum Language implements MasterDataType lt Integer gt Jer Englist zji Sire a clica Jr Deutsch ali ES DS tE Sci Jer SU Prancais EA aC Si Adel pt 4 pt null privato finnasi Integer idy private finat String ca privato final String description constructor and getters for the above properties In the above example you can see how the configured primary key is mapped to the id member the configured literal column is mapped to the cd
19. lt def value Number def value Field _ lt Bitwise operations _ d f unary_ def amp value T def amp value Field T det def value T value Field T def value T def value Field T def lt lt value T def lt lt value Field T def gt gt value T def gt gt value Field T An example query using such overloaded operators would then look like this select BOOK ID BOOK AUTHOR_ID BOOK ID BOOK AUTHOR_ID Number Number Number Number Number oh pets ROOK ms ASA O Ry from BOOK leftOuterJoin f select x ID x YEAR_OF_BIRTH from x due ab asTable x getName on BOOK AUTHOR TD x ID where BOOK ID lt gt 2 or BOOK TITLE in O Alquimista teteh Scala 2 10 Macros extends SAnyField T di pskSikel 2 Field Field 2 Fiera E Field 1 Field Field 2 miel Nela 2 Field ashe la diablo e FPieid e iii z Eield 2 Field 1 Ereid Eielg 2 ica gripal ko 2 Pield y inicio Brida 1 adding overloaded 4 9 SQL building in Scala 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 89 151 The jOOQ User Manual 5 SQL execution 5
20. simplified CompareCondition It 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 Override public final void 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 sql If the second field is null some convenience behaviour can be implemented here if field2 isNullLiteral switch comparator case EQUALS context sqi is nuli break case NOT_EQUALS context sgl is not null break default throw new IllegalStateException Cannot compare null with comparator By default also delegate the right hand side s SQL rendering to the underlying field else context sql comparator toSQL san 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 8 2 Pretty printing SQL As mentioned in the previous chapter about SQL rendering there are some elements in the org jooq RenderContext that are used for formatting pretty printing rendered SQL In order to obtain pretty printed SQL just use the following custom settings 2009
21. the jOOQ API Fetching This section contains some useful information about the various ways of fetching data with jOOQ 3 3 4 JOOQ Tor 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 O 2009 2014 by Data Geekery GmbH All rights reserved Page 15 151 The jOOQ User Manual 3 3 5 jOOQ for PROs 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 jOOQ API 3 3 5 JOOQ for PROs jOOQ isn t just a library that helps you build and execute SQL again
22. tr gt lt tbody gt lt table gt 5 9 5 Exporting Text Fetch books and format them as text String text create sellectFrom BOOK fetch format The above query will result in a text document looking like the following one ID AUTHOR_ID TITLE il 1 1984 2 1jAnimal Farm 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 2009 2014 by Data Geekery GmbH All rights reserved Page 111 151 The jOOQ User Manual 5 10 Importing data 5 10 Importing data 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 OOQ s exporting functionality and then modified in Microsoft Excel or any other spreadsheet tool ID AUTHOR ID 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 Factory create ne
23. 1 2011 7 25 08 PM org jooq impl JooqLogger info INFO GENERATION FINISHED 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 O 2009 2014 by Data Geekery GmbH All rights reserved Page 19 151 The jOOQ User Manual For import import import public public static void main String convenience SbdbwCueect generated lab lesa static org jooq impl Factory av aS quis class Main 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 catch Exception e For the sake of this tutorial e printStackTrace finally TE oa Se puh 46 try conn close catch SQLException ignore password let s keep exception handling simple 3 4 1 5 Step 5 Querying always static import your generated tables and j00Q functions to decrease verbosity This is pretty standard code for establishing a MySQL connection 3 4 1 5 Step 5 Querying Let s add a simple query Factory create new Factory conn Result lt Record gt result First get an instance of ySQL connection to Factory Note that the factory doesn t close the connection We ll have to do that SQLDialect MYSQL crea
24. 7 2 Copyright License and TAS A E aula eee aaa 8 ENE AA naa a A a a aa a AAA A A A A A 1 SA ROW O NN 1 3 2 The sample database Usd Ma ii dia RA 2 3 3 Different use cases for JOOQ minihi a A aR a A A A AA E 3 33514 000 asa SOL DUI A A dd dd dd Na ads 3 3 3 2 JOOQ as a SQL builder with code Zemerationan c ccccccscssssesessssesessnessstssseesessecsssssseessecsecseeseeseesusssssessneeseesecsisseesseeseesesteenseneenes 4 3 3 3 OOQ as a SQL executor 4 SA 1 RD a e in 5 A A O ER 6 e REEI A O EOS 6 3 4 1 jOOQ in 7 Easy SD ic sccestessccesstesscedaaduascedadisscetescea cede a E ida d cada a E E KESSE a SSE a DE 6 A RS 7 34 12 Step 2 YOUR data a bio 7 A SS Code generation anien mia a E A A A A A 7 34 1 4 Step A Connect to your Ad DS iS 9 3A lea Nep S QUEIN E orra E EE A N EAA EA EAE ER E E tenet terest ET er MRSE ASE OE SAM NE SAR Using OO in modern DE iia 3 4 3 Using jOOQ with Spring CE e e mo ne a8 3 4A4 A simple web applicatlon WOOD Os A Ia 35x QO aNd A ieidadiecnadisclensineddaediseleh a onde emia a aA 3 6 A dde e A SOL DU Ar a A o A a stacks MASON letal tie oie a toto wats NL 41 2 Connection Vys NN ANS T CUSTOMS SOU SS dada AVA Runtime Schema and table Map Oi Gs rrsess ecieceits beeen hivwys vials sthavetas oleae a aia Ail 5 Facto SUDGlASSOS A E A AO O A E O esis iacetn id iia ADO SOs STALIN CPUS sis 424 HOOQ SDS band MON D SILA ec sarees CIA 4 2 2 The SELECT statement AS RS A EEE E ee re eee ee
25. 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 mapping 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
26. 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 PROCUREMENT 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
27. ED_IN AUTHOR _ID 0 UJ m UP AUTHOR_ID PUBLISHED_IN corresponds to GROUPING SETS AUTHOR_ID AUTHOR_ID PUBLISHED_IN corresponds to GROUPING SETS AUTHOR_ID ED_IN AUTHOR_ID PUBLISHED_IN This is nicely explained in the SQL Server manual pages about GROUPING SETS and other grouping functions http msdn microsoft com en us library bb51042 7 v sql 105 jOOQ 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 Factory API thus includes The various grouping function constructors Field lt gt rollup Field lt gt fields Field lt gt cube Field lt gt fields Field lt gt groupingSets Field lt gt fields Field lt gt groupingSets Field lt gt fields Field lt gt groupingSets Collection lt Field lt gt gt fields The utility functions generating 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 65 151 The jOOQ User Manual 4 4 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 t
28. Geekery GmbH All rights reserved Page 66 151 The jOOQ User Manual 4 4 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 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 TH
29. 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 jooqg Param type Create a query with a named parameter 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 n order to actually render named parameter names in generated SQL use the Factory renderNamedParams method 2009 2014 by Data Geekery GmbH
30. THIS SOFTWARE EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE 2009 2014 by Data Geekery GmbH All rights reserved Page 8 151 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 permissions 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 Agre
31. The following sections explain some more details about SQL rendering and variable binding as well as other implementation details about QueryParts in general 4 8 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 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 form
32. When you alias Fields like above you can access those Fields values using the alias name System out print in Author record getValiue author System out printlin Books e orde bale books 4 4 3 Cast expressions jOOQ s source code generator tries to find the most accurate type mapping between your vendor specific data types and a matching Java type For instance most VARCHAR CHAR CLOB types will map to String Most BINARY BYTEA BLOB types will map to byte NUMERIC types will default to java math BigDecimal but can also be any of java math Biginteger Long Integer Short Byte Double Float Sometimes this automatic mapping might not be what you needed or jOOQ cannot know the type of a field In those cases you would write SQL type CASTs like this Let s say your Postgres column LAST_NAME was VARCHAR 30 Ther yon could dor Chis 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 2009 2014 by Data Geekery GmbH All rights reserved Page 55 151 The jOOQ User Manual The same thing can be achieved by casting a Field directly to String class as T type in Postgres to map to Java s String create select TAuthor LAST_NAME cast String class The complete CAST API in Field consists of these three methods publics interface pue ld lt i Cast this field to the type of
33. 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 5 3 4 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 Write 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 SE Or more concisely create selectFrom BOOK orderBy BOOK ID fetch new RecordMapper lt BookRecord Integer gt Ox even more concisel
34. a path SELECT select SUBSTR SYS_CONNECT_BY_PATH DIRECTORY NAME 2 sysConnectByPath DIRECTORY NAME substring 2 FROM DIRECTORY from DIRECTORY CONNECT BY connectBy 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 BY 1 orderBy 1 The output might then look like this es C eclipse C eclipse configuration C eclipse dropins C eclipse eclipse exe Sa 2l records truncated Some of the supported functions and pseudo columns are these available from the Factory LEVE CONNECT_BY_IS CYCLE e CONNECT_BY_IS_LEAF CONNECT_BY_ROOT SYS_CONNECT_BY_PATH PRIOR Note that this syntax is also supported in the CUBRID database 2009 2014 by Data Geekery GmbH All rights reserved Page 37 151 The jOOQ User Manual 4 2 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 P
35. 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 i gt Field lt 7 gt cast Class extends 2 gt type And additional convenience methods in the Factory public class Factory 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 cClass lt extends T gt type 4 4 4 Arithmetic expressions Numeric arithmetic expressions 4 4 4 Arithmetic expressions EXT is the default data Your database can do the math for you Arithmetic operations 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 1 2 5 3 7 2 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 Datetime arithmetic expressions jOOQ also supports the Oracle style syntax for adding days to a Field lt extends java util Date gt SELECT SYS
36. case insensitivity LOWER TITLE LOWER animal farm BOOK TITLE equalIgnoreCase animal farm LOWER TITLE lt gt LOWER animal farm BOOK TITLE notEqualIgnoreCase animal farm 4 5 4 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 Here are some examples supported by jOOQ 2009 2014 by Data Geekery GmbH All rights reserved Page 72 151 The jOOQ User Manual 4 5 5 NULL predicate TITLE ANY Animal Farm 1982 BOOK TITLE equalAny Animal Farm 1982 PUBLISHED_IN gt ALL 1920 1940 BOOK PUBLISHED_IN greaterThanAll 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 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 5 5 NULL predicate In SQL
37. 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 selectrFrom BOOK fetch formatcsv nullp 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 one 2009 2014 by Data Geekery GmbH All rights reserved Page 110 151 The jOOQ User Manual 5 9 4 Exporting HTML fields name Ud ly type type I name field 2 typet type 2 seer uname le field n typot sl records values valus 2 value 2 1 value 2 2 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 formatHIML The above query will result in an HTML document looking like the following one lt table gt lt thead gt lt tr gt lt th gt ID lt th gt lt th gt AUTHOR_ID lt th gt lt th gt TITLE lt th gt Ele lt thead gt lt tbody gt lt tr gt 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 tr gt lt td gt 2 lt td gt lt td gt 1 lt td gt lt td gt Animal Farm lt td gt lt
38. 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 create select FROM AUTHOR from AUTHOR JOIN BOOK ON BOOK AUTHOR_ID AUTHOR ID join BOOK onKey In case of ambiguity you can also supply field references for your foreign keys or the g key reference to the onKey method 2009 2014 by Data Geekery GmbH All rights reserved enerated foreign Page 35 151 The jOOQ User Manual 4 2 2 4 The WHERE clause 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 both 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 Assuming that both tables contain AUTHOR_ID columns A AS E E 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 th
39. 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 BY 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 44 151 The jOOQ User Manual 4 2 2 12 Oracle style hints 4 2 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 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 4 2 3 The INSERT statement The INSERT statement is
40. 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 2 6 4 jar jooq meta 2 6 4 jar jooq codegen 2 6 4 jar mysql connector java 5 1 18 bin jar org joog util GenerationTool library xml or type this on a UNIX Linux Mac system colons instead of semi colons java classpath jooq 2 6 4 jar jooq meta 2 6 4 jar jooq codegen 2 6 4 jar mysql connector java 5 1 18 bin jar org joog 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 jOOQ 2 6 4 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 18 151 The jOOQ User Manual Non dbs oka Tsetse 06 PM org jooq INFO Initialising properties NOV EA TEOS 07
41. executeQuery Eoi while rs next 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 can 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 gt clauersyelel dl SELECT FROM author ORDER BY id ASC EE te 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 43 151 The jOOQ User Manual 4 2 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 2 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
42. 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 to name a few here are some of jOOQ s fetching modes 2009 2014 by Data Geekery GmbH All rights reserved Page 91 151 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 fet
43. gt results create fetchMany sp_help author Using generics the resulting structure is immediately clear 5 3 8 Later fetching 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 The result should be ready now Result lt BookRecord gt result future get 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 2009 2014 by Data Geekery GmbH All rights reserved Page 100 151 The jOOQ Us
44. is given here 2009 2014 by Data Geekery GmbH All rights reserved Page 13 151 The jOOQ User Manual 3 3 2 00Q as a SQL builder with code generation 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 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 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 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 JOOQ 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 adds a lot of power and expressiveness to just simply constructing SQL usi
45. 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 way 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 Main UNIQUE keys In SQL a primary key is always also a unique key In fact unique keys have very similar properties as primary keys For instance they can be referenced from
46. 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 All elements that are generated from your schema several Java regular expressions separated by comma 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 excluded from your schema several Java regular expressions separated by comma Excludes match before includes 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 srce 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
47. mean time 2009 2014 by Data Geekery GmbH All rights reserved Page 118 151 The jOOQ User Manual 5 11 5 Optimistic locking 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 Properly configure the Factory Factory optimistic new Factory 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 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 a s tome mara lel thus fail 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 This column indicates when each book record was modified for the last time MODIFIED TIM
48. member and the configured description member is 2009 2014 by Data Geekery GmbH All rights reserved Page 143 151 The jOOQ User Manual 6 13 Custom data types and type conversion mapped to the description member and output as Javadoc In other words LANGUAGE is a table with 4 rows and at least three columns The general contract is that there must be Asingle column primary key column of character or integer type An optional unique literal column of character or integer type otherwise the primary key is used as enum literal An optional description column of any type Using MasterDataTypes The point of MasterDataTypes in jOOQ is that they behave exactly like true ENUM types When the above LANGUAGE table is referenced by BOOK instead of generating foreign key navigation methods and a LANGUAGE _ID Field lt Integer gt a Field lt TLanguage gt is generated public class Book extends UpdatableTableImp1 lt BookRecord gt HH Wes el public static final TableField lt BookRecord Language gt LANGUAGE_ID new TableFieldImpl lt BookRecord Language gt Which can then be used in the BookRecord directly public class BookRecord extends UpdatableRecordImp1 lt BookRecord gt WE Nose public Language getLanguageld public void setLanguageld Language value When to use MasterDataTypes You can use master data types when you re actually mapping master
49. other tables foreign keys in most databases In the absence of a formal primary key OOQ assumes that the first unique key it encounters will serve as a primary key substitute This is called the main key in jOOQ In other words a main key is F The primary key if available The first unique key otherwise For simplicity the term primary key will be used in the sense of such a main unique key in this manual 2009 2014 by Data Geekery GmbH All rights reserved Page 114 151 The jOOQ User Manual 5 11 1 Simple CRUD 5 11 1 Simple CRUD If you re using jOOQ s code generator it will generate org joog UpdatableRecord implementations for every table that has a primary key When fetching such a record form the database these records are attached to the Factory 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 See the manual s section about serializability for some more insight on attached objects Storing Storing a record will perform an INSERT statement or a
50. reserved Page 14 151 The jOOQ User Manual 3 3 4 OOQ for CRUD Execute the SOL statement directly with j000 Result lt Record 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 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 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 sgl Or execute that SOL with JDBC fetching the ResultSet with j000 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 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
51. 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 BY 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 Override 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 me
52. 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 2009 2014 by Data Geekery GmbH All rights reserved Page 29 151 The jOOQ User Manual 4 1 5 Factory subclasses 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 1 5 Factory subclasses There are a couple of subclasses for the general Factory Each SQL dialect has its own dialect specific factory For instance if you re only using the MySQL dialect you can choose to create a new Factory using any of the following types A general dialect unspecific factory Factory create new Factory connection SQLDialect MYSQL A MySQL specific factory MySQLFactory create new MySQLFactory connection The advantage of using a dialect specific Factory 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 Another type of Factory subclasses are each generated schema s factories If you generate your schema EST then you will have access to a TestFactory By default such a schema specific Factory will not render the schema name 4
53. 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 joog Support annotation as such Add an Oracle specific lt code gt CONNECT BY lt code gt clause to the query Support CUBRID ORACLE SelectConnectByConditionStep connectBy Condition condition jOOQ API methods which are not annotated with the org jooq 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 Create a new DSL select statement nie Support Selece Select ot op select Pield lt gt fields 2009 2014 by Data Geekery GmbH All rights reserved Page 25 151 The jOOQ User Manual 4 1 2 Connection vs DataSource 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 suppor
54. 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 Factory plain SQL API Plain SQL API methods are usually 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 pindings z Construct a condition taking other j00Q object arguments Example condition a 0 val 1 Condition conditirontSstring sal QueryPart parts Please refer to the org joog impl Factory Javadoc for more details The following is a more complete listing of plain SQL construction methods from the Factory 2009 2014 by Data Geekery GmbH All rights reserved Page 78 151 The jOOQ User Manual 4 6 Plain SQL 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
55. 009 2014 by Data Geekery GmbH All rights reserved Page 141 151 The jOOQ User Manual 6 11 Generated UDTs 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 SOLDataType NUMERIC A constructor for a new empty procedure call public AuthorExists 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 WW Tos all Package and member procedures or functions 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 jooq UDT implementation that looks like this public class AddressType extends UDTImpl lt AddressTypeRecord gt The singleton UDT instance public static final UAddressType U_ADDRESS_TYPE new UAddressTy
56. 1940 and 1920 PUBLISHED_IN NOT BETWEEN SYMMETRIC 1940 AND 1920 BOOK PUBLISHED_IN notBetweenSymmet ric 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 5 8 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 2009 2014 by Data Geekery GmbH All rights reserved Page 74 151 The jOOQ User Manual 4 5 9 IN predicate TITLE LIKE abc BOOK TITLE like abc TITLE NOT LIKE abc BOOK TITLE notLike abc 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 sThe Sign Books ESCAPE BOOK TITLE like The Sign Book TITLE NOT LIKE The Sign Book ESCAPE BOOK TITLE notLike The Sign Book Inthe above predicate expressions the exclamation mark character is passed as the escape character to escape wildcard characters _ and 1 as well as to escape the escape character itself
57. 4 2 SQL Statements jOOQ currently supports 6 types of SQL statements All of these statements are constructed from a Factory 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 4 2 1 JOOQ s DSL and non DSL API jOOQ ships with its own DSL or Domain Specific 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 LINO to SOL Here is an example to illustrate what that means gt Select all books by authors born after 1920 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 hb title fetch 2009 2014 by Data Geekery GmbH All rights reserved Page 30 151 The jOOQ User Manual 4 2 1 OOQ s DSL and non DSL API 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 Many other frameworks have similar APIs with similar feature sets Yet what makes jOO
58. 51 The jOOQ User Manual 5 11 4 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 Worrell AuthorRecord author book fetchAuthor FOREIGN KEY AUTHOR_ID REFERENCES author ID Find other books by that author List lt BookRecord gt books author fetchBookList These methods are safe for use with several foreign keys referencing the same tables CREATE TABLE book BookRecord book create fetch BOOK BOOK ID equal 5 AUTHOR_ID NUMBER 7 NOT NULL AuthorRecord author book fetchAuthorByAuthorId CO_AUTHOR_ID NUMBER 7 NOT NULL Find the author of a book les ol AuthorRecord coAuthor book fetchAuthorByCoAuthorlad FOREIGN KEY AUTHOR_ID REFERENCES author ID Find other books by those authors FOREIGN KEY CO_AUTHOR_ID REFERENCES author ID List lt BookRecord gt books author fetchBookListByAuthorlad List lt BookRecord gt books coAuthor fetchBookListByCoAuthorId Note that unlike in Hibernate jOOQ s generated 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 serializability for some more insight on attached objects 5 11 4 Non updatable records Tables w
59. 8 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 All rights reserved Page 121 151 The jOOQ User Manual 5 14 ExecuteListeners DataAccessException General exception usually originating from a java sq SQLExc
60. 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 offset 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 SOL Server 2012 Oracle 12c the SOM 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 OFFSE
61. 983 INFO TEST STATISTICS AO a See SSeS SSeS INFO READ INFO WRITE INFO DDL INFO BATCH INFO ROUTINE INFO OTHER 919 executions 7 117 executions 2 executions 4 executions 21 executions 30 executions ExecuteListener Javadoc for more details Writing a custom ExecuteListener for logging 5 15 Logging The following depicts an example of a custom ExecuteListener which pretty prints all queries being executed by jOOQ to stdout import import import import import orga org j org j org j orge ooq ExecuteContext oog conf Settings oog impl DefaultExecuteListener ooq impl Factory ooq tools StringUtils public class PrettyPrinter extends DefaultExecuteListener Hook into the query execution lifecycle before executing queries Override public void executeStart ExecuteContext ctx Create a new factory for logging rendering purposes This factory doesn t need a connection only the SQLDialect Factory factory new Factory ctx getDialect IN 26 and the flag for pretty printing new Settings withRenderFormatted true If we re executing a query abe ctx query trial System out println factory renderInlined ctx query Ji TE wetre executing a routine else if ctx routine null System out println factory renderInlined ctx routine If we re executing anything else else if
62. ABASE 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 Then create a guestbook xml that looks like this 2009 2014 by Data Geekery GmbH All rights reserved Page 17 151 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 2 5 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 DefaultGenerator gt lt name gt org jooq util DefaultGenerator lt name gt
63. AME equal Orwell Condition combinedl Condition combined2 a or b These OR connected conditions form a new condition wrapped in parentheses combinedl andNot c The left hand side of the AND NOT operator is already wrapped in parentheses The Condition API Here are all boolean operators on the org joog Condition interface 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 SQL to the right hand side andExists Select lt gt 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 om String Object 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 SQL to the right hand side orExists Select lt gt Combine con
64. ARENT_ID IS NULL StartWith DIRECTORY PARENT_ID isNull ORDER SIBLINGS BY 1 orderSiblingsBy 1 4 2 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 AUTHOR_ID As defined 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 The above example counts all books per author 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 are 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 jOOQ supports this syntax as jOOQ is not doing any checks internally about the consistence of tables fields f
65. BLE book_to_book_store name VARCHAR2 400 NOT NULL book_id INTEGER NOT NULL stock INTEGER PRIMARY KEY name book_id CONSTRAINT fk_b2bs_book_store FOREIGN KEY name REFERENCES book_store name ON DELETE CASCADE CONSTRAINT fk_b2bs_book FOREIGN KEY book_id REFERENCES book id ON DELETE CASCADE More entities types e g UDT s ARRAY types ENUM types etc stored procedures and packages are introduced for specific examples 2009 2014 by Data Geekery GmbH All rights reserved Page 12 151 The jOOQ User Manual 3 3 Different use cases for jOOQ 3 3 Different use cases for OOQ 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 Typesafe database object referencing through generated schema table column record procedure type dao pojo artefacts see the chapter about code generation Typesafe SQL construction through a complete API modelling SQL as a domain specific language in Java see the chapter about the 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 features in simpler databases see the chapter about SQL
66. 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 2009 2014 by Data Geekery GmbH All rights reserved Page 40 151 The jOOQ User Manual 4 2 2 9 The LIMIT OFFSET clause jOOQ s understanding of SELECT ORDER BY The SQL standard defines 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 4 2 2
67. DATE 3 FROM DUAL create select currentTimestamp add 3 For more advanced datetime arithmetic use the Factory s timestampDiff and dateDiff functions as well as jOOQ s built in SQL standard INTERVAL data type support 2009 2014 by Data Geekery GmbH All rights reserved Page 56 151 The jOOQ User Manual 4 4 5 String concatenation INTERVAL YEAR TO MONTH org jooq types YearToMonth E INTERVAL DAY TO SECOND org jooq types DayToSecond 4 4 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 SAA E TC FRON DUAL Or in MySQL For all RDBMS including MySQL SELECT Conca Man TB C1 EROM DUAL greate select concat 1At BM EMI 4 4 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 Factory COALESCE Get the first non null value in a list of arguments NULLIF Return NULL if both arguments are equal or the first ar
68. Data Geekery GmbH All rights reserved Page 61 151 The jOOQ User Manual 4 4 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 over on it See the manual s section about window functions for more details 4 4 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
69. E BOOK AUTHOR_ID SELECT ID FROM AUTHOR WHERE LAST_NAME Orwell Example Derived table SELECT nested FROM SELECT AUTHOR_ID count books FROM BOOK GROUP BY AUTHOR_ID nested ORDER BY nested books DESC 2009 2014 by Data Geekery GmbH All rights reserved create select from BOOK where BOOK AUTHOR_ID equal create select AUTHOR ID from AUTHOR where AUTHOR LAST_NAME equal Orwell Table lt Record gt nested create select BOOK AUTHOR_ID count as books from BOOK groupBy BOOK AUTHOR_ID asTable nested create select nested getFields from nested orderBy nested getField books Page 51 151 The jOOQ User Manual 4 3 5 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 orderBy books AUTHOR ID 4 3 5 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
70. EN 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 4 17 The CASE expression The CASE expression 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 67 151 The jOOQ User Manual 4 4 18 Sequences and serials CASE WHEN AUTHOR FIRST_NAME Paulo THEN brazilian create decode WHEN AUTHOR FIRST_NAME George THEN english
71. ESTAMP NOT NULL ese 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 SELECT FOR UPDATE statement prior to an UPDATE or DELETE statement jOOQ adds a WHERE clause to the UPDATE or DELETE statement checking for TIMESTAMP s integrity This can be best illustrated with an example Properly configure the Factory Factory optimistic new Factory 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 7i This store will 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 fie
72. HOR 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 2009 2014 by Data Geekery GmbH All rights reserved Page 46 151 The jOOQ User Manual 4 2 4 The UPDATE statement Add another author 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 AU
73. IS 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 5 7 BETWEEN predicate The 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 PUBLISHED_IN BETWEEN SYMMETRIC 1940 AND 1920 BOOK PUBLISHED_IN betweenSymmet ric
74. ML 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 a amp example a 3 src X library xml Xx log4j xml BA JRE System Library JavaSE 1 7 4 El Referenced Libraries org jooq codegen jar ora jooq meta jar la jooq jar ud log j 1 2 16 jar arg mysql connector java 5 1 15 bin jar Once the project is set up correctly with all required artefacts on the classpath you can configure an Eclipse Run Configuration for org jooq utilGenerationTool O 2009 2014 by Data Geekery GmbH All rights reserved Page 128 151 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 129 151 The jOOQ User Manual 6 1 Configuration and setu
75. 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_CONCAT 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
76. PM org joog INFO Database parameters Nov be Oa Eos 07 PM org jooqg 3 4 1 4 Step 4 Connect to your database impl JooqLogger info guestbook xml impl JooqLogger info impl JooqLogger info DIOS a SS N v T OA ITAS INFO dialect Novo ty ZO AE INFO schema NO dhe Oki LoS 07 PM org jooqg 07 PM org jooqg 07 PM org jooqg INFO target dir Nov P 20T Eo 07 PM org jooq INFO target package Nov hs Boba EAS 07 PM org jooqg impl JooqLogger info MYSQL impl JooqLogger info guestbook impl JooqLogger info Users j00Q Document s workspace MySQLTest src impl JooqLogger info test generated impl JooqLogger info LOS 2 2 sass os eS SS NOAA OA Eo INFO Emptying NOIA Eo INFO Generating NOR A Eb INFO Generating NOV O Eo 07 PM org joog Classes in 07 PM org jooqg schema 07 PM org jooqg INFO Schema generated impl JooqLogger info Users jO0Q workspace MySQLTest src test generated impl JooqLogger info Users j00Q workspace MySQLTest src test generated impl JooqLogger info Guestbook java impl JooqLogger info Total 122 18ms Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Sequences fetched 0 0 included 0 excluded Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Masterdata tables fetched 0 0 included 0 excluded Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Tables fetched 5 5 included 0
77. Page 64 151 The jOOQ User Manual CUBE with two arguments SELECT AUTHOR_ID PUBLISHED_IN COUNT FROM BOOK GROUP BY CUBE AUTHOR_ID PUBLISHED_IN 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 1 1948 1 lt GROUP BY 2 NULL 2 lt GROUP BY AUTHOR_ID 2 1988 1 lt GROUP BY 2 1990 1 lt GROUP BY 4 4 4 GROUPING SETS 4 4 14 Grouping functions The same query using UNION ALL SELECT AUTHOR_ID PUBLISHED_IN COUNT FROM BOOK GROUP BY AUTHOR_ID PUBLISHED_IN UNION ALL SELECT AUTHOR_ID NULL COUNT FROM BOOK GROUP BY AUTHOR_ID UNION ALL SELECT NULL PUBLISHED_IN COUNT FROM BOOK GROUP BY PUBLISHED_IN UNION ALL SELECT NULL NULL COUNT FROM BOOK GROUP BY ORDER BY 1 NULLS FERST 2 NULLS FIRST AUTHOR_ID PUBLISHED_IN AUTHOR_ID PUBLISHED_IN AUTHOR_ID PUBLISHED_IN AUTHOR_ID PUBLISHED_IN GROUPING SETS are the generalised way to create multiple groupings From our previous examples RO PUBLIS 2 CU PUBLIS
78. Q 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 OOQ 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 Factory create new Factory connection dialect Result lt gt result create select from AUTHOR Join BOOK on BOOK AUTHOR_ID equal AUTHOR ID fetch Unlike other simpler frameworks that use fluent APIs or method chaining jOOQ s BNF based interface hierarchy will not allow bad query syntax The following will not compile for instance Factory create new Factory connection dialect Result lt gt result create select Join BOOK on BOOK AUTHOR_ID equal AUTHOR ID 1 join is not possible here from AUTHOR Peete ls Result lt gt result create select from AUTHOR Join BOOK fetch IE RARE on is missing here Result lt gt result create select rowNumber A REE SER EU over is missing here from AUTHOR fetch History of SQL building and incremental query building a k a the non DSL API Historically OOQ started out as an object oriented SQL builder library like any other This meant that all q
79. RCENT 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 2 2 10 The FOR UPDATE clause For inter process synchronisation 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 SELEC create select FROM BOOK from BOOK WHERE ID 3 where BOOK ID equal 3 FOR UPDATE forUpdate The above example will produce a record lock locking the whole record for updates Some databases also support cell locks using FOR UPDATE O SELECT create select FROM BOOK from BOOK WHERE ID 3 where BOOK ID equal 3 FOR UPDATE OF TITLE forUpdate of BOOK TITLE 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 2009 2014 by Data Geekery GmbH All rights reserved Page 42 151 The jOOQ User Manual 4 2 2 10 The FOR UPDATE clause FOR UPDATE NOWAIT This is the default behaviour If the lock cannot be acquired the query fails immediately FOR UPDATE WAIT n Try to wa
80. RM Column_name Type Sengbh Prec Scale Eee id lint 4 NULL NULL 01 first_name Ivarchar SO NULL NULL wi last_name Ivarchar 50 NULL NULL oij date_of_birth date 4 NULL NULL sl year_of_birth int 4 NULL NULL a The correct and verbose way to do this with JDBC is as follows 2009 2014 by Data Geekery GmbH All rights reserved Page 99 151 The jOOQ User Manual 5 3 8 Later fetching ResultSet rs statement executeQuery Repeat until there are no more result sets for fe il Empty the current result set while rs next LE ea ao Ec witm be ll Get the next result set if available if statement getMoreResults rs statement getResultSet else break Be sure that all result sets are closed statement getMoreResults Statement CLOSE_ALL_ RESULTS statement close 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
81. 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 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
82. T 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 41 151 The jOOQ User Manual 4 2 2 10 The FOR UPDATE clause DBZ with OFFSET SOL Server 200 With OFESET SELECI EROM SELECT BOOK ROW_NUMBER OVER ORDER BY ID ASC AS RN FROM BOOK ASES WHERE RN gt 1 AND RN lt 3 DEA Mas OS h SOL Server 2009 with DEFSET 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 Ora Clone NE 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 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 PE
83. TE OR REPLACE FUNCTION author_exists author_name VARCHAR2 RETURN NUMBER The generated artefacts can then be used as follows This is the rendered SOL 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 modules to represent this concept even if this is rarely implemented 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 2009 2014 by Data Geekery GmbH All rights reserved Page 108 151 The jOOQ User Manual 5 8 2 Oracle member procedu
84. THOR 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 2 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 possible 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 4 2 5 The DELETE statement
85. 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 O 2009 2014 by Data Geekery GmbH All rights reserved Page 54 151 The jOOQ User Manual 4 4 2 Aliased columns SELECT BOOK ID BOOK TITLE create select BOOK i 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 TableField which is parameterised with its associated lt R extends Record gt record type See the manual s section about generated tables for more information about what is really generated by the code generator 4 4 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 JOIN BOOK ON AUTHOR ID AUTHOR_ID GROUP BY FIRST_NAME LAST_NAME Here is how it s done with jOOQ Record record create select concat AUTHOR FIRST_NAME val AUTHOR LAST_NAME as author count as books y from AUTHOR join BOOK on AUTHOR ID equal BOOK AUTHOR_ID groupBy AUTHOR FIRST_NAME AUTHOR LAST_NAME fetchAny
86. 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 It explains the central factories the supported SQL statements and various other syntax elements Code generation This section explains how to configure and use the built in source code generator SQL execution This section will get you through the specifics of what can be done with jOOQ at runtime in order to execute queries perform CRUD operations import and export data and hook into the jOOQ execution lifecycle for debugging Tools This section is dedicated to tools that ship with jOOQ such as the jOOQ console Reference This section is a reference for elements in this manual 2009 2014 by Data Geekery GmbH All rights reserved Page 2 151 The jOOQ User Manual Table of contents RN ene eee ore Gee
87. UNT 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 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
88. WHEN MATCHED THEN UPDATE SET FIRST_NAME John whenNotMatchedThenInsert AUTHOR LAST_NAME WHEN NOT MATCHED THEN INSERT LAST_NAME VALUES Hitchcock values Hitchcock MERGE Statement 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 if there is already an author called Hitchcock create mergeInto 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 Jobn Hitehcock 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 4 2 1 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 even with ON DELETE CASCADE clauses in place P
89. YPES aN Ty DESCOMVEPSION da 44 6 14 Mapping generated schemata and tables 45 Ve MOOS da 46 Th JOOQ CO ida aid 46 SIRO Crta daa 47 81 Supported RDBMS art io o a es 47 2 DAI DES AIDA Roa ie noia 48 3 24 BLOBS NO COB ii it is 48 8 2 2 Unsigned integer Pta ibid 48 8 2 3 INTERVAL data type Siona a ad 49 SILA XML data a on 49 8 25 A wise T eh aise AEO cedstactundeds tvined A AEO 50 826 CURSOR data PS aia aabi s 50 8 237 ARRAY and TABLE data POS ta DAD te ea E O a LO 50 8 3 OOQ s BNF pseudo notation 50 SAS ts O retin O acest A A A samen ee eae 51 O 2009 2014 by Data Geekery GmbH All rights reserved Page 6 151 The jOOQ User Manual 1 Preface 1 Preface jOOQ s reason of being compared to JPA Java and SQL have come a long way SQL is an ancient 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 ca
90. a flag in your Factory s Settings and all queries produced by that factory will be executed as static statements with all bind values inlined An example is given here This Factory executes PreparedStatements Factory prepare new Factory connection SQLDialect ORACLE This Factory exeecutes static Statements Factory inlined new Factory 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 val 1 equal 1 Eetchad SELECT 1 FROM DUAL WHERE 1 1 inlined select val 1 where val 1 equal 1 fetch 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 able 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 ca
91. a given numeric value G REATEST 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 R OUND 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 Factory Javadoc for more details 4 4 8 Bitwise functions Interestingly bitwise functions and bitwise arithmetic is not very popular among SQL databases 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 i NNnDWWWWWWOW 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 a
92. able and view columns that are used as version fields for optimistic locking several Java regular expressions separated by comma 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 several Java regular expressions separated by comma 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 trues gt lt unsignedTypes 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 comb
93. aded operator BOOK ID BOOK AUTHOR_ID 3 4 Using the overloaded operator BOOK TITLE I Arabet i A 3y Using the overloaded operator lil from BOOK No need to use parentheses or here leftOuterJoin Tis f select x ID x YEAR_OF_BIRTH Dereference fields from aliased table from x iy limit 1 asTable x getName if Ha 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 println r lid Ma For more details about jOOQ s Scala integration please refer to the manual s section about SQL building with Scala 3 6 Dependencies jOOQ has no dependencies on any third party libraries This rule has some exceptions logging APIs are referenced as optional dependencies jOOQ tries to find slf4j or log4j on the classpath If it fails it will use the java util logging Logger Oracle ojdbc types used for array creation are loaded using reflection Small libraries with compatible licenses are incorporated into jOOQ OOR jOOU OpenCSyV json simple commons lang javax persistence and javax validation will be needed if you activate the relevant code generation flags Build your own In order to build jOOQ please download the sources from https github com jOOQ jOOQ and use Maven to build it pre
94. alue will render a question mark When the query binds its variables each bind value will generate the appropriate bind value index O 2009 2014 by Data Geekery GmbH All rights reserved Page 80 151 The jOOQ User Manual 4 7 2 Named parameters Extract bind values from a query Should you decide to run the above query outside of jOOQ 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 1 2 Named parameters
95. arameters AS A NN ANZ Named Pa aMSN A o aan 477 3 AMINE CID A Sione i e ciate dete haces ai eatin AWM HAG ai sea neeheteh ded a eh Mie Raat ATA SQLinjection ana plait SOL Query Part Sarianna Eaa aA ORO A OROA OE ONE AEA AOE 82 O 83 LS a cid TAA 83 A A NN 84 A VanlabDle BIN GUNS va sien tr vetsccd a EN caceeds E ceca N EE E E AA A deinen sansa nasi eeets 85 ABA EXE OOO WITH CUSEOIM WS cias 86 O 2009 2014 by Data Geekery GmbH All rights reserved Page 4 151 The jOOQ User Manual 4 8 5 Plain SQL QueryParts 4 8 6 Serializability 4 9 SQL building in Scala DOL ED CULO st A 51 Comparison between JOOO and JDBC ii aaa 5 2 Query vs ResultQuery 5 Se ROU a a e a o O A MaS 5 3 1 Record vs TADlO RECO Ola cional de cedida ida chain Dad en sda DAL dida paa 532 Atrays MS O a dice DiS SA RECOPO Alles ena n a a a old ole Abt a a E A Wa De le ast ves ideas vasstdscavevadyeucastleevsesst T ia sdovaiuesselactues Wtaaia seiscnsevdta seaieaiertala E A ieiusatauet E OOS EEEN esas evga uses dita AAE ead tuts ead ias 5 3 6 RN E RS SN EAN RESUME aa ia 5391 0 Data De CONV latina 5 A Static statements vs Prepared Statements zueneen AEN r EA edad e A A OI 5 5 Reusing a Query s PreparedStatement 5 0 USTMEJDBE Datel ODOT MS cerca sies tots eects mises EET ous eta EE E POS AOS NN 5 8 Stored ProcedurescanG FUNGUO ar haus E a a O a lana EA A 5 85 Oracle Packages sents a ET 5 8 2 Oracle member DKOCC CUES ran
96. at both tables contain AUTHOR_ID columns AP naturaldoini 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 OOQ 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 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 2 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 c
97. atIndentEnd 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 boolean renderNamedParams CastMode castMode RenderContext castMode CastMode mode Boolean Cast RenderContext castModeSome SQLDialect dialects 2009 2014 by Data Geekery GmbH All rights reserved Page 83 151 The jOOQ User Manual 4 8 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
98. bases 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 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 2009 2014 by Data Geekery GmbH All rights reserved Page 39 151 The jOOQ User Manual 4 2 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
99. be combinable by such a set operator each subselect must return a table expression of the same arity 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 don 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 INTERSECT is the operation that produces only those s that are returned by both subselects EXCEPT is the operation that returns only those s that are returned exclusively in the first subselect Both operators will remove duplicates from their results The SQL standard allows to specify the ALL keyword 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
100. ching 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 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 asynchrono
101. classname org jooq util GenerationTask gt lt classpath gt lt fileset dir path to jooq distribution gt lt include name jooq 2 6 4 jar gt lt include name jooq meta 2 6 4 jar gt lt include name jooq codegen 2 6 4 jar gt lt fileset gt lt fileset dir path to mysql driver gt lt include name mysql driver jar gt lt fileset gt lt classpath gt lt taskdef gt lt Run the code generation task gt lt target name generate test classes gt lt generate classes jdbcurl jdbc mysql localhost test jdbcuser root jdbcpassword generatordatabaseinputschema test generatortargetpackage org jooq test generatedclasses generatortargetdirectory basedir src gt lt target gt Note that when running code generation with ant s lt java gt task you may have to set fork true SAMBA code generation task gt lt target name generate test classes gt lt java fork true classname org jooq util GenerationTool gt lt java gt lt target gt Integrate generation with Maven Using the official OOQ codegen maven plugin you can integrate source code generation in your Maven build process 2009 2014 by Data Geekery GmbH All rights reserved Page 131 151 The jOOQ User Manual 6 1 Configuration and setup of the generator lt plugin gt SIS PECES Ven code generator pig in lt groupId gt org jooq lt grouplId gt lt artifactId gt jooq codegen mave
102. ct BOOK ID BOOR TITLE from BOOK order by BOOK ID asc Limit offset 7 gt with bind values 2 select BOOK rb BOOR TITLEN from BOOK order by BOOK IDM asc limit 2 offset 1 Query executed lt Total 1T 439ms Fetched result SS a mos E 2 Animal Farm ze 3 0 Alquimistal E 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 16 Performance considerations Many users may have switched from higher level abstractions such as Hibernate to jOOQ because of Hibernate s hard to manage performance when it comes to large database schemas and complex second level caching strategies jOOQ 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 124 151 The jOOQ User Manual 5 16 Performance considerations t takes some time to con
103. ct ORACLE settings Run queries that omit rendering schema names create select from AUTHOR where LAST_NAME equal Poe 4 7 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 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 82 151 The jOOQ User Manual 4 8 QueryParts 4 8 QueryParts A org joog Query and all its contained objects is a org joog 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
104. ctory Factory create new Factory 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 factory Factory create new Factory connection SQLDialect ORACLE settings Run queries with the mapped factory create selectFrom AUTHOR fetch The query executed with a Factory 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
105. data to a Java enum When the underlying table changes frequently those updates will not be reflected by the statically generated code Also be aware that it will be difficult to perform actual JOIN operations on the underlying table with jOOQ once the master data type is generated 6 13 Custom data types and type conversion When using a custom type in jOOQ you need to let OOQ know about its associated org joog 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_O 2009 2014 by Data Geekery GmbH All rights reserved Page 144 151 The jOOQ User Manual 6 14 Mapping generated schemata and tables 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 converter Note a custom type can only have one converter in jO00Q 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 forcedT
106. 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 Field lt Integer gt intArray The above array type is supported by these SQL dialects 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 2009 2014 by Data Geekery GmbH All rights reserved Page 150 151 The jOOQ User Manual 8 4 Credits 8 4 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 domain as jOOQ jOOQ has gotten great inspiration from pre existing tools a
107. 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 OOQ to wrap strings literals and other user defined objects into an object oriented type safe AST modelling your SQL statements An example
108. ding on some conditions if Orwell equals book fetchAuthorByAuthorId getLastName book delete a 5 11 2 IDENTITY values Many databases support the concept of IDENTITY values or SEQUENC E 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 Messi CONSTRAINT pk_book PRIMARY KEY id O 2009 2014 by Data Geekery GmbH All rights reserved Page 116 151 The jOOQ User Manual 5 11 3 Navigation methods If you re using jOOQ s code generator the above table will generate a org joog UpdatableRecord with an IDENTITY column This information is used by jOOQ internally to update IDs after calling store BookRecord book create newRecord BOOK book settitlie isa book store The generated ID value is fetched after the above INSERT statement System out println book getld 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
109. 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 JOOQ 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 new Factory SQLDialect ORACLE selectOne getSQL SELECT 1 new Factory 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 2 2 3 The JOIN clause jOOQ supports many different types of standard SQL JOIN operations O 2009 2014 by Data Geekery GmbH All rights reserved Page 34 151 The jOOQ User Manual INNER JJOIN z EFT OUTER JOIN E RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN NATURAL JOIN a ATURA FT OUTER JOIN NATURAL RIGHT OUTER JOIN All of these JOIN methods can be called on org jooq Table types or directly after the convenience The following example joins AUTHOR and BOOK Factory create new Factory 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 SEetoh yz I cali a on the type returned by il Resul
110. ditions 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 Factory not Condition 4 5 3 Comparison predicate In SQL comparison predicates are formed using common comparison operators 2009 2014 by Data Geekery GmbH All rights reserved Page 71 151 The jOOQ User Manual 4 5 4 Quantified comparison predicate 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 operator overloading hence these operators are also implemented as methods in jOOQ like any other SQL syntax elements The relevant parts of the org joog Field interface are these eq or equal 1 gt some bind value eq or eg al Fielda lt T gt i some column expression eq or equal Select lt T gt ii some scalar SELECT statement ne or nothqual T lt gt some bind value ne or notEqual Field lt T gt lt gt some column expression ne or notEqual Select lt T gt lt gt some scalar SELECT statement lt or lessThan T fm so
111. 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 rowIndex 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 113 151 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
112. e 4222 IMESFROMAClAUSE dai i 34 4 2 2 3 The JOIN clause adds 34 ARA S WHERE aUssi mea e aaah 36 A225 Me CONNECT BY ca US Adina iaio ide 37 A276 O OS 38 AUD 2 72 MAES HAVING ClAUSE ia o Belge sous a Sabbaths e aaa aaa aaa aa aa aaa a 39 AD 278 MORDER e A 39 22 ne LIMIT OFRSE A e a elit ANA 41 4 2 2 10 The FOR UPDATE ClaUSB cinc iieiea ainai eeii i a das ada ada EEES DONDE UA di eta evaedd 42 42211 UNION INTERSECTON and EXCEP ic adi 44 ADRIAN ed de 45 RI THE ANSERT Stabe me rites cies cis aise ales chia as E E E E E 45 AZAR WRDALE StateMme niin ai dida LIA 47 AD ne DELETE Statement e porua ai ia ia a dado baaa 47 O 2009 2014 by Data Geekery GmbH All rights reserved Page 3 151 The jOOQ User Manual 46 The MERGE State Menta asa 4 2 7 The TRUNCATE statement 4 3 Table expressiONS coccininininicin 4 3 1 Generated Tables E E NN 4 3 3 Joined tables 4 3 4 Nested SELECTS 4 3 3 TheOrade Tte PIVOT ClAUSe nin eui iaaa setos ests E o Rabi 52 4 3 6 OOQ S rela tionaldivision Synta X sasa a a di 52 ABST Aray and CUrSor UNNESTNE insyn aoe a a ney call N E aed aed adh ae E 53 A Eea AT A E 53 AA COMAS a A a a aT aai 54 E cen cota diania aa a a aa a 54 AAP Al ASCO COMMIS a a a A NON 55 AA a e e e 55 AAA Arithmetic expr ESSOR deve tube eepeaih a iii 56 4A A ON 57 AA O a o ahs 57 4 4 7 Numeric functi 4 4 8 PE Ea ETETE E ety
113. e creating 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 gt 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 _ SelectFromStep sl create celeste SelectJoinStep s2 sl1 from BOOK SelectJoinStep s3 s1 from AUTHOR The following can be said sl s2 The internal object is always the same s2 s3 The internal object is always the same Mutability may be removed in a future version of jOOQ 4 2 2 The SELECT statement When you don t just perform CRUD i e SELECT FROM your_table WHE RE ID you re usually generating new record types using custom projections With jOOQ this is as intuitive as if using SQL directly A more or less complete example of the standard SQL syntax plus some extensions is provided by a query like this get all authors first and last names and the number And with j00Q of books 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 t
114. e expressions are incompatible with most other QueryParts but they can be used as a basis for constructing various conditional expressions such as comparison predicates IN predicates or the degree 2 row value expression only OVERLAPS predicate See the relevant sections for more details about how to use row value expressions in predicates Using row value expressions in UPDATE statements The UPDAT E statement also supports a variant where row value expressions are updated rather than single columns See the relevant section for more details 4 5 Conditional expressions Conditions O 2009 2014 by or conditional expressions are widely used in SQL and in the jOOQ API They can be used in Data Geekery GmbH All rights reserved Page 69 151 The jOOQ User Manual 4 5 1 Condition building 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 DE The CONNECT BY clause of a SELECT statement The HAVING clause of a SELECT statement The MERGE statement s ON clause ETE statement Boolean types in SQL Before SQL 1999 boolean types did not really exist in SQL They were modelled by O and 1 numeric char va
115. e involving a Converter for java util GregorianCalendar 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 i 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 2009 2014 by Data Geekery GmbH All rights reserved Page 102 151 The jOOQ User Manual 5 4 Static statements vs Prepared Stateme
116. e lt interfaces gt lt Generate DAOs in addition to POJO classes Defeutts 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 Defaults to farget gt lt jpaAnnotations gt false lt jpaAnnotations gt lt Annotate POJOs and Records with JSR 303 validation annotations Defaults to fakse s gt lt validationAnnotations gt false lt validationAnnotations gt lt generate gt 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 2009 2014 by Data Geekery GmbH All rights reserved Page 136 151 The jOOQ User Manual 6 4 Generated tables 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 t
117. e 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 3 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 SOL 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 be 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 TODO document this O 2009 2014 by Data Geekery
118. early communicates its underlying intent will be very hard to add later on e g how would you model Oracle s partitioned outer join syntax How would you model ANSI ISO SQL 1999 grouping sets How can you support scalar subquery caching etc jOOQ has come to fill this gap jOOQ 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 7 151 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 license 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
119. ect 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 The DISTINCT keyword can be included in the method name constructing a SELECT clause 2009 2014 by Data Geekery GmbH All rights reserved Page 33 151 The jOOQ User Manual 4 2 2 2 The FROM clause SELECT DISTINCT BOOK TITLE Select lt gt selectl create selectDistinct BOOK TITLE 4 2 2 2 The FROM clause The SQL FROM clause allows for specifying any number of table expressions to select 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
120. ed Page 139 151 The jOOQ User Manual 6 7 Generated Interfaces dateAsTimestamp This influences all relevant getters and setters unsignedTypes This influences all relevant getters and setters interfaces If interfaces are generated POJOs will implement them 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 are 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 Every table in your database will generate an interface that looks like this public interface IBook extends java io Serializable Every column generates a getter and a setter public void setId Integer value public Integer getId el 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 Ever
121. ed 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 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 Result lt Record gt result2 query fetch This will lazily create a new PreparedStatement 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 2009 2014 by Data Geekery GmbH All rights reserved Page 104 151 The jOOQ User Manual 5 6 Using JDBC batch operations 5 6
122. ed 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 LISTAGG 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 a
123. edures 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 3 8 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 53 151 The jOOQ User Manual 4 4 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 new Fac
124. ee Connection vs DataSource for more details java sql DataSource An optional JDBC DataSource that will be re used for the whole lifecycle of your Factory 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 conf Settings An optional runtime configuration see Custom Settings for more details As a Configuration object a Factory can construct statements for later execution An example is given here The Factory is configured with a Connection and a SQLDialect Factory create new Factory connection dialect This select statement contains an internal reference to the Factory Select lt gt select create selectOne Using the internally referenced Factory the select statement can now be executed Result lt gt result select fetch 4 1 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 joog SQOLDialect enum type The SQL dialect is one of the main attributes of a Factory Queries created from such factories will assume dialect specific behaviour when rendering SQL and binding bind
125. eel 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 A short example jOOQ application in Scala might look like this 2009 2014 by Data Geekery GmbH All rights reserved Page 22 151 The jOOQ User Manual 3 6 Dependencies import collection JavaConversions _ Import implicit defs for iteration over org jooq Result iy import java sql DriverManager Hae ig import org jooq _ Ha import org jooq impl _ il import org jooq impl Factory _ He import org jooq scala example h2 Tables _ iy import org jooq scala Conversions _ Import implicit defs for overloaded j000 SQL operators iy object Test Hse def main args Array String Unit if val c DriverManager getConnection jdbc h2 test sa Standard JDBC connection val f new Factory c SOLDialect H2 ef val x AUTHOR as x SQL esque table aliasing for lig lt lt f Iteration over Result r is an org jooq Record select li BOOK ID BOOK AUTHOR_ID Using the overlo
126. egate functions Aggregate functions work just like functions even if they have a slightly different semantics Here are some example aggregate functions from the Factory 2009 2014 by Data Geekery GmbH All rights reserved Page 60 151 The jOOQ User Manual 4 4 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 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 se
127. eloper 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 jooq 2 6 4 Jjar The main library that you will include in your application to run jOOQ joog meta 2 6 4 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 2 6 4 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 ri
128. ement 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 2009 2014 by Data Geekery GmbH All rights reserved 2 Copyright License and Trademarks Page 9 151 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 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
129. en 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 methoq 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 method 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 SQL using bind values The value 5 is bound to the first variable Animal Farm to the second variable create selectFrom BOOK where BOOK ID AND TITLE 5 Animal Farm Plain SQL 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 8 6 Serializability The only transient non serializable element in any jOOQ object is the Factory s underlying java sql Connection When you want t
130. eption 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 jOOQ 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 jOOQ Factory Settings let you specify a list of org joog ExecuteListener classes 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 ResultSet to jOOQ in apropriate methods For convenience and better backwards compatibility consider extending org joog impl DefaultExecuteListe
131. er Manual 5 3 9 ResultSet fetching 5 3 9 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 3000 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 asilos cnn 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 joog 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 jO0Q 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 jOOQ Result ResultSet rs connection createStatement executeQuery SELECT FROM BOOK As a Resul
132. essException org joog 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 even 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
133. ession Constant TRUE and FALSE conditional expressions 2009 2014 by Data Geekery GmbH All rights reserved Page 70 151 The jOOQ User Manual 4 5 2 AND OR NOT boolean operators Connect conditions using boolean operators Conditions can also be connected using boolean operators as will be discussed in a subsequent chapter 4 5 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 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 Orwel1 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 Condition b Condition c BOOK TITLE equal Animal Farm BOOK TITLE equal 1984 AUTHOR LAST_N
134. esult 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 and records records with named columns In Java this is not possible 2009 2014 by Data Geekery GmbH All rights reserved Page 93 151 The jOOQ User Manual 5 3 2 Arrays Maps and Lists to the same extent Yet still sometimes you wish to use strongly typed records when you know 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 println Title book getTitle System out println Published in book getPublishedIn When you use the Factory selectFrom method OOQ will return the record type supplied wi
135. excluded Nov 1 2011 7 25 07 PM org jooq impl JooqgLogger info INFO Generating tables Users jO0Q workspace MySQLTest src test generated tables Nov 1 2011 7 25 07 PM org jooq impl JooqgLogger info INFO ARRAYs fetched 0 0 included 0 excluded Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Enums fetched 0 0 included 0 excluded Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO UDTs fetched 0 0 included 0 excluded Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Generating table Posts java Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Tables generated Total 680 464ms 558 284ms Nov 1 2011 7 25 07 PM org jooq impl JooqLogger info INFO Generating Keys Users jO0Q workspace MySQLTest src test generated tables Nov 1 2011 7 25 08 PM org jooq impl JooqLogger info INFO Keys generated TO TARA S67 mse 38 157ms Nov 1 2011 7 25 08 PM org jooq impl JooqgLogger info INFO Generating records Users jO0Q workspace MySQLTest src test generated tables records Nov 1 2011 7 25 08 PM org jooq impl JooqLogger info INFO Generating record PostsRecord java Nov 1 2011 7 25 08 PM org jooq impl JooqLogger info INFO Table records ge erated y Total 782 545m53 63 924ms Nov 1 2011 7 25 08 PM org jooq impl JooqLogger info INFO Routines fetched 0 0 included 0 excluded Nov 1 2011 7 25 08 PM org jooq impl JooqLogger info INFO Packages fetched 0 0 included 0 excluded Nov
136. ferably in Eclipse 2009 2014 by Data Geekery GmbH All rights reserved Page 23 151 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 OOQ 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 notation 4 1 The Factory class 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 joog impl Factory class is the main class from where you will create all jOOQ objects The Factory serves two types of purposes It se
137. 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 120 151 The jOOQ User Manual 5 13 Exception handling An example generated BookDao class public class BookDao extends DAOImp1 lt BookRecord Book Integer gt Columns with primary unique keys produce fetchOne methods public Book fetchOneById Integer value Other columns produce fetch methods returning several records public List lt Book gt fetchByAuthorId 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 a Factory Factory create new Factory connection SQLDialect ORACLE Initialise the DAO with the Factory BookDao bookDao new BookDao create Start using the DAO Book book bookDao findById 5 Modify and update the POJO book setTitle 1984 book setPublishedIn 194
138. gationMethods This controls wh ether navigation methods will be generated or not daos Records are a pre requisite for DAOs If DAOs are generated records are generated as well jpaAnnotations JPA annotations are interfaces If interfaces are generated records will implement them used on generated records Flags controlling record generation Record generation can be deactivated usi ng the records flag 6 6 Generated POJOs JPA javax javax public annotations can be generated optionally persistence Entity persistence Table name BOOK class Book implements java io Serializable schema TEST Every table in your database will generate a POJO implementation that looks like this al 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 titre Every column generates a getter and a setter Id Column name Override public Integer getId return this id Ip Override public void setId Integer id this id aid Jif y Unique true nullable false precision 7 Flags influencing generated POJOs These flags from the code generation configuration influence generated POJOs 2009 2014 by Data Geekery GmbH All rights reserv
139. ggregate functions These functions have a window function analytical function variant as well For example Tl 2009 2014 by Data Geekery GmbH All rights reserved Page 63 151 The jOOQ User Manual 4 4 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 4 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 take 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 CO
140. ghts reserved Page 126 151 The jOOQ User Manual 6 1 Configuration and setup of the generator 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 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 generator gt lt database gt lt The database dialect from jooq meta Available dialects are named org util database database Database Known values are org jooq util ase ASEDatabase to be used with Sybase ASE org jooqg util cubrid CUBRIDDatabase org jooq util db2 DB2Database org jooqg util derby DerbyDatabase org jooqg util h2 H2Database org joog util hsqldb HSQLDBDatabase org jooqg util ingres IngresDatabase org jooqg util mysql MySQLDatabase org joog util oracle OracleDatabase org jooqg util postgres PostgresDatabase org joog util sqlite SQLiteDatabase org jooqg
141. gument 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 Factory Javadoc for more details 4 4 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 This is a list of numeric functions supported by jOOQ s Factory 2009 2014 by Data Geekery GmbH All rights reserved Page 57 151 The jOOQ User Manual 4 4 8 Bitwise functions BS Get the absolute value of a value A ACOS Get the arc cosine of a value A SIN Get the arc sine of a value ATAN Get the arc tangent of a value ATAN2 Get the atan2 function of two values Q C 2 E E E D EXP Calculate e value EIL Get the smalles integer value larger than a given numeric value OS Get the cosine of a value OSH Get the hyperbolic cosine of a value OT Get the cotangent of a value OTH Get the hyperbolic cotangent of a value EG Transform radians into degrees FLOOR Get the largest integer value smaller than
142. he actual singleton org joog 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 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 Z 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 UpdatableTableImpl 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 SQLDataType INTEGER this public final TableField lt BookRecord Integer gt AUTHOR_ID createField AUTHOR_ID SQLDataType INTEGER this public final TableField lt BookRec
143. he second and third row locking the rows for a subsequent update whew Factory create new Factory 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 LorUpdate Details about the various clauses of this query will be provided in subsequent sections 2009 2014 by Data Geekery GmbH All rights reserved Page 32 151 The jOOQ User Manual 4 2 2 1 The SELECT clause 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 Factory public lt R extends Record gt SimpleSelectWhereStep lt R gt selectFrom Table lt R g
144. ida 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 79 151 The jOOQ User Manual 4 7 Bind values and parameters 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 7 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 Prepar
145. ill 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 relational division for details on how jOOQ emulates this operation jOOQ supports all of these JOIN types except semi join and anti join directly on any table expression 2009 2014 by Data Geekery GmbH All rights reserved Page 50 151 The jOOQ User Manual 300Q s relational division conve
146. ined 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 master data table enum classes gt lt masterDataTables gt lt masterDataTables 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 Master data types Custom types joog codegen configuration Also you can add some optional advanced configuration parameters for the generator 2009 2014 by Data Geekery GmbH All rights reserved Page 135 151 The jOOQ User Manual i These properties canbe added to the geheraterelement gt lt generate gt lt Primary key foreign key relations should be generated and used This is a prerequisite for various advanced features Death Syst Ont scr ae lt relations gt true lt relations gt lt Generate navigation methods to navigate foreign key relationships directly from Record classe
147. ing 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 in jOOQ and how you can control the way they are rendered and bound to SQL 4 7 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 Factory val to explicitly create an indexed bind value You don t have to worry about that index When the query is rendered each bind v
148. ing 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 parts 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 Br
149. ion 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 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 6 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 77 151 The jOOQ User Manual 4 6 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
150. 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 2009 2014 by Data Geekery GmbH All rights reserved Page 10 151 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 b
151. it 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 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 skipLocked 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 updateXxxx methods to produce a cell lock row lock Here s a simplified example in JDBC try PreparedStatement stmt connection prepareStatement SELECT FROM author WHERE id IN 3 4 5 ResultSet TYPE_SCROLL_SENSITIVE ResultSet CONCUR_UPDATABLE ResultSet rs stmt
152. ithout UNIQUE keys 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 5 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 jOOQ will run a SELECT 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 mean time The record is successfully stored deleted if the record had not been modified in the
153. lass 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 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 utilHashMap 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 void setTitle 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 myBooks 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 give
154. lause 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 2009 2014 by Data Geekery GmbH All rights reserved Page 36 151 The jOOQ User Manual 4 2 2 5 The CONNECT BY clause 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 4 2 2 5 The CONNECT BY clause The Oracle database knows a very succinct syntax 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 SECS EROM Te WHERE CONNECT BY f NOCYCLE condition AND condition START WITH condition MACU TEY yc ORDERS SIBLINGS EA 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 lessO0rEqual 5 Here s a more complex example where you can recursively fetch directories in your database and concatenate them to
155. lds 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 119 151 The jOOQ User Manual 5 11 6 Batch execution 5 11 6 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 Factory as shown in the following example Fetch a bunch of books List 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 key Generated DAOs implement a common jOOQ type called org joog DAO This type contains the following methods
156. lease consider your database manual to learn more about its TRUNCATE implementation The TRUNCATE syntax is trivial TRUNCATE TABLE AUTHOR create truncate AUTHOR execute 4 FROM AUTHOR TRUNCATE is not supported by Ingres and SQLite jOOQ will execute a D statement instead 2009 2014 by Data Geekery GmbH All rights reserved Page 48 151 The jOOQ User Manual 4 3 Table expressions 4 3 Table expressions The following sections explain the various types of table expressions supported by jOOQ 4 3 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 SQL 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 can access table columns by dereferencing th
157. lity 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 76 151 The jOOQ User Manual 4 5 11 OVERLAPS predicate 4 5 11 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 vers
158. lled JPQL HQL JDOQL and various other inferior query languages jOOQ has come to fill this gap jOOQ s reason of 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 than simple CRUD and the occasional JOIN people will want to profit from the expressivity of SQL Due to the relational nature of SQL this is quite different from what object oriented and partially functional languages such as C Scala or Java can offer 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 cl
159. lock 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 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 Java usually being on the right In SQL Using j00Q0 SELECT 1 FROM DUAL create select 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 If Nothing Siles ale specified assume that idas Oracle syntax is used SELECT 1 FROM DUAL 2009 2014 by Data Geekery GmbH All rights reserved Page 11 151 The jOOQ User Manual 3 2 The sample database used in this manual Java assumptions gt Whenever you see standalone functions assume they were static imported from org jooq impl Factory exists max min val inline correspond to Factory exists Factory max Factory 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
160. 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 generated 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 Factory 2009 2014 by Data Geekery GmbH All rights reserved Page 28 151 The jOOQ User Manual 4 1 4 Runtime schema and table mapping Using a default schema Another option to switch schema names is to use a default schema for the Factory s underlying Connection Many RDBMS support a USE or SET SCHEMA command which you can call like this Set the default schema Schema MY_BOOK_WORLD create use MY_BOOK_WORLD Run queries with factory having a default schema create selectFrom AUTHOR fetch Queries generated from the above Factory will produce this kind of SQL statement the schema name is omitted from all SQL constructs SELECT FROM AUTHOR 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 fa
161. lues 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 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 5 1 Condition building With jOOQ 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 TITLE TITLE Animal Farm BOOK TITLE equal Animal Farm Animal Farm BOOK TITLE notEqual Animal Farm Create conditions from the Factory There are a few types of conditions that can be created statically from the Factory 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 expr
162. lying JDBC ResultSet finally if cursor null cursor close As a org jooq 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 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 joog 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 custom POJO types 5 3 7 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 gp help author Name Owner Object_type Object_status Create_date Seas Ele user table none gt ll 22 2011 TI O
163. m 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 3 7 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 supported 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 proc
164. me bind value lt or lessThan Field lt T gt UNS some column expression lt or lessThan Select lt T gt iil 2 some scalar SELECT statement le or lessOrEqual TI lt some bind value Te or TessOrequal Hielda lt li gt lt some column expression le or lessOrEqu ual Select lt T gt lt some scalar SELECT statement gt or greaterThan T it s some bind value gt or greaterThan Field lt T gt ii S some column expression gt or greaterThan Select lt T gt T s 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 T gt gt some scalar SELECT statement Note that 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 NULL in jOOQ s comparison predicates jOOQ has a special way of dealing with null bind values when you pass them to comparison predicates equal and notEqual For convenience jOOQ will render IS NULL or IS NOT NULL predicates jOOQ s convenience methods using comparison operators In addition to the above jOOQ provides a few convenience methods for common operations performed on strings using comparison predicates case insensitivity
165. n 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 book1 setTitle 1984 bookl store 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 POJOs jOOQ will assume the record is a new record It 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
166. n expressions in Select lt gt Construct an IN predicate from a subselect notIn Collection lt T gt Construct a NOT IN predicate from a collection of bind values O aN Construct a NOT IN predicate from bind values notin rueld e Construct a NOT IN predicate from column expressions not In Select lt gt Construct a NOT IN predicate from a subselect A sample IN predicate might look like this TTTLE IN Animal Farm 1984 BOOK TITLE in Animal Farm 1984 TITLE NOT IN Animal Farm 1984 BOOK TITLE notIn Animal Farm 1984 2009 2014 by Data Geekery GmbH All rights reserved Page 75 151 The jOOQ User Manual 4 5 10 EXISTS predicate 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 l BANDA l C Substitute C fOr NULL yo 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 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 expres
167. n here 2009 2014 by Data Geekery GmbH All rights reserved Page 97 151 The jOOQ User Manual 5 3 6 Lazy fetching A mutable POJO class public class MyBook public ant 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 Initialise a Factory Factory create new Factory connection SQLDialect ORACLE Initialise the DAO with the Factory BookDao bookDao new BookDao create 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 More complex data structures jOOQ currently doesn t support more com
168. n lt artifactId gt lt version gt 2 6 4 lt version gt si 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 a PostgreSQL database gt 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 dependency gt lt dependencies gt lt Specify the plugin configuration The configuration format is the same as for the standalone code generator gt lt configuration gt lt JDBC connection parameters gt lt jdbc 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 jdbc gt US Generator parameters 7 lt generator gt lt name gt org jooq util DefaultGenerator lt name 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 ge
169. nd 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 JPA The de facto standard in the javax persistence packages supplied by Oracle Its annotations are useful to jOOQ as well OneWebSQL A commercial SQL abstraction API with support for DAO source code generation which was integrated also in 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 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 151 151
170. ndowOverStep 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 62 151 The jOOQ User Manual 4 4 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_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 advanc
171. ner 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 lt settings gt lt executeListeners gt lt executeListener gt com example StatisticsListener lt executeListener gt lt executeListeners gt lt settings gt And log results any time with a snippet like this 2009 2014 by Data Geekery GmbH All rights reserved Page 122 151 The jOOQ User Manual log info STATISTICS f r ExecuteType type log info type name eet 3 MG Exegcutelype values StatisticsListener STATISTICS get type executions This may result in the following log output Please read the Biy T52 Sa SAA Sy T527 Soy 252 982 982 983 983 983 983 983
172. nerator gt lt configuration gt lt plugin gt Migrate properties files from OOQ 1 7 early versions of jOOQ 2 0 x Before jOOQ 2 0 4 the code generator was configured using properties files These files are still supported for source code generation but their syntax won t be maintained any longer If you wish to migrate to XML you can migrate the file using this command on the command line org joog util GenerationTool jooq config properties migrate Using the migrate flag OOQ will read the properties file and output a corresponding XML file on system out Use jOOQ generated classes in your application Be sure both jooq 2 6 4 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 O 2009 2014 by Data Geekery GmbH All rights reserved Page 132 151 The jOOQ User Manual 6 2 Advanced generator configuration 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 lt 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 DefaultGenerator gt lt name gt org jooq util DefaultGenerator lt name gt
173. ng 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 SOL 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 getSOL 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 000 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 types An example is given here 2009 2014 by Data Geekery GmbH All rights
174. nience 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 4 3 4 Nested SELECTs 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 3 4 Nested SELECTs A SE ECT statement can appear almost anywhere a table expression can Such a nested SELECT 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 FROM BOOK WHER
175. notated POJO class public class MyBook Column name ID public int myld Column name TITLE public String myTitle MyBook myBook create select from BOOK fetchAny into MyBook class List lt MyBook gt myBooks List lt MyBook gt myBooks create select from BOOK fetch into MyBook class The various into methods allow for fetching records into your custom POJOs 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 aid public String title The various into methods allow for fetching records into your custom POJOs MyBook1 myBook create select from BOOK fetchAny into MyBook1 class List lt MyBook1 gt myBooks create select from BOOK fetch into MyBook1 class List lt MyBook1 gt myBooks create select from BOOK fetchInto MyBook1 class Please refer to the Record into Javadoc for more details O 2009 2014 by Data Geekery GmbH All rights reserved Page 96 151 The jOOQ User Ma
176. nts Define your Enum public enum YNM YES NO MAYBE 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 7 book get little break case NO System out println I didn t like this book z Ut book getTitle break case MAYBE System out printin 1 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 4 Static statements vs Prepared Statements With JDBC you have full control over your SQL statements You can decide yourself if you want to execute a Static java sql 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 jOOQ this is easier As a matter of fact it is plain simple With jOOQ you can just set
177. nual 5 3 5 POJOs 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 int id public final String title public MyBook2 int id String title this 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 i With annotated immutable 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 c
178. o 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 values 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 aea WHERE AUTHOR ID koeoll This is how jOOQ binds values on such a condition 2009 2014 by Data Geekery GmbH All rights reserved Page 85 151 The jOOQ User Manual 4 8 4 Extend jOOQ with custom types 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 cust
179. o execute queries after de serialisation or when you want to store refresh delete Updatable Records you will have to re attach them to a Factory 2009 2014 by Data Geekery GmbH All rights reserved Page 87 151 The jOOQ User Manual 4 9 SQL building in Scala 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 Factory create new Factory connection SQLDialect ORACLE create attach select Automatically attaching QueryParts Another way of attaching QueryParts automatically or rather providing them with a new java sal Connection at will is to hook into the Execute Listener support More details about this can be found in the manual s chapter about ExecuteListeners 4 9 SQL building in Scala jOOQ Scala is a maven module used for leveraging some advanced Scala features for those users that wish to use OOQ 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 jooq Field type with SQL esque operators The following depicts a trait which wraps all fields A Scala esque representation of link org jooq Field adding overloaded opera
180. o 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 4 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 expressions can be used if they are unnested 4 4 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
181. om QueryParts and plain SOL QueryParts to learn about how to write your own query parts in order to extend jOOQ 4 8 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 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 TableRecordImp1 lt R gt These classes are declared public and covered by jOOQ s integration 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 OS correct order passing the render context 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 LE Every Que
182. ommon integration database In the code generation configuration you would then write lt schemata gt lt schema gt lt l Vse this as the developers schema gt lt inputSchema gt LUKAS_DEV_SCHEMA lt inputSchema gt lt Use this as the integration production database gt lt output Schema gt PROD lt outputSchema gt lt schema gt lt schemata gt 2009 2014 by Data Geekery GmbH All rights reserved Page 145 151 The jOOQ User Manual 7 Tools 7 Tools These chapters hold some information about tools to be used with jOOQ 7 1 JOOQ 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 146 151 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 1 DBZ i Derby 10 8 Firebird 2 5 1 213 161 SQLDB 2 2 5 ngre
183. one some use Java regular expressions and only a few ones use Perl regular expressions jOOQ 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 Factory Javadoc for more details 2009 2014 by Data Geekery GmbH All rights reserved Page 59 151 The jOOQ User Manual 4 4 10 Date and time functions 4 4 10 Date and time functions This is a list of date and time functions supported by jOOQ s Factory 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 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 4 11 System functions This is a list of system functions supported by jOOQ s Factory CURRENT_USER Get current user 4 4 12 Aggr
184. or 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 refer to the Factory 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 acer and operators 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
185. ord 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 PP less Flags influencing generated tables These flags from the code generation configuration influence generated tables 2009 2014 by Data Geekery GmbH All rights reserved Page 137 151 The jOOQ User Manual 6 5 Generated records recordVersionFields Relevant methods from super classes are overridden to return the VERSION field recordTimestampFields 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 JPA annotations can be generated optionally Enti
186. ow to form various types of column expressions with jOOQ A particular type of column expression is given in the section about s 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 Factory 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 Factory 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 notation to create new column expressions based on existing ones The SQL way would be to use the prefix notation with functions created from the Factory The Java way or object oriented way would be to use the postfix notation with functions created from org jooq Field objects Both ways ultimately create the same query part though 4 4 1
187. p of the generator Name GenerationTool MySQL Example Main 00 Arguments mi JRE E Source MG Environment E Common a Bootstrap Entries Up JRE System Library JavaSE 1 7 a User Entries Down ad eung Remove jooq codegen jar example lib jooq meta jar example lib Add Projects jooq jar example lib log4j 1 2 16 ar example lib mysql connector java 5 1 15 bin jar example lib Add External JAR Advanced Edit Restore Default Entries Apply Revert Finally run the code generation and see your generated artefacts a gt example a 3 src gt EH org jooq test mysql generatedclasses gt EH org jooq test mysql generatedclasses enums gt EY org jooq test mysql generatedclasses routines b 8 org jooq test mysql generatedclasses tables b E3 org jooq test mysql generatedclasses tables records Xx library xml X log4j xml gt mA JRE System Library JavaSE 1 7 gt BBA Referenced Libraries Run generation with ant You can also use an ant task to generate your classes As a rule of thumb remove the dots and dashes from the properties file s property names to get the ant task s arguments 2009 2014 by Data Geekery GmbH All rights reserved Page 130 151 The jOOQ User Manual 6 1 Configuration and setup of the generator stos Task definition gt lt taskdef name generate classes
188. parator 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 BigDecimal 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
189. pe 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 public static final UDTField lt AddressTypeRecord String gt COUNTRY createField COUNTRY SQLDataType VARCHAR U_ADDRESS_TYPE 2 2 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 public void setCity String value public String getCity f t public void setCountry String value public String getCountry PW es ol O 2009 2014 by Data Geekery GmbH All rights reserved Page 142 151 The jOOQ User Manual 6 12 Master data and enumeration tables Flags controlling UDT generation UDT generation cannot be deactivated 6 12 Master data and enumeration tables NOTE This feature is deprecated in jOOQ 2 5 0 and will be removed as of jOOQ 3 0 Only MySQL and Postgres databases support true ENUM types natively Some other RDBMS allow you to map the concept of an ENUM data type to a CHECK constraint but those constraints can contain arbitrary SQL
190. plex data structures the way Hibernate JPA attempt to map relational data onto POJOs While future developments in this direction are not excluded OOQ 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 6 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 here 2009 2014 by Data Geekery GmbH All rights reserved Page 98 151 The jOOQ User Manual 5 3 7 Many fetching Obtain a Cursor reference Cursor lt BookRecord gt cursor null try cursor create selectFrom BOOK fetchLazy Cursor has similar methods as Iterator lt R gt while cursor hasNext BookRecord book cursor fetchOne Util doThingsWithBook book i Close the cursor and the cursor s under
191. portant 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 java lang Number org jooq types YeartoMonth where Number intVvalue corresponds to the absolute number of months and org joog types DayToSecond where Number intValue corresponds to the absolute number of milliseconds 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 DATETIME or INTERVAL gt DATETIM NTERVAL DATETIME gt DATETIME lt NTERVAL INTERVAL gt INTERVAL NTERVAL or NUMERIC gt INTERVAL E NUMERIC INTERVAL gt INTERVA 8 2 4 XML data types XML data types are currently not supported 2009 2014 by Data Geekery GmbH All rights reserved Page 149 151 The jOOQ User Manual 8 2 5 Geospacial data types 8 2 5 Geospacial data types Geospacial
192. rate gt lt target gt lt The destination package of your generated classes within the destination directory gt lt packageName gt org jooq your packagename lt packageName gt lt I gt The destination directory of your generated classes gt 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 2 5 0 xsd Run jOOQ code generation Code generation works by calling this class with the above property file as argument O 2009 2014 by Data Geekery GmbH All rights reserved Page 127 151 The jOOQ User Manual 6 1 Configuration and setup of the generator org joog util GenerationTool jooq config xml Be sure that these elements are located on the classpath The XML configuration file ooq 2 6 4 ar joog meta 2 6 4 jar jooq codegen 2 6 4 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 Go to CMempljooq Run java cp jooq 2 6 4 jar jooq meta 2 6 4 jar jooq codegen 2 6 4 jar DBC driver jar org jooq util GenerationTool X
193. 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 result set type from result queries JDBC java sql ResultSet jOOQ org jooq Result 2009 2014 by Data Geekery GmbH All rights reserved Page 90 151 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 sq l SQLException OOQ wraps all exceptions in an unchecked org jooq exception DataAcc
194. res 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 Factory UAuthorType author create newRecord U_AUTHOR_TYPE Set the author ID and load the record using 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 5 9 1 Exporting XML Fetch books and format them as XML String xml create selectFrom BOOK fetch formatXML The abo
195. ri inii siei ira a E E Ta REEE EPEE E E E E AAA 59 Exporting to XML ESV JSON HTML Taiana AA Ana Aa ac IAEA AM A A a a O a a e o A AA 0 aesd sees 8s ves tose sees az cain A A evens baautraeanl anna itaviraaab iaemad ET 5 9 3 EXPONE SON iii do casinos 5 9 4 Exporting NN O REE NN A AN 10 1 Importi 10 2 Importi CRUD wi Simple J g Zz a lt y c OD va 1 2 NE A Sii A Non Updatable reco Sarita iia rta 5 6 ias A NN RSS RR AB EXCSplon Rada tot e O e da a tit PPA EXE GUTS EISEN GIS naaa ali lo in ROO US OS RN AR NO 16 Performance considerations A Code coa la A oia n Configuration aNd SELUP OF THE CO caca ci a alder cl sai i aah we cl EEL 6 2 Advahced generator COMMBUTATOM ii id 6 3 Generated global ata id dsd bes DAGA A A ana e a cain nites ales a 6S GeMet e ECOS ut a 676s Generated ROJOS Ms De ae es Es e o il aL elsa ale 6 7 Generated Interfac S isc eis see cern AR hace Beet ia ich Haat Gh ed tli EA dU RST cha ea ied 6 8 GENErated DAOS rehire asics sees aullasihieassudehdeasie estes luesty a eE a dati a r E bisa aniin acai N ie ied 2009 2014 by Data Geekery GmbH All rights reserved Page 5 151 The jOOQ User Manual 6 9 Generated seguen CES ce NN 41 6 10 Generated POCAS ironia ino LIN END EDS LEAD DO Ea ai aia 41 6 11 Generated UDTs on 42 6 12 Master data and enumeration tables 43 6 13 Custom data T
196. rmation about generated sequences refer to the manual s section about generated sequences For more information about executing standalone calls to sequences refer to the manual s section about sequence execution 4 4 19 Tuples or row value expressions According t o 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 s rows jOOQ s Factory allows for the construction of type safe s up to the degree arity of 8 Higher degree s are supported as well but without any type safety types are modelled as follows The Factory provides overloaded row value expression constructor methods public static public static public static public static IE Vee tam Degrees of public static lt gt ROW S lt TUS row TI El f lt lt lt T T2 gt ROWE T2 gt bowl Ghd El T2 E2 f asa t lt T T2 T3 gt Row3 lt Ti T2 T3 gt row tl T2 2 T3 E3 f asa I lt T T25 T3 Uae Rows lt T1 T2 T3 T4 gt row TI El T2 2 T3 E3 T4 EA f caa I for Row5 Row6 Row7 Row8 more than 8 are supported without type safety RowN row Object values Using row value expressions in predicates Row valu
197. rves as a Static factory for table expressions column expressions or fields conditional expressions and many other QueryParts It implements org joog Configuration an object that configures jOOQ s behaviour when executing queries see SQL execution for more details Factories allow for creating SQL statements that are already configured and ready for execution The static Factory 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 Factory class import static org Jood impl Factory ty Note that when working with Eclipse you could also add the Factory 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 Factory concat Factory trim FIRST_NAME Factory trim LAST_NAME 2009 2014 by Data Geekery GmbH All rights reserved Page 24 151 The jOOQ User Manual 4 1 1 SQL Dialect The Factory as a Configuration object As any Configuration object a Factory can be supplied with these objects org joog SQLDialect The dialect of your database This may be any of the currently supported database types see SQL Dialect for more details java sql Connection An optional JDBC Connection that will be re used for the whole lifecycle of your Factory s
198. ryPart 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 joog impl CustomField showing how to create a field multiplying another field by 2 2009 2014 by Data Geekery GmbH All rights reserved Page 86 151 The jOOQ User Manual 4 8 5 Plain SQL QueryParts 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 multiplication directly if context inline Context sol BOOk IDs UN In non inline mode render a bind value else Context sql BOOK ID Ss qu 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 Factory val 2 catch SQLException e throw translate getSQL e y Use the above field in a SOL statement create select IDx2 from BOOK 4 8 5 Plain SQL QueryParts If you don t need the integration of rather complex QueryParts into jOOQ th
199. s _ create batch create insertInto create insertInto Createwinse st into Create insertinto execute AUTHOR ID NAME values AUTHOR ID NAME values AUTHOR ID NAME values AUTHOR ID NAME values 1 Erich Gamma 2 Richard Helm 3 Ralph Johnson 4 John Vlissides 2 a single query _ create batch create insertInto AUTHOR ID NAME values bind 1 Erich Gamma bind 2 Richard Helm bind 3 Ralph Johnson bind 4 John Vlissides execute 2009 2014 by Data Geekery GmbH All rights reserved Page 105 151 The jOOQ User Manual 5 7 Sequence execution 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 Factory 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
200. s 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 45 151 The jOOQ User Manual 4 2 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 type safe 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 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 AUT
201. s This is only relevant if relations is set to true too Default e 10 Tati gt lt navigationMethods gt true lt navigationMethods gt lt Generate deprecated code for backwards compatibility Defaults to true gt 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 IDM KENNELS exe ente io lt generatedAnnotation gt true lt generatedAnnotation gt lt Generate jOOQ Record classes for type safe querying You can turn this off if you don t need active records for CRUD D fautts to triei gt lt records gt true lt records gt SU Generate Pogos in addition toeRecord classes for usage of the ResultQuery fetchInto Class API Defaults to false lt 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 t false 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 interfaces in Record into Class lt gt and similar methods to let jOOQ return proxy objects for them Defeutts to false gt lt interfaces gt fals
202. s 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 2009 2014 by Data Geekery GmbH All rights reserved Page 16 151 The jOOQ User Manual 3 4 1 1 Step 1 Preparation 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 lt dependency gt lt groupId gt org jooq lt groupid gt lt artefacts are jooq jooq meta jooq codegen gt lt artifactId gt joogq lt artifactId gt lt version gt 2 6 4 lt version gt lt dependency gt 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 DAT
203. s 10 1 0 ySQL 5 1 41 and 5 5 8 Oracle XE 10 2 0 1 0 and 11g a PostgreSQL 9 0 SQLite with inofficial JDBC driver vO56 SQL Server 2008 R8 Sybase Adaptive Server Enterprise 15 5 Sybase SQL Anywhere 12 These platforms have been observed to work as well but are not integration tested Google Cloud SQL MySQL Databases planned for support Any of the following databases might be available in the future Informix Interbase MS Access MS Excel SQL Azure Sybase SQL Anywhere OnDemand Teradata O 2009 2014 by Data Geekery GmbH All rights reserved Page 147 151 The jOOQ User Manual 8 2 Data types Databases being watched Any of the following databases are being observed for a potential integration Mondrian Netezza SQLFire Vectorwise Vertica VoltDB Feature matrix This section will soon contain a feature matrix documenting what feature is available for which database 3 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 8 2 1 BLOBs and CLOBs jOOQ currently doesn t explicitly support JOBC BLOB and CLOB data types If you use any of
204. saa sateen petsaer a A T a dma dani no oe een ieee 58 AAD SUMS UNCON Seo Ass vies ih cess ek eevcdtaects Getisieadetscrsvnessccetrnens eves caves ub cueneeel ainsi ctiaed tienen clam eneey ah tambon eee eae 59 4410 Date and UNS TUNCONS ysis AI 60 AA A a e E a a da 60 AA AS SS functio Sgonn r Scie eevee aisha iss hve a lube sid E Ea AEE E a EENET odnebaacds lub otshednabaalanenvessutacedna N 60 4 4132 WIRGOWsTUNCUONS taa i 62 BATA GCOUPING MUM CUONS Axis a a e e a a e a A 64 A RN 66 A ANG User defined ageregate UIC a ii 66 4 417 The CASES O a LR AE eves cass AA O OEE ERa EAE aaa das 67 4 4 18 Sequences ANC SerlalS A ceccesceesessssssessessecstssssssessecsessusssesecsuscsesuessscsscsscssssusssessecsessussnsssssnecsessessesecsscesesusssseneenecsessusessssecsecseassessenseeseess 68 A A 19s TUPLES OF TOW VE ot erp ldla 69 4 5 Conditional expressions dE sags ae AS Vi COMGIOME LIC INS sc sores ea is 45 2 AND OR NOT b olean Operators avs ccs cios etico N DECEO DURARON ada ceases id ER 71 4 53 Companson predicate maoe aa a A aA a A A A a A AA A EAR 71 ASA O antifiedscomparison PIC ia An A IAS OA 72 ASS NULL Pess 4450 DISTINGI predicat Eginin ac 457 BETWEEN predicaten esecuri ae E Sects size E E O AR A cute ean al 45S CKE predicate isis nannan eaaa E T E a E E A EAT A A AN ApS Qe N predicate rt tis Ea disease A IA iaa A 51O A AS AS OVEREAPS predicate neioii a a a a a a a a a a 4 6 A Ea Ola dotado asi entiendas 4 7 Bind values and p
205. ses prepared statements are probably better But you always have the option of forcing jOOQ to render inlined bind values O 2009 2014 by Data Geekery GmbH All rights reserved Page 103 151 The jOOQ User Manual 5 5 Reusing a Query s PreparedStatement 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 Factory inlineQ as documented in the manual s section about inlined parameters 5 5 Reusing a Query s 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 sq PreparedStatement by not closing it between subsequent executions An example is given here Execute the statement try PreparedStatement 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 execut
206. sions to see a boolean truth table 4 5 10 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 Factory using static methods This is 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 Factory 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 rea
207. st 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 Exporting and Importing OOQ 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 feature
208. struct jOOQ queries If you can reuse the same queries you might cache them Beware of thread safety issues though as jOOQ s Factory is not threadsafe and queries are attached to their creating Factory 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 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 125 151 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 dev
209. syntax doesnt 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 Something 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 3 Navigation methods When using jOOQ s code generator along with its record generation capabilities generated records can have navigation methods contained in them if properly configured 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 117 1
210. t Result lt Record gt result create fetch rs As a Cursor Cursor lt Record gt cursor create fetchhazy rs 5 3 10 Data type conversion Apart from a few extra features see the manual s section about master data types and 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 OOQ 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 2009 2014 by Data Geekery GmbH All rights reserved Page 101 151 The jOOQ User Manual 5 3 10 Data type conversion public interface Converter lt T U gt extends Serializable xe Convert a database object to a user object ey U from T databaseObject per Convert a user object to a database object E T to U userObject pr The database type ay Class lt T gt fromType The user type y 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 asome more elaborate exampl
211. t 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 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 fetchMaps 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 R
212. t 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 2009 2014 by Data Geekery GmbH All rights reserved Page 94 151 The jOOQ User Manual 5 3 3 RecordHandler 5 3 3 RecordHandler In a more functional operating mode you might want to write callbacks that receive 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
213. t 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 simple SELECT API is limited in the way that it does not support any of these clauses The SELECT clause The JOIN clause The GROUP BY clause The HAVING clause In most parts of this manual it is assumed that you do not use the simple SELECT API For more information about the simple SELECT API see the manual s section about fetching strongly or weakly typed records 4 2 2 1 The SELECT clause The SELECT clause lets you project your own record types referencing table fields functions arithmetic expressions etc The Factory provides several methods for expressing a SELECT clause The SELECT clause Provide a varargs Fields list to the SELECT clause SELECT BOOK ID BOOK TITLE Select lt gt sil create select BOOK 1D BOOK Ti TLE SELECT BOOK ID TRIM BOOK TITLE Select lt gt s2 create select BOOK ID trim BOOK TITLE Some commonly used projections can be easily created using convenience methods Simple SELECTs Sel
214. t 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 2009 2014 by Data Geekery GmbH All rights reserved Page 58 151 The jOOQ User Manual 4 4 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 4 9 String functions String formatting can be done efficiently in the database before returning results to your Java application As discussed in the 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 Factory 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 f
215. t lt gt result create select from AUTHOR join BOOK on BOOK AUTHOR_ID equal AUTHOR ID teteh tys 4 2 2 3 The JOIN clause FROM clause for 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 Nest joins and provide JOIN conditions on FROM AUTHOR create select LEFT OUTER JOIN from AUTHOR BOOK JOIN BOOK_TO_BOOK_STORE leftOuterJoin BOOK ON BOOK_TO_BOOK_STORE BOOK_ID BOOK ID join BOOK_TO_BOOK_STORE on BOOK_TO_BOOK_STORE BOOK_ID equal ON BOOK AUTHOR_ID AUTHOR ID on BOOK AUTHOR_ID equal AUTHOR ID ly at the end BOOK ID See the section about conditional expressions to learn more about the many ways to create org joog Condition objects in jOOQ See the section about table expressions to learn about the various ways of refer org joog Table objects in OOQ JOIN ON KEY convenience provided by OOQ encing Surprisingly SQL 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 alot of repetitive code re writing the same JOIN predicate again and again especially when your foreign keys
216. tch 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 fetchArray 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 g
217. te select from POSTS fetch Factory so we can write a simple S ourselves CT query We pass an instance of the We then use jOOQ s DSL to return an instance of Result We ll be using this result in the next step 3 4 1 6 Step 6 Iterating 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 System out printin TED AS ads cp Eon The full program should now look like this O 2009 2014 by Data Geekery GmbH All rights reserved t description Page 20 151 The jOOQ User Manual 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 Factory import java sql Connection import java sql DriverManager import java sql ResultSet import java sql Statement import org jooq Record import org jooq Result import test generated GuestbookFactory import test generated tables Posts public class Main pr param args y 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
218. ted 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 SQL 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 1 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 from such a Connection in order to execute statements The normal operation mode is to provide a Factory with a JDBC Connection whose lifecycle you will control yourself This means that jOOQ will not actively close connections rollback or commit transactions Interact with JDBC DataSources
219. th 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 The GROUP BY clause The HAVING clause 5 3 2 Arrays Maps and Lists By default OOQ returns an org jooq 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 idsl 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 g
220. these data types in your database OOQ 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 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 2009 2014 by Data Geekery GmbH All rights reserved Page 148 151 The jOOQ User Manual 8 2 3 INTERVAL data types org joog tools unsigned UByte Unsigned byte an 8 bit unsigned integer org joog tools unsigned UShort Unsigned short a 16 bit unsigned integer org joog tools unsigned Uinteger Unsigned int a 32 bit unsigned integer org joog tools unsigned ULong Unsigned long a 64 bit unsigned integer 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 im
221. thod 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 overloadIndex return _OverloadIndex_ overloadIndex 6 2 Advanced generator configuration The jOOQ User Manual 6 2 Advanced generator configuration 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 t
222. to com example generated Keys FK_BOOK_AUTHOR Whenever you see create being used in Java code assume that this is an instance of org jooq impl Factory Factory create new Factory connection SQLDialect ORACLE Settings jOOQ allows to override runtime behaviour using org joog conf Settings If nothing is specified the default runtime settings are assumed Sample database See 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 CREATE TABLE language sa NUMBER 7 NOT NULL PRIMARY KEY cd CHAR 2 NOT NULL description VARCHAR2 50 CREATE TABLE author id NUMBER 7 NOT NULL PRIMARY KEY first_name VARCHAR2 50 last_name VARCHAR2 50 NOT NULL date_of_birth DATE year_of birth NUMBER 7 distinguished NUMBER 1 CREATE TABLE book id NUMBER 7 NOT NULL PRIMARY KEY author_id NUMBER 7 NOT NULL title VARCHAR2 400 NOT NULL published_in NUMBER 7 NOT NULL language_id NUMBER 7 NOT NULL CONSTRAINT fk_book_author FOREIGN KEY author_id REFERENCES author id CONSTRAINT fk_book_language FOREIGN KEY language_id REFERENCES language id CREATE TABLE book_store name VARCHAR2 400 NOT NULL UNIQUE CREATE TA
223. tors for common jOOQ operations to arbitrary fields elf trait SAnyField T extends Field T String operations _ def value String 2 Field String def value Field _ Field String Comparison predicates _ def value T Condition def value Field T Condition def value T 2 Condition def value Field T 2 Condition def lt gt value T Condition def lt gt value Field T Condition def gt value T Condition def gt value Field T 7 Condition def gt value T Condition def gt value Field T 7 Condition def lt value T Condition def lt value Field T Condition def lt value T Condition def lt value Field T Condition def lt gt value T Condition def lt gt value Field T Condition The following depicts a trait which wraps numeric fields 2009 2014 by Data Geekery GmbH All rights reserved Page 88 151 The jOOQ User Manual Jer A Scala esque representation of link org jooq Field operators for common j00Q operations to numeric fields Zi trait SNumberField T lt Number Arithmetic operations _ def unary_ def value Number def value Field _ lt def value Number def value Field _ lt def value Number def value Field _ lt def value Number def value Field _
224. tory SQLDialect ASE JeselleccOne get SoL SELECT 1 FROM db root new Factory SQLDialect CUBRID ssellectOne getSoi SELECT 1 FROM SYSIBM DUAL new Factory SQLDialect DB2 selectOne get SQL SELECT 1 FROM SYSIBM SYSDUMMY1 new Factory SQLDialect DERBY selectOne getSQL SELECT 1 FROM dual new Factory SQLDialect H2 selectOne getSOL SELECT 1 FROM INFORMATION_SCHEMA SYSTEM_USERS new Factory SQLDialect HSQLDB escleccoOne ageesoi i SELECT 1 FROM select 1 as dual as dual new Factory SQLDialect INGRES select ne getSOL SELECT 1 FROM dual new Factory SQLDialect MYSQL select ne getsSgL SELECT 1 FROM dual new Factory SQLDialect ORACLE selectOne getSoL SELECT 1 new Factory SQLDialect POSTGRES selectOne getSQL SELECT 1 new Factory SQLDialect SQLITE j select ne gets SELECT 1 new Factory SQLDialect SQLSERVER selectOne getSQL SELECT 1 FROM SYS DUMMY new Factory SQLDialect SYBASE selectOne getSOL Note that some databases H2 MySQL can normally do without dual However 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 4 Column expressions Column expressions can be used in various SQL clauses in order to refer to one or several columns This chapter explains h
225. ttings settings new Settings settings setStatementType StatementType STATIC_STATEMENT Factory create new Factory connection dialect settings Subsequent sections of the manual contain some more in depth explanations about these settings Runtime schema and table mapping Execute listeners and SOL tracing Execute CRUD with optimistic locking enabled 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 2 5 0 xsd 4 1 4 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 27 151 The jOOQ User Manual 4 1 4 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
226. ty Table 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 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 setAuthorld Integer value public Integer getAuthorld Convenience methods for foreign key methods public void setAuthorId AuthorRecord value if value null setValue BOOK AUTHOR_ID null i else 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 l 2009 2014 by Data Geekery GmbH All rights reserved Page 138 151 The jOOQ User Manual 6 6 Generated POJOs Flags influencing generated records These flags from the code generation con figuration influence generated records dateAsTimestamp This influences al relevant getters and setters unsignedTypes This influences all re relations This is needed as a prereq levant getters and setters uisite for navigation methods navi
227. tyle 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 create 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 4 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 68 151 The jOOQ User Manual 4 4 19 Tuples or row value expressions You can the Reference t BigInteger nex Reference t create inserti values n use your generated sequence object directly in a SQL statement as such he sequence in a SELECT statement tID create select s fetchOne S_AUTHOR_ID nextval he sequence in an INSERT statement nto AUTHOR AUTHOR ID AUTHOR FIRST_NAME AUTHOR LAST_NAME S_AUTHOR_ID nextval val William val Shakespeare For more info
228. ueries 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 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 Factory create new Factory connection dialect SelectQuery query create selectQuery query addFrom AUTHOR Join books only under certain circumstances if join 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 QueryObject Note that you can always access the SelectQuery object to switch between DSL and non DSL APIs 2009 2014 by Data Geekery GmbH All rights reserved Page 31 151 The jOOQ User Manual Factory create new Factory connection dialect SelectFinalStep select create select from AUTHOR Add the JOIN clause on the internal QueryObject representation SelectQuery query select getQuery query addJoin BOOK BOOK AUTHOR_ID equal AUTHOR ID Mutability 4 2 2 The SELECT statement Note that for historic reasons the DSL API mixes mutable and immutable behaviour with respect to the internal representation of the QueryPart being constructed Whil
229. unctions that you provide it Empty GROUP BY clauses CT statements that return jOOQ supports empty GROUP BY clauses as well This will result in S only one record SELECT COUNT create selectCount FROM BOOK from BOOK GROUP BY groupBy 2009 2014 by Data Geekery GmbH All rights reserved Page 38 151 The jOOQ User Manual 4 2 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 2 2 7 The HAVING clause The HAVING clause 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 2 2 8 The ORDER BY clause Data
230. us 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 92 151 The jOOQ 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 fe
231. 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 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 EOLA Mrs dales ye 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 values 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 value
232. ut 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 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 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 joog 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 2009 2014 by Data Geeker
233. util sqlserver SQLServerDatabase org jooq util 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 such as a Hibernate hbm xml file gt lt name gt org jooq util oracle OracleDatabase lt name gt lt All elements that are generated from your schema several Java regular expressions separated by comma Watch out for case sensitivity Depending on your database this might be important You can create case insensitive regular expressions using this syntax i expr A comma separated list of regular expressions gt lt includes gt lt includes gt lt All elements that are excluded from your schema several Java regular expressions separated by comma Excludes match before includes gt lt excludes gt lt excludes gt 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 gene
234. ve query will result in an XML document looking like the following one 2009 2014 by Data Geekery GmbH All rights reserved Page 109 151 The jOOQ User Manual 5 9 2 Exporting CSV lt result xmlns http www jooq org xsd joog 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 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 ofthe XML export format http www joog org xsd joog export 1 6 2 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 1 1984 2 1 Animal Farm In addition to the standard behaviour you can also specify a separator
235. w Factory connection dialect Load data into the AUTHOR table from an input stream holding the CSV data create loadInto AUTHOR loadCSV inputstream Tels ED AUTHOR_ID TITLE execute Here are various other examples 2009 2014 by Data Geekery GmbH All rights reserved Page 112 151 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 onDuplicateKeyError 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
236. 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 create 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 less 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 s
237. y GmbH All rights reserved Page 107 151 The jOOQ User Manual 5 8 1 Oracle Packages 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 getld But you can also call the procedure using a generated convenience method in a global Routines class The generated Routines class contains static 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 CREA
238. y table in your database will generate a org joog DAO implementation that looks like this 2009 2014 by Data Geekery GmbH All rights reserved Page 140 151 The jOOQ User Manual 6 9 Generated sequences public class BookDao extends DAOImp1 lt BookRecord Book Integer gt Generated constructors public BookDao super BOOK Book class public BookDao Factory factory super BOOK Book class factory Every column generates at least one fetch method public List lt Book gt fetchById Integer values return fetch BOOK ID values public Book fetchOneById Integer value return fetchOne BOOK ID value public List lt Book gt fetchByAuthorId Integer values return fetch BOOK AUTHOR_ID values WW Les all 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 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 Every procedure or function routine in your database will generate a org joog Routine implementation that looks like this 2
239. y 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 2009 2014 by Data Geekery GmbH All rights reserved Page 95 151 The jOOQ User Manual 5 3 5 POJOs Oo 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 Factory 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 generated 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 A JPA an
240. 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 5 6 DISTINCT predicate Some databases support the 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 NULL 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 2009 2014 by Data Geekery GmbH All rights reserved Page 73 151 The jOOQ User Manual 4 5 7 BETWEEN predicate JA
241. ypes 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 list of comma separated regular expressions matching columns 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 public class TAuthor extends UpdatableTableImpl lt TAuthorRecord gt Ih Nese public final TableField lt TAuthorRecord GregorianCalendar gt DATE_OF_BIRTH TOF oleae dl HY he see 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 fetch AUTHOR DATE_OF_BIRTH 6 14 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 c

Download Pdf Manuals

Related Search

Related Contents

RIGEL 5 - Garajes Prefabricados SAS    SYBA SD-PEX10005  Blocs Fonction  Digitus DK-1532-070 networking cable  Black & Decker 5146471-01 Instruction Manual  取扱説明書 AK-702 ミニペダルセット マニュアル車用  Rexel Fasteners 80 x 50mm  advertencia - Victor Technologies  Origin Storage 256GB MLC SATA 2.5"  

Copyright © All rights reserved. Failed to retrieve file