Querying results directly to a DTO

Question

When i try to Querying results directly from spring data to a DTO class i get this error

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: owner_name of: test.dao.bean.Car [SELECT new test.dto.bean.CarOwners(c.owner_name, c.owner_nid, c.owner_phone, c.driver_name, c.driver_nid, c.driver_phone) FROM test.dao.bean.Car c] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:133) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:670) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_152] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_152] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_152] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_152] at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE] at com.sun.proxy.$Proxy123.createQuery(Unknown Source) ~[na:na] at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE] ... 76 common frames omitted

this is my query:

@Query("SELECT new test.dto.bean.CarOwners(c.owner_name, c.owner_nid, c.owner_phone, c.driver_name, c.driver_nid, c.driver_phone) FROM car c")
public List<CarOwners> findAllCarOwners();

and this is the DTO class:

test.dto.bean;

public class CarOwners{

private String owner_name;
private Long owner_nid;
private Integer owner_phone;
private String driver_name;
private Long driver_nid;
private Integer driver_phone;

 public CarOwners(String owner_name, Long owner_nid, Integer owner_phone, String driver_name, Long driver_nid, Integer driver_phone) {
        this.owner_name = owner_name;
        this.owner_nid = owner_nid;
        this.owner_phone = owner_phone;
        this.driver_name = driver_name;
        this.driver_nid = driver_nid;
        this.driver_phone = driver_phone;

    }

public String getOwner_name() {
    return owner_name;
}


public void setOwner_name(String owner_name) {
    this.owner_name = owner_name;
}


public Long getOwner_nid() {
    return owner_nid;
}


public void setOwner_nid(Long owner_nid) {
    this.owner_nid = owner_nid;
}


public Integer getOwner_phone() {
    return owner_phone;
}


public void setOwner_phone(Integer owner_phone) {
    this.owner_phone = owner_phone;
}


public String getDriver_name() {
    return driver_name;
}


public void setDriver_name(String driver_name) {
    this.driver_name = driver_name;
}


public Long getDriver_nid() {
    return driver_nid;
}


public void setDriver_nid(Long driver_nid) {
    this.driver_nid = driver_nid;
}


public Integer getDriver_phone() {
    return driver_phone;
}


public void setDriver_phone(Integer driver_phone) {
    this.driver_phone = driver_phone;
}
}

Any Help please ??

Ken Chan Ken Chan · Accepted Answer · 2019-02-17T09:40:11

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: owner_name of: test.dao.bean.Car

Apparently , the fields in the select clause should refer to the Java property name of the mapped entity (Car) rather than the property name in DTO.

So suppose the Car is :

@Entity
@Table(name="car")
public class Car{


    @Column(name="owner_name")
    private String ownerName;

}

Then you should write :

@Query("SELECT new test.dto.bean.CarOwners(c.ownerName) FROM car c")

Actually , the names of the DTO field have nothing to do with how to write the @Query string. It just matches the order between the DTO constructor parameter and the fields in the SELECT NEW clause. You can use whatever DTO name as long as their orders and data type are matched.

Querying results directly to a DTO

1 Answers