ffxx68
ffxx68

Reputation: 93

JPA OneToMany JoinColumn returning unrelated children

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

Answers (0)

Related Questions