ansaurus

Question

Answer 1

+1 A:

Why do you want to use HQL?

The idea of Hibernate is to work with entities and not with tables. Ideally, you would load the entities and iterate through them getting the fields you want.

I would suggest you to use Hibernate Criteria and Criterion to that query.

Criteria crit = session.createCriteria(ProjectAssignment.class);
List projectAssignments = crit.list();

And then iterate through the list getting the fields you want.

pakore 2010-06-11 11:04:40

In my experience the Criteria (and DetachedCriteria) don't yet have the full functionality you can get in writing your own HQL statements. Also, your criteria here doesn't address the distinct constraint. Finally, HQL still deals with the entities and not the tables since in HQL you query against the entities and their properties, not against tables and columns.

digitaljoel 2010-06-11 16:04:20

Sarang 2010-06-16 04:23:25

Answer 2

+1 A:

First of all, if you use projections like in:

select distinct aid, pid, userName from ProjectAssignment

You will get an Object[] as result (or a List<Object[]>), not a List<ProjectAssignment>. If you want to get strong typed objects as result when using projections, you should use the select NEW expression (assuming you provide the proper constructor):

select new com.hibernate.ProjectAssignment(...) from ProjectAssignment.

Secondly, I don't get what you actually want. From the plain english description, the query would be:

select distinct userName from ProjectAssignment

But I'm not sure this is what you expect. Maybe if you clarify in plain english what the expected result is I could help further.

Thirdly, the current problem has NOTHING to do with Hibernate nor casting:

java.lang.NumberFormatException: For input string: "userName"

You are just trying to format a string that is not a number as a number.

Pascal Thivent 2010-06-11 15:25:57

Answer 3

+1 A:

Query query = session.createQuery("select distinct aid, pid, userName from ProjectAssignment");

Actually, query.list() will return an instance of ArrayList containing Array of Objects as each element. And each element on array contains value for each column.

e.g. query.list().get(0) will return an array of three objects

array[0] = value of aid
array[1] = value of pid
array[2] = value of userName

so, List<Object[]> can not be cast to List<ProjectAssignment>

One possible solution is implement ResultTransformer

something like this.........

First tell Hibernate what transformer to use

query.setResultTransformer(new ProjectAssignmentTransformer());

Now implementation

class ProjectAssignmentTransformer implements ResultTransformer {

@Override
public Object transformTuple(Object[] tuples, String[] aliases) {
            Object aid = tuples[0];
            Object pid = tuples[1];
            Object userName = tuples[2];
             /* construct ProjectAssignment instance using appropriate args*/
    return new ProjectAssignment (aid, pid, (String) userName);
}

@Override
public List transformList(List collection) {
    List<ProjectAssignment> assignments = new ArrayList<ProjectAssignment>();
    for(Object o : collection)
    {
        assignments.add((ProjectAssignment)o);  
    }
    return assignments;
}

}

After this you will be able to cast to List<ProjectAssignment>.

Hope this helps!

-SE

becomputer06 2010-06-15 09:58:46

Thank you very much for this solution.

Sarang 2010-06-15 13:12:51

I just want to solve the casting error that I got from it.

Sarang 2010-06-15 13:13:19

ansaurus

tags:

views:

answers:

Hibernate Query Language Problem

related questions