多对多关系的条件查询

Question

我有三个实体:任务、工作流和标签。任务和工作流具有多对一和一对多关系。工作流和标签有多对多关系。

我想根据tagId检索记录。

任务:

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity(name = "xflow_task")
public class Task {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long taskId;
private String taskName;

@ManyToOne
@JoinColumn(name = "workflow_id")
private Workflow workflow;
}

工作流:

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity(name = "xflow_workflow")
public class Workflow {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long workflowId; // 123
@OneToMany(mappedBy = "workflow", fetch = FetchType.LAZY)
private List<Task> tasks;

@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name = "xflow_workflow_tag",
joinColumns = @JoinColumn(name = "workflow_id"), 
inverseJoinColumns = @JoinColumn(name = "tag_id"))
private List<Tag> tag;
}

标记:

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity(name = "xflow_tag")
public class Tag {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer tagId;
private String tagName;
@ManyToMany(mappedBy = "tag",fetch = FetchType.LAZY)
private List<Workflow> workflow;
}

条件查询如下所示:

public Long criteriaForCount(SearchCriteria searchCriteria) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
Root<Task> root = criteriaQuery.from(Task.class);
List<Predicate> predicateList = new ArrayList<>();   
if(CoreUtil.isNonEmpty(taskSearchCriteria.getTagIdList())) {      
predicateList.add(root.join("workflow").join("tag").in(taskSearchCriteria.getTagIdList()));
}

Predicate[] predicateArr = new Predicate[predicateList.size()];
Predicate predicate = criteriaBuilder.and(predicateList.toArray(predicateArr));
criteriaQuery.where(predicate);
criteriaQuery.select(criteriaBuilder.count(root));
return entityManager.createQuery(criteriaQuery).getSingleResult();
}

一个工作流可以有很多标签。查询运行正常，但返回多个重复项数据库中的条目。我想找到具有tagId(I)的不同任务。e taskSearchCriteria.getTagIdList())，我将其作为列表传递。

CoreUtil是一个辅助类，用于检查对象或集合是否为空。

Answer 1

我认为您放置的方法与此无关，因为它返回输入的计数。我会这样做:

public List<Task> getTaskByTag(SearchCriteria searchCriteria) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//If you want records it has to be of type Task, Long would be for the number of records
CriteriaQuery<Task> criteriaQuery = criteriaBuilder.createQuery(Task.class);
Root<Task> root = criteriaQuery.from(Task.class);

Join<Task,Workflow> joinWF = root.join("workflow");
Join<Workflow,Tag> joinTag = joinWF.join("tag");
// The code is clearer when more complex queries are made if you store the joins in variables
List<Predicate> predicateList = new ArrayList<>();   
if(CoreUtil.isNonEmpty(taskSearchCriteria.getTagIdList())) {    
//Access properties instead of using implicit access, for code clarity  
predicateList.add(joinTag.get("tagId").in(taskSearchCriteria.getTagIdList()));
}
//The and clause is automatically added between all the elements of the list
criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()]));
criteriaQuery.select(root).distinct(true);
return entityManager.createQuery(criteriaQuery).getResultList();
}