CODEWITHSUNDEEP
javajpaspring-data-jpah2
Antony Hutchison
Antony Hutchison

Reputation: 2991

How to store BigInteger in a H2/PSQL database using JPA?

I'm trying to store a SHA256 hash as a numerical value, which seems like the optimal way to do it from a space and indexing POV. I'm using JPA with a simple field definition:

@NonNull
private BigInteger mybiginteger;

However, it refuses to insert the value.

I'm using a H2 database during unit tests, and now that I've added a BigInteger value in JPA it fails

Caused by: org.h2.jdbc.JdbcSQLDataException: Value too long for column "MYBIGINTEGER DECIMAL(19, 2)": "-12804752987762098394035772686106585063470084017442529046078187006797464553387.00 (79)"; SQL statement:

Skipping the test cases and trying it with Postgres it also fails with 

Caused by: org.postgresql.util.PSQLException: ERROR: numeric field overflow
  Detail: A field with precision 19, scale 2 must round to an absolute value less than 10^17.

JPA has created this as a numeric(19,2) field according to the DB tools. I can manually change the column definition and it works, but not how to set it to be portable between H2 and PSQL.

How can I correctly store BigInteger in a H2/PSQL database using JPA? Also, why is it allowing two decimal places for a BigInteger type?

Upvotes: 1

Views: 3344

Answers (3)

NaveenT
NaveenT

Reputation: 59

I solved this issue by adding the following annotation on the property.

@Column(columnDefinition = "BIGINT")

Upvotes: 2

Benjamin Maurer
Benjamin Maurer

Reputation: 3743

I would disagree that BigInteger is the right type for a Hash. Do you want to do arithmetic with your hash? I don't think so.

Java's MessageDigest for example, will give you a byte[] array with the hash. You can map a byte array efficiently as a binary/BLOB column. Another portable way is to store it as a base64 encoded string.

@Lob
byte[] hash;

Assuming Postgresql, you can check the space requirements here: https://www.postgresql.org/docs/current/datatype.html

A 'bytea' column type would require up to 32 + 4 bytes. You can create indizes etc. on that column type.

Upvotes: 0

Antony Hutchison
Antony Hutchison

Reputation: 2991

I forced the column type with 

@Column(precision = 100, scale = 0)

Precision 100 is probably way too high, but I'll calculate the correct value later.

Upvotes: 1

Related Questions