CrudRepository查询多一对多关系

Question

我想使用crudRepository从数据库中删除一个条目。我有一个与Task实体具有一对多关系的timeecard实体，而Task实体与Project具有一对多关系。我想删除基于taskId和projectId的数据库中的一个元素。

实体的定义如下:

@Entity
@Table(name = "timecards")
public class TimeCard{
@Getter @Setter
@Column(name = "timecard_id", nullable = false)
@SequenceGenerator(name = "timecardSequence", sequenceName = "timecardIdSequence", allocationSize = 1, initialValue = 1)
@GeneratedValue(generator = "timecardSequence")
@Id
private Long timeCardId;
@Getter @Setter
@ManyToOne
@JoinColumn(name = "task_id", nullable = false, referencedColumnName = "id") 
private Task task;
}

@Entity
@Table(name = "tasks")
public class Task{
@Getter @Setter
@Column(name = "id", nullable = false)
@SequenceGenerator(name = "taskSequence", sequenceName = "taskIdSequence", allocationSize = 1, initialValue = 1)
@GeneratedValue(generator = "taskSequence")
@Id
private Long id;
@Getter
@Setter
@Column(name = "task_id", nullable = false)
private int taskId;
@Getter @Setter
@ManyToOne
@JoinColumn(name = "project_id", referencedColumnName = "project_id")
private Project project;
}

@Entity
@Table(name = "projects")
public class Project implements Serializable {

@Getter @Setter
@Column(name = "project_id", nullable = false)
@Id
private Long projectId;

@Repository
public interface TimeCardRepository extends CrudRepository<TimeCard, String>{
public void deleteTimeCardByTaskTaskIdAndTaskProjectProjectId(int taskId, Long projectId);
}

deletetimecardbytasktaskidandtaskprojectprojd是我的属性表达式，但不幸的是，我得到以下错误信息:

Caused by: io.quarkus.spring.data.deployment.UnableToParseMethodException: Entity com.timecardservice.timecard.TimeCard does not contain a field named: TaskProject_ProjectId. Offending method is deleteTimeCardByTaskTaskIdAndTaskProjectProjectId

我检查了https://evonsdesigns.medium.com/spring-jpa-one-to-many-query-examples-281078bc457b的截点，但我得到相同的错误信息。

做这个查询的正确方法是什么?

Answer 1

您需要使用_来访问连接表/实体的属性。

所以应该是

public void deleteTimeCardByTask_TaskIdAndTask_Project_ProjectId(int taskId, Long projectId);

您还可以使用

检查已记录的sql语句。

spring.jpa.show-sql=true

Answer 2

如果JPA的方式是不够的，您总是可以使用NativeQuery/JPQLQuery来完成工作:

@Modifying
@Query(value = "DELETE FROM TimeCard tc WHERE tc.task.taskId = :taskId and tc.task.project.projectId = :projectId")
List<Long> deleteByIds(@Param("taskId") Long taskId, @Param("projectId") Long projectId);

Answer 3

您可以显式地编写查询:

@Repository
public interface TimeCardRepository extends CrudRepository<TimeCard, String>{
@Modifying 
@Query(value = "DELETE FROM tc timeCard WHERE tc IN ( "
+ "SELECT timeCard FROM timeCard  "
+ " JOIN timeCard.task task "
+ " JOIN taks.project project "
+ " WHERE task.id = :taskId AND project.id= :projectId)") 
public void deleteTimeCardByTaskTaskIdAndTaskProjectProjectId(int taskId, Long projectId);
}