CODEWITHSUNDEEP
spring-data-jpaspring-restcontroller
Ayub
Ayub

Reputation: 599

incorrect sql query generated by spring data jpa

Given the repository below

@Repository
public interface DashboardRepository
       extends PagingAndSortingRepository<Dashboard, Long> {
    @Query("SELECT " +
           "    d " +
           "FROM " + 
           "    Dashboard d " + 
           "WHERE " + 
           "    d.branchUserId= :userId " +
           "OR  d.restUserId = :userId " + 
           "ORDER BY " +
           "    d.orderId DESC")
    Page<Dashboard> findByBranchUserIdOrRestUserId(@Param("userId") Long userId, Pageable pageable);
}

When I make a call to findByBranchUserIdOrRestUserId I see the following query generated:

SELECT
    COUNT(dashboard0_.ORDER_ID) AS col_0_0_
FROM
    DAILY_ORDERS dashboard0_
WHERE
    dashboard0_.BRANCH_USER_ID=?
OR dashboard0_.REST_USER_ID=?

Any clues what might be the issue?

UPDATE: The Dashboard entity is mapped to a database view. When I execute SELECT * FROM [view name] directly on the database I get rows back. However, when I execute dashboardRepository.count(), I get zero.

Upvotes: 0

Views: 456

Answers (1)

AshleyJ
AshleyJ

Reputation: 332

I would assume this has to do with Spring's inferred "countQuery"

In the docs is talks about Pagination and custom @Queries

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

Ref: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query

Upvotes: 1

Related Questions