Tag Archive | jooq

QueryDSL vs. jOOQ. Feature Completeness vs. Now More Than Ever


This week, Timo Westkämper from QueryDSL has announced feature completeness on the QueryDSL user group, along with his call for contributions and increased focus on bugfixes and documentation.

Timo and us, we have always been in close contact, observing each other’s products. In the beginning of jOOQ in 2009, QueryDSL was ahead of us.

But we learned quickly and removed all of our shortcomings such that jOOQ and QueryDSL were quickly at eye level by 2011. Ever since, we have been taking inspiration from one another, as in the end, we have had similar goals. Today, whenever someone is looking for a querying DSL, people tend to recommend either of our tools:

QueryDSL is often a good choice in JPA-based environments, while jOOQ is mostly the best choice in SQL-based environments, although jOOQ has already been given some credit in JPA-based environments as well:

Anyway, today, we’d like to congratulate Timo to his new job, and to QueryDSL’s feature completeness.

jOOQ, on the other hand, is far from feature complete.

jOOQ is what SQLJ should have been from the beginning.

We’re only at the beginning. Java and SQL are the two platforms that are used by most of the developers on this planet. According to db-engines.com, almost every popular DBMS is a SQL-based relational DBMS. According to TIOBE, Java currently ranks #2 among all languages.

We strongly believe that all of these developers are in dire need for better SQL integration into the Java language. While ORMs and JPA are very well integrated, SQL is not, and that is what we are working on. jOOQ will be feature complete when the Java compiler can natively compile actual SQL code and SQL code fragments into jOOQ, which will serve as its backing AST model for further SQL transformation.

Until we reach that goal, we’ll be adding support for more SQL goodness. A small selection of things that we already support, beyond QueryDSL’s “feature completeness”:

  • Table-valued functions
  • PIVOT tables
  • DDL (with jOOQ 3.4)
  • MERGE statement
  • Derived tables and derived column lists
  • Row value expressions
  • Flashback query
  • Window functions
  • Ordered aggregate functions
  • Common table expressions (with jOOQ 3.4)
  • Object-oriented PL/SQL
  • User-defined types
  • Hierarchical SQL
  • Custom SQL transformation
  • 16 supported RDBMS (even MS Access!)
  • … you name it

Our roadmap is full of great ideas. There’s plenty of work, so let’s get going! Join us, your partner for…

jOOQ is the best way to write SQL in Java

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:
http://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.

Java 8 Friday: API Designers, be Careful


At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

Lean Functional API Design

With Java 8, API design has gotten a whole lot more interesting, but also a bit harder. As a successful API designer, it will no longer suffice to think about all sorts of object-oriented aspects of your API, you will now also need to consider functional aspects of it. In other words, instead of simply providing methods like:

void performAction(Parameter parameter);

// Call the above:
object.performAction(new Parameter(...));

… you should now think about whether your method arguments are better modelled as functions for lazy evaluation:

// Keep the existing method for convenience
// and for backwards compatibility
void performAction(Parameter parameter);

// Overload the existing method with the new
// functional one:
void performAction(Supplier<Parameter> parameter);

// Call the above:
object.performAction(() -> new Parameter(...));

This is great. Your API can be Java-8 ready even before you’re actually targeting Java 8. But if you’re going this way, there are a couple of things to consider.

JDK dependency

The above example makes use of the JDK 8 Supplier type. This type is not available before the JDK 8, so if you’re using it, you’re going to limit your APIs use to the JDK 8. If you want to continue supporting older Java versions, you’ll have to roll your own supplier, or maybe use Callable, which has been available since Java 5:

// Overload the existing method with the new
// functional one:
void performAction(Callable<Parameter> parameter);

// Call the above:
object.performAction(() -> new Parameter(...));

One advantage of using Callable is the fact that your lambda expressions (or “classic” Callable implementations, or nested / inner classes) are allowed to throw checked exceptions. We’ve blogged about another possibility to circumvent this limitation, here.

Overloading

While it is (probably) perfectly fine to overload these two methods

void performAction(Parameter parameter);
void performAction(Supplier<Parameter> parameter);

… you should stay wary when overloading “more similar” methods, like these ones:

void performAction(Supplier<Parameter> parameter);
void performAction(Callable<Parameter> parameter);

If you produce the above API, your API’s client code will not be able to make use of lambda expressions, as there is no way of disambiguating a lambda that is a Supplier from a lambda that is a Callable. We’ve also mentioned this in a previous blog post.

“void-compatible” vs “value-compatible”

