JpaRepository with custom Query and Composite Primary Key: error unknown column

I have a SQL Server entity with a Composite Primary Key.

That is the Entity:

@Entity
@Table(name = "plz", schema = "dbo")
@IdClass(plzId.class)
public class Plz {

  @Id
  @Column(name = "plz", columnDefinition = "NCHAR(5)")
  private String plz;

  @Id
  @Column(name = "ort", columnDefinition = "NCHAR(30)")
  private String ort;

  @Column(name = "gueltigbis")
  private Date gueltigBis;

  @Column(name = "gueltigvon")
  private Date gueltigVon;

  @Id
  @Column(name = "value", columnDefinition = "NCHAR(5)")
  private String value;
}

and this is ID Class:

@Data
@EqualsAndHashCode
public class plzId implements Serializable {

  private String plz;
  private String ort;
  private String value;
}

That is the JpaRepository

public interface PlzRepository extends JpaRepository<Plz, PlzId> {

  @Query(value = "SELECT value, ort FROM dbo.plz WHERE plz = :plz AND gueltigvon <= :bezugsdatum AND gueltigbis >= :bezugsdatum",
    nativeQuery = true
  )
  List<Plz> findByPlzDatum(@Param("plz") String plz, @Param("bezugsdatum") Date bezugsdatum);
}

But when this Query is executed I get an error: Invalid Column name: plz

Answer

Finally I got the solution 🙂 The select wasn’t returing the full key / object. Validation on the object failed and there was not suitable constructor present.

Advertisement

Answer