Query not generated correctly with a composite key

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

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.

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.

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.

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.

because sequence would be reset every year

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.

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