mysql bigint java_使用Java和JDBC将无符号的64位数插入BigInt MySQL列

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?

解决方案

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.