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/

SQL tooling, the ranking

When you need to get up and running quickly with your database, the tooling becomes very important. When developing jOOQ and adding integrations for new databases, I really love those ones that provide me with simple ways to create new databases, schemata, users, roles, grants, whatever is needed, using simple dialogs where I can click next next next. After all, we’re in the 21st century, and I don’t want to configure my software with punchcards anymore.

Database tooling categories

So with jOOQ development, I’ve seen a fair share of databases and their toolings. I’d like to divide them into three categories. Please note, that this division is subjective, from the point of view of jOOQ development. With most of these databases, I have no productive experience (except Oracle and MySQL). Things may change drastically when you go into production. So here are the categories:

The “all-you-can-wish-for” ones

These friends of mine ship with excellent tooling already integrated into their standard deliverable for free. It is easy to start the tooling and use it right away, without any configuration. The tooling is actually an intuitive rich client and I don’t have to read thousands of manual pages and google all around, or pay extra license fees to get the add-on. This category contains (in alphabetical order):

  • CUBRID with its Eclipse-RCP based CUBRID Manager. This is a very nice tool for a newcomer.
  • DB2 with its Eclipse-RCP based IBM Data Studio. IBM created Eclipse. It would’ve been a shame if they hadn’t created the Data Studio.
  • Postgres with pgAdminIII. Very very nice looking and fast.
  • SQL Server with its SQL Server Management Studio. This is probably the most complete of all. You can lose yourself in its myriads of properties and configuration popups.
  • Sybase SQL Anywhere and Sybase ASE, both share the same tooling called Sybase Central. It looks a bit out of date, but all administrative operations can be done easily.

The ones with sufficient tooling

These databases have tooling that is “sufficient”. This means that they ship with some integrated scripting-enabled console. Some of them are also generally popular, such that there exist free open source tools to administer those databases. This includes MySQL and Oracle. Here are to “OK” ones:

  • H2. Its web-based console is actually quite nice-looking. It features DHTML-based auto-completion and scripting. I can live with that.
  • Ingres. This dinosaur seems not to have upgraded UI components since Windows 95, but it works as good as it has to.
  • MySQL, with phpMyAdmin. This is a very nice, independent, open source PHP application for MySQL administration. You can install it easily along with MySQL using XAMPP, a nice Apache, MySQL, PHP, Perl distribution. Yes, I like installing complete things using the next next next pattern!
  • Oracle. It has sql*plus for scripting and there are many commercial and open source products with user interfaces. My favourite ones are Toad and Toad Extensions, a really nice and free Eclipse plugin. It is worth mentioning, that if you pay the extra license fee, you will have access to Oracle Enterprise Manager and other very very fancy tools. With money, you clearly can’t complain here.

The other ones…

Here, you’re back to loading *.sql files with DDL all along. No help from the vendors, here.

  • Derby. I’m not aware of any tooling. Correct me if I’m wrong
  • HSQLDB. Its integrated console can execute SQL, but it doesn’t provide syntax highlighting, checking, autocompletion, etc. I’m probably better off using SQuirreL SQL, or any other generic SQL tool.
  • SQLite. Good luck there! This database is really minimal!

Screenshots (ordered by database, alphabetically)

A very refreshing point of view about language design

Have fun:

http://joshondesign.com/2012/03/09/open-letter-language-designers

Which database are you using jOOQ with?

I’d like to “feel” the needs of my users. That’s why it’s interesting to know for which databases I should add features next. Which database are you using jOOQ with?