I’ve recently (re-)discovered this interesting early JDK 8 compiler bug, where the compiler wasn’t able to disambiguate the following:

void run(Consumer<Integer> consumer);
void run(Function<Integer, Integer> function);

// Remember, the above types are roughly:
interface Consumer<T> {
    void accept(T t);
//  ^^^^ void-compatible
}

interface Function<T, R> {
    R apply(T t);
//  ^ value-compatible
}

The terms “void-compatible” and “value-compatible” are defined in the JLS §15.27.2 for lambda expressions. According to the JLS, the following two calls are not ambiguous:

// Only run(Consumer) is applicable
run(i -> {});

// Only run(Function) is applicable
run(i -> 1);

In other words, it is safe to overload a method to take two “similar” argument types, such as Consumer and Function, as lambda expressions used to express method arguments will not be ambiguous.

This is quite useful, because having an optional return value is very elegant when you’re using lambda expressions. Consider the upcoming jOOQ 3.4 transaction API, which is roughly summarised as such:


// This uses a "void-compatible" lambda
ctx.transaction(c -> {
    DSL.using(c).insertInto(...).execute();
    DSL.using(c).update(...).execute();
});

// This uses a "value-compatible" lambda
Integer result =
ctx.transaction(c -> {
    DSL.using(c).update(...).execute();
    DSL.using(c).delete(...).execute();

    return 42;
});

In the above example, the first call resolves to TransactionalRunnable whereas the second call resolves to TransactionalCallable whose API are like these:

interface TransactionalRunnable {
    void run(Configuration c) throws Exception;
}

interface TransactionalCallable<T> {
    T run(Configuration c) throws Exception;
}

Note, though, that as of JDK 1.8.0_05 and Eclipse Kepler (with the Java 8 support patch), this ambiguity resolution does not yet work because of these bugs:

So, in order to stay on the safe side, maybe you could just simply avoid overloading.

Generic methods are not SAMs

Do note that “SAM” interfaces that contain a single abstract generic method are NOT SAMs in the sense for them to be eligible as lambda expression targets. The following type will never form any lambda expression:

interface NotASAM {
    <T> void run(T t);
}

This is specified in the JLS §15.27.3

A lambda expression is congruent with a function type if all of the following are true:

  • The function type has no type parameters.
  • [ ... ]

What do you have to do now?

If you’re an API designer, you should now start writing unit tests / integration tests also in Java 8. Why? For the simple reason that if you don’t you’ll get your API wrong in subtle ways for those users that are actually using it with Java 8. These things are extremely subtle. Getting them right takes a bit of practice and a lot of regression tests. Do you think you’d like to overload a method? Be sure you don’t break client API that is calling the original method with a lambda.

That’s it for today. Stay tuned for more awesome Java 8 content on this blog.

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!

jOOQ Newsletter: April 30, 2014


Subscribe to this newsletter here

Tweet of the Day

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

Santiago M. Mola who appreciates jOOQ’s affinity to SQL features and its correctness

Vlad Mihalcea who’s reading the jOOQ docs to learn about SQL

Thanks for the shouts, guys! It looks our attempts to make our users competent and enthusiast SQL aficionados are effective. If you, our customers, are productive with Java and SQL, then we did our job right. Last but not least:

Tom Bujok who’s is now a happy SQL aficionado.

Want awesome free jOOQ stickers yourself? Contact us!.

Book Promotion – 10% off SQL Performance Explained

This book is a must-read for every SQL developer! Invest a very reasonable amount of money and around six hours of quality reading time, and you’ll stop wasting days and days of confused SQL tuning guesswork, because once you understand indexes, you are able to solve 90% of your performance issues.

Get your copy of SQL Performance Explained now! Use the limited-time “jOOQ” coupon to get a 10% partner discount:

http://sql-performance-explained.com

Community Zone – The jOOQ aficionados have been active!

In last week’s newsletter, we’ve praised Petri Kainulainen’s latest piece of work, his jOOQ / Spring / CRUD tutorial:

But that was not his last shout. He’s already promised another tutorial explaining how to do sorting and pagination with jOOQ:

Luckily for us Markus Winand – our SQL Performance Partner jumped in and reminded us of the difference between OFFSET pagination and KEYSET pagination. More details about these tools in his book – see above.

Michael Hughes from codinginthetrenches.com has also been discovering the benefits of using a SQL DSL, which he has explained in his article here. We always like it when jOOQ users realise the power of using jOOQ with stored procedures. You will be indefinitely more efficient compared to the standards JDBC or JPA.

