Share jOOQ on Facebook
Share jOOQ on Twitter
 

We're hacking JDBC...

... so you don't have to

Code that you don't want to write...

How to fetch generated keys in some DBs


case DERBY:
case H2:
case MARIADB:
case MYSQL: {
    try {
        listener.executeStart(ctx);
        result = ctx.statement().executeUpdate();
        ctx.rows(result);
        listener.executeEnd(ctx);
    }

    // Yes. Not all warnings may have been consumed yet
    finally {
        consumeWarnings(ctx, listener);
    }

    // Yep. Should be as simple as this. But it isn't.
    rs = ctx.statement().getGeneratedKeys();

    try {
        List<Object> list = new ArrayList<Object>();

        // Some JDBC drivers seem to illegally return null
        // from getGeneratedKeys() sometimes
        if (rs != null) {
            while (rs.next()) {
                list.add(rs.getObject(1));
            }
        }

        // Because most JDBC drivers cannot fetch all
        // columns, only identity columns
        selectReturning(ctx.configuration(), list.toArray());
        return result;
    }
    finally {
        JDBCUtils.safeClose(rs);
    }
}

How to fetch generated keys in other DBs



// [#2744] DB2 knows the SELECT .. FROM FINAL TABLE (INSERT ..) syntax
case DB2:

// Firebird and Postgres can execute the INSERT .. RETURNING
// clause like a select clause. JDBC support is not implemented
// in the Postgres JDBC driver
case FIREBIRD:
case POSTGRES: {
    try {
        listener.executeStart(ctx);
        rs = ctx.statement().executeQuery();
        listener.executeEnd(ctx);
    }
    finally {
        consumeWarnings(ctx, listener);
    }

    break;
}

How fetch all exceptions in SQL Server


switch (configuration.dialect().family()) {
    case SQLSERVER:
        consumeLoop: for (;;)
            try {
                if (!stmt.getMoreResults() &&
                     stmt.getUpdateCount() == -1)
                    break consumeLoop;
            }
            catch (SQLException e) {
                previous.setNextException(e);
                previous = e;
            }
}

Inlining boolean literals


// [#1153] Some dialects don't support boolean literals
// TRUE and FALSE
if (asList(ASE, DB2, FIREBIRD, ORACLE,
           SQLSERVER, SQLITE, SYBASE).contains(family)) {
    context.sql(((Boolean) val) ? "1" : "0");
}
else {
    context.keyword(((Boolean) val).toString());
}

How to deserialise lobs from UDTs


// [#1327] Oracle cannot deserialise BLOBs as byte[] from SQLInput
if (dataType.isLob()) {
    Blob blob = null;
    try {
        blob = stream.readBlob();
        return (T) (blob == null ? null :
                    blob.getBytes(1, (int) blob.length()));
    }
    finally {
        safeFree(blob);
    }
}
else {
    return (T) stream.readBytes();
}

How to serialise lobs to UDTs


// [#1327] Oracle cannot serialise BLOBs as byte[] to SQLOutput
// Use reflection to avoid dependency on OJDBC
if (dataType.isLob()) {
    Blob blob = null;

    try {
        blob = on("oracle.sql.BLOB").call("createTemporary",
                   on(stream).call("getSTRUCT")
                             .call("getJavaSqlConnection").get(),
                   false,
                   on("oracle.sql.BLOB").get("DURATION_SESSION")
               ).get();

        blob.setBytes(1, (byte[]) value);
        stream.writeBlob(blob);
    }
    finally {
        DefaultExecuteContext.register(blob);
    }
}
else {
    stream.writeBytes((byte[]) value);
}

How to handle BigInteger and BigDecimal


else if (type == BigInteger.class) {
    // The SQLite JDBC driver doesn't support BigDecimals
    if (ctx.configuration().dialect() == SQLDialect.SQLITE) {
        return Convert.convert(rs.getString(index),
                               (Class) BigInteger.class);
    }
    else {
        BigDecimal result = rs.getBigDecimal(index);
        return (T) (result == null ? null :
                    result.toBigInteger());
    }
}
else if (type == BigDecimal.class) {
    // The SQLite JDBC driver doesn't support BigDecimals
    if (ctx.configuration().dialect() == SQLDialect.SQLITE) {
        return Convert.convert(rs.getString(index),
                               (Class) BigDecimal.class);
    }
    else {
        return (T) rs.getBigDecimal(index);
    }
}

Inlining date literals


// The SQLite JDBC driver does not implement the escape syntax
// [#1253] SQL Server and Sybase do not implement date literals
if (asList(ASE, SQLITE, SQLSERVER, SYBASE).contains(family)) {
    context.sql("'").sql(escape(val)).sql("'");
}

else if (asList(ACCESS).contains(family)) {
    context.sql("#")
           .sql(new SimpleDateFormat("yyyy/MM/dd")
                    .format((Date) val))
           .sql("#");
}

// [#1253] Derby doesn't support the standard literal
else if (family == DERBY) {
    context.keyword("date('").sql(escape(val)).sql("')");
}

// Most dialects implement SQL standard date literals
else {
    context.keyword("date '").sql(escape(val)).sql("'");
}

We could go on for hours...

... or you just go ahead and


Image copyright information

The above images were used with permission from various sources:

The jOOQ Logo