Tag Archives: jooq

SLICK, integrating SQL into Scala


Now it’s official – even if version numbers are still preceded by a “zero” major release: SLICK has been publicly announced by Typesafe:

SLICK stands for Scala Language-Integrated Connection Kit, which is more or less the Scala equivalent for LINQ-to-SQL. Note that I say LINQ-to-SQL, not LINQ in general, as Scala already has sufficient means of querying collections using the Scala language itself.

Here’s a sample of what SLICK code will look like (taken from the SLICK website):

object Coffees extends Table[(String, Int, Double)]("COFFEES") {
  def name = column[String]("COF_NAME", O.PrimaryKey)
  def supID = column[Int]("SUP_ID")
  def price = column[Double]("PRICE")
  def * = name ~ supID ~ price
}

Coffees.insertAll(
  ("Colombian",         101, 7.99),
  ("Colombian_Decaf",   101, 8.99),
  ("French_Roast_Decaf", 49, 9.99)
)

val q = for {
  c <- Coffees if c.supID === 101
  //                       ^ comparing Rep[Int] to Rep[Int]!
} yield (c.name, c.price)

println(q.selectStatement)

q.foreach { case (n, p) => println(n + ": " + p) }

As you can see, SLICK neatly integrates with Scala’s own syntax. As with LINQ-to-SQL, SLICK’s goal is to

“write your database queries in Scala instead of SQL”

This is quite orthogonal to what jOOQ is aiming for:

“SQL was never meant to be anything other than… SQL!”

As a reminder, see my previous blog post about how jOOQ integrates with Scala, and how you can write almost-SQL queries in Scala using jOOQ against 13 popular databases. It would be interesting to compare the two approaches side-by-side in an independent evaluation, to see the pro’s and con’s of each one, in terms of

  • Developer productivity
  • Maintainability
  • Performance
  • Feature scope
  • etc.

I think it’s time someone made that evaluation. An example can be seen here:

http://stackoverflow.com/questions/10537766/closest-equivalent-to-sqlalchemy-for-java-scala


Funky String Function Simulation in SQLite


SQLite is so light, it doesn’t have any useful string functions. It doesn’t have ASCII(), LPAD(), RPAD(), REPEAT(), POSITION(), you name it. It does, however, have a wonderful RANDOMBLOB() function. So if you really need a good random number generator, use a SQLite database and generate a 1GB blob. That should give you a couple of random numbers for the next years.

For a full (or rather, empty) list see the SQLite function reference here:
http://www.sqlite.org/lang_corefunc.html

Function Simulation: REPEAT()

Not having any functions doesn’t mean that you can’t simulate them. You can. Take REPEAT(), for instance. Apart from the RANDOMBLOB(), you can also generate a ZEROBLOB(). It’s a blob with lots of zeros in it. But you can’t just go and do this:

-- Simulate REPEAT('abc', 3)
replace(zeroblob(3), 0, 'abc')

That would be too easy. The problem with the zeroblob is, that when cast to a string, it is actually a zero-terminated string. Quite usual when programming in C. But hey, the first character is a zero, so the resulting string is terminated right at the beginning. How useful is that??

But here’s a trick, QUOTE() the ZEROBLOB(). That would escape all characters in hex format. In other words:

quote(zeroblob(3)) yields X'000000'

Nice. Now we’ve got three extra letters around twice as many zeroes as we wanted. So we’ll simply do this

-- Simulate REPEAT('abc', 3)
replace(substr(quote(zeroblob(2)), 3, 3), '0', 'abc')

-- Or more generally: X = 'abc', Y = 3
replace(substr(quote(zeroblob((Y + 1) / 2)), 3, Y), '0', X)

Doesn’t that start to make fun? Note, I have documented this simulation also here:
http://stackoverflow.com/questions/11568496/how-to-simulate-repeat-in-sqlite

Function Simulation: LPAD() and RPAD()

REPEAT() was easy. But REPEAT() was inspired by LPAD() and RPAD(), which is similar to REPEAT(), except that a character is padded to the left or right of another string, until a given length of the resulting string is reached. ZEROBLOB() will help us again! Let’s consider RPAD():

-- Simulate RPAD('abc', 7, '-')
'abc' || replace(replace(substr(quote(zeroblob(4)), 3, 4), '''', ''), '0', '-')

-- Or more generally:
-- RPAD() Using X = 7, Y = '-', Z = 'abc'
Z || replace(
       replace(
         substr(
           quote(zeroblob((X + 1) / 2)), 
           3, (X - length(Z))
         ), '''', ''
       ), '0', Y
     )

-- LPAD() Using X = 7, Y = '-', Z = 'abc'
replace(
  replace(
    substr(
      quote(zeroblob((X + 1) / 2)), 
      3, (X - length(Z))
    ), '''', ''
  ), '0', Y
) || Z

Now if this isn’t funky! This was actually something, I didn’t come up with myself. This was an answer I was given on Stack Overflow, where great minds spend lots of spare time on weird problems like this:
http://stackoverflow.com/questions/6576343/how-to-simulate-lpad-rpad-with-sqlite

Of course, these simulations will be part of the next version of jOOQ, so you don’t have to worry any longer about how to do LPAD(), RPAD(), and REPEAT().


Array, list, set, map, tuple, record literals in Java


Occasionally, when I’m thrilled by the power and expressiveness of JavaScript, I find myself missing one or two features in the Java world. Apart from lambda expressions / closures or whatever you want to call “anonymous functions”, it’s the use of advanced literals for common data types, such as arrays, lists, sets, maps, etc. In JavaScript, no one would think about constructing a constant Map like this:

var map = new Object();
map["a"] = 1;
map["b"] = 2;
map["c"] = 3;

Instead, you’d probably write

var map = { "a":1, "b":2, "c":3 };

Specifically, when passing complex parameters to an API function, this turns out to be a very handy syntax.

What about these things in Java?

I’ve recently posted about a workaround that you can use for creating a “List literal” using Arrays.asList(…) here:

http://blog.jooq.org/2011/10/28/javas-arrays-aslist-is-underused/

This is somewhat OK. You can also construct arrays when you assign them, using array literals. But you cannot pass an array literal to a method:

// This will work:
int[] array = { 1, 2, 3 };

// This won't:
class Test {
  public void callee(int[] array) {}
  public void caller() {
    // Compilation error here:
    callee({1, 2, 3});
  }
}

Brian Goetz’s mentioning of various literals on lambda-dev

Missing this feature for quite a while, I was very thrilled to read Brian Goetz’s mentioning of them on the lambda-dev mailing list:

http://mail.openjdk.java.net/pipermail/lambda-dev/2012-May/004979.html

The ideas he was listing were these:

#[ 1, 2, 3 ]                          // Array, list, set
#{ "foo" : "bar", "blah" : "wooga" }  // Map literals
#/(\d+)$/                             // Regex
#(a, b)                               // Tuple
#(a: 3, b: 4)                         // Record
#"There are {foo.size()} foos"        // String literal

Unfortunately, he also added the following disclaimer:

Not that we’d embrace all of these immediately (or ever)

Obviously, at this stage of current Java language evolvements for Java 8, he cannot make any guarantee whatsoever about what might be added in the future. But from a jOOQ perspective, the idea of being able to declare tuple and record literals (with the appropriate backing language-support for such types!) is quite thrilling. Imagine selecting arbitrary tuples / records with their associated index/type, column/type pairs. Imagine a construct like this one in Java or Scala (using jOOQ):

// For simplicity, I'm using Scala's val operator here,
// indicating type inference. It's hard to guess what true
// record support in the java language should look like
for (val record : create.select(
                           BOOK.AUTHOR_ID.as("author"), 
                           count().as("books"))
                        .from(BOOK)
                        .groupBy(BOOK.AUTHOR_ID)
                        .fetch()) {
  
   // With true record support, you could now formally extract
   // values from the result set being iterated on. In other
   // words, the formal column alias and type is available to
   // the compiler:
   int author = record.author;
   int books = record.books;
}

Obviously, this is only speculation, but you can see that with true tuple / record support in the Java language, a lot of features would be unleashed in the Java universe with a very high impact on all existing libraries and APIs

Stay tuned! :-)


OneWebSQL™: Another competitor in the SQL schema generation “business”


I’ve been observing OneWebSQL™ for a year now, impatiently expecting its first public version and official go-live. I first discovered it during my regular jOOQ marketing work. I’m always closely observing competition for several reasons:

  • It shows that the market is alive and active, which helps making jOOQ even more relevant
  • jOOQ can incorporate the best ideas of competitor products
  • It’s always fun to meet people who share similar opinions

All of the above is a big motivation to make jOOQ better. For more competitor comparisons, see also the following recent articles:

OneWebSQL™

OneWebSQL™ is one of the more interesting ones, from a professional perspective. Similar to QueryDSL, OneWebSQL™ is backed by a company who has developed a SQL code generation and execution framework over the past years. Unlike jOOQ and QueryDSL, however, OneWebSQL™ is neither free nor open source. So lets have a brief look at what OneWebSQL™ is and how it compares to jOOQ and QueryDSL

Comparing jOOQ, QueryDSL, OneWebSQL™

The below list of compared features and attributes is meant to be objective and unbiased, even if it was written by the developer of jOOQ. Here goes

License

jOOQ ASL 2.0, free and open source
QueryDSL ASL 2.0, free and open source
OneWebSQL™ Proprietary license, closed source and partially obfuscated. Free runtime, somewhat expensive source code generator

Vendor and community

jOOQ Independent developer, active community
QueryDSL Mysema, active community
OneWebSQL™ e-point, newcomer with undisclosed community

Scope

Feature jOOQ QueryDSL OneWebSQL™
Source code generation [1] yes yes yes
Active Records [2] yes partially
(through JPA)
partially
(through generated DAOs)
Procedure support [3] yes no partially
(some JDBC abstraction)
UDT support [3] yes no no
DSL [4] yes
(formal BNF notation)
partially
(chained methods)
partially
(chained methods)
DML support yes yes yes
DDL support no no no
JPA / JPQL support partially
(can interact with JPA)
yes
(can render JPQL)
no
Other backends no yes no
Supported RDBMS 13 9
(or more, using JPA)
5
SQL dialect abstraction [5] yes yes partially
(simulates some syntax elements)
SQL dialect simulation [6] yes yes no
Scala examples [7] Partially
Proof of concept
Partially
Proof of concept
Probably (?)

Some explanations


Simulation of TRUNC() in Derby


Derby is missing out a lot of functions from the set of functions that other databases usually provide. One example is the TRUNC(value, decimals) function. According to the Wikipedia, truncation can be achieved as such:

-- trunc(x, n) 
CASE WHEN x > 0 
THEN
  floor(power(10, n) * x) / power(10, n) 
ELSE 
  ceil(power(10, n) * x) / power(10, n) 
END

Unfortunately, there is no POWER(base, exponent) function in Derby either. But no problem, we can simulate that as well. Let’s consider the Wikipedia again and we’ll find:

power(b, x) = exp(x * ln(b))

If we substitute that into the original simulation, we get for Derby:

-- trunc(x, n) 
CASE WHEN x > 0 
THEN 
  floor(exp(n * ln(10)) * x) / exp(n * ln(10))
ELSE 
  ceil(exp(n * ln(10)) * x) / exp(n * ln(10)) 
END

Verbose, probably quite inefficient, but effective! Let’s run a short test, too:

create table test (x numeric(10, 5), n int);

insert into test values (11.111, 0);
insert into test values (11.111, 1);
insert into test values (11.111, 2);
insert into test values (11.111, -1);

select
  x, n, 
  case when x >= 0
  then
    floor(exp(n * ln(10)) * x) / exp(n * ln(10))
  else
    ceil(exp(n * ln(10)) * x) / exp(n * ln(10))
  end "trunc(x, n)"
from test;

The above yields

X N TRUNC(X, N)
11.111 0 11
11.111 1 11.1
11.111 2 11.11
11.111 -1 10

Martin Fowler on “The Vietnam of Computer Science”


It couldn’t be a better match by a more suited person for jOOQ. Martin Fowler expresses his feelings about SQL, NoSQL, Object-relational mapping on his blog post, which is copied on DZone:

http://java.dzone.com/articles/martin-fowler-orm-hate

Think about the following two slogans:

“The Vietnam of Computer Science” – “A Peace Treaty Between SQL and Java”

Swell, no? :-)


How to simulate MySQL’s INSERT statement extensions


I have previously posted about the SQL MERGE statement, and how powerful it is here:
http://blog.jooq.org/2011/11/29/arcane-magic-with-the-sql2003-merge-statement/

Unfortunately, not all databases support this statement. Also, very often it is quite a bit of overkill, when what you want to do is to simply INSERT or UPDATE a single record, depending on whether it already exists.

MySQL’s nice INSERT statement extensions

MySQL has a couple of very nice extensions to the INSERT statement. Some of them are also supported by the CUBRID database. Let’s have a look at two features of the (simplified) syntax definition:

INSERT [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES (expr,...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

 
Essentially, we’re looking at two keywords / clauses:

  1. IGNORE: This will cause the INSERT statement to silently fail with a warning, if we’re about to insert a duplicate key
  2. ON DUPLICATE KEY UPDATE: This will cause the INSERT statement to perform an update on all affected records, if we’re about to insert a duplicate key

The full documentation can be seen here:
http://dev.mysql.com/doc/refman/5.5/en/insert.html

How jOOQ models these statements in other dialects

jOOQ’s API conveniently allows for using those MySQL features directly in insert statements. An example

// Insert an author who has recently married and thus
// changed their last name. If we already have that author, then
// update their last name:

create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
      .values(3, "Smith")
      .onDuplicateKeyUpdate()
      .set(AUTHOR.LAST_NAME, "Smith")
      .execute();

// Alternatively, we may not care if the author already
// exists, we just want to insert them without failure:

create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
      .values(3, "Smith")
      .onDuplicateKeyIgnore()
      .execute();

Note how jOOQ doesn’t model the IGNORE keyword as a keyword between INSERT [IGNORE] INTO. This keeps the public API a bit more concise, while at the same time being more consistent with pre-existing functionality, both in the INSERT syntax, as well as in the Loader API:

http://blog.jooq.org/2011/08/23/loading-csv-data-with-jooq-2/

Now, databases that do not support these syntax elements can simulate them using MERGE. The above statements will render something like this on an Oracle database:

-- ON DUPLICATE KEY UPDATE statement
merge into "AUTHOR" 
using (select 1 from dual) 
on ("AUTHOR"."ID" = 3) 
when matched then update set "LAST_NAME" = 'Smith' 
when not matched then insert ("ID", "LAST_NAME") values (3, 'Smith')

-- ON DUPLICATE KEY IGNORE statement
merge into "AUTHOR" 
using (select 1 from dual) 
on ("AUTHOR"."ID" = 3) 
when not matched then insert ("ID", "LAST_NAME") values (3, 'Smith')

Conclusion

These practices allow you to write short and concise code using the jOOQ API while still being able to target several backend databases optimally using efficient SQL statements. This will help you increase both your development as well as your runtime performance!


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

jOOQ website re-launch


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

http://www.jooq.org/


Follow

Get every new post delivered to your Inbox.

Join 217 other followers