Spring Data JPA SPEL 动态地添加了 where JPQL 子句。

huangapple go评论59阅读模式
英文:

Spring data jpa SPEL dynamically adds where JPQL clause

问题

以下是您要翻译的部分:

data class GetSourceListRequest(
    val category: List<Int>,
    val text: String,
    val tag: List<Int>,
    val order: Sort.Direction,  
    val pageSize: Int,    
    val currentPage: Int
)
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:
```kotlin
@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>

希望这对您有所帮助。

英文:

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>

答案1

得分: 0

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 your 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>
}
英文:

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

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

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&lt;SourceItemEntity, Long&gt; {
  @Query(
        &quot;&quot;&quot;
        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)
        &quot;&quot;&quot;
    )
    fun getIdByCondition(request: GetSourceListRequest, pageable: Pageable): Page&lt;Long&gt;
}

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

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

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

interface ISourceItemDao : JpaRepository&lt;SourceItemEntity, Long&gt; {
  @Query(
        &quot;&quot;&quot;
        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)
        &quot;&quot;&quot;
    )
    fun getIdByCondition(category: Collection&lt;Long&gt;, pageable: Pageable): Page&lt;Long&gt;
}

huangapple
  • 本文由 发表于 2023年5月11日 18:05:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76226436.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定