April 22, 2011 at 14:31

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 executing it for simple types takes up one line. A bit about jooq:



    What is good with us?


    • Support for MySQL 5.1.41 / 5.5.8, 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 planned for Sybase, MSSQL, Ingres, Firebird
    • Support for generating code not only for tables / fields, but also for representations, stored procedures, UDF, complex types like ENUM in MySQL, Blob (you can represent them as regular byte [])
    • Since the entire database schema is represented by classes, the IDE works with the completion of language constructs
    • No SQL Injection vulnerability when used properly - 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’s impossible to forget to fix any piece of code if you suddenly renamed a column in the database table. After repeated parsing of the database, the incorrect code simply does not compile
    • Basic ActiveRecord support (the record obtained from the database can be changed and written back in one or two lines)
    • Support not only SELECT queries, but also INSERT, UPDATE.
    • Support for nested queries, arbitrary fields, aggregate functions, UNION queries, arithmetic and other operations
    • Convenient methods for obtaining the results of query execution are fetch (), fetchAny (), fetchLazy (), fetchMap () and others.
    • Good support for SLF4J including for profiling the internal Jooq device.
    • A lot of things planned
    • The author is constantly on the Internet, very quickly answers questions in the jooq Google Group and on tickets in the bug tracker .
    • Maven support . Jooq distributions are available through Maven Central .
    • Support for 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?


    • While there is no direct support for some specific language features like "FOR UPDATE" or "index usage hints" constructs in SELECT. But there is a workaround .
    • If you work with more than one table in the same class, import static is difficult to use in some cases, which leads to some complication of the look of the query (an example with respect to the one indicated 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 when executing queries and crawling the result (I think it is small, tens to hundreds of microseconds, maybe?)
    • There is no immediate support for LAST_INSERT_ID () in MySQL. But there is a workaround . Sequences are supported .

     

    Subjectivity


    • There is no code yet on GitHub;), the main development is in Subversion
    • SourceForge project pages are slightly slower, which is a little annoying.
    • The manual seems to be quite detailed, but some simple but useful things are missing. It also seemed to me not too comfortable. It describes only the most basic without "chips", "buns" and amenities. However, if you have a long Java experience, you can probably get to it yourself quickly. In addition, it seemed to me that it is not too well structured. The 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 in another way how this can be done conveniently)

    Field jobTypeCountField = Job.JOBTYPE_ID.count().as("JOBTYPE_ID_COUNT");
Result 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. Such code will seem more understandable to some.
    • In version 2.0, very interesting buns are planned .
    • Non-wiki documentation (Trac) and not too convenient navigation on it. However, javadoc is enough in the code to understand everything by Ctrl-Q. However, I just think Trac is not too convenient, so I find fault ...
    • So far, a relatively small number of users.


    Jooq analogs




    More analogues, a slightly different plan



     

    Contacts:



     

    A few examples from Jooq sources (working with the information_schema database in MySQL)



    Perhaps it’s better to look at the examples 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);
            }
        }
    Tags:

    Also popular now: