How to use @Formula in Hibernate/JPA

Question

I am trying to see how @Formula annotation works using a simple piece of code below.

I am able to print out values of description and bidAmount columns but the fields annotated with @Formula i.e. shortDescription and averageBidAmount return null.

Can anyone please help point out what is wrong with the code here?

I am using Hibernate 5.0, postgresql-9.3-1102-jdbc41 and TestNG on a Mac OSX.

    import java.math.BigDecimal;
    import java.util.List;
    import javax.persistence.Entity;
    import javax.persistence.EntityManager;
    import javax.persistence.EntityManagerFactory;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.Persistence;
    import javax.transaction.UserTransaction;
    import org.testng.annotations.Test;
    import com.my.hibernate.env.TransactionManagerTest;

    public class DerivedPropertyDemo extends TransactionManagerTest {

      @Test
      public void storeLoadMessage() throws Exception {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("HelloWorldPU");
        try {
          {
            UserTransaction tx = TM.getUserTransaction();
            tx.begin();
            EntityManager em = emf.createEntityManager();

            DerivedProperty derivedProperty1 = new DerivedProperty();
            derivedProperty1.description = "Description is freaking good!!!";
            derivedProperty1.bidAmount = BigDecimal.valueOf(100D);

            DerivedProperty derivedProperty2 = new DerivedProperty();
            derivedProperty2.description = "Description is freaking bad!!!";
            derivedProperty2.bidAmount = BigDecimal.valueOf(200D);

            DerivedProperty derivedProperty3 = new DerivedProperty();
            derivedProperty3.description = "Description is freaking neutral!!!";
            derivedProperty3.bidAmount = BigDecimal.valueOf(300D);

            em.persist(derivedProperty1);
            em.persist(derivedProperty2);
            em.persist(derivedProperty3);

            tx.commit();

            for (DerivedProperty dp : getDerivedProperty(em)) {
              System.out.println("============================");
              System.out.println(dp.description);
              System.out.println(dp.bidAmount);
              System.out.println(dp.getShortDescription());
              System.out.println(dp.getAverageBidAmount());
              System.out.println("#############################");
            }
            em.close();
          }
        } finally {
          TM.rollback();
          emf.close();
        }
      }


     public List<DerivedProperty> getDerivedProperty(EntityManager em) {
      List<DerivedProperty> resultList = em.createQuery("from " + DerivedProperty.class.getSimpleName()).getResultList();
      return resultList;
  }
}

My Entity class is:

@Entity
class DerivedProperty {

  @Id
  @GeneratedValue
  protected Long id;

  protected String description;

  protected BigDecimal bidAmount;

  @org.hibernate.annotations.Formula("substr(description, 1, 12)")
  protected String shortDescription;

  @org.hibernate.annotations.Formula("(select avg(b.bidAmount) from DerivedProperty b where b.bidAmount = 200)")
  protected BigDecimal averageBidAmount;

  public String getShortDescription() {
    return shortDescription;
  }

  public BigDecimal getAverageBidAmount() {
    return averageBidAmount;
  }
}

EDIT

I am following the book Java Persistence with Hibernate 2nd Ed.

Thanks

Answer 1

Your DerivedProperty instances are returned from the persistence context (only their ids are used from the result set returned from the query). That's why formulas haven't been evaluated.

Persistence context is not cleared if you don't close the entity manager. Try adding em.clear() after you commit the first transaction to force clearing the persistence context.