How do I map a mysql query to an non entity object in spring

I am building a webapplication in Spring that converts raw data into different types of tables. Those table need to be created by sql every time it’s beeing summoned. So I am building mysql queries that create those tables. The results from those queries I want to put in an object throught the Spring repository, but I just cannot get Spring to fill any objects.

  • I tried to use @Query in the repository without success.
  • I searched the internet but only found solutions that use creating an entity.

The class I want to create an object from

public class DayTimeTable {


    private int monday;

    private int tuesday;

    private int wednesday;

    private int thursday;

    private int friday;

    private int saturday;

    private int sunday;

    private int total;

    private int time;

    public DayTimeTable(int monday, int tuesday, int wednesday, int thursday, int friday, int saturday, int sunday, int total, int time) {
        this.monday = monday;
        this.tuesday = tuesday;
        this.wednesday = wednesday;
        this.thursday = thursday;
        this.friday = friday;
        this.saturday = saturday;
        this.sunday = sunday;
        this.total = total;
        this.time = time;
    }

The query I want to use to fill the object

SELECT
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 2 THEN 1 END) AS monday,
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 3 THEN 1 END) AS tuesday,
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 4 THEN 1 END) AS wednesday,
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 5 THEN 1 END) AS thursday,
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 6 THEN 1 END) AS friday,
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 7 THEN 1 END) AS saturday,
     COUNT(CASE WHEN DAYOFWEEK(DATE(date_time)) = 1 THEN 1 END) AS sunday,
     COUNT(*)                                                   AS total,
     HOUR(date_time)                                            AS time
from data d
where record_id = 2758
AND DATE(date_time) NOT IN (
  SELECT DATE(start_date)
  FROM record
  where id = d.record_id
)
AND DATE(date_time) NOT IN (
  SELECT DATE(end_date)
  FROM record
  where id = d.record_id
)
GROUP BY HOUR(date_time)
ORDER BY time ASC

Leave a Reply

avatar
  Subscribe  
Notify of