Comment by jmartrican | Hacker News Reader

Comment by jmartrican | Hacker News Reader

In Java I'm a big fan of using SQL directly versus ORM. To that end I highly recommend JOOQ. I library that allows for object oriented SQL. Its not ORM, so you still compose SQL, you just get a lot objects and methods to allow you to construct your SQL in an OO, and functional, programming type way.

It also is a DB first approach. Where you construct your DB schemas and tables first, then use JOOQ to create objects that match your DB schemas. These objects allow you to reference the tables and fields of your DB in an OO way. From that point of view it may seem like ORM, but you use SQL to interact with the your DB.

Two of the things I really about JOOQ is that you stay in-tuned to SQL, which as we all know is very powerful and popular. Second, your SQL can get constructed in a procedural way based on input. So you can added to based on , for example, if query param were provided.

The creator of JOOQ, Lucas, has done good videos out there comparing SQL to ORM. What's cool about those videos is he does not mention JOOQ, maybe at the end. The videos are just comparing ORM to SQL and makes the case for SQL being way more powerful than ORM.

My team (Spring Boot / Angular greenfield app in an enterprise) has a guy that transitioned over from .NET and is totally new to the Java ecosystem. He has 30+ years experience with SQL and can't stand entity frameworks or make them do the best-practice stuff he knows we should be doing, but with JOOQ he's had a lot of success bringing his extensive knowledge base to bear on our project.

I fully understand him.

Dapper or ADO.NET, and with my Java hat on, myBatis.

With jOOQ you still don't write SQL, you write the DSL and still spend a lot of time looking up docs for how to write the query in the DSL, when all you really want is to write the SQL query without the ugliness of JDBC. To this end I highly recommend JDBI.

If the Java proposal for multi-line strings ever gets done it will make writing SQL queries a lot nicer.

Never heard of JDBI before. Will check it out. Its true there is a lot of DSL looking up. It does decrease over time, but then ramps back up as you do more complicated SQL... lol.

Please note the DSL is a one-to-one mapping to SQL. So its still SQL, you just have to identify the correct method that maps to your SQL element.

Soooorta. It can actually translate between SQL dialects so it's a more like 1:10 mapping.

I like that line of thoughts. Given that we have a parser, too, it's more like a 27:27 mapping: https://www.jooq.org/translate

You're here!

I asked you a bunch of questions and even filed a few issues a few months back.

You were incredibly patient and helpful as I tried to sort out the performance characteristics of the a few bulk insert approaches.

I want to publicly thank you again for all your help. You've set a uniquely high bar with all your work on and around jOOq.

Thanks for your nice words, I really appreciate it! :)

> If the Java proposal for multi-line strings ever gets done it will make writing SQL queries a lot nicer.

Groovy's multiline strings are really nice that way. Groovy essentially bundles its own version of JDBI as well (but better because it does automatic variable interpolation).

It is already accepted for Java 14.

I've had people tell me they learned a ton about SQL constructs they were unaware of because they just discovered about them by auto completing on the jOOQ API.

If you know SQL, you won't spend much time looking up how the jOOQ API thinks about it, and if something is not possible, you can always use jOOQ's templating feature: https://www.jooq.org/doc/latest/manual/sql-building/plain-sq...

In any case, I always recommend people build a ton of view libraries directly in their database, and then query them with jOOQ when they need dynamic queries on top...

Other alternative worth mentioning is myBatis.

Not a 100% compatible substitution but Spark can be even more OO and easier to manage.

Reply on news.ycombinator.com