Hello,

I was trying my hand on the Named Queries for SQL feature of Hibernate.

I wrote a SQL query like

select newsletter.name, article.title, author.name
from Newsletter newsletter, Article article, Author author, newsletter_to_article, Author_to_article where article.id = newsletter_to_article.article_id and newsletter.id = newsletter_to_article.newsletter_id and author.id = author_to_article.author_id and article.id = author_to_article.article_id

where you would notice that the newsletter and author table have the same column as "name".

Now for a named SQL query I have to define a return-scalar tag which identifies the return type of the result set data.

If I define the return-scalar as follows -
<sql-query>
...
<return-scalar column="newsletter.name" type="string"/>
<return-scalar column="article.title" type="string"/>
<return-scalar column="author.name" type="string"/>
...
</sql-query>
I get the following exception message -

Caused by: org.postgresql.util.PSQLException: The column name newsletter.name was not found in this ResultSet.

And if I define the return-scalar as follows -
<sql-query>
..
<return-scalar column="name" type="string"/>
<return-scalar column="title" type="string"/>
<return-scalar column="name" type="string"/>
..
</sql-query>

I get the newsletter name in place of the author name due to ambiguous column name.

Is there something which I have missed to overcome this problem.
Please Suggest. Thanks.



Hibernate version:

3.1.3

Mapping documents:

Excerpt from the mapping document:

<hibernate-mapping>
...
<sql-query name="com.incivinci.pojo.Newsletter.SQLarticles">
<return-scalar column="name" type="string"/>
<return-scalar column="title" type="string"/>
<!-- problem -->
<return-scalar column="author.name" type="string"/>
<![CDATA[
select newsletter.name, article.title, author.name
from Newsletter newsletter, Article article, Author author, newsletter_to_article, Author_to_article where article.id = newsletter_to_article.article_id and newsletter.id = newsletter_to_article.newsletter_id and author.id = author_to_article.author_id and article.id = author_to_article.article_id
and newsletter.name = ? and author.name = ?
]]>
</sql-query>
...
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.incivinci.dao.NewsletterDAO.getArticlesOfAuthorInNewsletter(NewsletterDAO.java:91)
at com.incivinci.test.Client.main(Client.java:137)
Caused by: org.postgresql.util.PSQLException: The column name newsletter.name was not found in this ResultSet.
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2362)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2184)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:139)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:183)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:595)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 8 more

Name and version of the database you are using:

PostGreSQL 8.1

The generated SQL (show_sql=true):

select
newsletter.name,
article.title,
author.name
from
Newsletter newsletter,
Article article,
Author author,
newsletter_to_article,
Author_to_article
where
article.id = newsletter_to_article.article_id
and newsletter.id = newsletter_to_article.newsletter_id
and author.id = author_to_article.author_id
and article.id = author_to_article.article_id

Debug level Hibernate log excerpt:

I think that


would work.

Look at Section 17.3.1 of the reference manual.

Thanks.