Reputation: 93
my first real-life experience with JPA. So please forgive naiveness!
I have two 1-to-n related DB tables and I need a read-only access through JPA to them.
I have realized the JPQA query like below
Query query = em.createQuery("select a from WineVariety a " +
" where :refDate between a.beginDate and a.endDate" +
" and (:varietyCode is null or :varietyCode = a.varietyCode)" );
List<WineVariety> Varieties = query
.setParameter("refDate", dtRef)
.setParameter("varietyCode", varietyCode)
.getResultList();
and the corresponding entities like this.
Parent:
@Entity
@Table(name = "WINE_PROC_VARIETIES_VW")
@Immutable
public class WineVariety
implements Serializable // required for @OneToMany not using PK
// https://hibernate.atlassian.net/browse/HHH-7668
{
@Column(name = "ID")
@NotNull
private @Id Long id; // pk
@Column(name = "VARIETY_CODE") // unique on this entity
@NotNull
private String varietyCode;
// . . .
// many to one is the owner side of this bidirectional relationship
@OneToMany(fetch = FetchType.LAZY, mappedBy="wineVariety")
private List<WineTypeVariety> wineTypeVarieties;
public WineVariety() {
this.wineTypeVarieties = new ArrayList<WineTypeVariety>();
}
// . . .
Children:
@Entity
@Table(name = "WINE_PROC_WINE_TYPOLOGIES_VARIETIES_VW")
@Immutable
public class WineTypeVariety {
@Column(name = "ID")
@NotNull
private @Id Long id; // pk
@Column(name = "VARIETY_CODE")
@NotNull
private String varietyCode;
// . . .
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(
name="VARIETY_CODE", // this entity table column
referencedColumnName="VARIETY_CODE", // parent entity table column
insertable=false, updatable=false)
private WineVariety wineVariety;
Seems like this is not the correct way, as when I provide a NULL for the :varietyCode parameter, I get both related and unrelated children in the results. E.g.
...
{
"id": 862,
"varietyCode": "862", <-----
"provIstatCode": "021",
"beginDate": "19000101",
"endDate": "99991231",
"wineTypeVarieties": [
{
"id": 1677,
"varietyId": 999,
"varietyCode": "224", <---- ????
"wineTypeId": 1677,
"minPercent": 0,
"maxPercent": 100,
"usageCode": "P",
"beginDate": "19000101",
"endDate": "99991231"
},
...
I'm searching and trying several different variants, but still can't get what I expect. Please help - What did I do wrong?
Thanks!
Upvotes: 0
Views: 32