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
--
[Message sent by forum member 'samsam8899']
View Post: http://forums.java.net/node/785795