java - inner - in criteria in hibernate
Criterios de Hibernate: realizar JOIN en Subquery/DetachedCriteria (2)
Me encuentro con un problema al agregar JOIN a una subconsulta usando DetachedCriteria. El código se ve más o menos así:
Criteria criteria = createCacheableCriteria(ProductLine.class, "productLine");
criteria.add(Expression.eq("productLine.active", "Y"));
DetachedCriteria subCriteria = 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));
Pero el SQL registrado no contiene el JOIN en la subconsulta, pero sí incluye el alias que arroja un 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
)
¿Cómo puedo agregar las uniones a 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
No tienes un error tipográfico en tu código en la siguiente línea:
criteria.add(Expression.eq("lang.langCode", "EN"));
Creo que deberías agregar esta restricción a los subcriterios y no a los criterios.
Si necesita unir tablas en subconsulta, intente hacer esto. Especifica explícitamente joinType en sus criterios separados.
subCriteria.createAlias("model.modelLanguages", "modelLang", CriteriaSpecification.LEFT_JOIN);
subCriteria.createAlias("modelLang.language", "lang", CriteriaSpecification.LEFT_JOIN);