Bug 337036

Comment 1 Michael OBrien 2011-02-15 11:14:16 EST

>This issue is how to persist BigInteger objects of very large > 64 or 128 bit sizes without getting a SQLDataException.
The following is the maximum number available via JPA.
private static final Long MAX_BIGINTEGER_IN_SQL = Long.MAX_VALUE;
= 0x7fffffffffffffffL
= 9,223,372,036,854,775,808 

We encounter these very large 10^19 numbers regularly in the following scenarios - we need a persistence strategy for users that wish to use them with JPA.
- scientific simulations
- cryptography
- nanosecond time calculations greater than 350 years
- factorials greater than 50 (or # of ways to order more than 50 objects)

In the above scenarios - scalar truncation must not be done by using FLOAT or DOUBLE types as the mantissa is also limited to 23 digits.


>NUMERIC data sizes vary by database but 128 bits is the current maximum.
EclipseLink creates a default 31 bit NUMERIC field using DDL generation
Hibernate creates a default 19 bit NUMERIC field using DDL generation


>EclipseLink exception due to DDL generation of a NUMERIC(31,0) field for BigInteger on Derby 10.5
FINE: INSERT INTO UNITOFWORK (ID, STARTTIMESTAMP, VERSION, MAXPATH, EXTENT, INITIAL, RETRIES, ENDTIMESTAMP, MAXVALUE, KNOWNPATH_ID, PROCESSOR_ID, KNOWNMAX_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        bind => [5, 1297192198347, 1, 1, 1267650600228229401496704253952, 1267650600228229401496703205377, 0, null, 1, 4, 1, 3]
FINE: VALUES(1)
WARNING: Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLDataException: The resulting value is outside the range for the data type BIGINT.
Error Code: -1
Call: INSERT INTO UNITOFWORK (ID, STARTTIMESTAMP, VERSION, MAXPATH, EXTENT, INITIAL, RETRIES, ENDTIMESTAMP, MAXVALUE, KNOWNPATH_ID, PROCESSOR_ID, KNOWNMAX_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        bind => [5, 1297192198347, 1, 1, 1267650600228229401496704253952, 1267650600228229401496703205377, 0, null, 1, 4, 1, 3]
Query: InsertObjectQuery(org.dataparallel.collatz.business.UnitOfWork[id=5])
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)

>Hibernate exception due to DDL generation of a NUMERIC(19,2) field size for BigInteger on Derby 10.5

NFO: Hibernate: update Parameters set bestIterationsPerSecond=?, globalDuration=?, globalStartTimestamp=?, maxPath=?, maxValue=?, nextNumberToSearch=?, partitionLength=?, version=? where id=? and version=?
INFO: Processing UOW: org.dataparallel.collatz.business.UnitOfWork[id=131082]
INFO: Hibernate: select parameters0_.id as id3_, parameters0_.bestIterationsPerSecond as bestIter2_3_, parameters0_.globalDuration as globalDu3_3_, parameters0_.globalStartTimestamp as globalSt4_3_, parameters0_.maxPath as maxPath3_, parameters0_.maxValue as maxValue3_, parameters0_.nextNumberToSearch as nextNumb7_3_, parameters0_.partitionLength as partitio8_3_, parameters0_.version as version3_ from Parameters parameters0_
INFO: New max value: 1414236446719942480
INFO: Hibernate: update Parameters set bestIterationsPerSecond=?, globalDuration=?, globalStartTimestamp=?, maxPath=?, maxValue=?, nextNumberToSearch=?, partitionLength=?, version=? where id=? and version=?
WARNING: SQL Error: -1, SQLState: 22003
SEVERE: The resulting value is outside the range for the data type DECIMAL/NUMERIC(19,2).
SEVERE: Could not synchronize database state with session
org.hibernate.exception.DataException: could not update: [org.dataparallel.collatz.business.Parameters#32768]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:77)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

>We will start with a TypeConverter workaround like
@Entity
@TypeConverters({@TypeConverter(name="BigIntegerToString",dataType=String.class,objectType=BigInteger.class)})
public class Parameters implements Serializable {
    private static final long serialVersionUID = -1979843739878183696L;
    @Column(name="maxValue", nullable=false, length=512)
    @Convert("BigIntegerToString")
    private BigInteger maxValue;

Comment 2 Michael OBrien 2011-02-15 11:19:45 EST

>This issue is independent of the JVM used whether 32 or 64 bit.
The issue is related to the size of a Long in Java which is 64 bits.

Comment 3 Michael OBrien 2011-02-15 13:58:54 EST

>partially followed
http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)#How_to_Use_the_.40TypeConverter_Annotation
http://wiki.eclipse.org/Introduction_to_Mappings_%28ELUG%29#Type_Conversion_Converter
- do not require 
<mapping-file>META-INF/orm.xml</mapping-file>
- do not require any interface implementation
- @TypeConverters
- @Column is required so we can set the column length for the eventual VARCHAR2
- I don't get any TypeConverter not found anymore

>The following works with @TypeConverter or either @TypeConverters location on the class or attribute

@Entity
@TypeConverter(name="BigIntegerToString",dataType=String.class,objectType=BigInteger.class)
public class UnitOfWork implements Serializable {
    @Column(nullable=false, length=1024)
    @Convert("BigIntegerToString")
    private BigInteger initial;
    @Column(nullable=false, length=1024)
    @Convert("BigIntegerToString")
    private BigInteger extent;

>For Derby 10.5 generates VARCHAR2 for fields up to 512 or VARCHAR for 1024+
CREATE TABLE UNITOFWORK (
		ID BIGINT NOT NULL,
		EXTENT VARCHAR(1024) NOT NULL,
		INITIAL VARCHAR(1024) NOT NULL,
		MAXPATH VARCHAR(1024) NOT NULL,
		MAXVALUE VARCHAR(1024) NOT NULL,
		VERSION BIGINT,

>We are now able to store integers around 2^124

[EL Fine]: 2011-02-15 14:21:58.311--ClientSession(13290230)--Connection(11308642)--Thread(Thread[[ACTIVE] ExecuteThread: '20' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--INSERT INTO UNITOFWORK (ID, ENDTIMESTAMP, EXTENT, INITIAL, MAXPATH, MAXVALUE, RETRIES, STARTTIMESTAMP, VERSION, KNOWNMAX_ID, KNOWNPATH_ID, PROCESSOR_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [27, null, 21267647932558653966460912964487610368, 21267647932558653966460912964486561793, 1488, 15728073752807962983290439751148709753444136, 0, 1297795918311, 1, null, null, 1]

Comment 4 Michael OBrien 2011-03-19 23:37:58 EDT

.

Comment 5 Eclipse Webmaster 2022-06-09 10:16:23 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink

Comment 6 Eclipse Webmaster 2022-06-09 10:19:59 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink