Hi,

I have a Usr table which has a field for country, it uses ISO code like 'US', 'CA', 'SG', etc. and I also have a country table, the code is same as ISO code, the two tables are independent from each other. usually I'll query the country table if I need to display the name, works fine. but this is not so efficient if big result set returned from User, it looks like a 'N + 1' problem. but I can't modify the table structures any more, is there a way to specify the country in the User table using country as linking field without changing the structure? Thanks,

Angelo



public class User {

private Long id;
private String code;
private String name;
private String country;
...
@Column(length=2)
public String getCountry() {
return country;
}

public void setCountry(String country) {
this.country = country;
}
}



public class Country {

private String code;
private String name;
...
}

Both of the entities just can be linked by primary key.
Why don't you use join in your query if you don't change the current schema?

yes, I use link table as follow:

select u, c.name as cname from User as u, Country as c where u.country=c.code

but i can't do a left join fetch to load some other tables:

select u, c.name as cname from User as u left join fetch u.credits, Country as c where u.country=c.code