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<>(); } 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; } 查询代码
