Use long or big decimal. Performance problems will most probably arise elsewhere before you factor a big decimal as the culprit

A BigDecimal is an exact way of representing numbers. A Double has a certain precision. Working with doubles of various magnitudes (say d1=1000.0 and d2=0.001) could result in the 0.001 being dropped altogether when summing as the difference in magnitude is so large. With BigDecimal this would not happen.

The disadvantage of BigDecimal is that it's slower, and it's a bit more difficult to program algorithms that way (due to + - * and / not being overloaded).

If you are dealing with money, or precision is a must, use BigDecimal. Otherwise Doubles tend to be good enough.

I do recommend reading the javadoc of BigDecimal as they do explain things better than I do here :)

BigInteger is capable of holding far bigger numbers than Long. BigInteger seems capable of holding (2 ^ 32) ^ Integer.MAX_VALUE, though that depends on the implementation (and, even if truly unbounded in the implementation, there will eventually be a physical resource limit) See explanation here.

The range of Long is [-9,223,372,036,854,775,808, +9,223,372,036,854,775,807].

Long will perform better than BigInteger so it really depends on what the size of your values will be. If they would all fall under Long's max value, it makes no sense not to use Long. If any would be bigger than the Long max value, you pretty much have to use BigInteger.

It is possible whenever the value of the BigDecimal is larger than what a long can hold.

Example:

BigDecimal num = new BigDecimal(Long.MAX_VALUE);
System.out.println(num);                  // prints: 9223372036854775807
System.out.println(num.longValue());      // prints: 9223372036854775807

num = num.add(BigDecimal.TEN);            // num is now too large for long
System.out.println(num);                  // prints: 9223372036854775817
System.out.println(num.longValue());      // prints: -9223372036854775799
System.out.println(num.longValueExact()); // throws: ArithmeticException: Overflow

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).