Krisztian Horvath from SequenceIQ has published a post about using jOOQ with HBase - why not? If you’re restricting yourself to using plain SQL, you can use the jOOQ Open Source Edition with a lot of databases, although you’re going to be missing out on the coolest features and productivity boosters, of course.

Harmeet Singh appreciates the jOOQ code generator in his introductory blog post, an important point to mention. jOOQ’s code generator has helped our customers avoid so many errors already at compile-time, instead of waiting for them to appear in production, at runtime.

SQL Zone – When to use views

There are a couple of great reasons why you should occasionally (or systematically) use views instead of tables. These reasons include:

  • Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema.
  • Views can model complex joins easily.
  • Views can hide database-specific stuff from you, e.g. using Oracle’s SYS_CONTEXT for security checks.
  • Views can be useful for managing GRANTS, and thus hiding tables from you.
  • Views can help you with backwards compatibility when you change the underlying schema, but leave the (versioned) views in place.
  • Views can implement an additional security layer, e.g. by using Oracle’s WITH CHECK OPTION directly in the view.

See the relevant Stack Overflow question here.

SQL Zone – Calculating a running total

We’ve said it before. Use your database for your calculations, when this is reasonable. And by this, we don’t just mean simple COUNT(*) or SUM(AMOUNT) calls. We cannot stress the fact that you should be using window functions enough.

Imagine you want to do something fancy like calculating a balance value on every bank transaction based on the current balance and each transaction’s amount:

| ID   | VALUE_DATE | AMOUNT |  BALANCE |
|------|------------|--------|----------|
| 9997 | 2014-03-18 |  99.17 | 19985.81 |
| 9981 | 2014-03-16 |  71.44 | 19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 |  -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

You can do this in SQL! Check out our recent blog post about how to calculate a SQL standard running total directly in SQL!

Upcoming Events

The next weeks are a bit quiet, but we’re going to be active again in May and June! 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 can I do This? – With SQL of Course!


Haven’t we all been wondering:

How can I do this? I have these data in Excel and I want to group / sort / assign / combine …

While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why not just use SQL?

The use-case: Counting neighboring colours in a stadium choreography

This might not be an everyday use-case for many of you, but for our office friends at FanPictor, it is. They’re creating software to draw a fan choreography directly into a stadium. Here’s the use-case on a high level:

Draw your fan choreography with FanPictor

Draw your fan choreography with FanPictor. In this case, a tribute to Roger Federer

It’s immediately clear what this fun software does, right?

  • You submit a choreography suggestion
  • The event organisers choose the best submission
  • The event organisers export the choreography as an Excel file
  • The Excel file is fed into a print shop, printing red/red, red/white, white/red, white/white panels (or any other colours)
  • The event helpers distribute the coloured panels on the appropriate seat
  • The fans get all excited

Having a look at the Excel spreadsheet

So this is what the Excel spreadsheet looks like:

Print shop instructions

Print shop instructions

Now, distributing these panels is silly, repetitive work. From experience, our friends at FanPictor wanted to have something along the lines of this, instead:

Print shop instructions for dummies

Print shop instructions for dummies

Notice that there are instructions associated with each panel to indicate:

  • … whether a consecutive row of identical panels starts or stops
  • … how many identical panels there are in such a row

“consecutive” means that within a stadium sector and row, there are adjacent seats with the same (Scene1, Scene2) tuple.

How do we solve this problem?

We solve this problem with SQL of course – and with a decent database, that supports window functions, e.g. PostgreSQL, or any commercial database of your choice! (you won’t be finding this sort of feature in MySQL).

Here’s the query:

with data
as (
  select 
    d.*,
    row(sektor, row, scene1, scene2) block
  from d
)
select 
  sektor,
  row,
  seat,
  scene1,
  scene2,
  case 
    when lag (block) over(o) is distinct from block 
     and lead(block) over(o) is distinct from block 
    then 'start / stop'
    when lag (block) over(o) is distinct from block 
    then 'start'
    when lead(block) over(o) is distinct from block 
    then 'stop'
    else ''
  end start_stop,
  count(*) over(
    partition by sektor, row, scene1, scene2
  ) cnt
from data
window o as (
  order by sektor, row, seat
)
order by sektor, row, seat;

That’s it! Not too hard, is it?

Let’s go through a couple of details. We’re using quite a few awesome SQL standard / PostgreSQL concepts, which deserve to be explained:

Row value constructor

The ROW() value constructor is a very powerful feature that can be used to combine several columns (or rows) into a single ROW / RECORD type:

row(sektor, row, scene1, scene2) block

This type can then be used for row value comparisons, saving you a lot of time comparing column by column.

