Saving Double.POSITIVE_INFINITY in MySQL (EJB / JBoss object)

Question

I have the following simple JPA object:

@Entity @Table( name = myentity_table ) public class MyEntity { private double a; private double b; //(...) } 

a and b can be set to Double.POSITIVE_INFINITY . When I try to save a double-set object up to + INF to a database (MySQL) using the standard object manager, I get an exception:

java.sql.SQLException: "Infinity" is not a valid numeric or approximate numeric value

As far as I know, MySQL may not support NaN / -INF / + INF numbers. Is there a way to save this object without writing HQL queries and translating + INF to null (or max double)? Ideally, I would like to do this through the object manager, as usual.

Thanks in advance.

Answer 1

Entity @PrePersist, @PreUpdate lifecycle callback methods can be used here to check the value of the NAN / -INF / + INF field, etc., and then set the default value accordingly.

  //-- @PrePersist @PreUpdate private void resetField() { if(field == Double.POSITIVE_INFINITY) field = somePredefinedValue; } //-- 

Answer 2

MySQL does not seem to support infinity. This article says that:

Saving and getting negative infinity in a MySQL database is done by inserting an arbitrarily large negative number.

The same goes for the positive. Other resources also use 1e500 .

Instead of using infinity, I would suggest that you use Float.MAX_VALUE and Float.MIN_VALUE (or Double equivalents)

If you cannot do this in your code when setting values, do it in @PrePersist , as already suggested.

Answer 3

I was able to solve this problem by adding a varchar column to store the text representation of Float.NaN , Float.POSITIVE_INFINITY and Float.NEGATIVE_INFINITY , while the original column will store NULL . Then I use setter and getter to manage these two columns.

In my class @Entity

 /** The value I persist. See it is a Float; */ @Column(name = "VALUE") private Float value; /** the 'value complement' that does the trick. */ @Column(name = "VALUE_COMPLEMENT") // You can see I've added a new column in my table (it is a varchar) private String valueComplement; /** * value getter. * If my value is null, it could mean that it is a NaN or +/- infinity. * Then let check the complement. */ public Float getValue() { if (value == null) { try { return Float.parseFloat(this.valueComplement); } catch (NumberFormatException e) { return null; } } else { return value; } } /** * value setter * If the given value is a NaN or Inf, set this.value to null * and this.complement to the string representation of NaN or +/- Inf. */ public void setValue(Float value) { if (value != null && (value.isNaN() || value.isInfinite())) { this.valueComplement = value.toString(); this.value = null; } else { this.value = value; } } 

Result:

 | ID | LABEL | VALUE | VALUE_COMPLEMENT | | -- | --------------------- | ----- | ---------------- | | 1 | PI | 3.14 | NULL | | 2 | gravity acceleration | 9.81 | NULL | | 3 | sqare root of -1 | NULL | NaN | | 4 | log of 0 | NULL | -Infinity |