Jooq - “LINQ” for Java, a type-safe SQL query builder in Java code

Recently, in search of a middle ground between JDBC and ORM, I came across an interesting open source library ( Apache Software License ), with which you can build SQL directly in Java code quite conveniently and safely. The library is called jooq . Jooq includes a code generator that parses the structure of your database and creates the necessary Java classes. In fact, you get something like this code:

Integer taskId = sqlFactory.select(ID).from(TASK).where(STATUS.equal(TaskStatus.QUEUED)). orderBy(LAST_UPDATED).limit(1).fetchOne(ID); 

As you can see, constructing a query and its execution for simple types takes one line. A little bit about jooq:
')

What is good with us?

• MySQL 5.1.41 / 5.5.8 support, Oracle XE 10.2.0.1.0, DB2 9.7, PostGreSQL 9.0, H2 1.3.154, HSQLDB 2.1.0, SQLite, Derby 10.7
• Support is planned for Sybase, MSSQL, Ingres, Firebird
• Support of code generation not only for tables / fields, but also views, stored procedures, UDF, complex types like ENUM in MySQL, Blob (you can present them as ordinary byte [])
• Since the entire database schema is represented by classes, the IDE runs the completion of language constructs
• No SQL Injection vulnerability when properly used - jooq uses parameter templates in queries
• Mostly database-independent syntax
• The use of generic types provides a sufficient number of checks even when compiling the code. Plus, it is impossible to forget to fix any piece of code if you suddenly renamed a column in the database table. After re-parsing the database, the incorrect code will simply not compile.
• Basic support for ActiveRecord (the record obtained from the database can be changed and written back with one or two lines)
• Support not only SELECT queries, but also INSERT, UPDATE.
• Support for subqueries, arbitrary fields, aggregate functions, UNION queries, arithmetic and other operations
• Convenient methods for retrieving query results are fetch (), fetchAny (), fetchLazy (), fetchMap (), and others.
• Good support for SLF4J , including for profiling Jooq internal device.
• Lots of things planned
• The author is constantly on the Internet, very promptly answers questions in the jooq Google Group and on tickets in the bug tracker .
• Maven support. The jooq distributions are available through Maven Central .
• Support for both DSL syntax (select (). From (). Where ()) query construction and OOP (a = new Query (); a.addSelect (); a.addFrom ())
• The query results and the query objects themselves are serializable.

What's wrong with us?

• There is no direct support for some specific language features like “FOR UPDATE” or “index usage hints” constructions in SELECT. But there is a workaround .
• If you work in one class with more than one table, import static in some cases is difficult to use, which leads to some complication of the query's appearance (an example in relation to what is shown above):

 Integer taskId = sqlFactory.select(Task.ID).from(Task.TASK).where(Task.STATUS.equal(TaskStatus.QUEUED)). orderBy(Task.LAST_UPDATED).limit(1).fetchOne(Task.ID); 

• A small overhead in the execution of requests and bypassing the result (I think it is small, maybe tens or hundreds of microseconds, maybe?)
• There is no direct support for LAST_INSERT_ID () in MySQL yet . But there is a workaround . Sequences are supported .

Subjectivity

• There is no code on GitHub;), the main development is in Subversion
• SourceForge project pages slightly slow down, which is a bit annoying.
• The manual seems to be quite detailed, but some simple but useful things are missing in it. It also seemed to me not very convenient. It describes only the most basic without "chips", "buns" and convenience. However, if you have a great Java experience, you probably can get to everything quickly. In addition, it seemed to me that it was not very well structured. Information on it is spread. I'm always in a hurry and a good manual for me must-have ...
• Not very convenient syntax for working with aggregated fields (in any case, I haven’t yet figured out how to do this conveniently)

 Field<Integer> jobTypeCountField = Job.JOBTYPE_ID.count().as("JOBTYPE_ID_COUNT"); Result<Record> jobTypeCountRecord = null; jobTypeCountRecord = sqlFactory.select(Job.JOBTYPE_ID, jobTypeCountField).from(Job.JOB) .where(Job.STATUS.equal(JobStatus.EXECUTING)).groupBy(Job.JOBTYPE_ID).fetch(); for (Record record : jobTypeCountRecord) { System.out.println(record.getValue(Job.JOBTYPE_ID) + " - " - record.getValue(jobTypeCountField)); } 

