Please, Run That Calculation in Your RDBMS

There’s one thing that you can do terribly wrong when working with RDBMS. And that thing is not running your calculations in the database, when you should.

We’re not advocating to blindly move all business logic into the database, but when I see a Stack Overflow question like this, I feel the urge to gently remind you of the second item in our popular 10 Common Mistakes Java Developers Make When Writing SQL.

Gently reminding you of the "right way"
Gently reminding you of the second item.
Some background on this illustration here (in German).tweet this

The Stack Overflow question essentially boils down to this (liberally quoted):

From the following medium-sized table, I wish to count the number of documents with status 0 or 1 per application ID:

AppID | DocID | DocStatus 
------+-------+----------
1     | 100   | 0
1     | 101   | 1    
2     | 200   | 0    
2     | 300   | 1
...   | ...   | ...

Should I use Hibernate for that?

And the answer: NO! Don’t use Hibernate for that (unless you mean native querying). You should use SQL for that. Es-Queue-El! You have so many trivial options to make your SQL Server help you run this query in a fraction of the time it would take if you loaded all that data into Java memory before aggregating!

For instance (using SQL Server):

Using GROUP BY

This is the most trivial one, but it might not return result in exactly the way you wanted, i.e. different aggregation results are in different rows:

SELECT [AppID], [DocStatus], count(*)
FROM [MyTable]
GROUP BY [AppID], [DocStatus]

Example on SQLFiddle, returning something like

| APPID | DOCSTATUS | COLUMN_2 |
|-------|-----------|----------|
|     1 |         0 |        2 |
|     2 |         0 |        3 |
|     1 |         1 |        3 |
|     2 |         1 |        2 |

Using nested selects

This is probably the solution that this particular user was looking for. They probably want each aggregation in a separate column, and one very generic way to achieve this is by using nested selects. Note that this solution might prove to be a bit slow in some databases that have a hard time optimising these things

SELECT [AppID],
       (SELECT count(*) FROM [MyTable] [t2]
        WHERE [t1].[AppID] = [t2].[AppID]
        AND [DocStatus] = 0) [Status_0],
       (SELECT count(*) FROM [MyTable] [t2]
        WHERE [t1].[AppID] = [t2].[AppID]
        AND [DocStatus] = 1) [Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]

Example on SQLFiddle, returning something like

| APPID | STATUS_0 | STATUS_1 |
|-------|----------|----------|
|     1 |        2 |        3 |
|     2 |        3 |        2 |

Using SUM()

This solution is probably the optimal one. It is equivalent to the previous one with nested selects, although it only works for simple queries, whereas the nested selects version is more versatile.

SELECT [AppID],
       SUM(IIF([DocStatus] = 0, 1, 0)) [Status_0],
       SUM(IIF([DocStatus] = 1, 1, 0)) [Status_1]
FROM [MyTable] [t1]
GROUP BY [AppID]

Example on SQLFiddle, same result as before

Using PIVOT

This solution is for the SQL Aficionados among yourselves. It uses the T-SQL PIVOT clause!

SELECT [AppID], [0], [1]
FROM (
    SELECT [AppID], [DocStatus]
    FROM [MyTable]
) [t]
PIVOT (
    count([DocStatus]) 
    FOR [DocStatus] 
    IN ([0], [1])
) [pvt]

SQL aficionados use PIVOT tweet this

Example on SQLFiddle, same result as before

Conclusion

You may freely choose your weapon among the above suggestions, and I’m sure there are more alternatives. All of them will outperform any Java-based aggregation implementation by orders of magnitude, even for trivially small data sets for sure. We’ll say this time and again, and we’ll quote Gavin King time and again for the same thing:

Just because you’re using Hibernate, doesn’t mean you have to use it for everything. A point I’ve been making for about ten years now.

And in our words:

Use SQL whenever appropriate! And that is much more often than you might think!

Java 8 Friday Goodies: SQL ResultSet Streams

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. We have blogged a couple of times about some nice Java 8 goodies, and now we feel it’s time to start a new blog series, the…

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.

Java 8 Goodie: SQL ResultSet Streams

Yes, the SQL subject must be dealt with again. Even if last week, we promised an article on concurrency, there is one very important aspect of Java 8 lambdas and interoperability with “legacy” APIs that we need to talk about, first.

Checked Exceptions

