Skip to main content

Very complex query with JPA CriteriaBuilder

Please note these java.net forums are being decommissioned and use the new and improved forums at https://community.oracle.com/community/java.
2 replies [Last post]
samsam8899
Offline
Joined: 2010-11-04

Hello,

I want to write a query in jpa criteriaquery. Here is the query:
<BBCode>
SELECT node.category_name, (COUNT(parent.category_name) - 1) AS depth
FROM category_subcategories AS node,
category_subcategories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_name = 'PORTABLE ELECTRONICS'
GROUP BY node.category_name
ORDER BY node.lft
</BBCode>
Here is the code I come up with:
<BBCode>
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CategorySubcategories> cq = cb.createQuery( CategorySubcategories.class );
Root<CategorySubcategories> node = cq.from( CategorySubcategories.class );
Root<CategorySubcategories> parent = cq.from( CategorySubcategories.class );
Predicate p1 = cb.equal(node.get("categoryName"), categoryName);
Predicate p2 = cb.between(node.get("lft").as(Integer.class), parent.get("lft").as(Integer.class), parent.get("rgt").as(Integer.class));
Order nodeLft = cb.asc(node.get("lft"));
cq.multiselect(node.get("categoryName"), cb.count(parent.get("categoryName")))
.where(p1, p2)
.groupBy(node.get("categoryName"))
.orderBy(nodeLft);

return em.createQuery(cq).getResultList();
</BBCode>

When I execue the test, it shown the following error:
</BBCode>
Testcase: testDepthOfSubtree(au.com.houseware.server.ejb.entity.facade.CategorySubcategoriesFacadeTest): Caused an ERROR
org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [au.com.houseware.server.ejb.entity.CategorySubcategories] [select new au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName, count(generatedAlias1.categoryName)) from au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias0, au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias1 where ( generatedAlias0.categoryName=:param0 ) and ( generatedAlias0.lft between generatedAlias1.lft and generatedAlias1.rgt ) group by generatedAlias0.categoryName]
java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [au.com.houseware.server.ejb.entity.CategorySubcategories] [select new au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName, count(generatedAlias1.categoryName)) from au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias0, au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias1 where ( generatedAlias0.categoryName=:param0 ) and ( generatedAlias0.lft between generatedAlias1.lft and generatedAlias1.rgt ) group by generatedAlias0.categoryName]
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1201)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:324)
at org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:227)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:441)
at au.com.houseware.server.ejb.entity.facade.CategorySubcategoriesFacadeMock.findDepthOfSubtreeBy_categoryName(CategorySubcategoriesFacadeMock.java:228)
at au.com.houseware.server.ejb.entity.facade.CategorySubcategoriesFacadeTest.testDepthOfSubtree(CategorySubcategoriesFacadeTest.java:44)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [au.com.houseware.server.ejb.entity.CategorySubcategories] [select new au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName, count(generatedAlias1.categoryName)) from au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias0, au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias1 where ( generatedAlias0.categoryName=:param0 ) and ( generatedAlias0.lft between generatedAlias1.lft and generatedAlias1.rgt ) group by generatedAlias0.categoryName]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:261)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:306)
</BBCode>

What is wrong with my criteriaQuery?

Any suggestion is very much appreciated.

Thanks
Sam

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
mgainty
Offline
Joined: 2004-05-21