The DISTINCT predicate

lag (block) over(o) is distinct from block 

The result of the above window function is compared with the previously constructed ROW by using the DISTINCT predicate, which is a great way of comparing things “null-safely” in SQL. Remember that SQL NULLs are some of the hardest things in SQL to get right.

Window functions

Window functions are a very awesome concept. Without any GROUP BY clause, you can calculate aggregate functions, window functions, ranking functions etc. in the context of a current row while you’re projecting the SELECT clause. For instance:

count(*) over(
  partition by sektor, row, scene1, scene2
) cnt

The above window function counts all rows that are in the same partition (“group”) as the current row, given the partition criteria. In other words, all the seats that have the same (scene1, scene2) colouring and that are located in the same (sector, row).

The other window functions are lead and lag, which return a value from a previous or subsequent row, given a specific ordering:

lag (block) over(o),
lead(block) over(o)
-- ...
window o as (
  order by sektor, row, seat
)

Note also the use of the SQL standard WINDOW clause, which is supported only by PostgreSQL and Sybase SQL Anywhere.

In the above snippet, lag() returns the block value of the previous row given the ordering o, whereas lead() would return the next row’s value for block – or NULL, in case of which we’re glad that we used the DISTINCT predicate, before.

Note that you can also optionally supply an additional numeric parameter, to indicate that you want to access the second, third, fifth, or eighth, row backwards or forward.

SQL is your most powerful and underestimated tool

At Data Geekery, we always say that

SQL is a device whose mystery is only exceeded by its powertweet this

If you’ve been following our blog, you may have noticed that we try to evangelise SQL as a great first-class citizen for Java developers. Most of the above features are supported by jOOQ, and translated to your native SQL dialect, if they’re not available.

So, if you haven’t already, listen to Peter Kopfler who was so thrilled after our recent jOOQ/SQL talks in Vienna that he’s now all into studying standards and using PostgreSQL:

Further reading

There was SQL before window functions and SQL after window functions

Java 8 Friday: No More Need for ORMs


At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

No More Need for ORMs

Debates about the usefulness of ORM (Object-Relational Mapping) have been going on for the last decade. While many people would agree that Hibernate and JPA solve a lot of problems very well (mostly the persistence of complex object graphs), others may claim that the mapping complexity is mostly overkill for data-centric applications.

JPA solves mapping problems by establishing standardised, declarative mapping rules through hard-wired annotations on the receiving target types. We claim that many data-centric problems should not be limited by the narrow scope of these annotations, but be solved in a much more functional way. Java 8, and the new Streams API finally allow us to do this in a very concise manner!

Let’s start with a simple example, where we’re using H2′s INFORMATION_SCHEMA to collect all tables and their columns. We’ll want to produce an ad-hoc data structure of the type Map<String, List<String>> to contain this information. For simplicity of SQL interaction, we’ll use jOOQ (as always, a shocker on this blog). Here’s how we prepare this:

public static void main(String[] args)
throws Exception {
    Class.forName("org.h2.Driver");
    try (Connection c = getConnection(
            "jdbc:h2:~/sql-goodies-with-mapping", 
            "sa", "")) {

        // This SQL statement produces all table
        // names and column names in the H2 schema
        String sql =
            "select table_name, column_name " +
            "from information_schema.columns " +
            "order by " +
                "table_catalog, " +
                "table_schema, " +
                "table_name, " +
                "ordinal_position";

        // This is jOOQ's way of executing the above
        // statement. Result implements List, which
        // makes subsequent steps much easier
        Result<Record> result =
        DSL.using(c)
           .fetch(sql)
    }
}

Now that we’ve set up this query, let’s see how we can produce the Map<String, List<String>> from the jOOQ Result:

DSL.using(c)
   .fetch(sql)
   .stream()
   .collect(groupingBy(
       r -> r.getValue("TABLE_NAME"),
       mapping(
           r -> r.getValue("COLUMN_NAME"),
           toList()
       )
   ))
   .forEach(
       (table, columns) -> 
           System.out.println(table + ": " + columns)
   );

The above example produces the following output:

FUNCTION_COLUMNS: [ALIAS_CATALOG, ALIAS_SCHEMA, ...]
CONSTANTS: [CONSTANT_CATALOG, CONSTANT_SCHEMA, ...]
SEQUENCES: [SEQUENCE_CATALOG, SEQUENCE_SCHEMA, ...]

How does it work? Let’s go through it step-by-step

DSL.using(c)
   .fetch(sql)

// Here, we transform a List into a Stream
   .stream()

