NativeQuery alias on column

Description

n a pure SQL I use alias on column(query is valid and server return true result) but Hibernate throw exception.
Also the problem is that the Hibernate retrieve wrong values for columns with the same name.

-------------------------------------------------- Example 1:

SQL = SELECT
organizacije.id,
koda.vrednost AS k,
tip.vrednost AS t
FROM
organizacije
LEFT JOIN
sif_kodorg AS koda
ON koda.id=organizacije.koda
LEFT JOIN
sif_tiporg AS tip
ON tip.id=organizacije.tip_org
WHERE
organizacije.id=64

EntityManager.createNativeQuery(SQL) // error

error: org.hibernate.exception.SQLGrammarException: could not execute query

------------------------------------------
Example 2:
SQL = SELECT
organizacije.id,
koda.vrednost,
tip.vrednost
FROM
organizacije
LEFT JOIN
sif_kodorg AS koda
ON koda.id=organizacije.koda
LEFT JOIN
sif_tiporg AS tip
ON tip.id=organizacije.tip_org
WHERE
organizacije.id=64
When retrieving result in a array I get the same value for both columns koda.vrednost, tip.vrednost
-----------------------------------
Why is here involved Hibernate Grammar if it's just a pure SQL, so is there any solutions how to retrieve values of columns with same name?

Regards, Tomaž

Environment

OS: Win XP
Java: 1.6
Hibernate 3.2.5.ga
MySQL: 5.1

Activity

Show:
Sanne Grinovero
February 3, 2010, 2:28 AM

Hi Tomaž,
thanks for reporting but this is the wrong issue tracker, this is about Search (fulltext search) only.
Could you please report it to http://opensource.atlassian.com/projects/hibernate/browse/HHH ?

Tomaž
February 3, 2010, 2:34 AM

1) With out @SqlResultSetMapping this issue could be solved only with pure jdbc connection(Connection conn = ((HibernateEntityManager) emAris).getSession().connection() but this method is deprecated.

2) To realy solved this is to use SqlResultSetMapping and this annotation can be applied to any entity class

@Entity
@Table(name = "organizacije")
@SqlResultSetMapping(name="orgScalar", columns={@ColumnResult(name="id"), @ColumnResult(name="k"), @ColumnResult(name="t")})
public class OrganizacijeEntity

....

and then in code:

String sql = "SELECT organizacije.id, koda.vrednost AS k, tip.vrednost AS t "
+ "FROM organizacije "
+ "LEFT JOIN sif_kodorg AS koda ON koda.id=organizacije.koda "
+ "LEFT JOIN sif_tiporg AS tip ON tip.id=organizacije.tip_org "
+ "WHERE organizacije.id="+orgId;
Query arisQuery = emAris.createNativeQuery(sql, "orgScalar");

Regards, Tomaz

Brett Meyer
April 8, 2014, 3:45 AM

In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.

If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.

Thank you!

Brett Meyer
July 9, 2014, 1:11 AM

Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!

Rejected

Assignee

Unassigned

Reporter

Tomaž

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Blocker