Hibernate：本机SQL查询重复列结果

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

在Hibernate环境中执行以下本机SQL查询时遇到一些奇怪的问题:

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.

如果查看返回的属性,您会注意到三个不同的表中有三个描述字段。我遇到的问题是列a.description的值在数组位置重复,应该对应于s.description和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]

[1,“A部分”,1,“问题A”,1,1,“答案A”,2,2,2]

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

Hibernate将为该行返回不正确的Object [],例如:

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

[1,“答案A”,1,“答案A”,1,1,“答案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.

起初我认为我需要使用别名,因为所有这些列都具有相同的名称,但正如您从上面的代码中看到的,a.description没有别名。这样做的原因是,如果我向所有三列添加别名,我会得到一个sqlexception,它读取未找到列“描述”。我完全被难倒了,就好像代码嘲笑我一样。

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.

甚至更奇怪,如果我从控制台获取生成的SQL查询并在MySQL上运行它可以正常工作。我怀疑我可能会有某种错字,但我一直在看这段代码,以至于我再也看不到任何东西了。

1 个解决方案

#1

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

您应该对所有查询投影使用addScalar方法。例如,

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