Query not generated correctly with a composite key


#1

I use spring boot 2, jpa and hibernate and I use composite key

@Entity
@IdClass(SamplesPK.class)
public class Samples extends BaseEntity {
    @Id
    private String sampleLetter;

    @Embedded
    private TestSamples testSamples;

    @Id
    @ManyToOne(optional=false)
    @JoinColumns({
        @JoinColumn(name = "sampling_id", referencedColumnName = "id"),
        @JoinColumn(name = "sampling_year", referencedColumnName = "year")})
    private Samplings sampling;

    //get set

}

public class SamplesPK implements Serializable {

    private SamplingsPK sampling;

    private String sampleLetter;

    public SamplesPK(SamplingsPK sampling, String sampleLetter) {
        this.sampling = sampling;
        this.sampleLetter = sampleLetter;
    }

        //get set
}


@Entity
@IdClass(SamplingsPK.class)
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public class Samplings {
    @Id
    private Integer year;

    @Id
    @GeneratedValue
    private Integer id;

    @OneToMany(mappedBy = "sampling", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Samples> samples = new ArrayList<>();

    //get set

}   

public class SamplingsPK implements Serializable {

    private int year;

    private Integer id;

    public SamplingsPK(int year, Integer id) {
        this.id = id;
        this.year = year;
    }
}

I try to do search sample with composite key

@Query(value="select s from Samples s Join fetch s.sampling sp Join fetch sp.product p Join fetch p.productType Join Fetch s.testSamples.compressionTest where s.sampling.id=:id and s.sampling.year=:year and s.sampleLetter=:sampleLetter and sp.id=:id and sp.year=:year")
public Samples findSamplesWithFullProductAndTest(@Param("id") Integer id, @Param("year") Integer year,  @Param("sampleLetter") String sampleLetter);

I get this error when the quer is called

org.postgresql.util.PSQLException: ERROR: operator does not exist: record = integer Indice : No operator matches the given name and argument type(s). You might need to add explicit type casts.

Generated query is

select
        samples0_.sample_letter as sample_l1_20_0_,
        samples0_.sampling_id as sampling0_20_0_,
        samplings1_.id as id2_21_1_,
        samplings1_.year as year3_21_1_,
        products2_.id as id2_15_2_,
        producttyp3_.id as id1_16_3_,
        compressio4_.id as id1_3_4_,
        samples0_.sampling_id as samplin27_20_0_,
        samples0_.sampling_year as samplin28_20_0_,
        samples0_.compression as compres18_20_0_,
        samples0_.compression_number as compres19_20_0_,
        samples0_.compression_test_id as compres29_20_0_,
        samplings1_.available_for_test as availabl4_21_1_,
        samplings1_.dtype as dtype1_21_1_,
        products2_.created_at as created_3_15_2_,
        products2_.updated_at as updated_4_15_2_,
        products2_.name_en as name_en5_15_2_,
        products2_.dtype as dtype1_15_2_,
        producttyp3_.created_at as created_2_16_3_,
        producttyp3_.updated_at as updated_3_16_3_,
        producttyp3_.name_en as name_en4_16_3_,
        compressio4_.created_at as created_2_3_4_,
        compressio4_.updated_at as updated_3_3_4_
    from
        permacon.samples samples0_ 
    inner join
        permacon.samplings samplings1_ 
            on samples0_.sampling_id=samplings1_.id 
            and samples0_.sampling_year=samplings1_.year 
    inner join
        permacon.products products2_ 
            on samplings1_.product_id=products2_.id 
    inner join
        permacon.product_types producttyp3_ 
            on products2_.product_type_id=producttyp3_.id 
    inner join
        permacon.compressions compressio4_ 
            on samples0_.compression_test_id=compressio4_.id 
    where
        (
            samples0_.sampling_id, samples0_.sampling_year
        )=? 
        and samplings1_.year=? 
        and samples0_.sample_letter=? 
        and samplings1_.id=? 
        and samplings1_.year=?

Query is not generated correctly


#3

Can you try to reproduce the error with our test case template outside of Spring Boot?

See https://github.com/hibernate/hibernate-test-case-templates/tree/master/orm/hibernate-orm-5 .

That will help.


#4

I don’t think that you can nest mutliple levels of PKs inside the @IdClass object.

More, @IdClass is a really weird choice anyway. Why would you duplicate the properties both in the entity and the @IdClass? Use @EmbeddedId and provide all identifier properties inside that single @Embeddable. It should work like a charm, as explained in this article.


#5

Not sure if a sequence can be used with Embeddable.

Sampling contain all generic of sample

Need to be able to identify a sampling with year + id sequence

To identify an sample, we use letter. Habitually there are less then 10 samples.

A sample can’t exist without an sampling

I can’t use only letter for the PK, it will have duplicate

first Sampling of 2008 who have 3 samples

20081
A
B
C

Second Sampling of 2008 who have 2 samples

20082
A
B

Not sure if we can use a sequence with embeddedId

Maybe in SamplesPK instead of having SamplingsPK, I can put directly field year and id and from sampling copy this value to it.


#6

If you have a sequence value, that’s already unique, why do you need a composite identifier?

The composite identifier is needed when the individual values alone are not unique, but their combination provides uniqueness.


#7

because sequence would be reset every year


#8

It’s better to send a replicating test case so we can take a better look at your model. This post explains what you need to do.


#9

strangely, i switched for year Integer to int… and that worked