can you verify this constructor exists
au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName,
count(generatedAlias1.categoryName)) ?
Martin
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> To: users@glassfish.java.net
> Subject: Very complex query with JPA CriteriaBuilder
> From: forums@java.net
> Date: Sun, 27 Mar 2011 07:36:56 -0500
>
>
>
> Hello,
> I want to write a query in jpa criteriaquery. *Here is the query:*
>
>
> SELECT node.category_name, (COUNT(parent.category_name) - 1) AS depth
> FROM category_subcategories AS node,
> category_subcategories AS parent
> WHERE node.lft BETWEEN parent.lft AND parent.rgt
> AND node.category_name = 'PORTABLE ELECTRONICS'
> GROUP BY node.category_name
> ORDER BY node.lft
>
>
>
> *Here is the code I come up with:*
>
>
> CriteriaBuilder cb = em.getCriteriaBuilder();
> CriteriaQuery cq = cb.createQuery(
> CategorySubcategories.class );
> Root node = cq.from( CategorySubcategories.class );
> Root parent = cq.from( CategorySubcategories.class );
> Predicate p1 = cb.equal(node.get("categoryName"), categoryName);
> Predicate p2 = cb.between(node.get("lft").as(Integer.class),
> parent.get("lft").as(Integer.class), parent.get("rgt").as(Integer.class));
> Order nodeLft = cb.asc(node.get("lft"));
> cq.multiselect(node.get("categoryName"),
> cb.count(parent.get("categoryName")))
> .where(p1, p2)
> .groupBy(node.get("categoryName"))
> .orderBy(nodeLft);
> return em.createQuery(cq).getResultList();
>
>
> When I execue the test, it shown the following error:
>
>
> Testcase:
> testDepthOfSubtree(au.com.houseware.server.ejb.entity.facade.CategorySubcategoriesFacadeTest):
> Caused an ERROR
> org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate
> constructor on class
> [au.com.houseware.server.ejb.entity.CategorySubcategories] [select new
> au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName,
> count(generatedAlias1.categoryName)) from
> au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias0,
> au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias1
> where ( generatedAlias0.categoryName=:param0 ) and ( generatedAlias0.lft
> between generatedAlias1.lft and generatedAlias1.rgt ) group by
> generatedAlias0.categoryName]
> java.lang.IllegalArgumentException:
> org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate
> constructor on class
> [au.com.houseware.server.ejb.entity.CategorySubcategories] [select new
> au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName,
> count(generatedAlias1.categoryName)) from
> au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias0,
> au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias1
> where ( generatedAlias0.categoryName=:param0 ) and ( generatedAlias0.lft
> between generatedAlias1.lft and generatedAlias1.rgt ) group by
> generatedAlias0.categoryName]
> at
> org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1201)
> at
> org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
> at
> org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:324)
> at
> org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:227)
> at
> org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:441)
> at
> au.com.houseware.server.ejb.entity.facade.CategorySubcategoriesFacadeMock.findDepthOfSubtreeBy_categoryName(CategorySubcategoriesFacadeMock.java:228)
> at
> au.com.houseware.server.ejb.entity.facade.CategorySubcategoriesFacadeTest.testDepthOfSubtree(CategorySubcategoriesFacadeTest.java:44)
> Caused by: org.hibernate.hql.ast.QuerySyntaxException: Unable to locate
> appropriate constructor on class
> [au.com.houseware.server.ejb.entity.CategorySubcategories] [select new
> au.com.houseware.server.ejb.entity.CategorySubcategories(generatedAlias0.categoryName,
> count(generatedAlias1.categoryName)) from
> au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias0,
> au.com.houseware.server.ejb.entity.CategorySubcategories as generatedAlias1
> where ( generatedAlias0.categoryName=:param0 ) and ( generatedAlias0.lft
> between generatedAlias1.lft and generatedAlias1.rgt ) group by
> generatedAlias0.categoryName]
> at
> org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
> at
> org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
> at
> org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
> at
> org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:261)
> at
> org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
> at
> org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
> at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:101)
> at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:80)
> at
> org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
> at
> org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
> at
> org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
> at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)
> at
> org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:306)
>
>
> What is wrong with my criteriaQuery?
> Any suggestion is very much appreciated.
> Thanks
> Sam
>
>
> --
>
> [Message sent by forum member 'samsam8899']
>
> View Post: http://forums.java.net/node/785795
>
>

samsam8899
Offline
Joined: 2010-11-04

Hello,
Thank you for the answer.
There is no Constructor like that in my CategorySubcategories class.
I just want to construct a sql with multi select clause:
SELECT node.category_name, (COUNT(parent.category_name) - 1) AS depth
FROM category_subcategories AS node,
category_subcategories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_name = 'PORTABLE ELECTRONICS'
GROUP BY node.category_name

Here is my entity class:
@Entity
@Table(name = "category_subcategories", catalog = "houseware", schema = "")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "CategorySubcategories.findAll", query = "SELECT c FROM CategorySubcategories c"),
@NamedQuery(name = "CategorySubcategories.findByCategoryId", query = "SELECT c FROM CategorySubcategories c WHERE c.categoryId = :categoryId"),
@NamedQuery(name = "CategorySubcategories.findByCategoryName", query = "SELECT c FROM CategorySubcategories c WHERE c.categoryName = :categoryName"),
@NamedQuery(name = "CategorySubcategories.findByLft", query = "SELECT c FROM CategorySubcategories c WHERE c.lft = :lft"),
@NamedQuery(name = "CategorySubcategories.findByRgt", query = "SELECT c FROM CategorySubcategories c WHERE c.rgt = :rgt")
})
public class CategorySubcategories implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Column(name = "category_id", nullable = false)
private Integer categoryId;
@Size(max = 64)
@Column(name = "category_name", length = 64)
private String categoryName;
@Column(name = "lft")
private Integer lft;
@Column(name = "rgt")
private Integer rgt;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "categoryId")
private Collection<Product> productCollection;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "categorySubcategories")
private Collection<ProductCategoryAssociate> productCategoryAssociateCollection;

public CategorySubcategories() {
}

public CategorySubcategories(Integer categoryId) {
this.categoryId = categoryId;
}

public Integer getCategoryId() {
return categoryId;
}

public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}

public String getCategoryName() {
return categoryName;
}

public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}

public Integer getLft() {
return lft;
}

public void setLft(Integer lft) {
this.lft = lft;
}

public Integer getRgt() {
return rgt;
}

public void setRgt(Integer rgt) {
this.rgt = rgt;
}

@XmlTransient
public Collection<Product> getProductCollection() {
return productCollection;
}

public void setProductCollection(Collection<Product> productCollection) {
this.productCollection = productCollection;
}

@XmlTransient
public Collection<ProductCategoryAssociate> getProductCategoryAssociateCollection() {
return productCategoryAssociateCollection;
}

public void setProductCategoryAssociateCollection(Collection<ProductCategoryAssociate> productCategoryAssociateCollection) {
this.productCategoryAssociateCollection = productCategoryAssociateCollection;
}

@Override
public int hashCode() {
int hash = 0;
hash += (categoryId != null ? categoryId.hashCode() : 0);
return hash;
}

@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof CategorySubcategories)) {
return false;
}
CategorySubcategories other = (CategorySubcategories) object;
if ((this.categoryId == null && other.categoryId != null) || (this.categoryId != null && !this.categoryId.equals(other.categoryId))) {
return false;
}
return true;
}

@Override
public String toString() {
return "au.com.houseware.server.ejb.entity.CategorySubcategories[ categoryId=" + categoryId + " ]";
}

}
Thanks a lot
Sam