jpa/hibernate @onetomany 使用left join 添加多条件,可以使用过滤器filters (with-clause...

package com.ipinyou.mip.dataAsset.campaignManagement.entity;

import com.ipinyou.mip.utils.NumberUtils;
import com.ipinyou.mip.utils.StringHelper;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;
import org.apache.commons.lang3.builder.HashCodeExclude;
import org.hibernate.annotations.Filter;

import javax.persistence.*;

import java.text.DecimalFormat;
import java.util.HashSet;
import java.util.Set;

import static javax.persistence.FetchType.EAGER;
import static javax.persistence.FetchType.LAZY;

@Data
@Entity
@Table(name = "campaign_dashboard_dimension")
@ToString
@EqualsAndHashCode
public class CampaignDashboardDimensionDo {

    @Id
    private Long id;

    @Transient
    private Double impRate = 1.0;
    @Column(name = "3_reach")
    private Double reach3;
    @Column(name = "1_reach")
    private Double reach1;
    private Long campaignId;
    @OneToMany(fetch = LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "campaign_dashboard_dimension_id")
    @org.hibernate.annotations.OrderBy(clause = "imp desc")
    @Filter(name = "dimension",condition = "dimension_type=:dimension ")
    private Set<CampaignDashboardDimensionDetailDo> campaignDashboardDimensionDetailDo = new HashSet<>();

   
}

package com.ipinyou.mip.dataAsset.campaignManagement.entity;

import com.ipinyou.mip.utils.NumberUtils;
import com.ipinyou.mip.utils.StringHelper;
import lombok.Data;
import lombok.ToString;
import org.hibernate.annotations.FilterDef;
import org.hibernate.annotations.OrderBy;
import org.hibernate.annotations.ParamDef;
import org.hibernate.annotations.Where;

import javax.persistence.*;
import java.text.DecimalFormat;

import static javax.persistence.FetchType.EAGER;

@Data
@Entity
@Table(name = "campaign_dashboard_dimension_detail")
@ToString
@FilterDef(name="dimension", parameters=@ParamDef(name="dimension",type="java.lang.String"))
public class CampaignDashboardDimensionDetailDo {
    @Id
    private Long id;
    private Long version;
    private java.sql.Timestamp lastModified;
    private String removed;

    private Long imp;
    private Double reachRate;
    private String dimensionName;
    private String dimensionType;
    private Long uv;
    private Long click;
    private Double ta = 0.0;

    @Transient
    private Double impRate;
    @Transient
    private Double diffTa;
    @Transient
    private Double diffReach3 = 0.0;
    @Transient
    private Double targetTa = 0.0;
    @Transient
    private Double targetReach3 = 0.0;


}

  public List<CampaignDashboardDimensionDo> getCategoryData(List<String> displayType, String dimension, String source, Long campaignId) {

        List<CampaignDashboardDimensionDo> resultList=new ArrayList<>();
        if (displayType==null||displayType.isEmpty()){
            return resultList;
        }

        for (String type:displayType ){
            StringBuilder sb = new StringBuilder();
            sb.append("select cdd from CampaignDashboardDimensionDo as cdd left join fetch cdd.campaignDashboardDimensionDetailDo as cddd   where cdd.campaignId=:campaignId and cdd.source=:source" +
                    " and cdd.logDate=(select max(logDate) from CampaignDashboardDimensionDo where campaignId=:campaignId and source=:source and displayType=:type) and cdd.displayType=:type");
            Filter filter = entityManager.unwrap(Session.class).enableFilter("dimension");
            filter.setParameter("dimension",dimension);
            TypedQuery<CampaignDashboardDimensionDo> query = entityManager.createQuery(sb.toString(), CampaignDashboardDimensionDo.class);
            query.setParameter("type",type);
            query.setParameter("source", source);
            query.setParameter("campaignId", campaignId);
            List<CampaignDashboardDimensionDo> list = query.getResultList();
            Set<CampaignDashboardDimensionDo> set=new HashSet<>(list);
            resultList.addAll(set);
        }

        return resultList;

    }