CODEWITHSUNDEEP
javasqlrdbmsbigintegerrdbms-agnostic
Jus12
Jus12

Reputation: 18024

Storing java.math.BigInteger in an RDBMS

I need to store Java's java.math.BigInteger numbers in SQL. I can safely assume that the numbers will be positive and less than 2^k for some k that will be known at table creation time. Lets say k = 256.

How do I store it in an RDBMS such that it allows me comparisons (<, >, = etc).

I am currently converting to byte array and storing them as VARBINARY(k+1).

I can also store them as VARCHAR if that helps. I need a DBMS independent way to do it.

EDIT1: following up to the comment by JBNizet below. I need to do the following types of SQL queries:

SELECT * FROM myTable WHERE bigInteger > 100
UPDATE myTable SET bigInteger = bigInteger + 10 WHERE bigInteger = 123

EDIT2: It is possible to compare VARCHAR with lexicographic ordering with some effort. However, the increment gives weird behavior given below (tested on SQLite):

CREATE TABLE T(Id INTEGER PRIMARY KEY, Name VARCHAR, test VARCHAR);
INSERT INTO T VALUES(1,'Tom', '000030000000000000000000000000');
SELECT * FROM T;

 1|Tom|000030000000000000000000000000

UPDATE T SET test = test+1;  
SELECT * FROM T;

 1|Tom|3.0e+25

Upvotes: 2

Views: 830

Answers (2)

RealSkeptic
RealSkeptic

Reputation: 34618

The appropriate type is NUMERIC. However, the limitations on that type differ between different RDBMS. MySQL, for example, limits NUMERIC to 64 digits if I'm not mistaken, MS SQL limits to mere 38 digits, and PostgreSQL limits to 1000 digits.

You need to calculate how many decimal digits you'll need for the number. For example, 2256 is 1.15E77. This means you need 78 digits, so you'll declare your field NUMERIC(78). This will fail in MySQL and MS SQL, but will work fine on PostgreSQL.

There are database systems that use different names for this type, like DECIMAL (an alternative name in MySQL) or NUMBER (Oracle). Check the documentation of your particular RDBMS.

Upvotes: 3

Rafal G.
Rafal G.

Reputation: 4432

If you have a constant precision you have to keep, then you could just peform integer arithmetic and store ints (as in mathematic integer, not java int).

Example: Required precision = 4 Input BigDecimal = new BigDecimal("12.3456")

Value stored to DB: 12.3456 * 10000 = 123456

Since it would still be a big int or smth like that, you could easily compare such numbers no matter what underlying RDBMS is.

Upvotes: -1

Related Questions