Storing dollar amounts in MySQL: INT vs LONG vs BIGINT

Question

I am constructing a database where I need to store currency values. I am storing the currency values as cents ($100.00 = 10000 ¢). So, I decided against using INT to store the currency values (a signed int is limited to storing $21,474,836.48).

I saw that MySQL has two other similar types: BIGINT and LONG.

After researching and not being able to figure out the difference, I made an arbitrary decision to use BIGINT. But then when I went to write the PreparedStatement:

int id = ...;
BigInteger amount = ...;

String sql = "insert into transaction(id, amount) VALUES(?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
if(amount == null)
    pstmt.setNull(2, java.sql.Types.BIGINT);
else
    pstmt.setBigInteger(2, amount); // <---- This method does not exist

There is no PreparedStatement::setBigInt() method. The only PreparedStatement methods that exist are setInt(), setLong(), and setBigDecimal.

So, then I reversed my decision and decided to use LONG instead, but when I went to write the same code, I noticed this:

int id = ...;
BigInteger amount = ...;

String sql = "insert into transaction(id, amount) VALUES(?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
if(amount == null)
    pstmt.setNull(2, java.sql.Types.LONG); // <---- This java.sql.Type does not exist
else
    pstmt.setLong(2, amount.longValue());

So the PreparedStatement does not have a setBigInt (or setBigInteger) method, and the java.sql.Types package does not contain a LONG enum.

The questions are:

1. What is the difference between BIGINT and LONG on the MySQL level? Which is more ideal for storing currencies?
2. What would happen if I use PreparedStatement::setLong on a BIGINT column?
3. That also begs the question, what happens if I use PreparedStatement::setBigDecimal on a BIGINT column?

EDIT/UPDATE:

My apologies for the bad question. I had no idea that MySQL doesn't even have LONG datatypes. In part because the data modeler I am using to construct my database (MySQL's own: MySQL Workbench) does allow you to set LONG as a datatype. However, when you forward engineer the database, it changes the LONG to BIGINT.

(picture from MySQL Workbench datamodeler which I'm using to assist the construction of my DB).

Answer 1

1. What is the difference between BIGINT and LONG on the MySQL level?

   • java.sql.Types.BIGINT is defined as:

     The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type BIGINT.

     Under Integer Types, the MySQL manual documents that its BIGINT datatype is an 8-byte (i.e. 64-bit) integer. The Java primitive datatype long (and its associated wrapper class java.lang.Long) also represents an 8-byte integer.

     Consequently, as documented under Mapping SQL and Java Types:

     The JDBC type BIGINT represents a 64-bit signed integer value between -9223372036854775808 and 9223372036854775807.

     [ deletia — outdated ]

     The recommended Java mapping for the BIGINT type is as a Java long.

   • There is no LONG constant within java.sql.Types, nor does MySQL have a LONG datatype.

   Which is more ideal for storing currencies?

   Neither. Store the dollar amount as a discrete decimal value using the NUMERIC fixed-point datatype. If you insist on storing the cent amount in an integer type, the "correct" answer depends merely on the anticipated range of values that you wish to support.

2. What would happen if I use PreparedStatement::setLong on a BIGINT column?

   That is exactly how one should set the value of a BIGINT parameter. As documented under java.sql.PreparedStatement::setLong(int, long):

   The driver converts this to an SQL BIGINT value when it sends it to the database.

3. That also begs the question, what happens if I use PreparedStatement::setBigDecimal on a BIGINT column?

   As documented under java.sql.PreparedStatement::setBigDecimal(int, java.math.BigDecimal):

   The driver converts this to an SQL NUMERIC value when it sends it to the database.

   So, Java will send your value to MySQL as a NUMERIC (fixed-point decimal) datatype. MySQL will then perform type conversion as described under Type Conversion in Expression Evaluation: e.g. if you are simply storing the value in a BIGINT column, the value will be cast to a BIGINT for storage. Provided that the value is in range for a BIGINT, you shouldn't notice any ill-effect (except loss of the fractional part).