Java: JOOQ persistence framework performance and feed back [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can 开发者_如何学编程be answered with facts and citations by editing this post.

Closed 9 years ago.

Improve this question

I've stumbled over a nice SQL builder framework, called JOOQ. BTW, in Russian JOOQ sounds like noun meaning "bug" (as an insect), "beetle" ;)

If you have any feedback about JOOQ, it's performance and such, please share. Links to blogs about JOOQ also welcome.

---

I think I should answer here also because I started using jooq one and a half months ago so I have some experience with it.

I wanted to use tool like jooq because:

• ORM is an overkill in my current project (distributed calculations platform for cluster) since I need to read and write only separate fields from db, not complete table rows and some of my queries are complex enough not to be executed by simple and lightweight ORMs.
• I wanted syntax autocomplete for my queries so that I don't need to keep my whole DB in mind
• I wanted to be able to write queries directly in Java so that compiler could check basic query syntax on build.
• I wanted my queries to be type-safe so that I couldn't accidentally pass a variable of one type, where another one is expected.
• I wanted SQL, but I wanted it very convenient and easy to use

Well, with jooq I was able to achieve all that. My main requirement was for jooq to handle complex enough queries (nested, with grouping etc.). That was fulfilled.

I also wanted to be able to run queries using as few lines of code as possible and was able to reach this with jooq fluent API which allows jquery-like calls to perform SELECTs.

On my way using of jooq I reported a one or two bugs and I must say, that they were fixed surprisingly fast.

I also missed some features and again I must say, that I already have almost all of them.

What I liked very much, is that jooq now uses SLF4J for reporting some very interesting data about it's performance as well as for outputting the actual queries it has built. It really helped me with debugging.

Jooq even generate Java artifacts for stored procedures, UDFs and updatable recordsets, which I don't use currently, though.

What's important, jooq transparently supports DB2, Derby, H2, HSQLDB, MySQL, Oracle, PostGreSQL, SQLite, SQL Server, Sybase SQL Anywhere. Pretty extensive list, I think.

Jooq has support forum in Google groups where Lukas is day and night ready to answer even the stupidest of my questions.

Jooq supports Maven and that's a great relief for me since all my Java projects are Maven-based. We still miss Maven plugin for generator, but that's not important since running generator is a piece of cake.

Writting my queries with jooq I suddenly discovered, that they became really portable because I almost never used any MySQL-specific feature in the code since jooq tries to be as portable as possible. For those who can't live with such peculiarities, as I know support for SQL extensions is under the way also.

What does jooq lack for a moment from my point of view?

Well, there is no fluent API for statements other than SELECT. This complicates code a little and makes UPDATE/DELETE statements a little more complicated to write. But I think this will be added soon. Just implemented in 1.5.9! Ha! Too quick for me ;)

And one more thing. Jooq has a good manual, but... I don't know. May be I just don't understand it's structure or architecture... When I started using jooq for the first time, I opened one page after another looking for a feature I need. For example, try to guess, where in jooq manual UPDATE and DELETE statements are described, looking at contents... But that's really subjective, I believe. I also cannot even explain, what's wrong with manual from my point of view. When I can, I will post a ticket or two ;)

Manual also is not really well-navigable since Trac has no automatic "here, there and back"-like links.

Well, for me in Moscow (Russia) Trac pages doesn't open fast also so reading manual is a little boring.

Manual also misses a good architecture description of jooq for contributors. Jooq follows design-by-contract principle it seems and when I wanted to learn how certain feature is implemented inside by using my usual Ctrl-Click on some method name in IDE, I ended up inside a dull interface with no implementation ;) Not that I'm too smart to start improving jooq right away, but certainly I would enjoy understanding how exactly jooq is architectured from ground to up.

It's a pity also that we cannot contribute to jooq manual. I expected it to be in some kind of wiki.

What I would also want to improve, is the way news are reported. I would prefer link to manual there or examples how this or that new feature works.

Release notes link in manual is really just a roadmap. I think, I will do that one myself tomorrow...

Jooq also have relatively small community currently, but I am glad to report that it doesn't affect code quality or the way new features are introduced.

Jooq is really a good project. I will stick to it for my future projects as well. I really like it.

---

You can also take a look on MentaBean, a lightweight ORM and SQL Builder that lets you be as close as possible to SQL offering a lot of help with the boilerplate code. Here is an example:

Programmatic Configuration:

private BeanConfig getUserBeanConfig() {

    // programmatic configuration for the bean... (no annotation or XML)

    BeanConfig config = new BeanConfig(User.class, "Users");
    config.pk("id", DBTypes.AUTOINCREMENT);
    config.field("username", DBTypes.STRING);
    config.field("birthdate", "bd", DBTypes.DATE); // note that the database column name is different
    config.field("status", new EnumValueType(User.Status.class));
    config.field("deleted", DBTypes.BOOLEANINT);
    config.field("insertTime", "insert_time", DBTypes.TIMESTAMP).defaultToNow("insertTime");

    return config;
}

   // create table Users(id integer primary key auto_increment, 
   // username varchar(25), bd datetime, status varchar(20), 
   // deleted tinyint, insert_time timestamp)

A simple SQL join query:

Post p = new Post(1);

StringBuilder query = new StringBuilder(256);
query.append("select ");
query.append(session.buildSelect(Post.class, "p"));
query.append(", ");
query.append(session.buildSelect(User.class, "u"));
query.append(" from Posts p join Users u on p.user_id = u.id");
query.append(" where p.id = ?");

stmt = conn.prepareStatement(query.toString());
stmt.setInt(1, p.getId());

rset = stmt.executeQuery();

if (rset.next()) {

    session.populateBean(rset, p, "p");

    u = new User();

    session.populateBean(rset, u, "u");

    p.setUser(u);
}

---

If you are looking for only a SQL builder solution. I have one project which is an ORM framework for Java but it is still premature and in continuous development however handles many primitive usages of databases. https://github.com/ahmetalpbalkan/orman

There is no documentation in this stage however it can build safe queries using only Java chain methods and can handle many SQL operations. It can also map classes-fields to tables-columns respectively.

Here's a sample query building operation for query

SELECT COUNT(*) FROM sailors WHERE
    rating>4 AND rating<9 GROUP BY rating HAVING AVG(age)>20;

Java code:

    QueryBuilder qb = QueryBuilder.getBuilder(QueryType.SELECT);
    System.out.println(qb
            .from("sailors")
            .where(
                    C.and(
                            C.gt("rating", 5),
                            C.lt("rating", 9)))
            .groupBy("rating")
            .having(
                    C.gt(
                            new OperationalField(QueryFieldOperation.AVG,
                    "age").toString(), 20)
                    ).getQuery());

(LOL just give up developing that framework!)

Most probably that won't work for you but just wanted to announce my project :P