Hi,
I have a problem with a query in Hibernate. In My case there are 5 classes.
Code:
public class A implements Serializable {
//--------------------------------------------------------------------------
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "AID")
private Integer AID;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Size(max = 255)
@Column(name = "AHinweis")
private String ANote;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@OneToMany(mappedBy = "adAllocation", fetch = FetchType.EAGER)
private java.util.List<AD> adAllocation;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@OneToMany(mappedBy = "abAllocation", fetch = FetchType.EAGER)
private java.util.List<AB> abAllocation;
//--------------------------------------------------------------------------
}
Code:
public class AB implements Serializable {
//--------------------------------------------------------------------------
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "ABID")
private Integer ABID;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Size(max = 75)
@Column(name = "ABName")
private String aBName;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Size(max = 555)
@Column(name = "ABBeschreibung")
private String aBDescription;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@ManyToOne
@JoinColumn(name = "FKAID", nullable = true)
private A aAllocation;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@ManyToOne
@JoinColumn(name = "FKCID", nullable = true)
private C cAllocation;
//--------------------------------------------------------------------------
}
Code:
public class AD implements Serializable {
//--------------------------------------------------------------------------
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "ADID")
private Integer ADID = null;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Size(max = 255)
@Column(name = "ADName")
private String aDName;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@ManyToOne
@JoinColumn(name = "FKAID", nullable = true)
private A aAllocation;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "tbl_C_AD_Zuordnung",
joinColumns = {@JoinColumn(name = "FKADID") },
inverseJoinColumns = {@JoinColumn(name = "FKADTID")}
)
private java.util.List<ADT> aDTAllocation;
//--------------------------------------------------------------------------
}
Code:
public class ADT implements Serializable {
//--------------------------------------------------------------------------
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "ADTID")
private Integer ADTID;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@ManyToOne
@JoinColumn(name = "FKCID", nullable = true)
private C cAllocation;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Size(max = 255)
@Column(name = "ADTText")
private String ADTText;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "tbl_AD_ADT_Zuordnung",
joinColumns = {@JoinColumn(name = "FKADTID") },
inverseJoinColumns = {@JoinColumn(name = "FKADID")}
)
private java.util.List<AD> aDAllocation;
//--------------------------------------------------------------------------
}
Code:
public class C implements Serializable {
//--------------------------------------------------------------------------
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "CID")
private Integer CID = null;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Size(max = 25)
@Column(name = "CName")
private String cName;
//--------------------------------------------------------------------------
//--------------------------------------------------------------------------
@Lob
@Column(name = "CBild")
private byte[] cPicture;
//--------------------------------------------------------------------------
}
Without DISTINCT I get 10 A's.
Code:
SELECT g
FROM A g
join g.abAllocation ab
join g.adAllocation ad
left outer join ad.aDTAllocation adt
WHERE ab.cAllocation = :c
AND (adt.cAllocation = :c1 OR adt.cAllocation IS NULL)
GROUP BY g.AID
With DISTINCT I get 1 A.
Code:
SELECT DISTINCT g
FROM A g
join g.abAllocation ab
join g.adAllocation ad
left outer join ad.aDTAllocation adt
WHERE ab.cAllocation = :c
AND (adt.cAllocation = :c1 OR adt.cAllocation IS NULL)
GROUP BY g.AID
But I get an A with a list of AD's an all ADT's but I wanted a list with just of one A, all matching AD's and just the selected ADT's who match the type of C!
I don't undstand why this happens by the moment. Is it because of the "FetchType.EAGER"?
Can somebody help me please to understand my mistake.
Login
FAQ
Search
