|
Situation:
I have objects, that have multiple-column primary keys(one column is number, other is nvarchar).
Now main object has multipe other objects as childres (contains a list of children). Every child object also contains a list of other objects (grand childrens to main object)
Schema is something like that:
MAIN_OBJECT
|
---LIST OF CHILDREN
|
|
---LIST OF GRAND CHILDREN(Mapped to CHILD)
|
Java class hierarchy
public class IssilavinimoPazymejimas { .... @OneToMany(fetch = FetchType.EAGER, mappedBy = "issilavinimoPazymejimas", orphanRemoval = true) @Cascade(CascadeType.ALL) @Fetch(value = FetchMode.SUBSELECT) private List<Serija> pazymejimoSerijos; .... }
public class Serija {
@ManyToOne
@JoinColumns( { @JoinColumn(name = "ser_paz_id", referencedColumnName = "paz_id"), @JoinColumn(name = "ser_paz_versija", referencedColumnName = "paz_versija")}
)
private IssilavinimoPazymejimas issilavinimoPazymejimas;
@OneToMany(fetch = FetchType.EAGER, mappedBy = "serija", orphanRemoval = true)
@Cascade(CascadeType.ALL)
@Fetch(value = FetchMode.SUBSELECT)
private final List<Vaizdas> vaizdai = new ArrayList<>();
}
public class Vaizdas {
@ManyToOne
@JoinColumns( { @JoinColumn(name = "vzd_ser_id", referencedColumnName = "ser_id"), @JoinColumn(name = "vzd_ser_versija", referencedColumnName = "ser_versija")}
)
private Serija serija;
}
BUG: when I try to load IssilavinimoPazymejimas - that contains multiple Serija objecs, and every Serija contains multipe Vaizdas objects - hibernate generates invalid SQL and loading fails.
Problem is IN WHERE condition - select in where condition returns 2 rows
and this SQL fails on Microsoft DB
Hibernate generates this SQL:
SELECT
vaizdai0_.vzd_ser_id AS vzd_ser_8_7_1_,
vaizdai0_.vzd_ser_versija AS vzd_ser_9_7_1_,
vaizdai0_.vzd_id AS vzd_id1_8_1_,
vaizdai0_.vzd_versija AS vzd_vers2_8_1_,
vaizdai0_.vzd_id AS vzd_id1_8_0_,
vaizdai0_.vzd_versija AS vzd_vers2_8_0_,
vaizdai0_.vzd_paskut_redagav AS vzd_pask3_8_0_,
vaizdai0_.vzd_vart_id AS vzd_vart4_8_0_,
vaizdai0_.vzd_pavad AS vzd_pava5_8_0_,
vaizdai0_.vzd_ser_id AS vzd_ser_8_8_0_,
vaizdai0_.vzd_ser_versija AS vzd_ser_9_8_0_,
vaizdai0_.vzd_vaizd_tipas AS vzd_vaiz6_8_0_,
vaizdai0_.vzd_turinio_tipas AS vzd_turi7_8_0_,
vaizdai0_.vzd_vaizdas AS vzd_vai10_8_0_
FROM
dbo.paz_vaizdai vaizdai0_
WHERE
(
vaizdai0_.vzd_ser_id, vaizdai0_.vzd_ser_versija) IN
(
SELECT
pazymejimo0_.ser_id,
pazymejimo0_.ser_versija
FROM
dbo.paz_serija pazymejimo0_
LEFT OUTER JOIN
dbo.view_ins_teikejai teikejas1_
ON
pazymejimo0_.ser_ireg_prasym_teik_ins_data=teikejas1_.ins_data
AND pazymejimo0_.ser_ireg_prasym_teik_ins_id=teikejas1_.ins_id
LEFT OUTER JOIN
dbo.view_ins_teikejai teikejas2_
ON
pazymejimo0_.ser_isreg_prasym_teik_ins_data=teikejas2_.ins_data
AND pazymejimo0_.ser_isreg_prasym_teik_ins_id=teikejas2_.ins_id
LEFT OUTER JOIN
dbo.view_ins_tvirtintojai tvirtinant3_
ON
pazymejimo0_.ser_tvirtin_ins_data=tvirtinant3_.ins_data
AND pazymejimo0_.ser_tvirtin_ins_id=tvirtinant3_.ins_id
LEFT OUTER JOIN
dbo.view_ins_uzsakovai uzsakovoin4_
ON
pazymejimo0_.ser_uzsakov_ins_data=uzsakovoin4_.ins_data
AND pazymejimo0_.ser_uzsakov_ins_id=uzsakovoin4_.ins_id
LEFT OUTER JOIN
dbo.paz_apsauga apsauga5_
ON
pazymejimo0_.ser_id=apsauga5_.aps_ser_id
AND pazymejimo0_.ser_versija=apsauga5_.aps_ser_versija
LEFT OUTER JOIN
dbo.paz_pazymejimas issilavini6_
ON
apsauga5_.aps_paz_id=issilavini6_.paz_id
AND apsauga5_.aps_paz_versija=issilavini6_.paz_versija
LEFT OUTER JOIN
dbo.view_ins_teikejai teikejas7_
ON
issilavini6_.paz_ireg_prasym_teik_ins_data=teikejas7_.ins_data
AND issilavini6_.paz_ireg_prasym_teik_ins_id=teikejas7_.ins_id
LEFT OUTER JOIN
dbo.view_ins_teikejai teikejas8_
ON
issilavini6_.paz_isreg_prasym_teik_ins_data=teikejas8_.ins_data
AND issilavini6_.paz_isreg_prasym_teik_ins_id=teikejas8_.ins_id
LEFT OUTER JOIN
dbo.view_ins_tvirtintojai tvirtinant9_
ON
issilavini6_.paz_tvirtin_ins_data=tvirtinant9_.ins_data
AND issilavini6_.paz_tvirtin_ins_id=tvirtinant9_.ins_id
LEFT OUTER JOIN
dbo.view_ins_uzsakovai uzsakovoin10_
ON
issilavini6_.paz_uzsakov_ins_data=uzsakovoin10_.ins_data
AND issilavini6_.paz_uzsakov_ins_id=uzsakovoin10_.ins_id
WHERE
pazymejimo0_.ser_paz_id=?
AND pazymejimo0_.ser_paz_versija=?)
|
