'How to implement count sql query by my multiple columns and one of them is constant?

I have following method and it is working. But It doesn't work if I remove @Query annotation.

import org.springframework.data.jdbc.repository.query.Query;
...
@Repository
public interface MyRepository extends PagingAndSortingRepository<MyRow, UUID> {
     @Query("select count(*) cnt from myrow where my_job_id = :jobId and to_delete = true")
     int getRowsCountByToDeleteTrueAndMyJobId(UUID jobId);

I tried to ask google about it but I wasn't abe to ask properly I suppose.

sqlspring-bootspring-data-jdbc


Solution 1:[1]

"select count(*) cnt from myrow where my_job_id = :jobId and to_delete = true"

This means that you have an entity named myrow which has some class fields named my_job_id and to_delete.

Spring Data which offers JPA support will mess the underscores _ that you have used in the class fields, because it understands the _ as a separator for multiple fields when used in the name of the method.

So firstly you need to refactor your code so that

  • to_delete field is renamed into toDelete
  • my_job_id field is renamed into myJobId

Then Spring Data will be able to understand a method with the following name

int countByToDeleteTrueAndMyJobId(UUID jobId);

without the need of @Query as it will be built by spring automatically from the name of the method.

Solution 2:[2]

int countByJobIdAndToDeleteIsTrue(UUID jobId) should do the trick.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Panagiotis Bougioukos
Solution 2 Jens Schauder

Related Questions