How to calculate sum from in Spring Boot JPA repository

Question

I'm working on a Spring Boot application and I have encountered the following challenge when trying to find the SUM. I have tried using JPQL and JPA syntax, but I'm still getting a null pointer exception.

@Getter
@Setter
@NoArgsConstructor
@Entity
public class ReportData extends Auditable {
    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long reportId;
    private Integer tests;
}

@Repository
public interface reportRepository  extends JpaRepository <ReportData, Long>  {
// or "SELECT SUM(m.tests) FROM ReportData m" 
  @Query(value = "SELECT SUM(tests) FROM ReportData", nativeQuery = true)
  int totalTest ();
}

@GetMapping(value = "/reports")
public String statisticsPage (Model model){
   model.addAttribute("tests", reportRepository.totalTest());
   return ("statistics");
}

The error I get is:

How are you declaring `reportRepository`? – Dilermando Lima Oct 15 '21 at 17:53 — Dilermando Lima, Oct 15 '21 at 17:53

score 2 · Answer 1 · answered Oct 15 '21 at 17:57

Please note that the error you are getting is not in your query. That NullPointerExceptions is being thrown because the repository is null. Please, share all the code of your controller or ensure you are properly injecting/declaring the repository.

The problem with the query is in your repository interface.

@Repository
public interface reportRepository  extends JpaRepository <ReportData, Long>  {
// or "SELECT SUM(m.tests) FROM ReportData m" 
  @Query(value = "SELECT SUM(tests) FROM ReportData", nativeQuery = true)
  int totalTest ();
}

With this syntax in the @Query annotation, you are using JPQL, not SQL. While JPQL is a representation of SQL, they are not the same. Thus, the usage of the nativeQuery flag is unnecesary. If you want to use a native SQL query, then you should do something like

@Query(value = "SELECT SUM(m.tests) FROM report_data m", nativeQuery = true)
  int totalTest ();

Please, change tests and report_data to their respective field and table in your database. You could also use the query you already have, just remove the nativeQuery attribute. Also note that the usage of native queries prevents your code to work with any other database. In other words, you will need to rewrite your queries if you ever change from MySQL to Oracle (for example).

thanks so much i have found where the mistake was am really gratefull — timmy tembo, Oct 15 '21 at 18:17

How to calculate sum from in Spring Boot JPA repository

1 Answers1