// We're collecting Stream elements into a new
// collection type
   .collect(

// The Collector is a grouping operation, producing
// a Map
            groupingBy(

// The grouping operation's group key is defined by
// the jOOQ Record's TABLE_NAME value
       r -> r.getValue("TABLE_NAME"),

// The grouping operation's group value is generated
// by this mapping expression...
       mapping(

// ... which is essentially mapping each grouped
// jOOQ Record to the Record's COLUMN_NAME value
           r -> r.getValue("COLUMN_NAME"),

// ... and then collecting all those values into a
// java.util.List. Whew
           toList()
       )
   ))

// Once we have this List<String, List<String>> we
// can simply consume it with the following Consumer
// lambda expression
   .forEach(
       (table, columns) -> 
           System.out.println(table + ": " + columns)
   );

Got it? These things are certainly a bit tricky when playing around with it for the first time. The combination of new types, extensive generics, lambda expressions can be a bit confusing at first. The best thing is to simply practice with these things until you get a hang of it. After all, the whole Streams API is really a revolution compared to previous Java Collections APIs.

The good news is: This API is final and here to stay. Every minute you spend practicing it is an investment into your own future.

Note that the above programme used the following static import:

import static java.util.stream.Collectors.*;

Note also, that the output was no longer ordered as in the database. This is because the groupingBy collector returns a java.util.HashMap. In our case, we might prefer collecting things into a java.util.LinkedHashMap, which preserves insertion / collection order:

DSL.using(c)
   .fetch(sql)
   .stream()
   .collect(groupingBy(
       r -> r.getValue("TABLE_NAME"),

       // Add this Supplier to the groupingBy
       // method call
       LinkedHashMap::new,
       mapping(
           r -> r.getValue("COLUMN_NAME"),
           toList()
       )
   ))
   .forEach(...);

We could go on with other means of transforming results. Let’s imagine, we would like to generate simplistic DDL from the above schema. It’s very simple. First, we’ll need to select column’s data type. We’ll simply add it to our SQL query:

String sql =
    "select " +
        "table_name, " +
        "column_name, " +
        "type_name " + // Add the column type
    "from information_schema.columns " +
    "order by " +
        "table_catalog, " +
        "table_schema, " +
        "table_name, " +
        "ordinal_position";

I have also introduced a new local class for the example, to wrap name and type attributes:

class Column {
    final String name;
    final String type;

    Column(String name, String type) {
        this.name = name;
        this.type = type;
    }
}

Now, let’s see how we’ll change our Streams API method calls:

result
    .stream()
    .collect(groupingBy(
        r -> r.getValue("TABLE_NAME"),
        LinkedHashMap::new,
        mapping(

            // We now collect this new wrapper type
            // instead of just the COLUMN_NAME
            r -> new Column(
                r.getValue("COLUMN_NAME", String.class),
                r.getValue("TYPE_NAME", String.class)
            ),
            toList()
        )
    ))
    .forEach(
        (table, columns) -> {

            // Just emit a CREATE TABLE statement
            System.out.println(
                "CREATE TABLE " + table + " (");

            // Map each "Column" type into a String
            // containing the column specification,
            // and join them using comma and
            // newline. Done!
            System.out.println(
                columns.stream()
                       .map(col -> "  " + col.name +
                                    " " + col.type)
                       .collect(Collectors.joining(",\n"))
            );

            System.out.println(");");
        }
    );

The output couldn’t be more awesome!

CREATE TABLE CATALOGS(
  CATALOG_NAME VARCHAR
);
CREATE TABLE COLLATIONS(
  NAME VARCHAR,
  KEY VARCHAR
);
CREATE TABLE COLUMNS(
  TABLE_CATALOG VARCHAR,
  TABLE_SCHEMA VARCHAR,
  TABLE_NAME VARCHAR,
  COLUMN_NAME VARCHAR,
  ORDINAL_POSITION INTEGER,
  COLUMN_DEFAULT VARCHAR,
  IS_NULLABLE VARCHAR,
  DATA_TYPE INTEGER,
  CHARACTER_MAXIMUM_LENGTH INTEGER,
  CHARACTER_OCTET_LENGTH INTEGER,
  NUMERIC_PRECISION INTEGER,
  NUMERIC_PRECISION_RADIX INTEGER,
  NUMERIC_SCALE INTEGER,
  CHARACTER_SET_NAME VARCHAR,
  COLLATION_NAME VARCHAR,
  TYPE_NAME VARCHAR,
  NULLABLE INTEGER,
  IS_COMPUTED BOOLEAN,
  SELECTIVITY INTEGER,
  CHECK_CONSTRAINT VARCHAR,
  SEQUENCE_NAME VARCHAR,
  REMARKS VARCHAR,
  SOURCE_DATA_TYPE SMALLINT
);

