Sql中的BIGINT与Java中的BigInteger之间的映射 [英] Mapping between BIGINT in Sql with BigInteger in Java

问题描述

我们面临一些问题，当我们尝试将Java中的BigInteger与sql中的BigInt映射时，其值被更改了.我们还尝试在映射之前将其转换为longValue，但是由于无法处理其值而失败.我们尝试使用toString()并成功了，但是除了使用toString()之外，他们还有其他解决方法吗?

We are facing some issue in which when we try to map BigInteger in Java with BigInt in sql, its value got changed. We also try to convert it to longValue before mapping but it also failing as long could not able to handle its value. We tried with toString() and it got worked but is their any other workaround apart from using toString() ?

推荐答案

Java的BigInteger与SQL Server的bigint不对应-尽管名称相似，但它们几乎完全不同:

Java's BigInteger does not correspond with SQL Server's bigint - despite the similar names they are almost completely different:

• 在Java中，BigInteger是代表任意精度整数的类型.在内部用非稀疏字节向量表示，该向量可以随值的增加而自由调整大小.
• 在T-SQL中，bigint表示固定的64位整数. Java等效为long.请注意，T-SQL的bigint和Java的long均为带符号类型.它是由主机硬件本地处理的值的固有类型.

• In Java, BigInteger is a type that represents an arbitrary-precision integer. Internally is is represented by a non-sparse byte vector that is free to resize as values increase.
• In T-SQL, bigint represents fixed 64-bit integers. The Java equivalent is long. Note that both T-SQL's bigint and Java's long are signed-types. It is an intrinsic type for a value that is natively handled by the host computer's hardware.

BigInteger可以表示任何bigint值是正确的，但事实并非如此:您不能在bigint列中存储大多数BigInteger值.

It's true that BigInteger can represent any bigint value, but the reverse is not true: you cannot store most BigInteger values in a bigint column.

正如您所说的那样，您使用Java中的BigInteger作为规范值，这意味着您不能在表中使用bigint，而应使用varbinary(n)(其中n是一些合理的上限BigInteger值的大小，然后像这样使用它:

As you say you're using BigInteger in Java as your canonical value, it means you cannot use bigint in your table, you should instead use varbinary(n) (where n is some reasonable upper-bound for the size of your BigInteger values, then use it like this:

yourTableRow.hugeValue = yourBigIntegerValue.toByteArray()

您的实体类yourTableRow的hugeValue成员的类型为byte[]，它与T-SQL中的varbinary(n)兼容.

Where your entity class yourTableRow's hugeValue member is of type byte[] which is compatible with varbinary(n) in T-SQL.

我建议不要使用varbinary(MAX)，这是因为如何存储blob值[1]. (16个字节)甚至2^256(32个字节)都不太可能遇到整数值，因此您不需要超过varbinary(16)或varbinary(32).

I advise against using varbinary(MAX) owing to how blob values are stored[1]. It is highly unlikely you'll encounter integer values beyond 2^128 (16 bytes) or even 2^256 (32 bytes) so you shouldn't need more than varbinary(16) or varbinary(32).

如果您知道自己的数字不会超过2^128，最好将其存储为两个单独的bigint(64位)值:

If you know your numbers won't exceed 2^128 you'd be better off storing it as two separate bigint (64-bit) values:

COLUMN hugeValueUpper bigint NOT NULL,
COLUMN hugeValueLower bigint NOT NULL

还有一些Java提取高8个字节和低8个字节:

And some Java to extract the upper 8 bytes and lower 8 bytes:

byte[] bytes = someBigIntegerValue.toByteArray();
if( bytes.length > 16 ) throw ...

yourRow.hugeValueLower = (long)bytes[ 0] << 56) | 
                         (long)bytes[ 1] << 48) |
                         (long)bytes[ 2] << 40) |
                         (long)bytes[ 3] << 32) |
                         (long)bytes[ 4] << 24) |
                         (long)bytes[ 5] << 16) |
                         (long)bytes[ 6] <<  8) |
                         (long)bytes[ 7]      );
yourRow.hugeValueUpper = (long)bytes[ 8] << 56) |
                         (long)bytes[ 9] << 48) |
                         (long)bytes[10] << 40) |
                         (long)bytes[11] << 32) |
                         (long)bytes[12] << 24) |
                         (long)bytes[13] << 16) |
                         (long)bytes[14] <<  8) |
                         (long)bytes[15]      );

反之亦然，以获取价值.

And vice-versa to get values out.

[1]的确，如果值足够小，SQL Server可以选择将行中的varbinary(MAX)值内联存储，但是我认为任何BigInteger值太大而无法在行中存储(因此会被转移到BLOB存储)，因为这是错误情况的征兆，因此在varbinary(n)列上设置下限意味着您的程序将更快地失败.

[1] It is true that SQL Server will optionally store varbinary(MAX) values inline in a row if the value is small enough, but I would consider any BigInteger value that is too big to be stored in-row (and so would be moved to BLOB storage) as being symptomatic of an error situation, so setting a low bound on a varbinary(n) column means your program will fail-faster.

这篇关于Sql中的BIGINT与Java中的BigInteger之间的映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！