Geek asks and answers

Insert an unsigned 64-bit number into a MySQL BigInt column using Java and JDBC

I searched for the answer to this question and found only vague answers. Here is the problem: I have an example table in MySQL: this is the timestamp data BigInt (20) unsigned

The data field is selected so that it can take a maximum value of 2 ^ 64: 18446744073709551616 (20 digits and therefore BigInt (20)).

I have a csv file that contains unsigned 64 bit integers.

Now I use Java and JDBC to insert this data and problems with it, because Java does not have an unsigned long. So I tried just passing the string, but that 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 the BigInteger class in Java, but JDBC does not have a method that accepts this type. I can convert BigInteger to a "long" that is signed, and that is not what I want. For instance:

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

Interestingly, 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 an unsigned 64-bit number into MySQL BigInt (20) columns?

+1
source share
3 answers

So here is the solution:

If you do not use the prepare statement and simply insert it using the SQL string operator, you have nothing to worry about because JDBC simply moves your statement to the MySQL server, and the MySQL server can correctly parse the string to an unsigned 64th number (BigInt- 20 )

, . :

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

MySqlDataTruncation, JDBC .

:

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

, Java, .

, :

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

MySQL .

- JDBC, 64- .

+3

, , PreparedStatement.setObject. , , : BigInteger java.sql.Types.BIGINT , .

, , SQLData, setObject.

: ; javadocs.

0

MySQL, BIGINT UNSIGNED.

:

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

getObject():

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

rs.getObject("downSpeed",BigInteger.class), .

, , BIGINT UNSIGNED, 1,8 * 10 ^ 9. . : MySQL DataTypes

0

More articles:


All Articles