Excited? The ORM era may have ended just now

This is a strong statement. The ORM era may have ended. Why? Because using functional expressions to transform data sets is one of the most powerful concepts in software engineering. Functional programming is very expressive and very versatile. It is at the core of data and data streams processing. We Java developers already know existing functional languages. Everyone has used SQL before, for instance. Think about it. With SQL, you declare table sources, project / transform them onto new tuple streams, and feed them either as derived tables to other, higher-level SQL statements, or to your Java program.

If you’re using XML, you can declare XML transformation using XSLT and feed results to other XML processing entities, e.g. another XSL stylesheet, using XProc pipelining.

Java 8′s Streams are nothing else. Using SQL and the Streams API is one of the most powerful concepts for data processing. If you add jOOQ to the stack, you can profit from typesafe access to your database records and query APIs. Imagine writing the previous statement using jOOQ’s fluent API, instead of using SQL strings.

jooq-the-best-way-to-write-sql-in-java

The whole method chain could be one single fluent data transformation chain as such:

DSL.using(c)
   .select(
       COLUMNS.TABLE_NAME,
       COLUMNS.COLUMN_NAME,
       COLUMNS.TYPE_NAME
   )
   .from(COLUMNS)
   .orderBy(
       COLUMNS.TABLE_CATALOG,
       COLUMNS.TABLE_SCHEMA,
       COLUMNS.TABLE_NAME,
       COLUMNS.ORDINAL_POSITION
   )
   .fetch()  // jOOQ ends here
   .stream() // Streams start here
   .collect(groupingBy(
       r -> r.getValue(COLUMNS.TABLE_NAME),
       LinkedHashMap::new,
       mapping(
           r -> new Column(
               r.getValue(COLUMNS.COLUMN_NAME),
               r.getValue(COLUMNS.TYPE_NAME)
           ),
           toList()
       )
   ))
   .forEach(
       (table, columns) -> {
            // Just emit a CREATE TABLE statement
            System.out.println(
                "CREATE TABLE " + table + " (");

            // Map each "Column" type into a String
            // containing the column specification,
            // and join them using comma and
            // newline. Done!
            System.out.println(
                columns.stream()
                       .map(col -> "  " + col.name +
                                    " " + col.type)
                       .collect(Collectors.joining(",\n"))
            );

           System.out.println(");");
       }
   );

Java 8 is the future, and with jOOQ, Java 8, and the Streams API, you can write powerful data transformation APIs. I hope we got you as excited as we are! Stay tuned for more awesome Java 8 content on this blog.

The Top 10 Productivity Booster Techs for Programmers


This is the list we’ve all been waiting for. The top 10 productivity booster techs for programmers that – once you’ve started using them – you can never do without them any longer.

Here it is:

1. Git

logo@2x Before, there were various version control systems. Better ones, worse ones. But somehow they all felt wrong in one way or another.

Came along Git (and GitHub, EGit). Once you’re using this miraculous tool, it’s hard to imagine that you’ll ever meet a better VCS again.

You’ve never used Git? Get started with this guide.

2. Stack Overflow

stackoverflow

No kidding. Have you ever googled for anything tech-related back in 2005? Or altavista’d something back in 2000? Or went to FidoNet in search for answers in 1995? It was horrible. The top results always consisted in boring forum discussions with lots of un-experts and script kiddies claiming wrong things.

These forums still exist, but they don’t turn up on page 1 of Google search results.

Today, any time you search for something, you’ll have 2-3 hits per top 10 from Stack Overflow. And chances are, you’ll look no further because those answers are 80% wonderful! That’s partially because of Stack Overflow’s cunning reputation system, but also partially because of Stack Overflow’s even more cunning SEO rewarding system. (I already got 98 announcer, 19 booster, and 5 publicist badges. Yay).

While Stack Overflow allows its more active user to pursue their vanity (see above ;-) ), all the other users without any accounts will continue to flock in, finding perfect answers and clicking on very relevant ads.

Thumbs up for Stack Overflow and their awesome business model.

3. Office 365

excel We’re a small startup. Keeping costs low is of the essence. With Office 365, we only pay around $120 per user for a full-fledged Office 2013 suite, integrated with Microsoft Onedrive, Sharepoint, Exchange, Access, and much more.

In other words, we get enterprise-quality office software for the price of what students used to pay, before.

