[Sébastien de Renty]

Hi all,

I’m trying to load a List of objects from a JPQL query in many tables (for stats).

Here’s my quite simple query :

     /**
     * JPQL query : getTargetRankingStatsByTopic 
     * 
     */
    @Transactional
    public List<AjaxStat> getTargetRankingStatsByTopic(String topicName) {
        String sqlQuery = "SELECT" +
            " myAnswer.audit.target.name as label," +
            " COUNT(myAnswer.id) as percentage," +
            " FROM Answer myAnswer" +
            " WHERE myAnswer.audit.target.enable = 1" +
            " AND myAnswer.audit.enable = 1" +
            " AND myAnswer.audit.status = 1" +
            " AND myAnswer.enable = 1" +
            " AND myAnswer.status = 1" +
            " AND (myAnswer.irrelevant = 0 OR myAnswer.irrelevant is null)" +
            " AND myAnswer.done = 1" +
            " AND myAnswer.topic = ?1" +
            " GROUP BY myAnswer.audit.target.name";
        
        List <AjaxStat> stats = executeQuery(sqlQuery, topicName);
        return stats;
    }

I first wrote my query in the AnswerDAO, then in a new AjaxStatDAO with the same error :

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.aaa.bbb.domain.AjaxStat

Below is my AjaxStat POJO (object where I want to store results)

package com.aaa.bbb.domain;

import java.math.BigDecimal;

/**
 * 
 * @author sderenty
 * Objet mutualisé contenant les stats pour classement centre, classement par question ou % par topic (radar)
 *
 */
public class AjaxStat {
    
    /**
     * classement centre / identifiant question / null
     */
    String id; 
    
    /**
     * Target Name / Question Description / Topic Name
     */
    String label;
    
    /**
     * percentage
     */
    BigDecimal percentage;
    
    /**
     * null / family (B4...) / null
     */
    String detail;

    public AjaxStat() {
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    public BigDecimal getPercentage() {
        return percentage;
    }

    public void setPercentage(BigDecimal percentage) {
        this.percentage = percentage;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }
    
}

Is there (probably) something else to do?

Thanks.
Sébastien.

[Sébastien de Renty]

I finally solved my problem by writing:

        Query query = createQuery(sqlQuery, -1, -1, topicName);
        
        List results = query.getResultList();
        List<AjaxStat> stats = new ArrayList<AjaxStat>();
    
        Iterator it = results.iterator();
        while (it.hasNext()) {
            Object[] result = (Object[])it.next();
            AjaxStat stat = new AjaxStat();
            stat.setLabel((String) result[0]);
            [...]
            stats.add(stat);
        }
    
        return stats;

But I don’t find it very clean. There must be a better solution…

[jkennedy]

I haven’t tried this personally, so let me know if you try it and you can’t get it to work and I will dig in with you, but this seems like an approach that may work for you.

http://stackoverflow.com/questions/2355728/jpql-create-new-object-in-select-statement-avoid-or-embrace.

Not sure if you can use calculated fields with this approach as you are above, but if that does work, seems like it would be ideal since you would get instances of your pojo back from the select so that you don’t have to do the object to field mapping.

Let me know how it goes.
Jack

[Sébastien de Renty]

Thanks Jack !

It works like a charm.

I just had to add a constructor in my AjaxStat class :

    public AjaxStat() {
    }
    public AjaxStat(String id, String label, Long done, Long total, String detail) {
        this.id = id;
        this.label = label;
        this.percentage = BigDecimal.valueOf(done/total);
        this.detail = detail;
    }

I calculate the percentage in my constructor and not in the HQL query to avoid the bug :
“Using subselects as operands for arithmetic operations causes NullPointerException” :
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2917?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel

If you calculate the percentage in the query, I think you should add two constructors (if you get a Long or a Double).

My query finally looks like that :

        String sqlQuery = "SELECT" +
        " new com.aaa.bbb.domain.AjaxStat(" +
        " myAnswer.audit.target.ref," +
        " myAnswer.audit.target.name," +
        " (" +
            " SELECT" +
            " COUNT(myAnswer3.id)" +
            " FROM Answer myAnswer3" +
            " WHERE [...]" +
        " )," +
        " (" +
            " SELECT" +
            " COUNT(myAnswer2.id)" +
            " FROM Answer myAnswer2" +
            " WHERE [...]" +
        " )," +
        " myAnswer.audit.target.detail)" +
        " FROM Answer myAnswer" +
        " WHERE [...]" +
        " GROUP BY myAnswer.audit.target.name";

Thanks again,
Sébastien.

[jkennedy]

Thanks for posting the details of your final resolution, they will be helpful for others.

Jack

[Author]

Posts