Exception in getting list from CriteriaQuery

Question

for some reason i can't tell, there is exception when i try to get a list from CriteriaQuery using subquery. some one please help!!! here is the code:

public String[] getProductsDistinctBySubQueriesName(String category) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
    //subquery
    Subquery<Integer> subqueries = criteria.subquery(Integer.class);
    Root<Productscategory> productCategory = subqueries.from(Productscategory.class);
    subqueries.select(productCategory.<Integer>get("productscategoryid"))
            .where(builder.equal((productCategory.<String>get("productcatgoryname")), category));
    //outerquery
    Root<Products> root = criteria.from(Products.class);
    criteria.multiselect(root.get(Products_.productname)).distinct(true)
            .where(builder.in(root.get("productscategoryid")).value(subqueries));


    List<Tuple> tupleResult = em.createQuery(criteria).getResultList(); // the exception is thrown here
    String[] arrayProducts = new String[tupleResult.size()];
    for (int i = 0; i < tupleResult.size(); i++) {
        arrayProducts[i] = (String) tupleResult.get(i).get(0);
    }
    return arrayProducts;

}

here is the exception

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = 'Pri' at line 1 Error Code: 1064 Error Code: 1064 Call: SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productscategoryid IN (SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = ?)) bind => [1 parameter bound] Call: SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productscategoryid IN (SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = ?)) bind => [1 parameter bound] Query: TupleQuery(referenceClass=Products sql="SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productscategoryid IN (SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = ?))") Query: TupleQuery(referenceClass=Products sql="SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productscategoryid IN (SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = ?))") at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378) at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260) at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469) at com.inventory.service.ProductsServices.getProductsDistinctBySubQueriesName(ProductsServices.java:112) at com.inventory.service.ProductsServices.getAllByName(ProductsServices.java:211) at com.inventory.server.InventorySocketRequest.getServiceClass(InventorySocketRequest.java:77) at com.inventory.server.InventorySocketRequest.run(InventorySocketRequest.java:44) Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = 'Pri' at line 1 Error Code: 1064 Call: SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productscategoryid IN (SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = ?)) bind => [1 parameter bound] Query: TupleQuery(referenceClass=Products sql="SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productscategoryid IN (SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = ?))") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1991) at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2675) at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:848) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1793) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1775) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1740) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258) ... 5 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.productscategoryid FROM productscategory t1 WHERE (t1.productcatgoryname = 'Pri' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:525) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642) ... 24 more

Answer 1

Try to use this query instead of yours, this works perfectly with EclipseLink provider, not sure what your query is not working in eclipselink but works in hibernate, I tested the query below and you will be able to select Products based on a ProductCategoryName.

    EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaQuery");
    EntityManager em = emf.createEntityManager();

    String category = "cat2";

    CriteriaBuilder builder = em.getCriteriaBuilder();

    CriteriaQuery<Products> query = builder.createQuery(Products.class);
    Root<Products> products = query.from(Products.class);

    Subquery<Productscategory> squery = query.subquery(Productscategory.class);
    Root<Productscategory> productCategoryRoot = squery.from(Productscategory.class);
    Join<Productscategory, Products> join = productCategoryRoot.join("productsCollection");

    squery.select(productCategoryRoot)
    .where(builder.equal(join, products), builder.equal(productCategoryRoot.get("productcatgoryname"), category));

    query.where( builder.exists(squery));
    List<Products> productList = em.createQuery(query).getResultList(); 

    for (Products product : productList) {
           System.out.println(product);
    }

In which category is the name of the category you are trying to search..

This solved the problem =).. If this works, don't forget to accept the answer XD.