CODEWITHSUNDEEP
hibernate
MarioC
MarioC

Reputation: 3228

Hibernate from SQL to HQL - endless loop?

I'm trying to convert my Sql queries int HQL because they are easier to manage in views and controllers.

So I have 2 Entity, Luce and Illuminazione. Illuminazione has a ManyToOne annotation with Luce, in this way

@ManyToOne
    @JoinColumn(name = "id_luce")
    public Luce getLuce() {
        return this.luce;
    }

    public void setLuce(Luce luce) {
        this.luce = luce;
    }

Now, I have a list of Illuminazione elements and I need to grab the ones with the maximum date, grouped by Luce elements. In other words, I need to take only the last status for every Luce object from the Illuminazione entity.

The SQL query, which works, is

String query = "select i.id_evento, i.id_luce, i.last_date_time, l.numero_luce, l.nome_luce, ill.MaxDate, i.isLit "
    + "from illuminazione i inner join  luci l on i.id_luce= l.id_luce "
    + "inner join (SELECT `id_luce` as numeroLuce2, max(date_time) as MaxDate from illuminazione i2 group by `i2`.`id_luce`) ill "
    + "on i.id_luce = ill.`numeroLuce2` and i.`date_time` = ill.MaxDate order by i.`id_luce` asc";
    SQLQuery q = getSession().createSQLQuery(query);
    q.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

I've tried with this HQL query, but i end un in an endless loop... so I think there is something weird..

String query = "select i from Illuminazione i where i.dateTime = (select max(ii.dateTime) from Illuminazione ii where i.luce = "
                + "ii.luce)";

Is there a way to reach what i need?

EDIT Actually it doesn't go into an endless loop but after 6-7 minutes it finished, and the result is correct, but how it's possibile this long time? It's just 2000 values in the table...

Upvotes: 1

Views: 625

Answers (1)

Orden
Orden

Reputation: 611

If the SQL is working, just keep it. Hibernate is not a religion and you will not go to hell for bypassing this abstraction layer.

It will be also easier to debug using a SQL client.

The only drawback is if you have to support multiple database types.

To make things easier, you can execute 

 String query = "select {i.*} "
        + "from illuminazione i inner join  luci l on i.id_luce= l.id_luce "
        + "inner join (SELECT `id_luce` as numeroLuce2, max(date_time) as MaxDate from illuminazione i2 group by `i2`.`id_luce`) ill "
        + "on i.id_luce = ill.`numeroLuce2` and i.`date_time` = ill.MaxDate order by i.`id_luce` asc";

    SQLQuery q = getSession().createSQLQuery(query)
        .addEntity("i", Illuminazione.class);

Upvotes: 0

Related Questions