Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JOOQ? Really?

 
Paul Anilprem
Enthuware Software Support
Ranch Hand
Posts: 3819
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After SpringDB, Hibernate, JDO, iBatis, JPA, do people still need another way to make java talk to db?
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Having multiple choices is never a problem....but honestly, I think you are right.
Maybe it would be interesting having something like LINQ ...
 
Lukas Eder
Ranch Hand
Posts: 49
5
Java Oracle Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul, Lukas from Data Geekery here (the company behind jOOQ).

Java is indeed a very interesting ecosystem where there have always been dozens of alternative ways to do the same thing. This happens when a platform vendor (Oracle) does not provide out-of-the-box solutions for most standard use-cases, e.g. the way Microsoft does on the .NET platform. Just check out how many web frameworks there are...

In our opinion, there are at least three very distinct ways of talking to the database from any client language like Java:

Using ORMs

When you're using an ORM, you probably don't care about querying as much as you care about persisting your objects, possibly a graph of objects. ORMs do this pretty well. They mostly help you pretend you don't need SQL in your everyday work. This is great for small projects, and for complex conversations with the database.

Among the APIs you've listed, this category would include:

- Hibernate
- JDO
- JPA (of which Hibernate is an implementation)

Using externalised SQL

Sometimes (in our opinion very often), an ORM doesn't do the job for you because you want to get down to the metal writing actual SQL, e.g. for reporting, analytics, bulk/batch data processing, or just for the odd query that is sligthly more complex than what an ORM can offer. When you do that, you have two choices: embedding your SQL in your Java classes, or externalising it. iBatis (nowadays called MyBatis) is the only popular API in this field (although jOOQ can do this as well):

- iBATIS / MyBatis

Using embedded SQL

jOOQ mostly fits the "embedded SQL" API category. In this category, you want your SQL statements close to your "other" data access logic: Your DAOs, your services, etc.

Among the APIs you've listed, the following fit this category:

- Spring (with JdbcTemplate)
- jOOQ

There is also:

- plain JDBC, of course

What jOOQ offers uniquely, unlike any other framework, is compile-time type safety for all your SQL statements. The Java compiler will type-check not only your SQL syntax for you, but also your tables and columns and their respective data types, as a source code generator will generate those objects for you, automatically. This goes further: Since you're constructing the AST (Abstract Syntax Tree) of your SQL statement dynamically at runtime, you also benefit from awesome features like:

- Dynamic SQL composition (you can store parts of your SQL statement and reuse it many times, e.g. a common predicate)
- SQL injection prevention (jOOQ forces you to use bind variables, without you actually noticing)
- SQL transformation capabilities (e.g. you can generate different SQL statements depending on your session, e.g. for security reasons)
- SQL standardisation (the same SQL AST will generate different SQL strings for MySQL and Oracle)

Also, in advanced use-cases where you're using stored procedures, jOOQ will generate objects for each procedure such that you can pretend those procedures are regular Java code, which is extremely easy to call.

Do people still need another way to make Java talk to a DB?

Yes, absolutely. jOOQ is gaining market share. None of the other vendors, nor Java EE have something like jOOQ (although JPA has Criteria API for JPQL, which is similar, but not for SQL). According to independent studies by ZeroTurnaround or InfoQ, the market share may already reach 5%. The surveys are from 2014, so we're expecting even more market share of the jOOQ Open Source Edition by today.

For more info, we've written up our vision for jOOQ on this website:
http://www.jooq.org/doc/latest/manual/preface

Note that jOOQ is not just about our product - we also believe that we're helping Java developers better understand the SQL language as a whole, which is why we publish many useful articles on our product blog, about Java and SQL: http://blog.jooq.org. We also offer trainings where we help Java developers go one step beyond with their SQL knowledge, helping them learn about things like:

- Common table expressions
- Table valued functions
- Lateral join / cross apply / outer apply
- Arrays and nested collections
- Stored procedures
- Row value expressions
- Ordered-set aggregate functions
- Window functions (my favourite)
- Bulk updates
- Advanced data types and ORDBMS features

All of the above features are made accessible very easily via jOOQ, too.

Hope this helps. Let me know if you have any additional questions.

- Lukas
 
Paul Anilprem
Enthuware Software Support
Ranch Hand
Posts: 3819
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Lukas,
Thank you for responding. I am sure JOOQ is a great and useful product. I was thinking from another angle when I posted the above. When a new framework comes up, someone in the team gets really gung ho about it and if the PM or TM is supportive, they use it in a project. So far so good. The problem comes when that person moves on to another role. Any new person coming in has to learn that exotic framework and understand its nuances before they can be productive. The new person may not be as excited about this exotic framework as the previous person and gets frustrated. The same cycle continues for the life of the project.

Of course, every new framework starts the same way. At one time, Spring and Hibernate also faced the same issue. However, at that time there were no other options. They were really pioneers (in the Java world at least) and every one had to learn something or the other to solve the problems that Spring and Hibernate solved.

But now, I have to think a hundred times before introducing any new framework in the system. Unless the new framework adds a huge value to the system, or the problem at hand cannot be solved by commonly used frameworks, I will simply not introduce a new point of failure in the system. I understand that I may be losing a few neat features but the question in my mind is its maintainability. Can the same thing be achieved with Spring DB or Hibernate? If the answer is yes, even if it requires a little more effort, I would rather use those instead of a new framework because those are very well known entities. Experts on these frameworks can be found easily and making a new person learn these frameworks incurs less resistance because the skills are marketable.

