Convert large string to bigInteger and pass it to prepared statement

Question

I have this string say reference = '2039487894563827398440987'

I am trying to convert it to BigInteger by doing something like

def reference = new BigInteger(reference)

and then in prepared statement I am setting it as

ps.setLong(reference);

The database column(XYZ) on which I am mapping this value has dataType BigInt(40). But when I execute the statement it shows exception:

SQLSTATEEXCEPTION: Out of range value of column `XYZ` at row 1

How to deal with such situation.

Edit: Tried setBigDecimal(reference); as well.

score 1 · Answer 1 · answered Jan 14 '15 at 10:24

1

You can use setBigDecimal() instead of setLong().

answered Jan 14 '15 at 10:24

sankar

161
13

Might be worth noting that to use setBigDecimal, you'll need to convert the BigInteger to a BigDecimal i.e. ps.setBigDecimal(new BigDecimal(reference)); – J Richard Snape Jan 14 '15 at 10:27
@JRichardSnape yes I have made sure of that. – Sajjad Jan 14 '15 at 10:32

score 1 · Answer 2 · edited May 23 '17 at 10:33

Your number is too large for a MySQL BIGINT column. If you read these docs - you see that the BIGINT data type cannot take such a large number (maximums are ±9223372036854775807 signed, or 18446744073709551615 unsigned). See this answer for a more comprehensive look through documentation - across different server types.

If you can change the type in the database, you might want to consider changing it to a NUMERIC field, or storing the number as a string, or converting it to a byte array and storing it as a binary blob.

EDIT:

For completeness - in case anyone is confused about what the 40 means in BIGINT(40). It means almost nothing. It certainly doesn't affect the size of number stored - that's always 8 bytes (64 bits). It is a hint to the database about how to display the number - i.e. display the first 40 digits. It might affect zero padding - e.g. if you put values 123456789 and 5 into a BIGINT(5) column, they might well display as 123456789 and 00005. Check the docs for other types available.

Note that the longest number a BigInt field can store is 20 digits long in base 10, so 40 really does mean almost nothing at all.

Yeah I am aware of the fact with BIGINT(40). Its just I am wondering that isn't there any other way instead of changing dataType in DB — Sajjad, Jan 14 '15 at 10:59
I think not, basically - because 2039487894563827398440987 > 18446744073709551615 - it just won't fit. You need another Data type if you're going to store numbers that big. — J Richard Snape, Jan 14 '15 at 11:48

Convert large string to bigInteger and pass it to prepared statement

2 Answers2