jOOQ Newsletter: May 21, 2014 – jOOQ Community Video Tutorials

Subscribe to this newsletter here

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world. Here are:

Chris Martin who聽has switched jobs and is now really missing jOOQ. Too bad we can’t offer jOOQ for Python, Chris!

Moutaz Salem who聽had been waiting for jOOQ for all these years. But we’re here now, Moutaz. The pain has ended!

Simon Martinelli who聽is contemplating migration to jOOQ from Hibernate. Go for it Simon!

Thanks for the shouts, guys! You make the jOOQ experience rock!

Closing in on jOOQ 3.4

We’re almost there! In early June, we’ll ship the awesome jOOQ 3.4 – a very ambitious project in which we’re finally going to tackle a couple of very important missing features. The ones that have made it through our roadmap optimisations are:

  • Support for type safe DDL statements.
  • Support for common table expressions.
  • A unified SQL transformation API.

SQL transformation can be very effective for dialect standardisation, multi-tenancy, soft-deleting, row-level security and many more features. Have you used it yet? You should!

Better SQL transformation APIs will make your VisitListeners even more powerful, and custom QueryParts even easier to implement. If you’re a hard-core SQL transformation aficionado, this is great news for you as you can exert even more control over jOOQ’s SQL rendering.

In an earlier newsletter, we had promised to implement Informix support, but we had to optimise our roadmap to ensure the above gets shipped with the usual quality that we’re offering to our customers. We’re very sorry if that has affected you. We’ll try to have this in jOOQ 3.5, though.

Of course, there are lots of other minor improvements, so stay tuned for the release, very soon

Community Zone – The jOOQ aficionados have been active!

In previous newsletters, we’ve advertised jOOQ community blog post contributions, which are a really awesome way to show your love – thank you very much, again!

In the previous weeks, however, we’ve discovered these two video contributions:

A jOOQ video tutorial by Ruben Alexander More Valencia聽(Spanish)

A jOOQ and Flyway presentation by Dmitry Lebedev and Rustam Arslanov (Latvian)

This is really great, thank you so much, guys! If you want to present jOOQ to your local JUG, do so today! If you need any brand material, example slides, example projects, or other material, please do聽contact us. We’re more than happy to provide you with what you need.

SQL Zone – Sort indirection

Every now and then, you may want to sort your records in a bit of a “quirky” way, i.e. by explicitly putting 1-2 values at the top and all the other values below. We refer to this as “sort indirection”, which can be achieved with a clever ORDER BY clause along with a CASE expression:

ORDER BY
  CASE WHEN name = 'val1' THEN 1
       WHEN name = 'val2' THEN 2
       WHEN name = 'val3' THEN 3
       WHEN name = 'val4' THEN 4
  END

Of course, jOOQ natively implements a couple of convenience methods to achieve the same.

Read the full article here:
https://blog.jooq.org/2014/05/07/how-to-implement-sort-indirection-in-sql/

SQL Zone – Identifier madness

We’ve said it before. We’ll say it again.

You should NEVER rely on case-sensitivity (or case-insensitivity) when writing vendor-agnostic SQL. It is hard enough to remember the rules in one single database, but if you ship your software to support various RDBMS, always force your identifiers to be case-sensitive (and settle for either all-uppercase or all-lowercase).

Fortunately, jOOQ takes care of case-sensitivity by default, quoting all your identifier as they were reported from the database.

You want to know why this is important? Consider reading this article where the various caveats and differences between identifier parts are explained and compared between databases:
http://wiki.ispirer.com/sqlways/sybase/identifiers

Upcoming Events

GeeCON in Krak贸w聽is already over. Great conference and many great speakers!

As always in recent conferences, topics mainly included JavaScript, Akka, Node.js and Vert.x and other hip subjects. But we’re always amazed to see how big of a crowd can still be motivated into a “plain old SQL” talk like ours. SQL (and the latest standards) is still very awesome, and every time we explain the merits of window functions, we see hundreds of wide-open eyes, with awe and revelation.

;-)

Have you missed any of our previous jOOQ talks? Soon you’ll get another chance to hear us talk about jOOQ or SQL in general in any of these upcoming events:

Stay informed about 2014 events on聽www.jooq.org/news.

How to Implement Sort Indirection in SQL

I’ve recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order.

They wrote

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')

They got

CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
GE526OTACCD3ANPH-149839
NI564FAACJSFANPH-162605

They wanted

CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
NI564FAACJSFANPH-162605
GE526OTACCD3ANPH-149839

Very often, according to your business rules, sorting orders are not “natural”, as in numeric sorting or in alpha-numeric sorting. Some business rule probably specified that GE526OTACCD3ANPH-149839 needs to appear last in a list. Or the user might have re-arranged product names in their screen with drag and drop, producing new sort order.

We could discuss, of course, if such sorting should be performed in the UI layer or not, but let’s assume that the business case or the performance requirements or the general architecture needed for this sorting to be done in the database. How to do it? Through…

Sort Indirection

In fact, you don’t want to sort by the product name, but by a pre-defined enumeration of such names. In other words, you want a function like this:

CE367FAACDHCANPH-151556 -> 1
CE367FAACEX9ANPH-153877 -> 2
NI564FAACJSFANPH-162605 -> 3
GE526OTACCD3ANPH-149839 -> 4

With plain SQL, there are many ways to do the above. Here are two of them (also seen in my Stack Overflow answer):

By using a CASE expression

You can tell the database the explicit sort indirection easily, using a CASE expression in your ORDER BY clause:

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')
ORDER BY 
  CASE WHEN name = 'CE367FAACDHCANPH-151556' THEN 1
       WHEN name = 'CE367FAACEX9ANPH-153877' THEN 2
       WHEN name = 'NI564FAACJSFANPH-162605' THEN 3
       WHEN name = 'GE526OTACCD3ANPH-149839' THEN 4
  END

Note that I’ve used the CASE WHEN predicate THEN value END syntax, because this is implemented in all SQL dialects. Alternatively (if you’re not using Apache Derby), you could also save some characters when typing, writing:

ORDER BY 
  CASE name WHEN 'CE367FAACDHCANPH-151556' THEN 1
            WHEN 'CE367FAACEX9ANPH-153877' THEN 2
            WHEN 'NI564FAACJSFANPH-162605' THEN 3
            WHEN 'GE526OTACCD3ANPH-149839' THEN 4
  END

Of course, this requires repeating the same values in the predicate and in the sort indirection. This is why, in some cases, you might be more lucky …

By using INNER JOIN

In the following example, the predicate and the sort indirection are taken care of in a simple derived table that is INNER JOIN‘ed to the original query:

SELECT product.name
FROM product
JOIN (
  VALUES('CE367FAACDHCANPH-151556', 1),
        ('CE367FAACEX9ANPH-153877', 2),
        ('NI564FAACJSFANPH-162605', 3),
        ('GE526OTACCD3ANPH-149839', 4)
) AS sort (name, sort)
ON product.name = sort.name
ORDER BY sort.sort

The above example is using PostgreSQL syntax, but you might be able to implement the same in a different way in your database.

Using jOOQ’s sort indirection API

Sort indirection is a bit tedious to write out, which is why jOOQ has a special syntax for this kind of use-case, which is also documented in the manual. Any of the following statements perform the same as the above query:

// jOOQ generates 1, 2, 3, 4 as values in the
// generated CASE expression
DSL.using(configuration)
   .select(PRODUCT.NAME)
   .from(PRODUCT)
   .where(NAME.in(
      "CE367FAACDHCANPH-151556",
      "CE367FAACEX9ANPH-153877",
      "NI564FAACJSFANPH-162605",
      "GE526OTACCD3ANPH-149839"
   ))
   .orderBy(PRODUCT.NAME.sortAsc(
      "CE367FAACDHCANPH-151556",
      "CE367FAACEX9ANPH-153877",
      "NI564FAACJSFANPH-162605",
      "GE526OTACCD3ANPH-149839"
   ))
   .fetch();

// You can choose your own indirection values to
// be generated in the CASE expression
   .orderBy(PRODUCT.NAME.sort(
      new HashMap<String, Integer>() {{
        put("CE367FAACDHCANPH-151556", 2);
        put("CE367FAACEX9ANPH-153877", 3);
        put("NI564FAACJSFANPH-162605", 5);
        put("GE526OTACCD3ANPH-149839", 8);
      }}
   ))

Conclusion

Sort indirection is a nice trick to have up your sleeves every now and then. Never forget that you can put almost arbitrary column expressions in your SQL statement’s ORDER BY clause. Use them!