Discussions

News: A new, simple and intuitive approach to interact with your database from Java

  1. Introduction

    In this article, I want to announce the first stable release 1.4.2 of jOOQ, which stands for Java Object Oriented Querying. Find more information on http://jooq.sourceforge.net

    Abstract

    Many companies and software projects seem to implement one of the following two approaches to interfacing Java with SQL

    • The very basic approach: Using JDBC directly or adding a home-grown abstraction on top of it. There is a lot of manual work associated with the creation, maintenance, and extension of the data layer code base. Developers can easily use the full functionality of the underlying database, but will always operate on a very low level, concatenating Strings all over the place.
    • The very sophisticated approach: There is a lot of configuration and a steep learning curve associated with the introduction of sophisticated database abstraction layers, such as the ones created by Hibernate or iBatis, or even plain old EJB entity beans. While the generated objects and API's may allow for easy manipulation of data, the setup and maintenance of the abstraction layer may become very complex. Besides, these abstraction layers provide so much abstraction on top of SQL, that SQL-experienced developers have to rethink.

    A new paradigm

    I tried to find a new solution addressing many issues that I think most developers face every day. With jOOQ - Java Object Oriented Querying, I want to embrace the following paradigm:

    • SQL is a good thing. Many things can be expressed quite nicely in SQL. There is no need for complete abstraction of SQL.
    • The relational data model is a good thing. It has proven the best data model for the last 40 years. There is no need for XML databases or truly object oriented data models. Instead, your company runs several instances of Oracle, MySQL, MSSQL, DB2 or any other RDBMS.
    • SQL has a structure and syntax. It should not be expressed using "low-level" String concatenation in JDBC - or "high-level" String concatenation in HQL - both of which are prone to hold syntax errors.
    • Variable binding tends to be very complex when dealing with major queries. THAT is something that should be abstracted.
    • POJO's are great when writing Java code manipulating database data.
    • POJO's are a pain to write and maintain manually. Code generation is the way to go. You will have compile-safe queries including datatype-safety.
    • The database comes first. While the application on top of your database may change over time, the database itself is probably going to last longer.
    • Yes, you do have stored procedures and user defined types (UDT's) in your legacy database. Your database-tool should support that.

    How does jOOQ fit in this paradigm?

    Not only does jOOQ completely address the above paradigm, it does so quite elegantly. Let's say you have this database that models your bookstore. And you need to run a query selecting all books by authors born after 1920. You know how to do this in SQL:

        -- Select all books by authors born after 1920 from a catalogue:
        SELECT *
        FROM t_author a
        JOIN t_book b ON a.id = b.author_id
        WHERE a.year_of_birth > 1920
        ORDER BY b.title

    Now, from your single starting point - the "Factory" - jOOQ lets you assemble the same SQL statement in objects.

        // Instanciate your factory using a JDBC Connection or DataSource,
        // and specify the SQL dialect you're using. Of course you can
        // have several factories in your application.

        Factory create = new Factory(datasource, SQLDialect.MYSQL);

        // Create the query using generated, type-safe objects. You could
        // write even less code when static importing generated classes!

        SelectQuery q = create.selectQuery();
        q.addFrom(TAuthor.T_AUTHOR);
        q.addJoin(TBook.T_BOOK, TAuthor.ID, TBook.AUTHOR_ID);
        q.addCompareCondition(TAuthor.YEAR_OF_BIRTH, 1920, Comparator.GREATER);
        q.addOrderBy(TBook.TITLE);

    But that's not it! jOOQ comes with a lot of convenience methods, so that you can write your SQL in a very intuitive way, almost like it were a domain specific language inside of Java

        // Do it all "on one line".
        SelectQuery q = create.select()
            .from(T_AUTHOR)
            .join(T_BOOK).on(TAuthor.ID.equal(TBook.AUTHOR_ID))
            .where(TAuthor.YEAR_OF_BIRTH.greaterThan(1920))
            .orderBy(TBook.TITLE).getQuery();

    Executing the query and fetching results is simple

        // Execute the query and fetch the results
        q.execute();
        Result<?> result = q.getResult();

        // Loop over the resulting records
        for (Record record : result) {

            // Type safety assured with generics
            String firstName = record.getValue(TAuthor.FIRST_NAME);
            String lastName = record.getValue(TAuthor.LAST_NAME);
            String title = record.getValue(TBook.TITLE);
            Integer publishedIn = record.getValue(TBook.PUBLISHED_IN);

            System.out.println(title + " (published in " + publishedIn + ") by " + firstName + " " + lastName);

        }

    If you're not joining several tables, but just selecting from one table instead, you can take advantage of the fact that jOOQ knows the datatype ahead of selection. In that case, jOOQ will provide you with basic OR-mapping functionality:

        // Similar query, but don't join books to authors.
        // Note the generic type that is added to your query:
        SimpleSelectQuery<TAuthorRecord> q = create.select(T_AUTHOR)
            .where(TAuthor.YEAR_OF_BIRTH.greaterThan(1920))
            .orderBy(TAuthor.LAST_NAME).getQuery();

        // When executing this query, also Result holds a specific generic type:
        q.execute();
        Result<TAuthorRecord> result = q.getResult();

        for (TAuthorRecord author : result) {

            // With generate record classes, you can use generated getters and setters:
            String firstName = author.getFirstName();
            String lastName = author.getLastName();

            System.out.println("Author : " + firstName + " " + lastName + " wrote : ");

            // Use generated foreign key navigation methods
            for (TBookRecord book : author.getTBooks()) {
                System.out.println(" Book : " + book.getTitle());

            }
        }

    And even more, you can use jOOQ for simple CRUD operations, as you would expect from an OR-mapper:

        // Create a new record and insert it into the database
        TBookRecord book = create.newRecord(T_BOOK);
        book.setTitle("My first book");
        book.store();

        // Update it with new values
        book.setPublishedIn(2010);
        book.store();

        // Delete it
        book.delete();

    How is jOOQ different from JPA and CriteriaQuery?

    The JPA has somehow adopted Hibernate's CriteriaQuery functionality, implementing roughly similar concepts. Nevertheless, Hibernate and JPA have added a lot of abstraction on top of SQL and the neat SQL features of your database. It is very difficult, if not impossible, to integrate stored procedures / functions, UDT's, database-specific functions (such as DECODE, NVL, COALESCE, INSTR, etc), which are performed a lot faster in the database than in Java. Also, relational joins and nested selects are not easily supported. The JPA stack adds a lot of power to persistence, the price for which is their complexity and the fact that useful database features are hidden from you.

    jOOQ is a good compromise. While it does not have the full OR-mapping power like Hibernate - while it does not yet provide you with JPA-annotated POJO's (not yet) it will allow for a full use of your database and SQL the way you're used to.

    jOOQ brings the relational world to Java without trying to cover up its origins. jOOQ is relational. And object oriented. Just in a different way. Try it for yourself and I would be very glad for any feedback you may have. Find jOOQ on http://jooq.sourceforge.net

    Cheers
    Lukas Eder

    About the author

    Lukas Eder graduated from Ecole Polytechnique Fédérale de Lausanne with a Masters degree in Computer Science. He works as a senior software engineer in a major Swiss software company, focusing on the design and implementation of software in the E-Banking field.

    Threaded Messages (20)

  2. Not reaaly new[ Go to top ]

    lookup Jaqu on the h2 site

  3. Not reaaly new[ Go to top ]

    lookup Jaqu on the h2 site

    Hi there, thanks for your post and your reference. I like Jaqu's list of similar projects. In the next couple of months I want to elaborate a feature and behaviour comparison between jOOQ and various similar projects.

    What's new about jOOQ in my opinion seems to me to be the fact that jOOQ's goal is to support as much native SQL as possible. Your database comes first, then any ideas or abstractions you might have in an object-oriented world. JPA and other abstractions are nice-to-have, but if the full power of nested selects, stored procedures, complex joins etc, are not supported, then a framework might not be suitable for some users. That's where jOOQ fills a gap.

  4. YAJPT[ Go to top ]

    = Yet Another Java Persistence Tool.

  5. YAJPT[ Go to top ]

    = Yet Another Java Persistence Tool.

    Thanks for your insight, Marco! :-)
    What are your favorite ones of the many that exist?

  6. What you have introduced here is a straight approach that I think will someday be the deminant approach of how Java deals with databases

    The only threat yo your idea here is the development of the java persistence APIs and what might come from it as they might address the issues you mentioned here

    Very good work

    Java Persistence API

     

  7. What you have introduced here is a straight approach that I think will someday be the deminant approach of how Java deals with databases

    The only threat yo your idea here is the development of the java persistence APIs and what might come from it as they might address the issues you mentioned here

    Very good work

    Java Persistence API

    Thanks for your hint. I am well aware of JPA and especially CriteriaQuery, which has become a part of it. Today, however, jOOQ and JPA follow quite different strategies. Hence, jOOQ could be an extension to JPA one day.

  8. What you have introduced here is a straight approach that I think will someday be the deminant approach of how Java deals with databases

    The only threat yo your idea here is the development of the java persistence APIs and what might come from it as they might address the issues you mentioned here

    Very good work

    Java Persistence API

     

  9. Support for transactions[ Go to top ]

    Thanks for the nice tool. It will be great if you give an example of transaction handling in CRUD using jOOQ.

  10. Support for transactions[ Go to top ]

    Thanks for the nice tool. It will be great if you give an example of transaction handling in CRUD using jOOQ.

    The transaction is created on the JDBC Connection, which is passed to the jOOQ Factory for creating and persisting objects. The Connection authority, however, remains in the client code. For the moment, transaction handling is not on the feature roadmap, as I cannot see the use case. What would you like to be able to do with jOOQ as a transaction manager?

  11. Support for transactions[ Go to top ]

    The example you showed here works with DataSource, not Connection.

     Factory create = new Factory(datasource, SQLDialect.MYSQL);

     

    So my guess is that the framework takes care of creating and managing the connection. So how do I handle transaction if I do manage the connection?

  12. Support for transactions[ Go to top ]

    Hmm, you're right about the DataSource, that's a design flaw. I will take this issue into the roadmap. When you use a Connection on the factory, however, the problem does not arise as you will be responsible for the transaction yourself.

  13. the nice tool[ Go to top ]

    It is a nice tool but some code is very similar to the Ujorm ORM framework (http://ujorm.com).

    Compare an Ujorm class to jOOQ class. It seems that the idea not new nor original. Sorry.

     

  14. the nice tool[ Go to top ]

    It is a nice tool but some code is very similar to the Ujorm ORM framework (http://ujorm.com).

    Compare an Ujorm class to jOOQ class. It seems that the idea not new nor original. Sorry.

    Thanks for your feedback. It is intuitive to conceive a mapping between tables/fields and classes/members, even using generics. You are right, that is not new nor original. Hibernate has adopted this approach a long time ago. What I think distinguishes jOOQ from the frameworks I had seen before (and I will happily check out yours as well) is its closeness to actual SQL.

  15. Shouldn't it be book.bin() ?

    db actions on the object reminds me of php's Doctrine ORM. And early ejb. They had it right all along!!!

    ;)

  16. Shouldn't it be book.bin() ?

    db actions on the object reminds me of php's Doctrine ORM. And early ejb. They had it right all along!!!

    ;)

    Hi Damian,

    The great news about jOOQ is that you can override code generation and name your delete method whatever you like! :)

    Cheers
    Lukas

  17. Excellent approach![ Go to top ]

    Hi Lukas,

    This is exactly what we have been discussing within the team I belong to. Your implementation looks neat. Excellent work!

    regards,
    Mariano

  18. Excellent approach![ Go to top ]

    Hi Lukas,

    This is exactly what we have been discussing within the team I belong to. Your implementation looks neat. Excellent work!

    regards,
    Mariano

    Hi Mariano,

    As you can see in the backlog of my post, the idea itself is not new, there are quite a few solutions to this "requirement". What's new about jOOQ in my opinion is its approach of not abstracting SQL too much but providing a big SQL feature set.

    Please feel free to evaluate jOOQ and provide more feedback / bug reports / feature requests if needed

    Cheers
    Lukas

  19. +1[ Go to top ]

    Very nice. Please keep it lean and clean as you have...

  20. Domain Objects[ Go to top ]

    TBookRecord is not a domain object. Can the record return the domain object>

    Book book = tBookRecord.getModel()

    Later you might do: tBookRecord.setModel(book);

    tBookRecord.store();

    Or, you might do:

    Book book = new Factory().select(...).getQuery().getModel(), again where the book is the domain model. We get rid of the costant repetitive looping thru the result set.

     

  21. Domain Objects[ Go to top ]

    Hi Florin,

    Thanks for the hint.

    I'm not sure I understand what you mean by the domain object. Maybe you're confusing TBook (implements Table<TBookRecord>) with TBookRecord (implements Record)? The latter should be exactly what you refer to as a domain object.

    From what I understand, you would like to see a faster way of iterating over result sets, or accessing single results. The Query (or more precisely, the ResultProviderQuery<R extends Record>) could provide convenience execution methods, such as List<R> fetch(), or R fetchOne(), R fetchAny()...

    I have added this to the feature roadmap: http://sourceforge.net/apps/trac/jooq/ticket/100
    Thanks again for your input