Issue with UNSIGNED BIGINT of MySQL while fetching using JDBC client

Question

As per MySQL docs, the maximum value for Unsinged Bigint = 18446744073709551615

I inserted a value 9223372036854776900 (far lower than max limit) in an unsinged Bigint column.

No error is shown.

When I tried to access it programmatically via JDBC client, I got exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '9223372036854776900' in column '10' is outside valid range for the datatype BIGINT. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1026) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ResultSetImpl.throwRangeException(ResultSetImpl.java:7964) at com.mysql.jdbc.ResultSetImpl.parseLongAsDouble(ResultSetImpl.java:7248) at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2946) at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2911)

---

MySQL version : 5.5.41-0ubuntu0.14.04.1

Answer 1

As suggested by the stack trace, I was able to recreate your issue when I tried to use ResultSet#getLong

Long l = rs.getLong(1);

because the stored value, 9223372036854776900, is larger than the maximum value for a (signed) Long in Java: 9223372036854775807.

However, I was able to successfully retrieve the value as a BigDecimal using

java.math.BigDecimal bd = rs.getBigDecimal(1);

or as BigInteger using

java.math.BigInteger bi = (java.math.BigInteger) rs.getObject(1);

Answer 2

You're finding your response in the following table of website http://www.mysqlab.net/knowledge/kb/detail/topic/java/id/4929

Table A.2. Unsigned Types Mapping

    Data TypeJava Type 
    TINYINT UNSIGNED      java.lang.Integer
    SMALLINT UNSIGNED     java.lang.Integer
    MEDIUMINT UNSIGNED    java.lang.Long
    INT UNSIGNED          java.lang.Long
    BIGINT UNSIGNED       java.math.BigInteger

Note: Before MySQL Connector/J 3.1.3, BIGINT UNSIGNED was mapped to java.math.BigDecimal.

Answer 3

Max value of BIGINT is 9223372036854775807.

From the Oracle documentation (Mapping SQL and Java Types)

8.3.7 BIGINT The JDBC type BIGINT represents a 64-bit signed integer value between -9223372036854775808 and 9223372036854775807.

The corresponding SQL type BIGINT is a nonstandard extension to SQL. In practice the SQL BIGINT type is not yet currently implemented by any of the major databases, and we recommend that its use be avoided in code that is intended to be portable.

The recommended Java mapping for the BIGINT type is as a Java long.

There is a solution posted to another question that might be useful you: Inserting unsigned 64-bit number into BigInt MySQL column using Java and JDBC