What feature are you missing most in jOOQ?

This is your chance to influence future developments!

The comprehensive SQL bitwise operations compatibility list

One of the rather nasty, off-the-beaten-track battlefields in SQL non-standards are bitwise operations. To my knowledge, they’re not in any SQL standard (I’ve checked SQL:2008 drafts), but almost all databases support some form of bitwise operations. Here’s the relevant MySQL documentation page, as an example:

http://dev.mysql.com/doc/refman/5.5/en/bit-functions.html

So we’re talking about these operations: bit_count(), and (&), or (|), xor (^), not (~), left shift (<<), right shift (>>). Agreed, that’s not the most important functionality in a database. But these operations may come in handy every now and then.

The Ranking

So here’s the ranking of bitwise support as far as the upcoming jOOQ support is concerned:

1. Making the world a better place

These databases are bit-friends. This is probably because some C / Assembler guys are on the development teams. I don’t want to have a look at the source code… The winners are:

  • MySQL (Max score! The only one to support bit_count())
  • Postgres

2. Attending their duty

Nothing wrong with these databases. They support the important operations: and (&), or (|), xor (^), not (~). The left / right shift isn’t so important anyway…

  • DB2 (Even has support for BITANDNOT! Operations are called BITAND, BITOR, BITXOR, BITNOT)
  • SQLite (Misses out on the xor (^) operator, though)
  • SQL Server
  • Sybase Adaptive Server
  • Sybase SQL Anywhere

3. Wait a minute

Where did the not (~) operation go on these ones? Hmm… But we still have and (&), or (|), xor (^)

  • H2 (Operations are called BITAND, BITOR, BITXOR)
  • HSQLDB (Operations are called BITAND, BITOR, BITXOR)

4. A bit disappointing, for once

Usually the winner in these comparisons, here’s now a loser. It only supports the and (&) operation:

  • Oracle (Operation is called BITAND)

5. Out of the game

And finally, the usual suspects that lack bitwise functionality entirely

  • Ingres (Has hard-to-use BIT_AND and BIT_OR support. Input and output types have to be converted several times, so it doesn’t count)
  • Derby (No bitwise operations whatsoever)

Simulation in jOOQ

As usual, jOOQ hides such incompatibility facts from the developers where possible. The API is simple. Call upon any Field<?>:

    Field<Integer> bitCount();
    Field<T> bitNot();

    // The below methods are overloaded to also support field parameters
    Field<T> bitAnd(Number value);
    Field<T> bitNand(Number value);
    Field<T> bitOr(Number value);
    Field<T> bitNor(Number value);
    Field<T> bitXor(Number value);
    Field<T> bitXNor(Number value);
    Field<T> shl(Number value);
    Field<T> shr(Number value);

Here’s how these methods are simulated:

bit_count()

So MySQL’s bit_count() function can be simulated in most databases using this algorithm. In this case, for TINYINT data types. It’ll get quite mad for BIGINT’s:

SELECT (my_field &  1 )      +
       (my_field &  2 ) >> 1 +
       (my_field &  4 ) >> 2 +
       (my_field &  8 ) >> 3 +
       (my_field &  16) >> 4 +
        ...
       (my_field & 128) >> 7
FROM my_table

There’s also quite a freaky method in Java’s Integer.bitCount(int) and Long.bitCount(long) methods. It’s too freaky for me to understand, so I didn’t check if that’s the same as what MySQL does:

public static int bitCount(int i) {
    // HD, Figure 5-2
    i = i - ((i >>> 1) & 0x55555555);
    i = (i & 0x33333333) + ((i >>> 2) & 0x33333333);
    i = (i + (i >>> 4)) & 0x0f0f0f0f;
    i = i + (i >>> 8);
    i = i + (i >>> 16);
    return i & 0x3f;
}

Should you have a better idea, here’s where you could place it and score some reputation:

