JPA踩坑记录

JPA带有外键约束时，直接delete时报：Cannot delete or update a parent row: a foreign key constraint fails

// JPA带有外键约束时
@Access(value = AccessType.PROPERTY)
@ManyToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
@JoinTable(name = "t_perf_report_agent", joinColumns = {@JoinColumn(name = "report_id", referencedColumnName = "id")}, inverseJoinColumns = {@JoinColumn(name = "agent_id", referencedColumnName = "id")})
private List<PerfAgentEntity> agentEntityList;

// 删除的时候需要先把相应字段设置为 null
perfReportEntity.setAgentEntityList(null);
perfReportRepository.deleteById(reportId);

JPA中直接写sql，Demo

@Query(nativeQuery = true, value = "SELECT `id`, `name` FROM `t_perf_resource`")
List<Map<String, Object>> getAllNames();

@Query(nativeQuery = true,  value = "SELECT * FROM t_test_case_result WHERE case_num = (?1) ORDER BY id DESC LIMIT 1")
Optional<TestCaseResultEntity> findByCaseNum(String caseNum);

@Query(nativeQuery = true, value = "SELECT count(0) as count, executor FROM t_test_task_case WHERE task_id = ?1 AND last_result > 0 GROUP BY executor")
List<Object[]> countExector(int taskId);

@Query(nativeQuery = true, value = "SELECT * FROM t_test_task WHERE is_delete = 0 AND demand_id in (?1) ORDER BY id DESC LIMIT ?2, ?3")
List<TestTaskEntity> findAllByDemandId(Iterable<Integer> demandIds, int pageNo, int pageSize);

@Query(nativeQuery = true, value = "SELECT count(0) FROM t_test_task WHERE is_delete = 0 AND demand_id in (?1)")
Map<String, BigInteger> countByDemandId(Iterable<Integer> demandIds, int pageNo, int pageSize);