I have a working native MySQL query to obtain some data by grouping up a date column by year and month (ex: 2017-01, 2017-02)
SELECT
DATE_FORMAT(production.production_date,'%Y-%m') AS 'month',
SUM(operation.planned_quantity) AS 'planned_total',
SUM(operation.actual_quantity) AS 'actual_total',
((SUM(operation.actual_quantity) / SUM(operation.planned_quantity)) *100) AS 'adherence'
FROM operation
INNER JOIN production on production.id = operation.production_id
INNER JOIN control_point on control_point.id = production.control_point_id
INNER JOIN work_center on work_center.id = control_point.work_center_id
INNER JOIN cost_center on cost_center.id = work_center.cost_center_id
INNER JOIN section on section.id = cost_center.section_id
WHERE production.production_date BETWEEN '2017-01-01 00:00' AND '2017-05-31 23:59'
AND section.id = 4
GROUP BY DATE_FORMAT(production.production_date,'%Y-%m')
With this i can get the output as expected the result looks like this
for now I tried two days to get this result into my spring boot project. Tried named native query, stored procedure and method signature patterns. but failed in every try. at last I had to discard changes using my git client.
Things I am trying to achieve is group by partial date, map result to custom Pojo or obtain row MySQL result in to spring app.
Please help me in this.
-------------------2017-06-07---------------
Problem Solved
I managed to achieve the requirement by mixing MySQL function DATE_FORMAT(xxx,'%Y-%m') in JPQL i really did not have any idea of mixing both, but it works.
My working spring function and query:
@Query(value = "SELECT "
+ " new com.trendsmixed.fma.dao.MonthlyScheduleAdherence(DATE_FORMAT(operation.production.productionDate,'%Y-%m'), SUM(operation.actualQuantity), SUM(operation.plannedQuantity), (SUM(operation.actualQuantity)/SUM(operation.plannedQuantity))*100) "
+ " FROM Operation operation"
+ " WHERE operation.production.productionDate BETWEEN :startDate AND :endDate"
+ " AND operation.production.controlPoint.workCenter.costCenter.section= :section"
+ " GROUP BY DATE_FORMAT(operation.production.productionDate,'%Y-%m')")
public List getMonthlyScheduleAdherenceBySection(@Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("section") Section section);
the POJO to map the result:
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
public class MonthlyScheduleAdherence {
String month;
long actualTotal;
long plannedTotal;
long adherence;
}
With that I get:
[
{
month: "2017-02",
actualTotal: 8640,
plannedTotal: 10664,
adherence: 81
},
{
month: "2017-03",
actualTotal: 8907,
plannedTotal: 10352,
adherence: 86
},
{
month: "2017-04",
actualTotal: 3224,
plannedTotal: 4164,
adherence: 77
},
{
month: "2017-05",
actualTotal: 8545,
plannedTotal: 12435,
adherence: 68
}
]
And that is what I wanted.
I managed to successfully make this chart with this data.
Thank you
Sql function is available in JPQL. My JPA version is 1.11.9. Your DATE_FORMAT function not worked for me. I use function query for the group by day:
@Query(value = "SELECT count(ac) as count, function('date_format', max(ac.subscriptionStartDate), '%Y, %m, %d') as date FROM MyTable ac " +
"WHERE ac.subscriptionStartDate BETWEEN :startDate AND :endDate GROUP BY function('date_format', ac.subscriptionStartDate, '%Y, %m, %d')")
public List<Map<String,Object>> findRegisteredCustomersHistory(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
The result of the query is the list that records the number of records grouped by days in formatted form.
Sample result rows:
count: 3, date: 2019, 09, 10
count: 1, date: 2019, 09, 11
Simple function query in JPQL:
@Query(value = "SELECT function('date_format', s.date, '%Y, %m, %d') as date from Transactions s;")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With