Hibernate: Native SQL query repeating column results

Question

I'm experiencing some strange issues when executing the following native SQL query within a Hibernate environment:

public List<Object[]> getExcelResults(Integer idSurvey)
{
    StringBuffer queryString = new StringBuffer();

    //build query string
    queryString.append("select s.idSection, s.description as sectionDescription, ");
    queryString.append("q.idQuestion, q.description as questionDescription, q.type, ");
    queryString.append("a.idAnswer, a.description, ");
    queryString.append("sum(sa.checked), avg(sa.value), count(sl.idSurveyLog) ");
    queryString.append("from surveylog sl ");
    queryString.append("inner join surveyanswerlog sa ");
    queryString.append("on sl.idSurveyLog = sa.idSurveyLog ");
    queryString.append("inner join answer a ");
    queryString.append("on sa.idAnswer = a.idAnswer ");
    queryString.append("inner join question q ");
    queryString.append("on a.idQuestion = q.idQuestion ");
    queryString.append("inner join section s ");
    queryString.append("on q.idSection = s.idSection ");
    queryString.append("where sl.idSurvey = :idSurvey ");
    queryString.append("group by s.idSection, q.idQuestion, a.idAnswer ");
    queryString.append("order by s.idSection, q.idQuestion, a.idAnswer asc; ");

    //create query
    Query query = this.sessionFactory.getCurrentSession().createSQLQuery(queryString.toString());
    query.setInteger("idSurvey", idSurvey);

    return query.list();
}

If you look at the properties that are being returned, you'll notice that there are three description fields from three different tables. The problem that I'm having is that the value of the column a.description is being duplicated in the array positions that should correspond to s.description and q.description.

For example, if I had a result row such as:

[1, "Section A", 1, "Question A", 1, 1, "Answer A", 2, 2, 2]

Hibernate would return an incorrect Object[] for that row such as:

[1, "Answer A", 1, "Answer A", 1, 1, "Answer A", 2, 2, 2]

At first I thought that I needed to use aliases since all of those columns have the same names, but as you can see from the code above, a.description doesn't have an alias. The reason for this is that, if I add an alias to all three columns, I get a sqlexception which reads Column "description" not found. I'm completely stumped, it is as if the code were mocking me.

Even weirder, if I take the generated SQL query from the console and run on it on MySQL it works fine. I suspect that I might have some sort of typo, but I've been looking at this code for so long that I no longer see anything.

Just a note: using + to concatenate the sections of you query would be more readable, and more efficient. — JB Nizet
– JB Nizet, Commented Jan 9, 2013 at 21:40

Dave L. · Accepted Answer · 2013-01-09 21:35:50Z

2

You should use the addScalar method for all the query projections. For example,

  Query query = sessionFactory.getCurrentSession().createSQLQuery(queryString.toString())
    .addScalar("sectionDescription", StandardBasicTypes.STRING)
    .addScalar("questionDescription", StandardBasicTypes.STRING)
    .setInteger("idSurvey", idSurvey);

answered Jan 9, 2013 at 21:35

Dave L.

9,8408 gold badges48 silver badges70 bronze badges

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

4 Comments

JayPea Over a year ago

Thanks, I was testing this but for some reason I can't find a reference to StandardBasicTypes. Do you know which Jar this is on or another way to set that type?

Dave L. Over a year ago

org.hibernate.type.StandardBasicTypes. It is in the core jar file hibernate-core-4.1.9.Final.jar. Maybe you are using a very old version?

JayPea Over a year ago

I'm using version 3. I'll look for the equivalent for that version. Thanks.

JayPea Over a year ago

Ah yes, that was it. The hibernate 3 equivalent for that constant is Hibernate.STRING

Collectives™ on Stack Overflow

Hibernate: Native SQL query repeating column results

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related