Java 8 Friday Goodies: Map Enhancements

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. tweet this

Java 8 Goodie: Map Enhancements

In previous posts, we’ve already dealt with a couple of new Streams features, for instance when sorting. Most API improvements are indeed part of the new Streams API. But a few nice methods were also added to java.util.List and most importantly, to java.util.Map. If you want a quick overview of feature additions, go to the JDK8 Javadoc and click on the new “Default Methods” tab:

java.util.Map default methods
java.util.Map default methods

For backwards-compatibility reasons, all new methods added to Java interfaces are in fact default methods. So we have a couple of exciting new additions!

compute() methods

Often, we fetch a value from a map, make some calculations on it and put it back into the map. This can be verbose and hard to get right if concurrency is involved. With Java 8, we can pass a BiFunction to the new compute(), computeIfAbsent(), or computeIfPresent() methods and have the Map implementation handle the semantics of replacing a value.

The following example shows how this works:

// We'll be using this simple map
// Unfortunately, still no map literals in Java 8..
Map<String, Integer> map = new HashMap<>();
map.put("A", 1);
map.put("B", 2);
map.put("C", 3);

// Compute a new value for the existing key
System.out.println(map.compute("A", 
    (k, v) -> v == null ? 42 : v + 41));
System.out.println(map);

// This will add a new (key, value) pair
System.out.println(map.compute("X", 
    (k, v) -> v == null ? 42 : v + 41));
System.out.println(map);

The output of the above program is this:

42
{A=42, B=2, C=3}
42
{A=42, B=2, C=3, X=42}

This is really useful for ConcurrentHashMap, which ships with the following guarantee:

The entire method invocation is performed atomically. Some attempted update operations on this map by other threads may be blocked while computation is in progress, so the computation should be short and simple, and must not attempt to update any other mappings of this Map.

forEach() method

This is a really nice goodie which lets you pass a method reference or a lambda to receive (key, value) pairs one by one. A trivial example would be this:

map.forEach((k, v) -> 
    System.out.println(k + "=" + v));

Its output being:

A=1
B=2
C=3

merge() method

Now this one is really not so easy to understand. The Javadoc uses this example here:

map.merge(key, msg, String::concat)

Given the following contract:

If the specified key is not already associated with a value or is associated with null, associates it with the given value. Otherwise, replaces the value with the results of the given remapping function, or removes if the result is null.

So, the above code translates to the following atomic operation:

String value = map.get(key);
if (value == null)
    map.put(key, msg);
else
    map.put(key, value.concat(msg));

This is certainly not an everyday functionality and might just have leaked from an implementation to the top-level API. Additionally, if the map already contains null (so, null values are OK), and your remappingFunction returns null, then the entry is removed. That’s quite unexpected. Consider the following program:

map.put("X", null);
System.out.println(map.merge(
    "X", null, (v1, v2) -> null));
System.out.println(map);

Its output is:

null
{A=1, B=2, C=3}

Update: I first wrote the above code first with JDK 8 build 116. With build 129, things have changed completely again. First off, the value passed to merge() is not allowed to be null. Secondly. nullvalues are treated by merge() just like absent values. To produce the same output, we’ll write:

map.put("X", 1);
System.out.println(map.merge(
    "X", 1, (v1, v2) -> null));
System.out.println(map);

This merge() operation has thus removed a value from the map. That’s probably OK because the semantics of “merge” is often a combination of INSERT, UPDATE, and DELETE if we’re using SQL-speak. And a somewhat reasonable way to indicate that a value should be removed is to return null from such a function.

But the map is allowed to contain null values, which can never be inserted into the map using merge(). tweet this

getOrDefault()

This is a no-brainer. Right? Right! Wrong!

Unfortunately, there are two types of Maps. Those supporting null keys and/or values and those who don’t support nulls. While the previous merge() method didn’t distinguish between a map not containing a key and a map containing a key with a null value, this new getOrDefault() only returns the default when the key is not contained. It won’t protect you from a NullPointerException:

map.put("X", null);
try {
  System.out.println(map.getOrDefault("X", 21) + 21);
}
catch (NullPointerException nope) {
  nope.printStackTrace();
}

That’s quite a bummer. In general, it can be said the Map API has become even more complex with respect to nulls. tweet this

Trivial additions

There are a few more methods, like putIfAbsent() (pulled up from ConcurrentHashMap, remove() (with key and value arguments), replace().

Conclusion

All in all, it can be said that a lot of atomic operations have made it to the top-level Map API, which is good. But then again, the pre-existing confusion related to the semantics of null in maps has deepened. The terminologies “present” vs. “absent”, “contains”, “default” don’t necessarily help clarifying these things, which is surprisingly against the rules of keeping an API consistent and most importantly, regular. Thus as a consumer of this API, ideally, you should keep null out of maps, both as keys and as values!

Next week in this blog series, we’re going to look at how Java 8 will allow you to define local transactional scope very easily, so stay tuned!

More on Java 8

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

No CROSS JOIN in MS Access

For the upcoming jOOQ 3.3, we’re now integrating support for the MS Access database through the JDBC-ODBC bridge, which is included in the JDK up until Java SE 7. Note that it will be removed in Java 8! Alternative access to access databases (pun intended) can be obtained through a hack involving ucanaccess, which is basically combining the HSQLDB parser with Jackcess, a low-level I/O library for MS Access database files.

MS Access is still an immensely popular relational database, ranking in the top 10 at db-engines.com’s DBMS ranking. Yet it has quirks. Many of them. One is the fact that there is no formal CROSS JOIN operation. Remember, most databases support explicit CROSS JOINing as such:

SELECT     p1.name player1, 
           p2.name player2
FROM       player p1 
CROSS JOIN player p2

The above query will generate all the pairings between two players.
The same can be written in pre-ANSI SQL-92 with comma-separated table lists:

SELECT     p1.name player1, 
           p2.name player2
FROM       player p1, 
           player p2

The first syntax, however, is more powerful and more expressive as it can be used in nested JOIN expressions and it shows your intent more clearly. A nice example was given here in a previous blog post.

How to work around a missing CROSS JOIN

Usually, missing support for CROSS JOIN can be emulated trivially using an INNER JOIN with a TRUE predicate as such:

SELECT     p1.name player1, 
           p2.name player2
FROM       player p1 
JOIN       player p2
ON         1 = 1

This is what jOOQ does for the Sybase Adaptive Server Enterprise database. But this doesn’t work for MS Access, because the JOIN operation there explicitly requires column references from either table on either side. The documentation reads:

Syntax:

FROM table1 INNER JOIN table2 
ON table1.field1 compopr table2.field2

This is quite a bummer from many points of view, not only for CROSS JOIN emulation. Given that any ANSI-92 JOIN syntax can be transformed into an ANSI-86 join expression (table list in the FROM clause and all predicates in the WHERE clause), it is also a bit surprising.

A simple workaround that seems to work for some use-cases is to take any numeric column from either table, and multiply it by zero:

SELECT     p1.name player1, 
           p2.name player2
FROM       player p1 
JOIN       player p2
ON         p1.id * 0 = p2.id * 0

But if the database itself is already this quirky, I suspect that it might not be able to optimise the above SQL.

In short…

MS Access does not support CROSS JOIN. For the time being, try to work around it using comma-separated table lists, while we work out more sophisticated SQL transformation in jOOQ.

jOOQ Newsletter: February 12, 2014

Subscribe to the newsletter here

Tweet of the Day

Our followers, users and customers are shouting to the world. Here are:

The Data Geekery Business Case at RedHat’s opensource.com

In our last newsletter, we have promoted our cooperation with RedHat’s content marketing team regarding our Open Source vendor business model. In the mean time, we have published another article on their platform about the 5 lessons learned for any business transitioning to a revenue-based model with Open Source. We recommend this interesting read to anyone who plans to make money with Open Source:

http://opensource.com/business/14/1/5-lessons-open-source-revenue-based-model

Our cooperation with other Open Source projects is a crucial part of our marketing strategy. Our ASL 2.0 / commercial dual licensing model might prove to be a viable use-case also for other Open Source projects building on top of jOOQ. Examples of such projects that are currently investigating an integration are

Also in the future, we’ll make an effort to better integrate with such projects to help spread the good jOOQ news to the world.

Community Work

It looks like great community-contributed work doesn’t stop appearing around jOOQ. We’ve worked together with Loiane Groner who has written this great tutorial about jOOQ in Portuguese. Loiane is consultant working for IBM and a well-known writer of books that mostly talk about Sencha Ext JS. She has also been promoting MyBatis in the past. We’re looking forward to more from her!

Ben Hood has been a long-term jOOQ aficionado who had been very active on the user group. He has taken inspiration from jOOQ to build his own DSL for Apache Cassandra, calling it CQLC, which is great news for the Cassandra community!Now, we’re looking forward to other APIs that cover MongoDB or Neo4j!

And just recently, Breandan Considine (who has lost ten pounds because of jOOQ) published great video tutorial for jOOQ, Gradle and IntelliJ IDEA users

Upcoming Events

In January, we have been visiting probably the largest JUG in Germany, theRheinJUG in Düsseldorf and also the JUGM in Munich. The German-speakers among you can watch the whole RheinJUG presentation. The audience was very very interactive and has left the talk with thousands of insights to continue discussing, as our talks inspire not only jOOQ business, but also SQL love!

Here is an overview of our upcoming events.

With our new SQL-talk, we’ll no longer just spread some jOOQ love, but also some SQL love in general. We believe that SQL deserves more presence in today’s software engineering talks, and who would be better to talk about SQL than us? Are you interested in hosting this talk at your company? Contact us!

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

Java Zone – Java 8

In this section of the newsletter, we usually talk about SQL in the “SQL Zone”, but our other beloved technology is Java and exciting times are ahead in 2014 for us Java folks. This is why we have started a new series in our blog, the Java 8 Friday Goodies.

In this series, we talk about little everyday things that improve / impair / or just change with Java 8 when we code. Some examples:

If you haven’t already, sign up for the Java 8 Friday Goodies blog category!

Why Everyone Hates Operator Overloading

… no, don’t tell me you like Perl. Because you don’t. You never did. It does horrible things. It makes your code look like…

Designing Perl 6
Designing Perl 6 as found on the awesome Perl Humour page

Perl made heavy use of operator overloading and used operators for a variety of things. A similar tendency can be seen in C++ and Scala. See also people comparing the two. So what’s wrong with operator overloading?

People never agreed whether Scala got operator overloading right or wrong:

Usually, people then cite the usual suspects, such as complex numbers (getting things right):

class Complex(val real:Int, 
              val imaginary:Int) {
    def +(operand:Complex):Complex = {
        new Complex(real + operand.real, 
                    imaginary + operand.imaginary)
    }
 
    def *(operand:Complex):Complex = {
        new Complex(real * operand.real - 
                    imaginary * operand.imaginary,
            real * operand.imaginary + 
            imaginary * operand.real)
    }
}

The above will now allow for adding and multiplying complex numbers, and there’s absolutely nothing wrong with that:

val c1 = new Complex(1, 2)
val c2 = new Complex(2, -3)
val c3 = c1 + c2
 
val res = c1 + c2 * c3

But then, there are these weirdo punctuation things that make average programmers simply go mad:

 ->
 ||=
 ++=
 <=
 _._
 ::
 :+=

Don’t believe it? Check out this graph library!

To the above, we say:

Operator Overloading? Meh

How operator overloading should be

Operator overloading can be good, but mostly isn’t. In Java, we’re all missing better ways to interact with BigDecimal and similar types:

// How it is:
bigdecimal1.add(bigdecimal2.multiply(bigdecimal3));

// How it should be:
bigdecimal1 + bigdecimal2 * bigdecimal3

Of course, operator precedence would take place as expected. Unlike C++ or Scala, ideal operator overloading would simply map common operators to common method names. Nothing more. No one really wants API developers to come up with fancy ##-%>> operators.

While Ceylon, Groovy, and Xtend implemented this in a somewhat predictable and useful way, Kotlin is probably the language that has implemented the best standard operator overloading mechanism into their language. Their documentation states:

Binary operations

Expression Translated to
a + b a.plus(b)
a – b a.minus(b)
a * b a.times(b)
a / b a.div(b)
a % b a.mod(b)
a..b a.rangeTo(b)

That looks pretty straightforward. Now check this out:

“Array” access

Symbol Translated to
a[i] a.get(i)
a[i, j] a.get(i, j)
a[i_1, …, i_n] a.get(i_1, …, i_n)
a[i] = b a.set(i, b)
a[i, j] = b a.set(i, j, b)
a[i_1, …, i_n] = b a.set(i_1, …, i_n, b)

Now, I really don’t see a single argument against the above. This goes on, and unfortunately, Java 8 has missed this train, as method references cannot be assigned to variables and invoked like JavaScript functions (although, that’s not too late for Java 9+):

Method calls

Symbol Translated to
a(i) a.invoke(i)
a(i, j) a.invoke(i, j)
a(i_1, …, i_n) a.invoke(i_1, …, i_n)

Simply beautiful!

Conclusion

We’ve recently blogged about Ceylon’s awesome language features. But the above Kotlin features are definitely a killer and would remove any other sorts of desires to introduce operator overloading in Java for good.

Let’s hope future Java versions take inspiration from Kotlin, a language that got operator overloading right.

Java 8 Friday Goodies: Lambdas and SQL

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. tweet this

Java 8 Goodie: Lambdas and SQL

If you’re used to writing Groovy, this may appear “so 2003” to you. We know. Groovy has known a very useful way to write string-based SQL since its early days. Here’s an example written in Groovy (see the official docs here):

import groovy.sql.Sql
sql = Sql.newInstance( 
    'jdbc:h2:~/test', 'sa', '', 
    'org.h2.Driver' )
sql.eachRow( 
    'select * from information_schema.schemata' 
) { 
    println "$it.SCHEMA_NAME -- $it.IS_DEFAULT" 
}

Note also Groovy’s built-in String interpolation, where you can put expressions into strings. But we’re in Java land, and with Java 8, things get better in the Java / SQL integration as well, if we’re using third-party libraries, instead of JDBC directly.

In the following examples, we’re looking at how to fetch data from an H2 database and map records into custom POJOs / DTOs using these three popular libraries:

As always, the sources are also available from GitHub. For these tests, we’re creating a little POJO / DTO to wrap schema meta-information:

class Schema {
    final String schemaName;
    final boolean isDefault;

    Schema(String schemaName, boolean isDefault) {
        this.schemaName = schemaName;
        this.isDefault = isDefault;
    }

    @Override
    public String toString() {
        return "Schema{" +
               "schemaName='" + schemaName + '\'' +
               ", isDefault=" + isDefault +
               '}';
    }
}

Our main method will get an H2 connection through DriverManager:

Class.forName("org.h2.Driver");
try (Connection c = getConnection(
        "jdbc:h2:~/test", "sa", "")) {

  String sql = "select schema_name, is_default "+
               "from information_schema.schemata "+
               "order by schema_name";
  // Library code here...
}

Now, how does Java 8 improve upon the jOOQ API, when using String-based SQL? Greatly! Check out the following little query:

DSL.using(c)
   .fetch(sql)
   .map(r -> new Schema(
       r.getValue("SCHEMA_NAME", String.class),
       r.getValue("IS_DEFAULT", boolean.class)
   ))
   .forEach(System.out::println);

This is how things should be, right? Note that jOOQ’s native APIs are also capable of mapping the database Record onto your POJO directly, as such:

DSL.using(c)
   .fetch(sql)
   .into(Schema.class)
   .forEach(System.out::println);

Things look just as nice when doing the same with Spring JDBC and RowMapper (note, the following still throws checked SQLExceptions):

new JdbcTemplate(
        new SingleConnectionDataSource(c, true))
    .query(sql, (rs, rowNum) -> 
        new Schema(
            rs.getString("SCHEMA_NAME"),
            rs.getBoolean("IS_DEFAULT")
        ))
    .forEach(System.out::println);

… and if you’re using Apache DbUtils, you can do almost the same:

new QueryRunner()
    .query(c, sql, new ArrayListHandler())
    .stream()
    .map(array -> new Schema(
        (String) array[0],
        (Boolean) array[1]
    ))
    .forEach(System.out::println);

Conclusion

All three solutions are more or less equivalent and quite lean. The point here, again, is that Java 8 will improve all existing APIs. The more unambiguous (few overloads!) methods accepting SAM arguments (single abstract method types), the better for a Java 8 integration.

Java 8 and SQL look very lean tweet this

Next week, we’re going to see a couple of things that will greatly improve when using the java.util.Map API

More on Java 8

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

You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!

You’re very likely to have gotten SQL date time arithmetic wrong. And why is that? Google it! You’ll quickly find blog posts like these:

And they’re all advocating stuff like this:

SYSDATE + (10/1440) is ten minutes from now.

Is it really? What about this beauty:

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
       trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;

Did you think about timezones? Did you think about daylight savings time? Did you think about leap seconds? And there many other things that can go horribly wrong, when you think you can beat date time arithmetic by counting seconds, days and other entities. Intead of adding things up by yourself, use vendor-specific built-in functions. Unfortunately, they’re a bit hard to remember.

But luckily, there’s jOOQ to standardise SQL. We’ve blogged about expression standardisation before, and we’re doing it again, with this simple date time arithmetic test programme:

import static org.jooq.DatePart.DAY;
import static org.jooq.DatePart.HOUR;
import static org.jooq.DatePart.MINUTE;
import static org.jooq.DatePart.MONTH;
import static org.jooq.DatePart.SECOND;
import static org.jooq.DatePart.YEAR;
import static org.jooq.SQLDialect.INGRES;
import static org.jooq.SQLDialect.SQL99;
import static org.jooq.impl.DSL.select;
import static org.jooq.impl.DSL.timestampAdd;
import static org.jooq.impl.DSL.using;

import java.sql.Timestamp;
import java.util.EnumSet;

import org.jooq.QueryPart;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;

public class Compatibility {

    public static void main(String[] args) {
        Timestamp t = new Timestamp(0);

        // Using the new version of the 
        // timestampAdd() function
        // that will be added in jOOQ 3.3
        print(select(
            timestampAdd(t, 2, YEAR)  .as("yy"),
            timestampAdd(t, 2, MONTH) .as("mm"),
            timestampAdd(t, 2, DAY)   .as("dd"),
            timestampAdd(t, 2, HOUR)  .as("hh"),
            timestampAdd(t, 2, MINUTE).as("mi"),
            timestampAdd(t, 2, SECOND).as("ss")
        ));
    }

    private static void print(QueryPart part) {
        System.out.println("Printing " + part);
        System.out.println("---------------------");

        // Get only SQLDialect families, don't
        // distinguish between
        // SQL Server 2008 or SQL Server 20012
        EnumSet<SQLDialect> dialects =
            EnumSet.noneOf(SQLDialect.class);
        for (SQLDialect dialect:SQLDialect.values())
            if (dialect != SQL99 && dialect != INGRES)
                dialects.add(dialect.family());

        // Render the passed in SQL clause to
        // all supported SQL dialects
        for (SQLDialect dialect: dialects)
            System.out.println(
                String.format("%1$s: \n%2$s\n",
                dialect, using(dialect, new Settings()
                         .withRenderFormatted(true))
                         .renderInlined(part)
            ));

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

And here’s the output for most databases currently supported by jOOQ:

-- CUBRID: 
select 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 year) "yy", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 month) "mm", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 day) "dd", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 hour) "hh", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 minute) "mi", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 second) "ss"
from "db_root"

-- DERBY: 
select 
  {fn timestampadd(sql_tsi_year, 2, timestamp('1970-01-01 01:00:00.0')) } as "yy", 
  {fn timestampadd(sql_tsi_month, 2, timestamp('1970-01-01 01:00:00.0')) } as "mm", 
  {fn timestampadd(sql_tsi_day, 2, timestamp('1970-01-01 01:00:00.0')) } as "dd", 
  {fn timestampadd(sql_tsi_hour, 2, timestamp('1970-01-01 01:00:00.0')) } as "hh", 
  {fn timestampadd(sql_tsi_minute, 2, timestamp('1970-01-01 01:00:00.0')) } as "mi", 
  {fn timestampadd(sql_tsi_second, 2, timestamp('1970-01-01 01:00:00.0')) } as "ss"
from "SYSIBM"."SYSDUMMY1"

-- FIREBIRD: 
select 
  dateadd(year, 2, timestamp '1970-01-01 01:00:00.0') "yy", 
  dateadd(month, 2, timestamp '1970-01-01 01:00:00.0') "mm", 
  dateadd(day, 2, timestamp '1970-01-01 01:00:00.0') "dd", 
  dateadd(hour, 2, timestamp '1970-01-01 01:00:00.0') "hh", 
  dateadd(minute, 2, timestamp '1970-01-01 01:00:00.0') "mi", 
  dateadd(second, 2, timestamp '1970-01-01 01:00:00.0') "ss"
from "RDB$DATABASE"

-- H2: 
select 
  dateadd('year', 2, timestamp '1970-01-01 01:00:00.0') "yy", 
  dateadd('month', 2, timestamp '1970-01-01 01:00:00.0') "mm", 
  dateadd('day', 2, timestamp '1970-01-01 01:00:00.0') "dd", 
  dateadd('hour', 2, timestamp '1970-01-01 01:00:00.0') "hh", 
  dateadd('minute', 2, timestamp '1970-01-01 01:00:00.0') "mi", 
  dateadd('second', 2, timestamp '1970-01-01 01:00:00.0') "ss"
from dual

-- HSQLDB: 
select 
  {fn timestampadd(sql_tsi_year, 2, timestamp '1970-01-01 01:00:00.0') } as "yy", 
  {fn timestampadd(sql_tsi_month, 2, timestamp '1970-01-01 01:00:00.0') } as "mm", 
  {fn timestampadd(sql_tsi_day, 2, timestamp '1970-01-01 01:00:00.0') } as "dd", 
  {fn timestampadd(sql_tsi_hour, 2, timestamp '1970-01-01 01:00:00.0') } as "hh", 
  {fn timestampadd(sql_tsi_minute, 2, timestamp '1970-01-01 01:00:00.0') } as "mi", 
  {fn timestampadd(sql_tsi_second, 2, timestamp '1970-01-01 01:00:00.0') } as "ss"
from "INFORMATION_SCHEMA"."SYSTEM_USERS"

-- MARIADB: 
select 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual

-- MYSQL: 
select 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual

-- POSTGRES: 
select 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' year')::interval) as "yy", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' month')::interval) as "mm", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' day')::interval) as "dd", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' hour')::interval) as "hh", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' minute')::interval) as "mi", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' second')::interval) as "ss"

-- SQLITE: 
select 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' year') yy, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' month') mm, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' day') dd, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' hour') hh, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' minute') mi, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' second') ss

-- DB2: 
select 
  (timestamp '1970-01-01 01:00:00.0' + 2 year) "yy", 
  (timestamp '1970-01-01 01:00:00.0' + 2 month) "mm", 
  (timestamp '1970-01-01 01:00:00.0' + 2 day) "dd", 
  (timestamp '1970-01-01 01:00:00.0' + 2 hour) "hh", 
  (timestamp '1970-01-01 01:00:00.0' + 2 minute) "mi", 
  (timestamp '1970-01-01 01:00:00.0' + 2 second) "ss"
from "SYSIBM"."DUAL"

-- ORACLE: 
select 
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'year')) "yy", 
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'month')) "mm", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'day')) "dd", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'hour')) "hh", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'minute')) "mi", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'second')) "ss"
from dual

-- SQLSERVER: 
select 
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy], 
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm], 
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd], 
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh], 
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi], 
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]

-- SYBASE: 
select 
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy], 
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm], 
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd], 
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh], 
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi], 
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]
from [SYS].[DUMMY]

If you’ve read thus far, you’ve realised that you shouldn’t perform date-time arithmetic using fractional days as many people think they should when they write Oracle SQL. Just use built-in functions and/or interval data types. Always!

JDBC 4.0’s Lesser-known Clob.free() and Blob.free() Methods

When I talk about jOOQ at conferences, I always show this slide containing a bunch of very common JDBC mistakes that people often commit:

Six common JDBC bugs in this image
Six common JDBC bugs in this image

Can you find the bugs? Some of them are obvious, such as:

  • Line 4: Syntax errors resulting from bad concatenation on line 3
  • Line 7: Syntax errors and SQL injection risk due to variable inlining
  • Line 8: Wrong bind index resulting from a potential mismatch on line 3
  • Line 14: Wrong column name due to sloppy rename
  • Line 18: Bad resource management

But then, there’s another very subtle bug that most people are unaware of because the fix was only possible since the upgrade in Java 6 / JDBC 4.0. See the solution, below:

Solution to the previous six bugs
Solution to the previous six bugs

With JDBC 4.0, the Clob.free() and the Blob.free() methods were introduced. While calling them is optional, it may be a very bad idea not to call them as early as possible, as you should not rely on the garbage collector to kick in early enough to free these resources. In fact, in certain databases / JDBC drivers, LOBs can outlive individual statements and/or transactions. They’re beasts of their own. If you’re reading through the JDBC tutorial (and also in the JDBC specification), it says:

Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction.

The same is true for arrays, which also have an Array.free() method since Java 6 / JDBC 4.0.

So if your application has long-running transactions, do call these free() methods, or make it a habit to always call them. We’ll file an issue to FindBugs to make this a potential bug pattern.