How to map dynamic SQL column to Hibernate Entity object?

Question

I am using an Entity class which has 4 columns, and so my DB also has 4 columns only. But on writing my HQL, I am using custom SQL query which generates 5 columns. The distance field is generated dynamically.

@Entity
public class Entity {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int id;
    private String name;
    private String latitude;
    private String longitude;
    @Transient
    private double distance;
}

Now on executing for example a query, as follows -

String q= "Select id, name, latitude, longitude, (latitude - 1) as distance from Entity";
        SQLQuery query = s.createSQLQuery(q);
        query.addEntity(Entity.class);
        List<Entity> results = query.list();

all other columns are mapped to Entity object but my distance field is not getting mapped. I annotated distance as Transient because I don't want the column in my DB, but because of field being Transient, its also ignored in HQL entity object. Please help me with this. How this can be done?

Answer 1

There is two possible solutions. In your case it would be better to declare calculated field with @Formula annotation. Using this annotation you can specify SQL fragment instead of mapping property into column:

@Entity
public class Entity {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int id;
    private String name;
    private String latitude;
    private String longitude;

    @Formula(latitude - 1)
    private double distance;
}

Now you can fetch entity with ordinal normal HQL request, and field will be calculated. But note, this solution will be working only when you use HQL, but not in JPA, because JPA does not support @Formula.

In JPA it would be better to use @PostLoad annotation:

@Entity
public class Entity {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int id;
    private String name;
    private String latitude;
    private String longitude;

    @Transient
    private double distance;

    @PostLoad 
    public void postLoad(){
        distance = latitude - 1;
    }
}

And after that you can fetch this entity with ordinary JPQL.