0

I have a problem with my Query, when the query executes in intellij console with my parameters it works fine and gives output: CAR EARNINGS 5 140 6 100 And that's fine but I need to create a new list of object and send it back to my frontend in json so what I need is to create this list. When my front send start date parameter and end date to controller and call method getStats then it seems like my resuly form query can't be mapped to class EarningStatistics. I really don't know why and I tried a lot of different approaches but without result... Here is my code:

@Query(value = "SELECT car_id as CAR, SUM(r.cost) as EARNINGS from Rent r where r.rent_date BETWEEN ?1 AND ?2 GROUP BY r.car_id", nativeQuery = true)
List<EarningStatistics> getStatistics(String startDate, String endDate);

And class (I tried to rename fields in this class to car_id and cost but without result)

public class EarningStatistics implements Serializable {
public int CAR;
public double EARNINGS;

public int getCAR() {
    return CAR;
}

public void setCAR(int CAR) {
    this.CAR = CAR;
}

public double getEARNINGS() {
    return EARNINGS;
}

public void setEARNINGS(double EARNINGS) {
    this.EARNINGS = EARNINGS;
}

public EarningStatistics(int CAR, double EARNINGS) {
    this.CAR = CAR;
    this.EARNINGS = EARNINGS;
}

}

How should I code this class and name the fields in it?

Snooze Snoze
  • 109
  • 1
  • 10
  • 1
    Why don't you use a JPQL query (and respect the Java naming conventions)? `select new EarningStatistics(car.id, sum(rent.cost)) from Rent rent join rent.car car where rent.date between ?1 and ?2 group by car.id)`. There is no way JPA can't possibly know from your query that it should create instances of EarningStatistics. – JB Nizet Jan 11 '20 at 10:35
  • You are selecting `car_id` and try to map it to `EarningStatistics`... Use `select new EarningStatistics(car_id, ...)` if you need to map query result to that object. – Yann39 Jan 11 '20 at 10:36
  • That's because what I posted is a **JPQL** query, not a SQL query. So you need to remove `nativeQuery = true`. And of course, the query must be adapted to your actual class and property names. Don't just copy and paste the code without understanding it. – JB Nizet Jan 11 '20 at 10:45
  • @JBNizet I rebuilded my class and query `@Query(value = "select new com.backend.backend.modelstatistics.EarningStatistics(car.id, sum(rent.cost)) from Rent rent join rent.car car where rent.date between ?1 and ?2 group by car.id") List getStatistics(String startDate, String endDate);` – Snooze Snoze Jan 11 '20 at 11:01
  • And a Class: `public class EarningStatistics implements Serializable { public int car; public double cost; public EarningStatistics(int car, double cost) { this.car = car; this.cost = cost; } public int getCar() { return car; } public void setCar(int car) { this.car = car; } public double getCost() { return cost; } public void setCost(double cost) { this.cost = cost; } }` – Snooze Snoze Jan 11 '20 at 11:02
  • But spring throws org.springframework.beans.factory.UnsatisfiedDependencyException:Error creating bean with name'jwtAuthenticationController': Unsatisfied dependency expressed through field 'rentDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'rentDAO': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.backend.backend.dao.RentDAO.getStatistics(java.lang.String,java.lang.String)! and I have really no idea what is wrong – Snooze Snoze Jan 11 '20 at 11:03
  • @JBNizet Do I need entity manager to run this query or syntax is wrong? – Snooze Snoze Jan 11 '20 at 11:08
  • I have no idea either, because you haven't posted the rest of the stack trace, nor the code of your entities. You also need to use the fully quelified name of the EarningStatistics class in the query. – JB Nizet Jan 11 '20 at 11:11
  • @JBNizet This stack trace i soo long so I will paste it on pastebin. Here it is https://pastebin.com/eZH5a1uw – Snooze Snoze Jan 11 '20 at 11:13
  • It says: *could not resolve property: date of: com.backend.backend.modelrent.Rent*. So your Rent class doesn't have a date property, yet you're trying to use that unexisting property in your query. Again, you have to adapt the query I posted to the actuall class and property names of your entity. – JB Nizet Jan 11 '20 at 11:14
  • @JBNizet yea I changed date to rent_date and that's it. How should I store rent_date property in database? I'm already using java.util.Date and when I tesed it now another error occured (In this case it is my string date doesn't match java.util.Date) . I can send rent_date as a String from my rest api but if store data as a string then rent.rent_date between won't work is it right? Should I pass a parameters with type of Data as my arguments for this function? – Snooze Snoze Jan 11 '20 at 11:22
  • String is not the right type to represent a date. A String is a sequence of characters, not a point in time. Date is completely obsolete. the modern equivalent is Instant, or LocalDate, or LocalDateTime, depending on what this date actually represents. JSON can only represent a date as a string or a number, but you should parse that to the appropriate type, and use the appropriate type everywhere in the code. Especially when it reaches the lowest persistence layer. – JB Nizet Jan 11 '20 at 11:25
  • You should also respect the Java naming conventions. `rent_date` violates them. – JB Nizet Jan 11 '20 at 11:26
  • @JBNizet I parsed my strings to Date from java.util.Date and the response is empty. My controller send back the empty response. I tried in postman and in the Body I can see only [ ]. In my rest controller I have : `return ResponseEntity.ok(rentDAO.getStatistics(parsedStartDate,parsedEndDate));` but it makes me confused. My native query a few days ago after a lot of try and rebuilds returned the same result. Is it possible to send back my list of EarningStatistics like this or am I doing it/thinking wrong? – Snooze Snoze Jan 11 '20 at 11:30
  • 1
    If it's empty, it simply means that the query returns an empty list. And if it returns an empty list, it means that no data matches the criteria you're passing. So, check your data, and check the criteria. Use your debugger. Add logging statements. Standard debugging. – JB Nizet Jan 11 '20 at 11:32
  • @JBNizet I removed `@JoinColumn` from my Rent class and it works. Thanks for Your time and explanation, I'm grateful :) – Snooze Snoze Jan 11 '20 at 16:00

0 Answers0