Úvodní stránka > Databáze, Java > Číselné typy v Javě a SQL

Číselné typy v Javě a SQL

int, long & NUMBER

One paranoid delusional fear that programmers frequently have is running out of sequence numbers. Since most sequence strategies just keep incrementing a number it is unavoidable that you will eventually run out. However as long a large enough numeric precision is used to store the sequence id this is not an issue. For example if you stored your id in a NUMBER(5) column, this would allow 99,999 different ids, which on most systems would eventually run out. However if you store your id in a NUMBER(10) column, which is more typical, this would store 9,999,999,999 ids, or one id each second for about 300 years (longer than most databases exist). But perhaps your system will process a lot of data, and (hopefully) be around a very long time. If you store your id in a NUMBER(20) this would be 99,999,999,999,999,999,999 ids, or one id each millisecond for about 3,000,000,000 years, which is pretty safe.

But you also need to store this id in Java. If you store the id in a Java int, this would be a 32 bit number , which is 4,294,967,296 different ids, or one id each second for about 200 years. If you instead use a long, this would be a 64 bit number, which is 18,446,744,073,709,551,616 different ids, or one id each millisecond for about 600,000,000 years, which is pretty safe.

  • int (32 bit number, 4,294,967,296 different ids) ≈ NUMBER(10)
  • long (64 bit number, 18,446,744,073,709,551,616 different ids) ≈ NUMBER(20)

Q: When should I use NUMBER and when should I use VARCHAR2?

A: If you need to perform mathematical operations on a value, use NUMBER. If you are not doing math (or if the values are not used as mathematical values), store the value in a VARCHAR2.

Example: You have a data warehouse and you store an account number and a sale price. Sale price may be added or multiplied so it is NUMBER. Account number may be all numeric digits but it makes no sense to perform mathematical operations against it. Store account number as VARCHAR2.

Even if your application will not perform math against the sale price, store it as NUMBER because it makes sense to perform math against it. This is pretty much a common sense thing to me.

There is always an exception. Surrogate keys (as in sequences) – store them as a NUMBER. While you won’t be adding and subtracting them, there are benefits to storing them as a NUMBER. For one thing, you will not have to convert from numeric (result of a sequence) to string to store them.

Rubriky:Databáze, Java