Hibernate Criteria: Perform JOIN in Subquery/DetachedCriteria
I'm running into an issue with adding JOIN's to a subquery using DetachedCriteria. The code looks roughly like this:
Criteria criteria = createCacheableCriteria(ProductLine.class, "productLine");
criteria.add(Expression.eq("productLine.active", "Y"));
DetachedCriteria subC开发者_开发知识库riteria = DetachedCriteria.forClass(Model.class, "model");
subCriteria.setProjection(Projections.rowCount());
subCriteria.createAlias("model.modelLanguages", "modelLang");
subCriteria.createAlias("modelLang.language", "lang");
criteria.add(Expression.eq("lang.langCode", "EN"));
subCriteria.add(Restrictions.eqProperty("model.productLine.productLineId","productLine.productLineId"));
criteria.add(Subqueries.lt(0, subCriteria));
But the logged SQL does not contain the JOIN in the subquery, but does include the alias which is throwing an error
SELECT *
FROM PRODUCT_LINE this_
WHERE this_.ACTIVE=?
AND ? <
(SELECT COUNT(*) AS y0_
FROM MODEL this0__
WHERE lang3_.LANG_CODE ='EN'
AND this0__.PRODUCT_LINE_ID =this_.ID
)
How can I add the joins to the DetachedCriteria?
@Entity
@Table(name = "PRODUCT_LINE")
public class ProductLine implements java.io.Serializable {
private long productLineId;
private char active;
private Set<Models> models = new HashSet<Models>(0);
@OneToMany(fetch = FetchType.LAZY, mappedBy = "productLine")
public Set<Models> getModels() {
return this.models;
}
}
@Entity
@Table(name = "MODEL")
public class Model implements java.io.Serializable {
private long modelId;
private ProductLine productLine;
private String name;
private Set<ModelLanguages> modelLanguages = new HashSet<ModelLanguages>(0);
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PRODUCT_LINE_ID")
public ProductLine getProductLine() {
return this.productLine;
}
@Column(name = "NAME", nullable = false)
public String getName() {
return this.name;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "model")
public Set<ModelLanguages> getModelLanguages() {
return this.modelLanguages;
}
}
@Entity
@Table(name = "MODEL_LANGUAGES")
public class ModelLanguages implements java.io.Serializable {
private long id;
private Language language;
private Model model;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "LANGUAGE_ID", nullable = false, insertable = false, updatable = false)
public Language getLanguage() {
return this.language;
}
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "MODEL_ID", nullable = false, insertable = false, updatable = false)
public Model getModel() {
return this.model;
}
}
@Entity
@Table(name = "LANGUAGES", uniqueConstraints = @UniqueConstraint(columnNames = "LANG_CODE"))
public class Language implements java.io.Serializable {
private long languageId;
private String langCode;
private Set<ModelLanguages> modelLanguages = new HashSet<ModelLanguages>(
0);
@Column(name = "LANG_CODE", unique = true, nullable = false)
public String getLangCode() {
return this.langCode;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "language")
public Set<ModelLanguages> getModelLanguages() {
return this.modelLanguages;
}
}
Hibernate version: 3.2.6.ga
Hibernate core: 3.3.2.GA
Hibernate annotations: 3.4.0.GA
Hibernate commons-annotations: 3.3.0.ga
Hibernate entitymanager: 3.4.0.GA
Hibernate validator: 3.1.0.GA
Don't you have a typo in your code at the following line :
criteria.add(Expression.eq("lang.langCode", "EN"));
I think, you should add this restriction on the subcriteria and not the criteria.
If you need to join tables in subquery can you try make this. It is explicitly specifying joinType in your detached criteria.
subCriteria.createAlias("model.modelLanguages", "modelLang", CriteriaSpecification.LEFT_JOIN);
subCriteria.createAlias("modelLang.language", "lang", CriteriaSpecification.LEFT_JOIN);
精彩评论