And do note, Office 2013 is better than any other Microsoft (or Libre) Office suite before. While not a 100% Programmer thing, it’s still an awesome tool chain for a very competitive price.

4. IntelliJ

intellij

While Eclipse is great (and free), IntelliJ IDEA, and also phpStorm for those unfortunate enough to write PHP are just subtly better in almost every aspect of an IDE. You can try their free community edition any time, but beware, you probably won’t switch back. And then you probably won’t be able to evade the Ultimate edition for long ;-)

5. PostgreSQL

pg PostgreSQL claims to be the world’s most advanced Open Source database, and we think it’s also one of the most elegant, easy, standards-compliant databases. It is really the one database that makes working with SQL fun.

We believe that within a couple of years, there’s a real chance of PostgreSQL not only beating commercial databases in terms of syntax but also in terms of performance.

Any time you need a data storage system with a slight preference for SQL-based ones, just make PostgreSQL your default choice. You won’t be missing any feature in that database.

Let’s hear it for PostgreSQL.

6. Java

duke Java is almost 20 years old, but it’s still the #1 or #2 language on the TIOBE index (sharing ranks with C), for very good reasons:

  • It’s robust
  • It’s mature
  • It works everywhere (almost, really too bad it has never succeeded in the browser)
  • It runs on the best platform ever, the JVM
  • It is Open Source
  • It has millions of tools, libraries, extensions, and applications

While some languages may seem a bit more modern or sexy or geeky, Java has and will always rule them all in terms of popularity. It is a first choice and with Java 8, things have improved even more.

7. jOOQ

jooq-logo-black-100x80 Now, learning this from the jOOQ blog is really unexpected and a shocker, but we think that jOOQ fits right into this programmer’s must-have top-10 tool chain. Most jOOQ users out there have never returned back to pre-jOOQ tools, as they’ve found writing SQL in Java as simple as never before.

Given that we’ve had Java and PostgreSQL before, there’s only this one missing piece gluing the two together in the most sophisticated way.

And besides, no one wants to hack around with the JDBC API, these days, do they?

8. Less CSS

less When you try Less CSS for the first time, you’ll think that

Why isn’t CSS itself like this!?

And you’re right. It feels just like CSS the way it should have always been. All the things that you have always hated about CSS (repetitiveness, verbosity, complexity) are gone. And if you’re using phpStorm or some other JetBrains product (see above), you don’t even have to worry about compiling it to CSS.

As an old HTML-table lover who doesn’t care too much about HTML5, layout, and all that, using Less CSS makes me wonder if I should finally dare creating more fancy websites!

Never again without Less CSS.

9. jQuery

jqueryWhat Less CSS is for CSS, jQuery is for JavaScript. Heck, so many junior developers on Stack Overflow don’t even realise that jQuery is just a JavaScript library. They think it is the language, because we’ve grown to use it all over the place.

Yes, sometimes, jQuery can be overkill as is indicated by this slightly cynical website: http://vanilla-js.com

joox-logo-blackBut it helps so much abstracting all the DOM manipulation in a very fluent way. If only all libraries were written this way.

Do note that we’ve also published a similar library for Java, in case you’re interested in jQuery-style DOM XML manipulation. Along with Java 8′s new lambda expressions, manipulating the DOM becomes a piece of cake.

10. C8H10N4O2

764px-Caffeine.svgC8H10N4O2 (more commonly known as Caffeine) is probably the number one productivity booster for programmers.

Some may claim that there’s such a thing like the Ballmer Peak. That might be true, but the Caffeine Peak has been proven times and again.

Have Dilbert’s view on the matter:

http://dilbert.com/strips/comic/2006-10-19/

More productivity boosters

We’re certainly not the only ones believing that there is such a thing as a programmer-productivity-booster. Enjoy this alternative list by Troy Topnik here for more insight:

http://www.activestate.com/blog/2010/03/top-ten-list-productivity-boosters-programmers

jOOQ Newsletter: April 2, 2014


subscribe to this newsletter here

Tweet of the Day

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

Arturo Tena who simply loves jOOQ 3.3, and expresses this with a creative transformation of our version numbering scheme:

Florin T.Pătraşcu who cannot stop integrating jOOQ with MicroMVC, because he discovers more and more new features all the time:

Thanks for the shouts, guys!

New license models – available soon

In the early days of migrating towards dual-licensing, we’ve discussed many alternative licensing models with our long-term users and early adopters. Now, we’re almost ready to publish the new, additional license terms, which are due for next week. They essentially include:

  • The existing yearly subscription for default use-cases
  • A new monthly subscription for short-running tasks, such as DB migrations
  • A new major release perpetual license for long-running jOOQ integrations with little need for upgrades

