带过滤器的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;
}

我想从我的数据库中获得结果，它包含每个对象中的列表technicStatusList=new arraylist<>()，我想在其中有一个TechnicStatus仅值为isactive=true。

为此，我使用了相同的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.isactive=true,TechnicStatus.isactive=false）。

我希望以本机查询的形式获得结果：

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

与技术相关联的TechnicalStatus列表将始终是由映射定义的完整列表。

基本上你有两个选择。如果您只对状态为Active的TechnicalStatus感兴趣，那么可以在关联中使用非可移植的、特定于Hibernate的@where子句。

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

https://dzone.com/articles/hibernate-where-子句

否则，您所做的就是从查询方法返回一个TechnicalStatus列表，这不是您想要的，而是您所拥有的全部。