Spring data jpa SPEL dynamically adds where JPQL clause

Question

query parameter object

data class GetSourceListRequest(
    val category: List<Int>,
    val text: String,
    val tag: List<Int>,
    val order: Sort.Direction,  
    val pageSize: Int,    
    val currentPage: Int
)

query repository

interface ISourceItemDao : JpaRepository<SourceItemEntity, Long>, CrudRepository<SourceItemEntity, Long> {
  @Query(
        """
        select e.id 
        from SourceItemEntity e
        left join SourceItemToCategoryEntity ic on e.id = ic.itemId
        #{request.category.size() > 0 ? 'where ic.categoryId in (:#{#request.category}' : 'q')}
        where ic.categoryId in (:#{#request.category})
        """
    )
    fun getIdByCondition(request: GetSourceListRequest, pageable: Pageable): Page<Long>
}

The key code is this line (The code here is wrong):

#{request.category.size() > 0 ? 'where ic.categoryId in (:#{#request.category})' : ''}

I hope that when #{request.category} is an empty list, the where clause is not added, and when it is not an empty list, the where clause is added.

I just learned Spring Data JPA, and I didn't find out how to write such a spel statement.

dependencies:

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>3.0.4</version>
  <relativePath/> <!-- lookup parent from repository -->
</parent>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Result

After help finally my code is as follows:

@Query(
        """
        select e.id 
        from SourceItemEntity e
        left join SourceItemToCategoryEntity ic on e.id = ic.itemId
        where (:#{#request.category.isEmpty()} = true)
        or (:#{#request.category.isEmpty()} = false and ic.categoryId in (:#{#request.category})) 
        """
    )
    fun getIdByCondition(request: GetSourceListRequest, pageable: Pageable): Page<Long>

Answer 1

First of all in your code you don't need to extend from CrudRepository having the interface extending JpaRepository.

Also I see that you have duplicated where clause so, I suggest to leave only one of it simplified with Collection.isEmpty() method:

All right, it turned out that you cannot use ? expression of SpEL mixed with JPQL syntax as query validation fails in this case. For you method you can dodge this limitation as:

interface ISourceItemDao : JpaRepository<SourceItemEntity, Long> {
  @Query(
        """
        select e.id 
        from SourceItemEntity e
        left join SourceItemToCategoryEntity ic on e.id = ic.itemId
        where (:#{#request.category.isEmpty()} = true)
           or (:#{#request.category.isEmpty()} = false and ic.categoryId in :request.category)
        """
    )
    fun getIdByCondition(request: GetSourceListRequest, pageable: Pageable): Page<Long>
}

I've checked this on my own project having Child and Parent entities and this query works for me:

@Query("select child from Child child " +
  " join child.parent parent " +
  " where (:#{#ids.isEmpty()} = true) " +
  "    or (:#{#ids.isEmpty()} = false and parent.id in :ids)")
List<Child> findAllByParentIds1(@Param("ids") Collection<Long> ids);

If your query fails then try to extract the collection and use it directly:

interface ISourceItemDao : JpaRepository<SourceItemEntity, Long> {
  @Query(
        """
        select e.id 
        from SourceItemEntity e
        left join SourceItemToCategoryEntity ic on e.id = ic.itemId
        where (:#{#category.isEmpty()} = true)
           or (:#{#category.isEmpty()} = false and ic.categoryId in :category)
        """
    )
    fun getIdByCondition(category: Collection<Long>, pageable: Pageable): Page<Long>
}