Jooq Oracle Number Precision And Java Number Mapping

Can anyone tell me or provide a ref to the mapping between oracle number precisions and java types ie at what point does a number(x) get mapped to a short, int, long BigInteger etc

Solution 1:

Java's integer types are not a perfect match for Oracle's NUMBER types. Essentially, there are two ways to map between the worlds, both imperfect:

• The status quo: strictly less than NUMBER(3) -> Byte.

  This guarantees that a SQL value can always be read to its Java type. Some Java value might not be writable to the SQL type.

• The alternative: Byte -> NUMBER(3) or less.

  This will guarantee that a Java byte value can always be written to the database. Some DB values might not be readable into the Java type, though.

jOOQ defaults to the first one, because of the following assumptions:

• jOOQ is mostly used as a "database first" API
• most of the data is read from the DB, not written to the DB

The default behaviour

In jOOQ 3.8.4, the following logic is implemented in DefaultDataType.getNumericClass():

// Integers
if (scale == 0 && precision != 0) {
    if (precision < BYTE_PRECISION) {
        return Byte.class;
    }
    if (precision < SHORT_PRECISION) {
        return Short.class;
    }
    if (precision < INTEGER_PRECISION) {
        return Integer.class;
    }
    if (precision < LONG_PRECISION) {
        return Long.class;
    }

    // Default integer number
    return BigInteger.class;
}

// Non-integers
else {
    return BigDecimal.class;
}

With:

int LONG_PRECISION    = String.valueOf(Long.MAX_VALUE).length();    // 19
int INTEGER_PRECISION = String.valueOf(Integer.MAX_VALUE).length(); // 10
int SHORT_PRECISION   = String.valueOf(Short.MAX_VALUE).length();   // 5
int BYTE_PRECISION    = String.valueOf(Byte.MAX_VALUE).length();    // 3

Overriding the default

If in some cases you use NUMBER(3) to store byte numbers up to 127 for instance, you can override this default by specifying data type rewriting during the code generation phase. This is documented here:

http://www.jooq.org/doc/latest/manual/code-generation/data-type-rewrites