异常从CriteriaQuery中获取列表(Exception in getting list fr

由于某种原因，我也说不清，有例外，当我尝试使用子查询CriteriaQuery中获取列表。有人请帮助！这里是代码：

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;

}

这里是例外

内部异常：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：您的SQL语法错误; 检查对应于你的MySQL服务器版本使用附近的” .productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =‘小学’在行1个错误代码正确的语法手册：1064错误代码：1064电话：SELECT DISTINCT t0.productname FROM产品T0 WHERE t0.productscategoryid IN（SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =）？）结合=> [1个参数绑定]要求：SELECT DISTINCT t0.productname FROM产品T0其中T 0。 productscategoryid IN（SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =）？）结合=> [1个参数绑定]查询：TupleQuery（referenceClass =产品SQL =“SELECT DISTINCT t0.productname FROM产品T0 WHERE t0.productscategoryid IN（SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =）？） “）查询：TupleQuery（referenceClass =产品的sql =” SELECT DISTINCT t0.productname FROM PROD UCTS T0 WHERE t0.productscategoryid IN（SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =？））在org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException“）（QueryImpl.java:378 ）在org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException（QueryImpl.java:378）在org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery（QueryImpl.java:260）在org.eclipse.persistence。 internal.jpa.QueryImpl.getResultList（QueryImpl.java:469）在com.inventory.service.ProductsServices.getProductsDistinctBySubQueriesName（ProductsServices.java:112）在com.inventory.service.ProductsServices.getAllByName（ProductsServices.java:211）在COM .inventory.server.InventorySocketRequest.getServiceClass所致（InventorySocketRequest.java:77）在com.inventory.server.InventorySocketRequest.run（InventorySocketRequest.java:44）：异常[的EclipseLink-4002]（Eclipse持久服务 - 2.5.1。 v20130918-f2b9fc5）：org.eclipse.persistence.excepti ons.DatabaseException内部异常：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：您的SQL语法错误; 检查对应于你的MySQL服务器版本使用附近的” .productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =‘小学’在行1个错误代码正确的语法手册：1064电话：SELECT DISTINCT t0.productname FROM产品WHERE T0 t0.productscategoryid IN（SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =）？）结合=> [1参数绑定]查询：TupleQuery（referenceClass =产品SQL =“SELECT DISTINCT t0.productname FROM产品T0 WHERE t0.productscategoryid在组织（SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =？））“）在org.eclipse.persistence.exceptions.DatabaseException.sqlException（DatabaseException.java:340） .eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall（DatabaseAccessor.java:682）在org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall（DatabaseAccessor.java:558）在org.eclips e.persistence.internal.sessions.AbstractSession.basicExecuteCall（AbstractSession.java:1991）在org.eclipse.persistence.sessions.server.ServerSession.executeCall（ServerSession.java:570）在org.eclipse.persistence.internal.queries。 DatasourceCallQueryMechanism.executeCall（DatasourceCallQueryMechanism.java:242）在org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall（DatasourceCallQueryMechanism.java:228）在org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall（DatasourceCallQueryMechanism.java： 299）在org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows（DatasourceCallQueryMechanism.java:694）在org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable（ExpressionQueryMechanism.java:2738）在org.eclipse.persistence .internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows（ExpressionQueryMechanism.java:2675）在org.eclipse.persistence.queries.ReportQuery.execut eDatabaseQuery（ReportQuery.java:848）在org.eclipse.persistence.queries.DatabaseQuery.execute（DatabaseQuery.java:899）在org.eclipse.persistence.queries.ObjectLevelReadQuery.execute（ObjectLevelReadQuery.java:1127）在org.eclipse .persistence.queries.ReadAllQuery.execute（ReadAllQuery.java:403）在org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork（ObjectLevelReadQuery.java:1215）在org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery（UnitOfWorkImpl的.java：在org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery（AbstractSession.java:1793）在org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery（AbstractSession.java:1775）在组织2896）。 com.mysql：eclipse.persistence.internal.sessions.AbstractSession.executeQuery（AbstractSession.java:1740）在org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery（QueryImpl.java:258）... 5个所致.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：你有一个错误您的SQL语法; 检查对应于你的MySQL服务器版本正确的语法使用附近的手册” .productscategoryid FROM productscategory T1 WHERE（t1.productcatgoryname =‘小学’在管线1 sun.reflect.NativeConstructorAccessorImpl.newInstance0（本机方法）在阳光下。 reflect.NativeConstructorAccessorImpl.newInstance（NativeConstructorAccessorImpl.java:57）在sun.reflect.DelegatingConstructorAccessorImpl.newInstance（DelegatingConstructorAccessorImpl.java:45）在java.lang.reflect.Constructor.newInstance（Constructor.java:525）在com.mysql.jdbc .Util.handleNewInstance（Util.java:411）在com.mysql.jdbc.Util.getInstance（Util.java:386）在com.mysql.jdbc.SQLError.createSQLException（SQLError.java:1053）在com.mysql。 jdbc.MysqlIO.checkErrorPacket（MysqlIO.java:4120）在com.mysql.jdbc.MysqlIO.checkErrorPacket（MysqlIO.java:4052）在com.mysql.jdbc.MysqlIO.sendCommand（MysqlIO.java:2503）在com.mysql .jdbc.MysqlIO.sqlQueryDirect（MysqlIO.java:2664）在com.mysql.jdbc.ConnectionImpl.execSQL（ConnectionImpl.ja VA：2794）在com.mysql.jdbc.PreparedStatement.executeInternal（PreparedStatement.java:2155）在com.mysql.jdbc.PreparedStatement.executeQuery（PreparedStatement.java:2322）在org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor .executeSelect（DatabaseAccessor.java:1007）在org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall（DatabaseAccessor.java:642）... 24个

尝试使用此查询，而不是你的，这工作完全与EclipseLink的供应商，不知道您的查询不EclipseLink的工作，但在休眠的作品，我测试下面的查询，您将能够选择一个基于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);
    }

哪一类是你想搜索的类别名称..

这解决了这个问题=）。如果这个工作，不要忘了接受的答案XD。