Having said that, new projects/applications may be benefited by new frameworks that solve niche problems. So good luck to you I hope your framework becomes the next hibernate

Paul.
 
Lukas Eder
Ranch Hand
Posts: 49
5
Java Oracle Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul, I totally understand your point of view. I have the same impression every time I need to do something in the UI (I just prefer JSP with JSTL, or perhaps XSLT). And when I do HTML/JavaScript stuff, I won't go far beyond jQuery.

Perhaps, right now, you don't see the thing we think everyone will love about jOOQ in three years - and that thing is only offered by jOOQ right now. Type safe, composable SQL. The only reasons we can see why a new team member does not appreciate that is because they are in one of the other categories I've mentioned: 1) they absolutely want to use an ORM and not SQL, or 2) they absolutely want to use SQL as an external language to Java. For everyone else, jOOQ just feels incredibly natural, as if the Java compiler could compile real SQL statements (with auto-completion, syntax checking, etc.) So in fact, jOOQ requires much less effort than any other API you've mentioned (specifically JDO, which is pretty dead, or Hibernate/JPA, which is very difficult).

If everything you said was true, we wouldn't even have Java nor SQL in the first place. At the time, there was C and C++, which were "good enough". There were hierarchic and networked databases, which were "good enough". Why learn the new thing when there are experts on the old ones?

But you're absolutely right - you don't have to be the first one to jump on the new "exotic" framework (you wouldn't be, btw. The market share is already very significant). I'm sure we'll meet again, pretty soon. And I'm sure you'll love using jOOQ instead of all the other options that you've mentioned.

niche problems


It's THE problem. .NET has LINQ and everyone loves using it. Java now has jOOQ and soon, everyone will love using it.

I hope your framework becomes the next hibernate


It will, and I'm positive that the Hibernate teams will refactor their internals to build on top of jOOQ.

Cheers,
Lukas
 
Jelle Klap
Bartender
Posts: 1952
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I quite like Querydsl myself. Yes, it is yet another way to write queries, but the typesafe fluent API is so much more convenient than writing JPQL queries or using a Criteria API. Haven't tried jOOQ yet, but it looks good to me. Sometimes ORM just doesn't seem to fit, and where I'd use Spring's JdbcTtemplate, I guess I could give jOOQ a try instead.
 
Paul Anilprem
Enthuware Software Support
Ranch Hand
Posts: 3819
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your detailed post, Lukas. I browsed through JOOQ faq and saw the sample program. I understand that you need to first generate the Java classes for a particular set of tables (the set that you want to use in your application). You use these classes instead of raw sql to form your interaction with the database. It is the use of these classes that give you all the benefits that you mentioned, mainly compile time type safety. Assuming that this understanding is correct I have a couple of questions:

1. One great thing about JPA is the use of annotations. They eliminate almost all property files. This removes a whole set of problems associated with maintaining those files. The basic idea here is to reduce the number of steps that you need to take to do something. This helps a lot in the development cycle. How do you see JOOQ faring in this respect?
I remember around 1997/8, there was a similar tool (I am forgetting forgot the name but something like Java Relational Binding). That tool did something similar. You first run the tool to create a set of Java classes using your db structure and those Java classes contained all the db related code. Now, it did not provide the kind of functionality that JOOQ provides but I remember that that two step process was a real pain. We finally gave it up.

2. What are your thoughts on "fine tuning" the generated queries? I am yet to see a project that did not spend a substantial amount of time in "fine tuning" the queries. No matter what DB abstraction layer you use, there is always something that requires you to tweak some queries. Usually, it is because some report is too slow or the joins are too damn complicated to retrieve objects of a particular class. I would like to know what is the impact of using JOOQ on this activity.

thank you,
Paul.
 
Lukas Eder
Ranch Hand
Posts: 49
5
Java Oracle Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

Assuming that this understanding is correct...


It is, although, you're not strictly required to profit from that type safety - e.g. when you don't know the exact names of your tables/columns at compile time...

1. One great thing about JPA is the use of annotations. They eliminate almost all property files. This removes a whole set of problems associated with maintaining those files. The basic idea here is to reduce the number of steps that you need to take to do something. This helps a lot in the development cycle. How do you see JOOQ faring in this respect?


It works the same way as JPA with respect of reducing the number of steps needed to do something. However, in JPA - many people design their Java entities (with annotations) first, before they often generate the DDL that backs these Java entities. jOOQ always goes the other way round, embracing an RDBMS-first model, where you only manage DDL, and let the code generator generate the Java code for you.

Which approach better suits you is up to you. The Java-first approach might lead to faster development cycles at first. The SQL-first approach is certainly more desireable once you go to production.

I personally believe that you should always design your database schema first, but certainly, not everyone will agree on that.

Regarding the "two step process" I'm not sure if I follow. There are always two steps:

1. Designing your "main" model (JPA: Java, jOOQ: SQL)
2. Generating your "derived" model (JPA: SQL, jOOQ: Java)

You can automate this with your CI, e.g. using Flyway: http://stackoverflow.com/questions/28402745/using-embedded-database-with-flyway-and-jooq-in-maven-for-continuous-integration. And if you're using JRebel, you can even hot-deploy the updated code into a running JVM.

2. What are your thoughts on "fine tuning" the generated queries?


Yes, being able to tune is very important. With jOOQ, the generated SQL is exactly the way you wrote it, just as with JDBC. This task is certainly a lot easier with jOOQ than with JPA

Hope this helps,
Lukas
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic