ORM vs. SQL, compared to C vs. ASM

History is repeating itself. This is nothing new, but it takes wisdom (and Elephant memory) to remember when and how things had already happened in a similar way. When you feel that the whole SQL versus ORM debate is a bit boring and you may have seen it before, you’re probably right. It’s another religious war that can be compared to the war between C and Assembler some 40 years back. Think about it this way:

ORM vs. SQL

How many ORMs do you know that support:

  • Window functions
  • MERGE statement
  • Recursive queries
  • Common table expressions
  • Ordered aggregate functions
  • Statistical functions
  • Stored procedures (And I mean not just scalar ones!)
  • SQL-based XML
  • Temporary tables
  • … I could go on and on

ORMs are not just “higher-level” they are an entirely different paradigm, put on top of SQL. So, saying SQL is “lower-level” is not entirely accurate. It’s on a “different level”. ORMs are pretending that SQL is just a little “low-level” thing.

C vs. ASM

(citing reddit user henk53)

Which C compilers at the time made use of:

  • Advanced addressing modes
  • Opcode tricks
  • Special instructions (intrinsics came much later)
  • Jump tables (yeah, switch is close, but not quite it)
  • Build-in high level functions on some architectures (like string copy)
  • Page zero tricks
  • Segments (ugly things when all you have is small ones, but quite powerful when used fine-grained for ‘objects’ and structures)
  • Delay slot shadowing
  • Knowledge of fitting computations exactly in registers and L1.
  • … I could go on and on

C wasn’t just higher level, it was another paradigm, and yes, C compilers pretending that assembly is just a little “low-level” thing did raise our hackles back then. It threw away all the nice subtle intricacies of each specific CPU architecture and made them all equal by using only the common denominator of functionality that each CPU had.

It took a while, but eventually C compilers got better at optimizing than humans and could actually take advantage of advanced instructions in a CPU.

Original discussion

See an extract of the original thread here:
http://www.reddit.com/r/java/comments/sk25o/forget_hibernate_jooq_is_byfar_the_best_database/#c4f6z1w

jOOQ and Hibernate, a discussion

Starting out from a rather emotional and maybe not really objective comparison between jOOQ and Hibernate, this turned out to be quite an interesting discussion. A must-read for jOOQ and SQL aficionados:

http://www.reddit.com/r/java/comments/sk25o/forget_hibernate_jooq_is_byfar_the_best_database/

I personally like this comment here:

“You’d be hard-pressed to find a database abstraction layer that can map to SQL better than #jOOQ does, it pretty-much is #SQL.”

jOOQ users’ most frequently used databases

I have recently started an enquiry about which databases jOOQ users are most frequently using. The poll is here:
https://blog.jooq.org/2012/04/12/which-database-are-you-using-jooq-with/

Of course, such a poll is best analysed using jOOQ’s OLAP features. When dumped into a POLL table in our database, we can query the database for the ranking as such:

System.out.println(
create.select(
         denseRank().over().orderBy(POLL.VOTES.desc()),
         POLL.VOTES
             .mul(100)
             .div(sum(POLL.VOTES).over())
             .concat(" %")
             .lpad(4, ' ').as("percent"),
         POLL.DIALECT)
      .from(POLL)
      .orderBy(POLL.VOTES.desc())
      .fetch());

And the winners after 40 votes are:

+----------+-------+-------------------+
|dense_rank|percent|dialect            |
+----------+-------+-------------------+
|         1|22 %   |MySQL              |
|         1|22 %   |Oracle             |
|         2|15 %   |Postgres           |
|         2|15 %   |H2                 |
|         3|10 %   |SQLServer          |
|         4| 7 %   |HSQLDB             |
|         5| 2 %   |Other              |
|         5| 2 %   |DB2                |
|         5| 2 %   |Derby              |
|         6| 0 %   |SQLite             |
|         6| 0 %   |Ingres             |
|         6| 0 %   |Sybase SQL Anywhere|
|         6| 0 %   |Sybase ASE         |
|         6| 0 %   |CUBRID             |
+----------+-------+-------------------+

Exciting ideas in Java 8: Streams

Brian Goetz’s recent post on the State of the Lambda reveils exciting new ideas that are prone to be included in Java 8. One of them is the concept of “Streams” as opposed to “Collections”. Using the new Java 8 extension methods, the Iterable interface can be extended compatibly with a lot of “lazy” and “eager” methods for streaming behaviour:

public interface Iterable<T> {
    // Abstract methods
    Iterator<T> iterator();

    // Lazy operations
    Iterable<T> filter(Predicate<? super T> predicate) default ...
    <U> Iterable<U> map(Mapper<? super T, ? extends U> mapper) default ...
    <U> Iterable<U> flatMap(
        Mapper<? super T, ? extends Iterable<U>> mapper) default ...
    Iterable<T> cumulate(BinaryOperator<T> op) default ...

    Iterable<T> sorted(
        Comparator<? super T> comparator) default ...
    <U extends Comparable<? super U>> Iterable<T> sortedBy(
        Mapper<? super T, U> extractor) default ...
    Iterable<T> uniqueElements() default ...

    <U> Iterable<U> pipeline(
        Mapper<Iterable<T>, ? extends Iterable<U>> mapper) default ...
    <U> BiStream<T, U> mapped(
        Mapper<? super T, ? extends U> mapper) default ...
    <U> BiStream<U, Iterable<T>> groupBy(
        Mapper<? super T, ? extends U> mapper) default ...
    <U> BiStream<U, Iterable<T>> groupByMulti(
        Mapper<? super T, ? extends Iterable<U>> mapper) default ...

    // Eager operations
    boolean isEmpty() default ...;
    long count() default ...
    T getFirst() default ...
    T getOnly() default ...
    T getAny() default ...

    void forEach(Block<? super T> block) default ...
    T reduce(T base, BinaryOperator<T> reducer) default ...
    <A extends Fillable<? super T>> A into(A target) default ...

    boolean anyMatch(Predicate<? super T> filter) default ...
    boolean noneMatch(Predicate<? super T> filter) default ...
    boolean allMatch(Predicate<? super T> filter) default ...

    <U extends Comparable<? super U>> T maxBy(
        Mapper<? super T, U> extractor) default ...
    <U extends Comparable<? super U>> T minBy(
        Mapper<? super T, U> extractor) default ...
}

The above are rough and incomplete API ideas, or as Brian Goetz puts it: A “strawman writeup of the key concepts”.

The goal of this early publication is for the community to “TRY OUT THE CODE. *The single most valuable thing that the community can do to help move this effort forward, and improve the quality of the result, is to try out the code early and often.*”

Read the full article here:

http://cr.openjdk.java.net/~briangoetz/lambda/collections-overview.html

Must-have Eclipse plugin: AnyEdit Tools

On fresh Eclipse installations, I usually feel a bit naked, until I realise that this lovely little plugin is not part of Eclipse itself. It’s called AnyEdit tools, and it features the following nice things:

  • Convert Tabs <-> Spaces
  • Convert: Chars <-> Html entities
  • Convert: Camel <-> Underscores
  • Convert: Capitalize
  • Convert: Invert Case
  • Convert: To Upper Case
  • Convert: To Lower Case
  • Convert: From/To Base64
  • Compare/Replace with Clipboard
  • Compare/Replace with Workspace File…
  • Compare/Replace with External File…
  • Compare/Replace with Opened Editor…

Most of these features are available through shortcuts, too. Download it, too and you’ll never want to miss it again!

AnyEdit Tools screenshot

AnyEdit Tools screenshot

jDBI: A simple convenience layer on top of JDBC

I’m always looking out for similar tools like jOOQ, or at least tools that work in the same domain – the domain of database access abstraction. jDBI looks lovely. It provides simple solutions for what JDBC is lacking in general. Here are a couple of features (taken from the intro):

Fluent API

JDBC is quite verbose in that it usually requires three steps to get to a result:

  1. Obtain a connection
  2. Prepare a statement
  3. Fetch results (meaning iterate over a result set, even if you only need one value)

Here’s how jDBI models its fluent API, to ease some of that pain:

// using in-memory H2 database
DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test",
                                          "username",
                                          "password");
DBI dbi = new DBI(ds);
Handle h = dbi.open();
h.execute(
  "create table something (id int primary key, name varchar(100))");
h.execute(
  "insert into something (id, name) values (?, ?)", 1, "Brian");

String name = h.createQuery("select name from something where id = :id")
               .bind("id", 1)
               .map(StringMapper.FIRST)
               .first();
                    
assertThat(name, equalTo("Brian"));

h.close();

DAO layer simplification

In the DAO layer, you’re often writing the same SQL code again and again. Hibernate / JPA are quite convenient in handling this, but you don’t always want to have such big dependencies. So jDBI offers the essence of EJB 3.0. Simple annotations for named queries (although, I do think that Brian McCallister could use JPA annotations instead of his own ones):

public interface MyDAO
{
  @SqlUpdate(
    "create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);

  /**
   * close with no args is used to close the connection
   */
  void close();
}

Here’s how to use the above DAO:

// using in-memory H2 database via a pooled DataSource
JdbcConnectionPool ds = JdbcConnectionPool.create("jdbc:h2:mem:test2",
                                                  "username",
                                                  "password");
DBI dbi = new DBI(ds);
MyDAO dao = dbi.open(MyDAO.class);

dao.createSomethingTable();
dao.insert(2, "Aaron");

String name = dao.findNameById(2);
assertThat(name, equalTo("Aaron"));

dao.close();
ds.dispose();

Summary

There are a few other very nice features, which I am now going to check for their usefulness in jOOQ. Read the manual here and discover this little gem:

http://jdbi.codehaus.org/archive.html

Or get the sources here:

https://github.com/brianm/jdbi

jOOQ website re-launch

jOOQ has re-launched its website. Come back to see the new design here:

http://www.jooq.org/