• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

named sql-query dilema

 
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello ranchers,

I am in a rather big dilemma and I really need your help: in the Hibernate Reference book, there are two examples for named sql-query using join between tables:

1. <sql-query name="personsWith">
<return alias="person" class="eg.Person"/>
<return-join alias="address" property="person.mailingAddress"/>
SELECT person.NAME AS {person.name},
person.AGE AS {person.age},
person.SEX AS {person.sex},
adddress.STREET AS {address.street},
adddress.CITY AS {address.city},
adddress.STATE AS {address.state},
adddress.ZIP AS {address.zip}
FROM PERSON person
JOIN ADDRESS adddress
ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'
WHERE person.NAME LIKE :namePattern
</sql-query>

2. <sql-query name="person">
<return alias="pers" class="Person"/>
<return-join alias="emp" property="pers.employments"/>
SELECT NAME AS {pers.*}, {emp.*}
FROM PERSON pers
LEFT OUTER JOIN EMPLOYMENT emp
ON pers.ID = emp.PERSON_ID
WHERE ID=?
</sql-query>

Now, I have tested these two examples on my own DB: tables Season and Game (also 1:n relation, because in a season there are more games played)

The first example is not working when trying to chose only some columns from the table Game (in the example the table ADDRESS).

<sql-query name="myQuery">
<return alias="season" class="SeasonDAO"/>
<return-join alias="game" property="season.games"/>
SELECT season.IDSEASON AS {season.idSeason},
season.YEARS AS {season.years},
game.RESULT AS {game.result},
game.TEAM1 AS {game.team1},
game.TEAM2 AS {game.team2}
FROM Season season JOIN Game game ON season.IDSEASON = game.IDSEASON
<sql-query>
In this case I get "org.hibernate.QueryException: No column name found for property [result] for alias [game] ".

But the second example is working also for me (displaying all column for the second table) :

<sql-query name="myQuery">
<return alias="season" class="SeasonDAO"/>
<return-join alias="game" property="season.games"/>
SELECT season.IDSEASON AS {season.idSeason},
season.YEARS AS {season.years},
{game.*}
FROM Season season JOIN Game game ON season.IDSEASON = game.IDSEASON
</sql-query>


So, my first question is: why do I have to use the column names with capital letters (IDSEASON, YEARS, etc), even though in the database the column name are not like that (IdSeason, Years, etc)?

Second question is: why it doesn't work for me when I choose what column to take from the Game table ? Am I missing something ?

Thank you
 
reply
    Bookmark Topic Watch Topic
  • New Topic