jpa/hibernate @onetomany 使用left join 添加多条件,可以使用过滤器filters (with-clause not allowed on fetched associations; use filters异常信息)

时间：2023-03-08 17:36:56
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<>();

}

one

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;

}

many

  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;

    }

查询代码