With these options, we believe that we will be able to cover even more jOOQ integration use-cases from a legal perspective, helping to further improve your jOOQ experience.

Are you an existing customer of the jOOQ yearly subscription interested in a switch to other terms? Do not hesitate to contact sales for a tailor-made migration offer.

Java Zone – Java 8 is out

Java 8 has finally been released by Oracle, a moment we’ve been waiting for quite a while now. Unsurprisingly, blogs in all corners of the web have started publishing Java and Java 8 related articles. We absolutely agree with Craig Buckler claiming Java to be the best programming language to learn in 2014.

Want to stay up to date with examples, tutorials, insights on Java 8? Follow ourJava 8 Friday blog series, then. Every Friday, we’re publishing an insightful article on a specific area of Java that will be affected by Java 8. One of the most interesting articles that has even caught the attention of Erik Meijer is Dr. Ming-Yee Iu’s guest post about JINQ and JINQ-to-jOOQ, which you should be looking out for in the next 6 months.

For more great resources, see also our blogging partner Baeldung.com’s Java 8 resources collection.

SQL Zone – In-Memory Computing

Do you know your fastest way around in-memory computing with Oracle? It’s possible, but it’s not so trivial.

If you’re used to SQL Server, you would simply create a T-SQL temporary table, which is a typesafe in-memory table for use with procedural T-SQL.

In Oracle, you have two choices to do the same:

  • Using SQL TABLE OF OBJECT types
  • Using GLOBAL TEMPORARY TABLEs

In almost all cases, GLOBAL TEMPORARY TABLEs will outperform TABLE OF OBJECT types for a very simple reason: You can perform all operations in the SQL engine only, whereas with TABLE OF OBJECT types, you will have to resort to the PL/SQL engine to keep the table in memory. With significant amounts of data, this can become quite a problem.

Want to know more? Visit our recent Stack Overflow question on the subject.

Upcoming Events

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.

CUME_DIST(), a Lesser-Known SQL Gem


When doing reporting or statistics with SQL, you better know your window functions. There are many of them, and few SQL developers know about them.

CUME_DIST() is one such function. We’ve recently re-discovered it on Stack Overflow. The following query yields two times the same result for fraction1 and fraction2:

SELECT 
  ename, 
  CUME_DIST() OVER (ORDER BY ename) fraction1,
  ROWNUM / (MAX(ROWNUM) OVER()) fraction2
FROM emp
ORDER BY ename

The above query then yields:

|  ENAME | FRACTION1 | FRACTION2 |
|--------|-----------|-----------|
|  ALLEN |      0.08 |      0.08 |
|  BLAKE |      0.17 |      0.17 |
|  CLARK |      0.25 |      0.25 |
|   FORD |      0.33 |      0.33 |
|  JAMES |      0.42 |      0.42 |
|  JONES |       0.5 |       0.5 |
|   KING |      0.58 |      0.58 |
| MARTIN |      0.67 |      0.67 |
| MILLER |      0.75 |      0.75 |
|  SMITH |      0.83 |      0.83 |
| TURNER |      0.92 |      0.92 |
|   WARD |         1 |         1 |

… as can be seen in this SQLFiddle. In plain English, the CUME_DIST() (or cumulative distribution) of a value within a group of values helps you see how far “advanced” a value is in the ordering of the whole result set – or of a partition thereof.

The second expression using ROWNUM informally explains this with an equivalent expression. The value is always strictly greater than zero and smaller or equal to 1:

0 < CUME_DIST() OVER(ORDER BY ename) <= 1

Note that Oracle (and the SQL standard) also support CUME_DIST() as an “ordered aggregate function”, “ordered set function” or “hypothetical set function”:

SELECT 
  ename, 
  CUME_DIST(ename)
    WITHIN GROUP (ORDER BY ename) fraction
FROM emp
GROUP BY ename
ORDER BY ename

The standard specifies the above as:

<hypothetical set function> ::=
    <rank function type> <left paren>
        <hypothetical set function value expression list> 
            <right paren>
        <within group specification>

<within group specification> ::=
    WITHIN GROUP <left paren> 
        ORDER BY <sort specification list> 
            <right paren>

jOOQ also supports the cumeDist() window function, and the upcoming jOOQ 3.4 will also support the ordered aggregate function.

… and you, you should definitely make this nice function a part of your SQL vocabulary.

Follow

Get every new post delivered to your Inbox.

Join 1,613 other followers

%d bloggers like this: