CODEWITHSUNDEEP
javaspringhibernatejpa
Radhika Mantri
Radhika Mantri

Reputation: 197

Native Query on table which is having composite PK in Spring data jpa

I have two beans one is @Embeddable in which I have composition of PK.

@Embeddable
@JsonIgnoreProperties(ignoreUnknown = true)
public class PersonalInfoId implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(name = "username", nullable = false)
    private String username;
    @JsonFormat(pattern = "yyyy-MM-dd")

    @Temporal(TemporalType.DATE)

    private Date end_date;

other bean is @EmbeddedId in which I have other parameters

    @Table(name = "tblemployee_personal_info0001")
    @JsonIgnoreProperties(ignoreUnknown = true)
    public class tblemployee_personal_info0001 {

        @EmbeddedId

        private PersonalInfoId personalInfoId;
        private String emp_id;
        @JsonFormat(pattern = "yyyy-MM-dd")
        @Temporal(TemporalType.DATE)
        private Date start_date;
        private String last_changed_by;
        @Temporal(TemporalType.TIMESTAMP)
        private Date last_changed_date;
        private String emp_sub_group_key;
        private String emp_designation_key;

In Repository interface I want to write native query.What I have tried is

public interface PersonalInfoDataRepository extends JpaRepository<tblemployee_personal_info0001, PersonalInfoId> {
        List<tblemployee_personal_info0001> findByPersonalInfoIdUsername(String username);

    @Query("SELECT start_date, username FROM tblemployee_personal_info0001"
            + " p WHERE p.start_date=:start_date AND p.username=:userName")

    List<Object> find(@Param("start_date") Date start_date, @Param("userName") String userName);

Exception that I am getting is : could not resolve property: username of: com.pa.beans.tblemployee_personal_info0001

 How we can write query which will take parameter from both the class or beans?

Any help would be appreciated. Thanks in advance.

Upvotes: 2

Views: 2734

Answers (1)

Montassar El B&#233;hi
Montassar El B&#233;hi

Reputation: 637

Here you are not using a native sql query. You can use a jpql query to pass by the @EmbeddedId field personalInfoId:

@Query("SELECT p.start_date, p.personalInfoId.username FROM Tblemployee_personal_info0001 p WHERE ...")

(Please try to start your class names by an upperCase and use camelCase style: like TEmployeePersonalInfo)

If one day you want it native you need to pricise it in the @Query annotation:

@Query(nativeQuery = true, value = "SELECT...")

Upvotes: 2

Related Questions