Yes. Unfortunately, those beasts from the past still haunt us, more than ever when we’re using Java 8’s lambda expressions. Already before Java 8’s release, there are a couple of Stack Overflow questions related to the subject.

Let’s remember how the IOExceptions caused issues when traversing the file system. Unless you write your own utility, you’ll have to resort to this beauty:

Arrays.stream(dir.listFiles()).forEach(file -> {
    try {
        System.out.println(file.getCanonicalPath());
    }
    catch (IOException e) {
        throw new RuntimeException(e);
    }

    // Ouch, my fingers hurt! All this typing!
});

We think it is safe to say:

Java 8 and checked exceptions don’t match.tweet this

A workaround is to write your own CheckedConsumer that wraps the checked exception. Such a consumer will be highly reusable, but… Did you think of all the other FunctionalInterfaces? There are quite a few of them in the java.util.function package:

Some of the many types in java.util.function
Some of the many types in java.util.function

jOOλ – Fixing lambda in Java 8

jool-logo-blackWhile writing this Java 8 blog series, we’ve constantly run into the need to wrap checked exceptions inside lambda expressions. And what do we geeks do when we frequently run into a problem? We fix it! And we have created jOOλ (also jOOL, jOO-Lambda), ASL 2.0 licensed, where we have duplicated pretty much every FunctionalInterface that is available from the JDK to support checked exceptions. Here’s how you would use jOOλ in the above example:

Arrays.stream(dir.listFiles()).forEach(
    Unchecked.consumer(file -> {
        // Throw all sorts of checked exceptions
        // here, we don't care...
        System.out.println(file.getCanonicalPath());
    })
);

The above example shows how you can simply ignore and pass checked exceptions as RuntimeExceptions. If you actually want to handle them, you can pass an exception handler lambda:

Arrays.stream(dir.listFiles())
      .forEach(Unchecked.consumer(

    file -> {
        System.out.println(file.getCanonicalPath());
    },
    e -> {
        log.info("Log stuff here", e);
        throw new MyRuntimeException(e);
    }
);

The second example now seems equally verbose, but don’t worry. You will probably reuse that exception handler and fall back to this:

Arrays.stream(dir.listFiles())
      .forEach(Unchecked.consumer(
    file -> {
        System.out.println(file.getCanonicalPath());
    },
    myExceptionHandler
);

jOOλ – Providing JDBC ResultSet Streams

Unfortunately, most efforts in the Java 8 Streams API were made in the area of correctly implementing parallelisable streams. While this is very useful for those of us actually doing parallel computing, for most others better integration with legacy APIs would have been better. One API that seriously deserves some lifting is JDBC, and we’ve blogged about this before. With jOOλ, you can now generate Streams directly from ResultSets or even from PreparedStatements. Here’s how you prepare:

Class.forName("org.h2.Driver");
try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    try (PreparedStatement stmt = c.prepareStatement(sql)) {
        // code here
    }
}

Now, all you have to do when using jOOλ is stream your PreparedStatements as such:

SQL.stream(stmt, Unchecked.function(rs ->
    new SQLGoodies.Schema(
        rs.getString("SCHEMA_NAME"),
        rs.getBoolean("IS_DEFAULT")
    )
))
.forEach(System.out::println);

Where SQLGoodies.Schema is just an ordinary POJO. Some of the stream() method’s signatures are these ones:

public static <T> Stream<T> stream(
    PreparedStatement stmt,
    Function<ResultSet, T> rowFunction
);

public static <T> Stream<T> stream(
    PreparedStatement stmt,
    Function<ResultSet, T> rowFunction,
    Consumer<? super SQLException> exceptionHandler
);

Others are available as well.

That is awesome, isn’t it?

JDBC ResultSets should be Java 8 Streams.tweet this

Too bad, the above code didn’t make it into the JDK 8, as this would have been a chance to finally greatly improve on the JDBC API. Another, similar attempt at improving things has been done here by Julian Exenberger.

Java 8 alternatives of writing SQL

We’ve also published a couple of alternatives to jOOλ, using Java 8 with SQL here:

http://www.jooq.org/java-8-and-sql

Conclusion

While Java 8’s lambda expressions are awesome, the new Streams API is pretty incomplete. When implementing the above, we had to implement our own ResultSetIterator, and write all this mess to wrap the iterator in a Stream:

StreamSupport.stream(
    Spliterators.spliteratorUnknownSize(
        new ResultSetIterator<>(
            supplier, 
            rowFunction, 
            exceptionTranslator
        ), 0
    ), false
);

And it shouldn’t be necessary to write an Iterator in the first place, if only we were able to generate finite streams:

// Unfortunately, this method doesn't exist
Stream.generate(
    // Supplier, generating new POJOs
    () -> { 
        rs.next(); 
        return new SQLGoodies.Schema(
            rs.getString("SCHEMA_NAME"),
            rs.getBoolean("IS_DEFAULT")
        );
    },

    // Predicate, terminating the Stream
    () -> { !rs.isLast(); }
);

While jOOλ is an acceptable intermediate solution, and the Guava guys are probably already working out how to fix their library, it is really too bad, that Java 8 is lacking such utility functionality.

But we’re complaining on a high level. Next week, as promised, we’ll see a couple of examples related to concurrency, so stay tuned!

More on Java 8

In the mean time, have a look at Eugen Paraschiv’s awesome Java 8 resources page

When to Use a Framework

I’ve come across this interesting article titled “Don’t Reinvent the Wheel! Use a Framework!” They All Say. The essence of the article lies in this little fact:

[When should “they” use a framework?”] When they understand the basics of the language and would be able to code what the framework/library does anyway

Frameworks and libraries are great because someone out there had spent a lot of time thinking about a very specific problem domain. Chances that they have gotten it right are very high BUT if you had enough time and money, you could build at least the useful parts of that framework yourself. Nonetheless, it’s cheaper to use/buy their code and have them maintain that part for you.

This is very true with JPA / Hibernate, for instance. If you know SQL and you know SQL well, then JPA does a great deal of helping you getting all that repetitive and often complex CRUD right, and you’ll even know how to tweak and tune JPA or the generated SQL where needed. Gavin King himself has said time and again:

Just because you’re using Hibernate, doesn’t mean you have to use it for everything. A point I’ve been making for about ten years now.

Hibernate helps you write some of your SQL, it doesn’t replace SQL. If you’re new to programming, you shouldn’t use Hibernate right away. You should first learn to write SQL and get a good understanding of your RDBMS. From my experience at conferences and JUG talks, this doesn’t only apply to junior programmers, though. It is very interesting to see how few seniors and architects know about window functions, for instance.

So, if you’re using an RDBMS and Hibernate/JPA, have your team be trained on all the layers of your technology. SQL, HQL/JPQL, and Java.

An Open Source License to Increase Your Street Credibility

Many of us geeks don’t really care about users, tractions, etc. when we spam GitHub with our little toy projects. I mean, who knows if we really have the time to maintain them? Certainly, there’s almost no money in it anyway, so we might just as well give it away for free (e.g. jOOX).

Nonetheless, you should choose a license to show your intent. But which license to choose? Jeff Atwood says “Any License”. He then goes on to recommend the WTFPL or Do What The Fuck You Want Public License. I mean, this is just like arbitrarily throwing software around you in the streets:

But we claim that there is another, much better license for this kind of useless little toy project. It’s called Beerware. The license text reads:

/*
 * ------------------------------------------------
 * "THE BEER-WARE LICENSE" (Revision 42):
 *  wrote this file. As long as 
 * you retain this notice you can do whatever you 
 * want with this stuff. If we meet some day, and 
 * you think this stuff is worth it, you can buy
 * me a beer in return [Your Name Here]
 * ------------------------------------------------
 */

So, when you’re in for some street credibility, give this some serious thought!

Increase your street credibility, use the beerware license
tweet this

… just kidding. Use the ASL 2.0 and/or the CC-BY 3.0

Modern Shareholder Value Hopes

So, everyone is discussing the $19B Facebook / Whatsapp deal, discussing it on every media. And Zuck says it was actually a bargain.

Yeah right. A bargain. Read CNN’s 10 other things Facebook could have bought with $19 billion.

We at Data Geekery are more down to earth, selling actual value to our own customers. So we take the opportunity to issue this friendly reminder about what happens when you and your shareholders believe that a primitive chat service is worth $19B because of its oh-so-many users:

So, should our own business not work out, we can still make tons of money this year through PUT warrants on FB. Because the above is not the only issue with Facebook. Check out this video: