Community
Participate
Working Groups
>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;
>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.
>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]
.
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