http://stackoverflow.com/questions/7946349/how-to-simulate-the-mysql-bit-count-function-in-sybase-sql-anywhere

Left / Right Shifts

Simulated in DB2, H2, HSQLDB, Ingres, Oracle, SQL Server, Sybase ASE, Sybase SQL Anywhere. This can obviously be done by multiplication / division (running the risk of overflows). Here’s how

-- a << b
a * power(2, b)

-- a >> b
a / power(2, b)

If unavailable (Derby, SQLite), the power function is already simulated by jOOQ using the following formula:

-- power(a, b)
exp(ln(a) * b)

Not / Bitwise inverse

Simulated in H2, HSQLDB, Ingres, Oracle. This can be done arithmetically, by calculating

-- ~a, or bitnot(a)
-a - 1

All the rest

Simulated in Oracle. I’ve found something for that case, too:

-- a | b or bitor(a, b)
a - (a & b) + b

-- a ^ b or bitxor(a, b)
(a | b) - (a & b)

-- or also
~(a & b) & (a | b)

Conclusion

Once more, a slim layer of SQL abstraction proves to be very powerful to keep you from doing highly repetitive tasks. So download jOOQ right now, if you haven’t already! 🙂

http://www.jooq.org

Derby casting madness – the sequel

I have recently blogged about the general bind variable casting madness in SQL:

https://lukaseder.wordpress.com/2011/08/31/rdbms-bind-variable-casting-madness/

So this is the sequel of the above story, purely dedicated to Derby with its “conversion table from hell“. One of jOOQ’s goals is to make SQL as compatible as possible across various databases, in a way that you can re-use the same SQL on various environments. For instance:

  • Use Derby to develop your database
  • Use DB2 for production

While I personally discourage such set-ups, I know that many developers prefer that, especially when it comes to run fast-running integration tests. And the above coupling of Derby and DB2 is an especially good one, as Derby is quite similar to DB2. See also this Stack Overflow Question:

http://stackoverflow.com/questions/4419684/portable-schema-between-derby-and-db2

But back to casting. In order to make casting as compatible as possible, jOOQ generates casting SQL according to the following rules:

Casting NUMERIC to VARCHAR

Interestingly, this is not supported, but casting to CHAR is. So jOOQ generates:

-- When 123 is inlined:
trim(cast(cast(123 as char(38)) as varchar(32672)))

-- When 123 is bound as a variable
trim(cast(cast(cast(? as int) as char(38)) as varchar(32672)))

Casting CHAR/VARCHAR to DOUBLE/FLOAT/REAL

Again, this isn’t supported for some reason. So jOOQ generates:

-- When 123.0 is inlined:
cast(cast('123.0' as decimal) as float)

-- When 123.0 is bound as a variable
cast(cast(cast(? as varchar(32672)) as decimal) as float)

Casting NUMERIC to BOOLEAN

This can’t be expressed simply with a CAST clause. A CASE .. WHEN clause is rendered instead, by jOOQ (note that Derby doesn’t support the simple CASE clause either…):

case when cast(? as int) = 0 then false
     when cast(? as int) is null then null
     else true
end

Casting CHAR/VARCHAR to BOOLEAN

The Derby documentation claims that this should work, but I’ve experienced quite some trouble. Derby seems to accept only SQL standard boolean literals and rejects values such as ‘0’, ‘1’, etc… Most databases accept ‘0’, ‘1’ as boolean string values as well. So jOOQ simulates the following

case when       cast(? as varchar(32672))  = '0' then false
     when lower(cast(? as varchar(32672))) = 'false' then false
     when lower(cast(? as varchar(32672))) = 'f' then false
     when cast(? as varchar(32672)) is null then null
     else true
end

Casting other pairs

Luckily, all other common types of casts seem to work as expected, also in the Derby database.

Java’s Arrays.asList(…) is underused

Writing nice and concise code is feasible in Java as well, not only in those hyped, new, and fancy scripting languages. Here are some examples on how to use the Java 5 varargs Arrays.asList() method in nice contexts:

Run a block for n constant values

// If you have VAL_A, VAL_B, VAL_C and you want
// to execute something for every one of those values:
for (String value : Arrays.asList(VAL_A, VAL_B, VAL_C)) {
  doSomething(value);
}

// Here's how you can create a SQL-like IN operator
// to check for existence in a "set"
if (Arrays.asList(VAL_A, VAL_B, VAL_C).contains(value)) {
  doSomething();
}

// Of course, this would even be nicer to have, as
// syntactic sugar
if (value in [VAL_A, VAL_B, VAL_C]) {
  doSomething();
}

The latest example is taken from one of my Stack Overflow questions:

http://stackoverflow.com/questions/7390571/java-in-operator

And indeed, something like this is fathomable. There had been an old specification request by Josh Bloch, to support collection literals in Java:

https://docs.google.com/Doc?id=ddv8ts74_4cbnn5mhj&pli=1

Too bad it never made it into the JLS…

jOOQ in the wild

The first open source projects start to appear on the web with public dependencies on jOOQ. One of them is a small backup tool called blizzys-backup by Maik Schreiber. It uses jOOQ to handle a small H2 database with 3-4 relations describing scheduled backups and backup-file meta data. Interestingly, Maik seems to prefer lazy fetching his data using an org.jooq.Cursor, keeping the underlying java.sql.ResultSet open during iterations. An example code snippet from the BackupRun class can be seen here:

// below code (c) by Maik Schreiber (slightly modified)
Cursor<Record> cursor = null;
try {
  cursor = database.factory()
    .select(Backups.ID)
    .from(Backups.BACKUPS)
    .where(Backups.ID.notEqual(Integer.valueOf(backupId)))
    .orderBy(Backups.RUN_TIME.desc())
    .fetchLazy();

  while (cursor.hasNext()) {
    int backupId = cursor.fetchOne().getValue(Backups.ID).intValue();

    // [...]
  }

  // Note: The above loop could be re-written to this
  // as Cursor<R extends Record> extends Iterable<R>:
  for (Record record : cursor) {
    int backupId = record.getValue(...);
  }
} finally {
  database.closeQuietly(cursor);
}

Another example:

// below code (c) by Maik Schreiber (slightly modified)
Cursor<Record> cursor = null;
try {
  cursor = database.factory()
    .select(Files.ID,
            Files.BACKUP_PATH)
    .from(Files.FILES)
    .leftOuterJoin(Entries.ENTRIES)
      .on(Entries.FILE_ID.equal(Files.ID))
    .where(Entries.FILE_ID.isNull())
    .fetchLazy();

  while (cursor.hasNext()) {
    Record record = cursor.fetchOne();
    FileEntry file = new FileEntry(
      record.getValue(Files.ID).intValue(),
      record.getValue(Files.BACKUP_PATH));

    // [...]
  }
} finally {
  database.closeQuietly(cursor);
}

blizzys-backup auto-installs its schema at the first run. This can be seen in the Database class. It shows that jOOQ’s planned support for DDL statements is going to add even more value to jOOQ, as this is a real need for many open-source tools. Some sample statement (today, an example of going back to string concatenation…):

factory.query("CREATE TABLE IF NOT EXISTS backups (" + 
                "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
                "run_time DATETIME NOT NULL, " +
                "num_entries INT NULL" +
              ")").execute();

See more of blizzys-backup here:

https://github.com/blizzy78/blizzys-backup/

JDBC batch operations with jOOQ

So this was requested again, by another jOOQ user – support for an ancient JDBC feature – java.sql.Statement.addBatch(); With JDBC, you can easily execute several statements at once using the addBatch() method. Essentially, there are two modes in JDBC

  1. Execute several queries without bind values
  2. Execute one query several times with bind values

With code, this looks like the following snippet:

// 1. several queries
// ------------------
Statement stmt = connection.createStatement();
stmt.addBatch("INSERT INTO author VALUES (1, 'Erich Gamma')");
stmt.addBatch("INSERT INTO author VALUES (2, 'Richard Helm')");
stmt.addBatch("INSERT INTO author VALUES (3, 'Ralph Johnson')");
stmt.addBatch("INSERT INTO author VALUES (4, 'John Vlissides')");
int[] result = stmt.executeBatch();

// 2. a single query
// -----------------
PreparedStatement stmt = connection.prepareStatement(
  "INSERT INTO autho VALUES (?, ?)");
stmt.setInt(1, 1);
stmt.setString(2, "Erich Gamma");
stmt.addBatch();

stmt.setInt(1, 2);
stmt.setString(2, "Richard Helm");
stmt.addBatch();

stmt.setInt(1, 3);
stmt.setString(2, "Ralph Johnson");
stmt.addBatch();

stmt.setInt(1, 4);
stmt.setString(2, "John Vlissides");
stmt.addBatch();

int[] result = stmt.executeBatch();

This will also be supported by jOOQ

The upcoming version 1.6.9 of jOOQ will support executing queries in batch mode as follows:

// 1. several queries
// ------------------
create.batch(
    create.insertInto(AUTHOR, ID, NAME).values(1, "Erich Gamma"),
    create.insertInto(AUTHOR, ID, NAME).values(2, "Richard Helm"),
    create.insertInto(AUTHOR, ID, NAME).values(3, "Ralph Johnson"),
    create.insertInto(AUTHOR, ID, NAME).values(4, "John Vlissides"))
.execute();

// 2. a single query
// -----------------
create.batch(create.insertInto(AUTHOR, ID, NAME).values("?", "?"))
      .bind(1, "Erich Gamma")
      .bind(2, "Richard Helm")
      .bind(3, "Ralph Johnson")
      .bind(4, "John Vlissides")
      .execute();

Annotatiomania™, next level. JPA and JAXB combined

This is a very nice specimen of annotatiomania in the wild. When people not only confuse OO with relational, but also with XML. The “OOXMLational” model class, so to speak. Check this out:

@Entity
@Table(name = "Person", catalog = "TestDB", schema = "dbo")
@XmlRootElement
@NamedQueries({
    @NamedQuery(
        name = "Person.findAll", 
        query = "SELECT p FROM Person p"),
    @NamedQuery(
        name = "Person.findByPersonId", 
        query = "SELECT p FROM Person p WHERE p.personId = :pId"),
    @NamedQuery(
        name = "Person.findByPersonName", 
        query = "SELECT p FROM Person p WHERE p.personName = :pName"),
    @NamedQuery(
        name = "Person.findByPersonFamily", 
        query = "SELECT p FROM Person p WHERE p.personFamily = :pFamily"),
    @NamedQuery(
        name = "Person.findByPersonReference", 
        query = "SELECT p FROM Person p WHERE p.personReference = :pRef")})
public class Person implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "person_id", nullable = false)
    private Integer personId;

    @Size(max = 50)
    @Column(name = "person_name", length = 50)
    private String personName;

    @Size(max = 50)
    @Column(name = "person_family", length = 50)
    private String personFamily;

    @Column(name = "person_reference")
    private Integer personReference;

    @OneToOne(cascade = CascadeType.ALL, mappedBy = "person1", 
              fetch = FetchType.LAZY)
    private Person person;

    @JoinColumn(name = "person_id", referencedColumnName = "person_id", 
                nullable = false, insertable = false, updatable = false)
    @OneToOne(optional = false, fetch = FetchType.LAZY)
    private Person person1;

After years of fun with “Where’s Waldo”, we have a new game: “Where’s the Java Code”? Seen in the wild here:

https://groups.google.com/forum/#!topic/querydsl/4lgLx3QQqBA.