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?

@neilstockton Haha, all of this is just an example question. This way you will hate the class name and formula too :p — Dev Utkarsh
– Dev Utkarsh, Commented Feb 20, 2016 at 18:44

Ken Bekov · Accepted Answer · 2016-02-20 06:39:00Z

2

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.

edited Feb 20, 2016 at 6:39

answered Feb 20, 2016 at 6:33

Ken Bekov

14.1k3 gold badges40 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dev Utkarsh Over a year ago

Exactly what i wanted. Thanks! Can you also tell me in case of Formula or PostLoad, how I can use external parameters to pass in query?

Ken Bekov Over a year ago

Unfortunately, you can't pass dynamic param to @Formula. From @PostLoad you can read param from Java variable, where you stored it before.

Collectives™ on Stack Overflow

How to map dynamic SQL column to Hibernate Entity object?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related