Hibernate: Mapping One-way Redundant Columns

Question

In my schema there are a few redundant columns used to speed up report queries. For example, the purchase total of a sale is saved in the "purchase_total" column of the "sale" table, yet in the model, it is calculated dynamically by adding up the sold price of each item purchase in the sale. This value can be retrieved by calling the "getPurchaseTotal" accessor on a Sale object.

Is there a way to map this value to the purchase_total column in the sale table when inserting/updating but NOT have it included in the hydration of a Sale object when loading from the database? I guess it's similar to a derived or calculated column but in reverse.

Is it just best to handle this via triggers in the database itself?

Answer 1

Have you tried doing the following in your model?

public class Sales {

  /**
   * this set method ignores the value loaded from the database.
   */
  public void setPurchaseTotal(double val) {
    // ignore a value set explicitly by hibernate
  }

  /**
   * Compute the purchase total
   */
  public double getPurchaseTotal() {
     // sum item totals
     double sum = 10.0;

     return sum;
  }

}

This will ignore data that is loaded, but will persist the value when saved to the database.

HTH

Answer 2

Hibernate allows you to create types and override the fetching/saving metods of the types.

Create a type, e.g., InsertableOnly, which on the fetch sets throws away the database value.

Assuming your value is an Integer because you represent prices in cents to avoid rounding issues:

class InsertOnlyInteger extends org.hibernate.type.IntegerType {
  public Object get(ResultSet rs, String name) throws SQLException {
     return null;
  }
}

Then make the Hibernate type of the attribute InsertOnlyInteger (with xml or annotation, as it suits you).

This is a very similar answer to the answer given by sewardrobert, but it a) allows you to have a "normal" setter in the class, which b) collaborators /other/ than Hibernate (like your Java-code summing routine) can then use normally, c) it is re-usable, and d) with a decent name (like InsertableOnly) it's more-or-less self-documenting.

Answer 3

I would probably do it in the database, as those columns are not relevant for your main application.

You could either use triggers, or get rid of the redundant columns completely and use a materialized view (Oracle & PostgreSQL)/indexed view (SQL Server)/automatic summary table (DB2) for querying your report data.