0

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
gstackoverflow
  • 36,709
  • 117
  • 359
  • 710

2 Answers2

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.

Panagiotis Bougioukos
  • 15,955
  • 2
  • 30
  • 47
  • My names are already correspond the naming convention you ask. I use underscores for database columns. camelCase for class fields – gstackoverflow Jan 15 '22 at 18:25
  • @gstackoverflow if this is the case then the `@Query` in your question is wrong as it should be a native query. As you have it in your question it is not a native query. – Panagiotis Bougioukos Jan 15 '22 at 20:40
  • sorry but why not ? It works and works correctly. I use underscores inside Query annotation because in the database I have columns with undesrcores. – gstackoverflow Jan 15 '22 at 22:11
  • @gstackoverflow check the annotation `@Query` it has the default setting `nativeQuery = false` . So if this is the case your query refers to a JPA query not a database query. So what you write in the query refers to your java classes and not in database! – Panagiotis Bougioukos Jan 15 '22 at 22:24
  • @PanagiotisBougioukos The OP is asking about Spring Data JDBC not Spring Data JPA. – Jens Schauder Jan 16 '22 at 10:47
1

int countByJobIdAndToDeleteIsTrue(UUID jobId) should do the trick.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I have aleardy provided the solution you offer here to OP. Also check how `@Query` is defined as by default as the OP uses it, it does not refer to jdbc but jpa layer – Panagiotis Bougioukos Jan 16 '22 at 11:18
  • @PanagiotisBougioukos: Your answer suggests `countByToDeleteTrueAndMyJobId()` whereas Jens' answer suggests `countByJobIdAndToDeleteIsTrue()`. Is there a functional or meaningful difference between these? If I'm understanding your answer correctly, there isn't, since Spring is parsing the method name to correspond to generate a query by convention. But, to do so, they still need to update their field names to match their method syntax. Is that right? – Jeremy Caney Jan 17 '22 at 01:27
  • @JeremyCaney correct, you should update field names and also there should not be any difference between the two name versions provided by Jens Schauder and me – Panagiotis Bougioukos Jan 17 '22 at 08:13
  • @Jens I see error: **Null return value from advice does not match primitive return type for....** – gstackoverflow Jan 17 '22 at 14:03
  • can you post the full stack trace and the executed SQL statement in you question? – Jens Schauder Jan 17 '22 at 14:09
  • The query is: `SELECT ...field list withou any count.. FROM "myrow" WHERE "myrow"."to_delete" = TRUE AND ("myrow"."my_job_id " = ?)` – gstackoverflow Jan 17 '22 at 14:33
  • trace is http://dl4.joxi.net/drive/2022/01/17/0005/3037/338909/09/76723763b1.jpg – gstackoverflow Jan 17 '22 at 14:39
  • @Jens Schauder any news ? – gstackoverflow Feb 02 '22 at 20:53