However, opinions on this subject may be different. Someone such code will seem more understandable.

• In version 2.0 very interesting buns are planned.
• Non-wiki documentation (Trac) and not very convenient navigation. However, there is enough javadoc in the code to understand everything by Ctrl-Q. However, I just think the Trac is not very convenient, so I find fault ...
• While a relatively small number of users.

Jooq analogs

• Querydsl
• SQL Construction Kit (documentation for it could not be found)

More analogues, a slightly different plan.

• JPQL
• Jaqu
• HQL

Contacts:

• Project site - http://www.jooq.org
• Manual: http://sourceforge.net/apps/trac/jooq/wiki/Manual
• Forum - http://groups.google.com/group/jooq-user
• SVN Info: https://sourceforge.net/scm/?type=svn&group_id=283484

Some examples from Jooq source (work with MySQL information_schema database)

Perhaps examples are better to look here with syntax highlighting from Geshi.

  select(KeyColumnUsage.CONSTRAINT_NAME, KeyColumnUsage.TABLE_NAME, KeyColumnUsage.COLUMN_NAME) .from(KEY_COLUMN_USAGE).join(TABLE_CONSTRAINTS) .on(KeyColumnUsage.TABLE_SCHEMA.equal(TableConstraints.TABLE_SCHEMA)) .and(KeyColumnUsage.TABLE_NAME.equal(TableConstraints.TABLE_NAME)) .and(KeyColumnUsage.CONSTRAINT_NAME.equal(TableConstraints.CONSTRAINT_NAME)) .where(TableConstraints.CONSTRAINT_TYPE.equal(constraintType)) .and(KeyColumnUsage.TABLE_SCHEMA.equal(getSchemaName())) .orderBy(KeyColumnUsage.TABLE_NAME.ascending(), KeyColumnUsage.ORDINAL_POSITION.ascending()).fetch() 

  for (Record record : create().select( ReferentialConstraints.CONSTRAINT_NAME, ReferentialConstraints.TABLE_NAME, ReferentialConstraints.REFERENCED_TABLE_NAME, ReferentialConstraints.UNIQUE_CONSTRAINT_NAME, KeyColumnUsage.COLUMN_NAME) .from(REFERENTIAL_CONSTRAINTS) .join(KEY_COLUMN_USAGE) .on(ReferentialConstraints.CONSTRAINT_SCHEMA.equal(KeyColumnUsage.CONSTRAINT_SCHEMA)) .and(ReferentialConstraints.CONSTRAINT_NAME.equal(KeyColumnUsage.CONSTRAINT_NAME)) .where(ReferentialConstraints.CONSTRAINT_SCHEMA.equal(getSchemaName())) .orderBy( KeyColumnUsage.CONSTRAINT_NAME.ascending(), KeyColumnUsage.ORDINAL_POSITION.ascending()) .fetch()) { String foreignKey = record.getValue(ReferentialConstraints.CONSTRAINT_NAME); String foreignKeyColumn = record.getValue(KeyColumnUsage.COLUMN_NAME); String foreignKeyTableName = record.getValue(ReferentialConstraints.TABLE_NAME); String referencedKey = record.getValue(ReferentialConstraints.UNIQUE_CONSTRAINT_NAME); String referencedTableName = record.getValue(ReferentialConstraints.REFERENCED_TABLE_NAME); TableDefinition foreignKeyTable = getTable(foreignKeyTableName); if (foreignKeyTable != null) { ColumnDefinition column = foreignKeyTable.getColumn(foreignKeyColumn); String key = getKeyName(referencedTableName, referencedKey); relations.addForeignKey(foreignKey, key, column); } }