Storing Double.POSITIVE_INFINITY in MySQL (EJB entity/JBoss)

Question

I have the following simple JPA entity:

@Entity
@Table( name = myentity_table )
public class MyEntity {

  private double a;
  private double b;
  //(...)
}

a and b may be set to Double.POSITIVE_INFINITY. When I try to store entity with double set to +INF into database (MySQL) using standard entity manager I get 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 any way to store this entity without writing HQL queries and translating +INF into null (or max double) ? Ideally, I'd like to do it via entity manager as usual.

Thanks in advance.

Answer 1

Entity life-cycle callback methods @PrePersist, @PreUpdate can be used here to verify the field value NAN/-INF/+INF etc & then setting the default value accordingly.

 //--

 @PrePersist  
 @PreUpdate  
 private void resetField() {

      if(field == Double.POSITIVE_INFINITY)
            field = somePredefinedValue;
 }

 //--

Answer 2

MySQL doesn't seem to support "infinity". This article writes that:

Storing and retrieving negative infinity in a MySQL database is accomplished by inserting an arbitrarily large negative number.

Same goes for 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 can't do this in your code when setting the values, do it in a @PrePersist as already suggested.