Map null column as 0 in a legacy database (JPA)

Question

Using Play! framework and it's JPASupport class I have run into a problem with a legacy database.

I have the following class:

@Entity
@Table(name="product_catalog")
public class ProductCatalog extends JPASupport {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer product_catalog;

    @OneToOne
    @JoinColumn(name="upper_catalog")
    public ProductCatalog upper_catalog;

    public String name;
}

Some product catalogs don't have an upper catalog, and this is referenced as 0 in a legacy database. If I supply the upper_catalog as NULL, then expectedly JPA inserts a NULL value to that database column. How could I force the null values to be 0 when writing to the database and the other way around when reading from the database?

Answer 1

I don't see any easy way of achieving what you want with JPA directly (and there are great chance that even if you find a way that works with basic operation like save or load, that it will not work with more complex use case, like complex criteria / hql, none standard fetching mode, etc)

So i would do that :

@Entity
@Table(name="product_catalog")
public class ProductCatalog extends JPASupport {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer product_catalog;

    @Column(name="upper_catalog")
    public Long upper_catalog_id;

    public String name;

    public ProductCatalog getUpperCatalog() {
       if (upper_catalog_id == 0)
         return null;

       return ProductCatalog.findById(upper_catalog_id);
    }

    public void setUpperCatalog(ProductCatalog pc) {
       if (pc == null) {
         upper_catalog_id = 0;
       }
       else {
         if (pc.id == null) {
            // option 1. a bit like a cascade
            pc.save();
            // option 2. if you consider passing a transient entity is not valid
            throw new RuntimeException("transient entity " + pc.toString());
         }
         upper_catalog_id = pc.id;
       }
    }
}

Answer 2

I see two options:

• Use a primitive data type as Id (i.e. int instead of Integer)
• If you are using Hibernate as JPA provider, use a CustomType to do the conversion