英文:
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<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>
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论