Unable to convert sql (jpa query) returned columns to a custom class

Question

this question has been asked but I am unable to solve in my case.

previous question: How to return a custom object from a Spring Data JPA GROUP BY query

In my project I have a custom class defined as follows,

public class VerticalBarChartDomain {

    private String name;
    private Long value;
    // constructors
    public VerticalBarChartDomain() {
        // TODO Auto-generated constructor stub
    }
    public VerticalBarChartDomain(String name, Long value) {
        super();
        this.name = name;
        this.value = value;
    }
    //getters and setters
    public String getName() {
        return name;
    }
    public Long getValue() {
        return value;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setValue(Long value) {
        this.value = value;
    }

}

In JPA Repository, I have a query as follows,

@Query(value="select   new in.net.rserver.domain.VerticalBarChartDomain( DATE(r.start_time), count(distinct(r.ride_id)) ) from ride r where  r.start_time >= date_sub(curdate(), interval 6 day) group by DATE(r.start_time)",nativeQuery=true)
    public List<VerticalBarChartDomain> getRidesPerDayForVBChart();

When I execute this, I am getting an error as follows,

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.net.yaana.server.rserver.domain.VerticalBarChartDomain(DATE(r.start_time), coun' at line 1
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488) ~[na:na]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2494) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]

Please correct me where I am wrong.

Well, this is supposed to be SQL. And `new in.net.rserver.domain.VerticalBarChartDomain(...)` isn't valid SQL. It would be valid JPQL, but JPQL is not the same language as SQL, and the remaining of the query is not valid JPQL. In short, you're doing the equivalent of writing half of a class in Java and the remaining in C++. Nothing will be able to execute such a thing. Just return two columns, iterate through the List — JB Nizet, Jun 18 '18 at 21:53
@JBNizet , thanks for letting know my mistake, Can you please tell me how do I access the column for Object? — Anil, Jun 18 '18 at 22:20
suppose the query returns , # ride_date, rides '2018-06-14', '15' '2018-06-15', '40' '2018-06-16', '53' '2018-06-17', '74' '2018-06-18', '31', in the List of Object[], how to get each column from the Object — Anil, Jun 18 '18 at 22:26
Well row[0] is the first element of the row, and row[1] is the second one. It's an array. It works like all other arrays. — JB Nizet, Jun 18 '18 at 22:28
Correct me If my understanding is wrong, row[0] will return '2018-06-14' and row[1] will '15'? — Anil, Jun 18 '18 at 22:30
Yes, except that your query returns a date as its first value, so you won't get a String back. Why don't you just try, and use your debugger, or plain old System.out.println() to find out? `System.out.println("row[0] is " + row[0] + " and its type is " +row[0].getClass());` — JB Nizet, Jun 18 '18 at 22:33

Unable to convert sql (jpa query) returned columns to a custom class

0 Answers0