java - JPQL - 带过滤器的 JOIN 查询

Question

我有实体:

第一

@Entity
@Getter
@Setter
@NoArgsConstructor
public class Technic implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private String gosNumber;

    private String invNumber;

    private String shassisNumber;

    private String engineNumber;

    @Column(length = 100)
    private String yearOfMake;

    @ManyToOne
    private Farm farm;

    @JsonManagedReference
    @ManyToOne
    private TechGroup techGroup;

    @JsonManagedReference
    @ManyToOne
    private TechType techType;

    @JsonManagedReference
    @ManyToOne
    private TechMark techMark;

    @JsonIgnore
    @CreationTimestamp
    @Column(name = "creation_date", updatable = false)
    private LocalDateTime createdDate;

    @JsonIgnore
    @Column(name = "updated_date")
    @UpdateTimestamp
    private LocalDateTime updatedDate;

    @JsonIgnore
    @Column(columnDefinition = "Bool default false")
    private Boolean isDel;


    @JsonManagedReference
    @OneToMany(mappedBy = "technic")
    private List<TechnicStatus> technicStatusList = new ArrayList<>();

    public List<TechnicStatus> getTechnicStatusList() {
        return technicStatusList;
    }

    public void setTechnicStatus(TechnicStatus technicStatus) {
        this.technicStatusList = new ArrayList<>();
        this.technicStatusList.add(technicStatus);
    }

第二:

@Entity
@Getter
@Setter
@NoArgsConstructor
public class TechnicStatus implements Serializable  {



    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "technic_status_id")
    private Long id;

    @JsonBackReference
    @ManyToOne
    private Technic technic;

    @JsonManagedReference
    @ManyToOne
    private Status status;

    private Boolean isGarantia;

    private Boolean isLizing;

    private LocalDate visitedDate;

    private LocalDate notWorkDate;

    private String description;

    @JsonIgnore
    private boolean isActive;

    @JsonIgnore
    @CreationTimestamp
    @Column(name = "creation_date", updatable = false)
    private LocalDateTime createdDate;
}

我想从我的数据库中获取结果，其中包含每个对象 Technic 中的列表，我有 List technicStatusList = new ArrayList<>()，其中我只想拥有一个值为 isActive=true 的 TechnicStatus。

为此，我使用相同的 JPQL 查询:

TypedQuery<Technic> query = em.createQuery("Select t  from Technic t join TechnicStatus ts on t.id = ts.technic.id where t.isDel=false and ts.isActive=true and t.farm.id=:farmId order by t.techGroup.name, t.techType.name, t.techMark.name", Technic.class);

但获得包含 TechnicStatus 的结果，该结果返回具有 true 和 false 的 TechnicStatus(TechnicStatus.isActive=true、TechnicStatus.isActive=false)。

我想获得此 native 查询的结果:

SELECT 
    *
FROM
    technic
        JOIN
    technic_status ON technic.id = technic_status.technic_id
WHERE
    technic.is_del = FALSE
        AND technic_status.is_active = TRUE
        AND technic.farm_id = 1722

Answer 1

与技术关联的技术状态列表将始终是您的映射定义的完整列表。

基本上你有两个选择。如果您仅曾经对状态为 Active 的 TechnicalStatus 感兴趣，那么您可以在协会。

@JsonManagedReference
@OneToMany(mappedBy = "technic")
@Where("active = 1")
private List<TechnicStatus> technicStatusList = new ArrayList<>();

https://dzone.com/articles/hibernate-where-clause

否则，您所做的就是从查询方法返回 TechnicalStatus 列表，这不是您想要的，但却是您所拥有的。