英文:
How to check if an entity exists in DB using methods from SimpleJpaRepository
问题
我正在尝试使用SimpleJpaRepository中可用的方法来查找是否存在与几个条件匹配的任何实体。
我的实体如下：
@Entity
public class MyEntity{
@Id
private int id;
@ManyToOne
private TaskEntity taskEntity;
@Column
private String name;
...
我的方法如下：
/** 检查是否存在具有给定的taskId和name的实体 */
public boolean existsByTaskAndName(int taskId, String name){
MyEntity probe = new MyEntity();
probe.setTask(entityManager.getReference(TaskEntity.class, taskId));
probe.setName(name);
return exists(Example.of(probe, ExampleMatcher.matching().withIgnoreCase()));
}
我本以为SQL查询会类似于：
select top 1 * from my_entity where task=@taskId and lower(name)=lower(@name)
但实际上，SQL查询包含与TaskEntity和所有与TaskEntity相关的实体的内部连接，where子句包含来自TaskEntity的所有字段与所有其他相关实体的所有字段之间的比较。（总共有10+个内部连接和100+个where子句）。
我该如何编写此方法，以便仅比较“task”和“name”列，而无需进行任何连接并且不读取不必要的对象？
英文:
I am trying to find out if any entity exists matching a few conditions using methods available in SimpleJpaRepository.
My entity looks like this:
@Entity
public class MyEntity{
@Id
private int id;
@ManyToOne
private TaskEntity taskEntity;
@Column
private String name;
...
And my method looks like this:
/** Check if any entity exists with the given taskId and name */
public boolean existsByTaskAndName(int taskId, String name){
MyEntity probe = new MyEntity();
probe.setTask(entityManager.getReference(TaskEntity.class, taskId));
probe.setName(name);
return exists(Example.of(probe, ExampleMatcher.matching().withIgnoreCase()));
}
I was expecting the sql query to look something like this:
select top 1 * from my_entity where task=@taskId and lower(name)=lower(@name)
But in reality, the SQL query contained inner joins with TaskEntity and all entities related to TaskEntity, and the where clauses contained comparison between all fields from TaskEntity and all fields from all other related entities. (in total 10+ inner joins and 100+ where clauses).
How can I write the method so that it only compares the columns "task" and "name", without any joins and without reading unnecessary objects?
答案1
得分: 0
我会这样执行JPQL查询：
select case when (count(*) > 0) then true else false end from MyEntity m where m.taskEntity.id = :id and upper(m.name) = upper(:name)
它会返回一个漂亮的布尔值。
英文:
I would do a JPQL Query like this:
select case when (count(*) > 0) then true else false end from MyEntity m where m.taskEntity.id = :id and upper(m.name) = upper(:name)
It would return you a nice boolean.
专注分享java语言的经验与见解，让所有开发者获益！
评论