Hi,
I'm trying to create correct query but unfortunately without success.
Let's say I want to show some sport events grouped by season, country and tournament type (league) (for example footbal championship):
I have some entities according to my task:
Code:
@Entity
@Table(name = "seasons")
public class Season{
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "id", unique = true, nullable = false)
private Long id;
@Column (name="year")
private Integer year;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "season", cascade = CascadeType.ALL)
private List<Country> countries = new ArrayList<Country>(0);
//getters and setters
}
Country.java
Code:
@Entity
@Table(name = "countries")
public class Country {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "id", unique = true, nullable = false)
private Long id;
@Column (name="name")
private String name;
@ManyToOne(fetch = FetchType.LAZY)
@ForeignKey(name = "season_fk")
@JoinColumn(name = "season_fk", nullable = false)
private Season season;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "country", cascade = CascadeType.ALL)
private List<League> leagues = new ArrayList<League>(0);
}
and League.java:
Code:
@Entity
@Table(name = "leagues")
public class League{
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "id", unique = true, nullable = false)
private Long id;
@Column(name = "name")
private String name;
@ManyToOne(fetch = FetchType.LAZY)
@ForeignKey(name = "country_fk")
@JoinColumn(name = "country_fk", nullable = false)
private Country country;
//getters and setters
}
Now I want to build something like a report where all leagues will be grouped by countries and years and filtered by some property, like League.name.
So my query looks like:
Code:
select distinct s from League l
join l.country c
join c.season s
where l.name like :legueName
But when I start iterating the result list I get all countries and leagues without checking for where condition. I think that is due to lazy loading of associated collections. I need lazy loading, and want just for this particular query perform eager fetch and get properly filtered result. Could somebody give me a hint about how to achieve what I want.
Thanks in advance.
Login
FAQ
Search