由于某些原因，我无法判断，当我尝试使用子查询从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语法有错误；在第1行的'.productscategoryid FROM productscategary t1 WHERE(t1.productgoryname='Pri'错误代码：1064错误代码：1064调用：SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productionscategoryid IN(SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE(t1.productiongoryname=？))bind=>[1参数绑定]调用：SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productionscategoryid IN(SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE(t1.productiongoryname=？))bind=>[1参数绑定]查询：TupleQuery(referenceClass=Productssql="SELECT DISTINCT t0.productname FROM Products t0 WHERE t0.productionscategoryid IN(SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE(t1.productiongoryname=？))")查询：TupleQuery(referenceClass=Productssql="SELECT DISTINCT t0.productname FROM Products t0 WHERE t0.productionscategoryid IN(SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE(t1.productiongoryname=？))")在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.exceptions.DatabaseException内部异常：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：您的SQL语法有错误；在第1行的'.productscategoryid FROM productscategary t1 WHERE(t1.productgoryname='Pri'错误代码：1064调用：SELECT DISTINCT t0.productname FROM products t0 WHERE t0.productionscategoryid IN(SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE(t1.productiongoryname=？))bind=>[1参数绑定]查询：TupleQuery(referenceClass=Productssql="SELECT DISTINCT t0.productname FROM Products t0 WHERE t0.productionscategoryid IN(SELECT t1.productscategoryid.t1.productscategoryid FROM productscategory t1 WHERE(t1.productiongoryname=？))")位于org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)网址：org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)网址：org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)网址：org.eclipse.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.querys.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)网址：org.eclipse.persistence.internal.querys.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2675)网址：org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:848)在org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)位于org.eclipse.persistence.queries.ObjectLevelReadQuery.execure(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:2896)网址：org.eclipse.persistence.internal.sessions。AbstractSession。executeQuery(AbstractSession.java：1793)网址：org.eclipse.persistence.internal.sessions。AbstractSession。executeQuery(AbstractSession.java：1775)网址：org.eclipse.persistence.internal.sessions。AbstractSession。executeQuery(AbstractSession.java：1740)网址：org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)…还有5个引起原因：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：您的SQL语法有错误；在第1行的'.productscategoryid FROM productscategary t1 WHERE(t1.productgoryname='Pri'在sun.reflect.NativeConstructorAccessorImpl.newInstance0(本机方法)在sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessor Impl.java:57)在sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessor Impl.java:45)位于java.lang.reflect.Constructure.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.java:2794)网址：com.mysql.jdbc.PreparedStatement.executeInternal(PreparedSStatement.java:2155)网址：com.mysql.jdbc.PreparedStatement.executeQuery(PreparedSStatement.java:2232)网址：org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007)网址：org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)…还有24个
尝试使用此查询而不是您的查询，这与EclipseLink提供程序非常配合，不确定您的查询在EclipseLink中不起作用，但在hibernate中起作用，我测试了下面的查询，您将能够根据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。