Java Object Oriented Querying
jOOQ
Developer(s)Data Geekery GmbH
Stable release
3.10.0 / September 29, 2017 (2017-09-29)
Written inJava
Operating systemCross-platform
PlatformJava
TypeObject-relational mapping
LicenseDual-licensed: ASL 2.0 and commercial
Websitehttp://www.jooq.org

Java Object Oriented Querying, commonly known as jOOQ, is a light database-mapping software library in Java that implements the active record pattern. Its purpose is to be both relational and object oriented by providing a domain-specific language to construct queries from classes generated from a database schema.

Paradigm

jOOQ claims that SQL should come first in any database integration. Thus, it does not introduce a new textual query language, but rather allows for constructing plain SQL from jOOQ objects and code generated from a database schema. jOOQ uses JDBC to call the underlying SQL queries.

While it provides abstraction on top of JDBC, jOOQ does not have as much functionality and complexity as standard object-relational mapping libraries such as Hibernate and JPA.

jOOQ's closeness to SQL has advantages over typical object-relational mapping libraries. SQL has many features that cannot be used in an object oriented programming paradigm; this set of differences is referred to as the object-relational impedance mismatch. By being close to SQL, jOOQ helps to prevent syntax errors and type mapping problems. Also, variable binding is taken care of. It is also possible in jOOQ to create very complex queries, that involve aliasing, unions, nested selects and complex joins. jOOQ also supports database-specific features, such as UDTs, enum types, stored procedures and native functions.

Example

A nested query selecting from an aliased table

  -- Select authors with books that are sold out
  SELECT * FROM AUTHOR a
        WHERE EXISTS (SELECT 1
                   FROM BOOK
                  WHERE BOOK.STATUS = 'SOLD OUT'
                    AND BOOK.AUTHOR_ID = a.ID);

And its equivalent in jOOQ DSL:

  // Use the aliased table in the select statement
  create.selectFrom(table("AUTHOR").as("a"))
        .where(exists(selectOne
                     .from(table("BOOK"))
                     .where(field("BOOK.STATUS").equal(field("BOOK_STATUS.SOLD_OUT")))
                     .and(field("BOOK.AUTHOR_ID").equal(field("a.ID")))));

Or more simply, using code generation from the database metadata to generate constants:

  // Use the aliased table in the select statement
  final Author a = AUTHOR.as("a");

  create.selectFrom(a)
        .where(exists(selectOne
                     .from(BOOK)
                     .where(BOOK.STATUS.equal(BOOK_STATUS.SOLD_OUT))
                     .and(BOOK.AUTHOR_ID.equal(a.ID))));

See also

External links


  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

Java_Object_Oriented_Querying
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultlogic.com. Create an account using LinkedIn to manage and organize your omni-channel knowledge. defaultlogic.com is like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us