JPA query result is null

Question

Hi I have the following code:

Controller:

@GetMapping("/salesTrend")
private List<Object[]> salesTrend(){
    int[] profit = {1,2,3,4,5,6,7,8,9,10,11,12};
    return customerRepository.findByProfit(profit);
}

Repository:

@Query(
    value = "SELECT MONTH(f_date), SUM(profit) as p from finance where MONTH(f_date) = ?1",
    nativeQuery = true)
List<Object[]> findByProfit(int[] profit);

the result of this is null and I am struggling to know why that is because I tested this already on xampp and it works fine I don't know what I'm doing wrong thank you

Try to print the executed queries and check if they are ok: https://stackoverflow.com/questions/2536829/hibernate-show-real-sql — Iman H, Mar 17 '21 at 12:07
Hey, do you by chance want to return the sum of the profits for each month? I doubt JPA would automatically run your query 12 times, i,.e. for **each** array item. Instead, I think the DB receives the where expression check if `MONTH(f_date) == {1,2,3,4,...12}` which is never true. **To test, change the method signature to accept a single integer argument, amd check what it returns when running `customerRepository.findByProfit(1)`** — ppeterka, Mar 17 '21 at 12:11
@ppeterka yes that is exactly what I want to do I tried what you suggested and inputed 12 as a parameter and it did what I want it to do but I need it to produces the sum of the profits each month — Cedrix Cedrix, Mar 17 '21 at 12:19

score 1 · Accepted Answer · answered Mar 17 '21 at 12:43

I have solved it already thanks to the comment of @ppeterka I just made an array containing all the months then I looped each of the elements in the array to put them at the query one at a time like so

    int[] profit = {1,2,3,4,5,6,7,8,9,10,11,12,13};
    List<Object> profitList = new ArrayList<>();
    
    for(int i = 1; i < profit.length; i++) {
        List<Object[]> results = customerRepository.findByProfit(i);
        for(Object[] obj : results) {
            Map<Object, Object> profits = new LinkedHashMap<>();
            profits.put("Month", obj[0]);
            profits.put("Profit", obj[1]);
            
            profitList.add(profits);
        }
    }

Maybe one solution to get it done in a single query would be using an IN clause in the query. — ppeterka, Mar 17 '21 at 17:24

JPA query result is null

1 Answers1