Win a copy of Bad Programming Practices 101 (e-book) this week in the Beginning Java forum!

Lukas Eder

Ranch Hand
+ Follow
since Jul 22, 2013
Lukas likes ...
Java Oracle Postgres Database
Java and databases are my professional passion. When they work together, great software can evolve. That's why we have created jOOQ
Zurich
Cows and Likes
Cows
Total received
5
In last 30 days
0
Total given
0
Likes
Total received
7
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Lukas Eder

While you cannot use PL/SQL RECORD types directly as many others suggest, you can create an anonymous PL/SQL block and use that from JDBC. For instance:



As you can see, this now no longer requires you to pass the record type to the JDBC driver, but you can pass the individual record attributes instead

I've blogged about this more in detail in a blog post:
https://blog.jooq.org/2016/11/08/use-jooq-to-read-write-oracle-plsql-record-types/

The post also explains how you can automate this task, in case you have many of these record types in procedures / functions
1 year ago
Yes exactly. You get integration for the commercial databases and our commercial support as well as warranties and maintenance, of course.
Excellent question Tim, and I'm looking forward to meeting you next Monday in Belfast, to discuss more details!

I cannot give you a complete impression of what our customers feel like, when using jOOQ - and there are certainly very different setups, depending on how deep you will integrate with SQL. Do note, though, that the commercial jOOQ licenses are floating developer workstation licenses. You can re-assign them at will to any developer who happens to work with the jOOQ API at any given day. If you're writing as little SQL as you have described, 1 license might be enough for your team. (The rest of the team will profit from the free distribution right, just like your test and production servers). 1 license amounts to slightly more than 1 EUR per day.

To answer your questions: jOOQ makes most sense as soon as any of the below apply:

- You have a "large" schema, or multiple schemas (50+ tables)
- You have complex queries
- You write lots of dynamic SQL
- You are using stored procedures
- You are using / supporting more than one RDBMS

Once any of the above applies, of course, you will get all the additional benefits for free, like CSV/JSON import/export, SQL transformation features, active records, multi-tenancy capability, etc.

Does this help? I'll happily provide you with more info also here on Code Ranch, if you have any additional questions about any of the above.
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
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
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
Great, Cathal. Let me know if you have any further questions - I will be more than happy to help
My answer is obviously biased as I work for Data Geekery, the company behind jOOQ. We've helped a couple of clients migrate their JDBC-based applications to jOOQ - none of them have ever looked back, especially when using Oracle. The main advantages as you've already noticed are:

  • Being able to version control the generated representation of the database schema. The jOOQ code generator produces a kind of "snapshot" of your schema, which many jOOQ users like to check into version control. It is very easy to see what version of the schema (with individual tables, columns) corresponded to what version of the client code
  • The same feature allows you to track changes and their implications very easily at compile time, rather than at run time. When you evolve your database schema, you'll immediately notice what parts of your code needs to be adapted as columns have been added / removed, or as relations have gone from 1:1 to 1:N or even to M:N. This is particularly useful for your coming months, as you will be evolving your DB schema heavily. It's just much easier to keep the Java client code in sync when there's something for the compiler to compile
  • If you're using stored procedures, integrating them into your SQL statements type safely, or calling them standalone from your Java code becomes much easier. The same is true for Oracle AQ, or OBJECT and TABLE types


  • Regarding the very interesting question of building and version controlling generated source code, we've written a post on the subject:

    - http://blog.jooq.org/2014/09/08/look-no-further-the-final-answer-to-where-to-put-generated-code/

    The post is also based on this discussion on the jOOQ User Group, including valuable advice:

    - https://groups.google.com/forum/#!msg/jooq-user/kQO757qJPbE/UszW4aUODdQJ

    I'm very happy to also respond to other, particular questions that you may have.
    10 More Common Mistakes Java Developers Make when Writing SQL

    I was positively surprised to see how popular my recent listing about 10 Common Mistakes Java Developers Make when Writing SQL was, both on my own blog and on my syndication partner DZone. The popularity shows a couple of things:

  • How important SQL is to the professional Java world.
  • How common it is to forget about some basic SQL things.
  • How well SQL-centric libraries such as jOOQ or MyBatis are responding to market needs, by embracing SQL.


  • The common mistakes I listed previously are far from complete, so I will treat you to a sequel of 10 subtly less common, yet equally interesting mistakes Java developers make when writing SQL.

    Zachary Pry wrote:I was preparing presentation for Midao JDBC. I finished with it and did a little bit more. [...]



    Thanks. I'll have a look, soon!

    Ulf Dittmer wrote:

    How is authorship and reference attributed in that Wiki?


    The wiki is a community effort, so there are no author names attached to it. Just putting a link to your writeup with a short abstract might also be a good addition to the wiki.



    So I've added all 10 common mistakes to the Wiki here:

    https://www.coderanch.com/how-to/java/SqlBestPractices

    Feel free to moderate the entries.

    Ulf Dittmer wrote:

    How is authorship and reference attributed in that Wiki?


    The wiki is a community effort, so there are no author names attached to it. Just putting a link to your writeup with a short abstract might also be a good addition to the wiki.



    Thanks for the hints. I'm still trying to get used to this forum and what can be done here. So I have now copied one sample paragraph to the Wiki as a grounds for discussion:

    https://www.coderanch.com/how-to/java/SqlForgettingAboutNull

    Would that be an acceptable format? I don't want to spend too much time rewriting things in a new format or new wording. But I'd be fine with copy pasting from my own blog as long as the source is cited. Otherwise, feel free to summarise what I've posted in your own words and put it up on the Wiki :-)

    Martin Vajsar wrote:I have always thought that the most common mistake is not using PreparedStatement, either out of ignorance, or - less often - because of a belief, usually based on a flawed performance test, that a simple Statement is faster.



    Hmm, true, another one for the "10 More Common Mistakes" sequel post. Of course, Statement can be faster than PreparedStatement when you explicitly want to avoid problems derived from bad statistics and histograms in your database, if your database supports cursor caching (talking about Oracle). But even then, the problem should probably be fixed in the database, not in Java.

    Martin Vajsar wrote:But in any case, these are important points and a nice article. Lucas, wouldn't you want to incorporate some of these points into our SQL Best practices wiki page? It doesn't cover anything from your article yet.



    How is authorship and reference attributed in that Wiki?

    Jeanne Boyarsky wrote:Why not include returning too many columns if you are including not returning too many rows?



    I'll include it in the "10 More Common Mistakes" article :-)
    10 Common Mistakes Java Developers Make when Writing SQL

    Java developers mix object-oriented thinking with imperative thinking, depending on their levels of:

  • Skill (anyone can code imperatively)
  • Dogma (some use the "Pattern-Pattern", i.e. the pattern of applying patterns everywhere and giving them names)
  • Mood (true OO is more clumsy to write than imperative code. At first)


  • But when Java developers write SQL, everything changes. SQL is a declarative language that has nothing to do with either object-oriented or imperative thinking. It is very easy to express a query in SQL. It is not so easy to express it optimally or correctly. Not only do developers need to re-think their programming paradigm, they also need to think in terms of set theory.

    In this post, I have listed some common mistakes that a Java developer makes when writing SQL