Fetching joined properties in native SQL query?

pachura · **Joined:** Thu Sep 01, 2005 4:34 am **Posts:** 1

Hello,

I have two tables - TEAMS and PLAYERS. PLAYERS have foreign key referencing TEAMS.

These tables are mapped to entities:

Code:

class Team
{
  Long    id;
  String  name;
  Integer gamesWon;
  ...
}

class Player
{
  Long   id;
  String name;
  String surname;
  ...
  Team   team;
}

When I want to fetch all the players along with their teams, I use the following HQL query:

Code:

from PLAYERS as p left outer join fetch p.team

But now, I want to do the same using native SQL, in order to inject some Oracle optimizer hints.

How to do this? The properties of "Player" are fetched OK, but "Team" are left empty.

This is my best try:

Code:

String sql = "select {p.*} from Players p, Teams t where t.id = p.team_id";
List players = session.createSQLQuery(sql, String[]{"p", "t"},
   Class[]{Player.class, Team.class}).list();

Thanks for any help!

max · **Posted:** Thu Sep 01, 2005 5:18 am

use named queries and their load-collection etc. support