Inserting unsigned 64-bit number into BigInt MySQL column using Java and JDBC

Question

I searched for answer to this question and only found vague answers. Here is the problem: I have a sample table in MySQL: mytable time Timestamp data BigInt(20) unsigned

Data field is chosen so that it can take in 2^64 max value: 18446744073709551616 (20 digits and hence BigInt(20)).

I have a csv file which contains unsigned 64-bit integers.

Now I am using Java and JDBC to insert this data and running into issues because Java does not have unsigned long. So I tried just passing string - but it fails.

st = conn.prepareStatement("INSERT INTO pawan_table (mytime, data) VALUES(?, ?)");
st.setTimestamp(1, new Timestamp(86400000+i*1000));
st.setString(2, "18446744073709551616");
st.execute();

I also tried BigInteger class in Java - but JDBC does not have a method which takes that type. I can only convert BigInteger to "long" which is signed and that is not what I want. For example:

BigInteger bi = new BigInteger(string_from_csv_file);
st.setLong(2, bi.longValue());

Interesting thing is that MySQL Workbench can insert this data - but it is written in c#! and I need to write my code in Java!

How do Java programmers insert unsigned 64-bit number in MySQL BigInt(20) column?

Answer 1

So here is the solution:

If you do not use prepare statement and are just inserting using a string SQL statement, you have nothing to worry about because JDBC simply moves your statement to MySQL server and MySQL server can correctly parse the string to unsigned 64 number (BigInt-20).

If you use a prepare statement, then you are in trouble. If you do:

BigInteger bi = new BigInteger("18446744073709551615"); // max unsigned 64-bit number
statement.setObject(2, bi, Types.BIGINT);
statement.execute();

you will get an MySqlDataTruncation exception because JDBC wants to truncate this to signed long.

If you do:

BigInteger bi = new BigInteger(new Long(Long.MAX_VALUE).toString());
statement.setObject(2, bi, Types.BIGINT);
statement.execute();

This will work because the value is within Java's signed long.

So workaround which always works is:

BigInteger bi = new BigInteger("18446744073709551615"); // max unsigned 64-bit number
statement.setString(2, bi.toString());
statement.execute();

You end up passing the data as a string and let MySQL server parse it properly.

Someone should fix the JDBC driver because unsigned 64-bit numbers keep coming up in a lot of statistical applications.

Answer 2

You might be able to achieve this using PreparedStatement.setObject. There is a variant that accepts an argument specifying the type of the value: you could pass in a BigInteger and java.sql.Types.BIGINT and see if that works.

If that fails, then you could create a helper class that implements SQLData and pass that to setObject.

Disclaimer: I have not tried any of this; it's based off a quick look at the javadocs.

Answer 3

This works with the MySQL backend if the datatype is BIGINT UNSIGNED.

Storing the number works this way:

PreparedStatement statement = conn.prepareStatement("INSERT INTO Speeds (downSpeed) VALUES (?)");
statement.setObject(1, spd.downSpeed, Types.BIGINT);

Retrieving works with a raw getObject() call:

BigInteger downSpeed = (BigInteger) rs.getObject("downSpeed");

I tried using rs.getObject("downSpeed",BigInteger.class) but this gave me an unsupported conversion error.

Note the the biggest number that can be stored in the BIGINT UNSIGNED type is 1.8 *10^9. See here: MySQL DataTypes