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:

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo