Failing execution of dynamic query

[sunkara brahmam, modificado hace 12 años.]

Hi,
I am executing dynamic query which pulls blogs information from lportal schema using
final List<Object> results = BlogsEntryLocalServiceUtil.dynamicQuery(query);

During setting parameter to query, adding collection as parameter like
query.add(PropertyFactoryUtil.forName("entryId").in(blogIds)); here blogIds is collection which contains grater than 1000 element

So, when hibernate executing the query, bellow exception I am getting

com.liferay.portal.SystemException: org.hibernate.exception.SQLGrammarException: could not execute query
at com.liferay.portal.service.persistence.impl.BasePersistenceImpl.processException(BasePersistenceImpl.java:96)
at com.liferay.portlet.documentlibrary.service.persistence.DLFileEntryPersistenceImpl.findWithDynamicQuery(DLFileEntryPersistenceImpl.java:2789)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at com.liferay.portal.monitoring.ServiceMonitoringAdvice.invoke(ServiceMonitoringAdvice.java:205)......................

Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)

Can you pls let me know another approach let hibernate executes above kind of queries.

[David H Nebinger, modificado hace 12 años.]

I'd create a custom method to chunk it into multiple queries of 1000 each.

Since you're matching on ids, you can add all to the return list.

Something along the lines of:

List results = new LinkedList();
List localResults;

List<list<long>&gt; blogIdLists = splitBlogIdListIntoListsOf1000OrLess(blogIds);

for (List<long> sublist : blogIdLists) {
  localResults = doMyBlogsDynamicQuery(sublist);
  if ((localResults != null) &amp;&amp; (! localResults.isEmpty())) results.addAll(localResults);
}

return results;</long></list<long>

[sunkara brahmam, modificado hace 12 años.]

Tnx David,

Pls clarify on bellow

for retrieving documents (available at DLEntry), I am using bellow code to execute query ...

final DynamicQuery query = DynamicQueryFactoryUtil.forClass(DLFileEntry.class, PortalClassLoaderUtil
.getClassLoader());

query.add(PropertyFactoryUtil.forName("fileEntryId").in(documentIds));
query.addOrder(OrderFactoryUtil.desc("modifiedDate"));

final List<Object> results = DLFileEntryLocalServiceUtil.dynamicQuery(query);

Here I am getting 30000+ records and coun't find any Exception (i.e., either System or SQL Exception).

Similary for retrieving blogs (availabe at BlogEntry), I am using bellow code to build and execute query

final DynamicQuery query = DynamicQueryFactoryUtil.forClass(BlogsEntry.class, PortalClassLoaderUtil
.getClassLoader());

query.add(PropertyFactoryUtil.forName("entryId").in(blogIds));
query.addOrder(OrderFactoryUtil.desc("modifiedDate"));

final List<Object> results = BlogsEntryLocalServiceUtil.dynamicQuery(query);

In DBase, we have 27000+ records at table. but when we are using above code of Blogs, we are getting the SQL Execption which is attached in the file.


Above snippet is same, but the difference is used respective LocalServiceUtils (DLFileEntry and BlogsEntry).

Please let us know your comments on this.

[David H Nebinger, modificado hace 12 años.]

Your exception is not due to the number of records being returned, it's due to the number of items passed in the ".in(list)" portion of the dynamic query.

I'm guessing in the first case you are not passing 1000 or more documentIds, but in the second case you are passing 1000 or more blogIds.

The chunking method I outlined would basically loop you through the calls to dynamic query for the blogs always using less than 1000 blogIds and aggregating the results.

[sunkara brahmam, modificado hace 12 años.]

Yes David, in first case we'r passsing 30000+ (documentIds) in the ".in(list)" and also second cases, being passed 27000+ (blogIds) in the ".in(list)".

Pls clarify, If I chuck total list by 1000 element and execute query much times, is it leads to any performance issue?.

for example, If I have 100000 elements, chunk by 1000 and create 100 sub lists. Subsequently executing query 100 times.

Yes, I know hibernate supports first level caching (not creates query every time).

Any how I will implement chunk approach. Pls let us know if you have any other thoughts.

Thanks,

[Hitoshi Ozawa, modificado hace 12 años.]

The error you're seeing is probably from the limitation of Oracle.

http://jgvimalan.wordpress.com/2010/09/29/ora-01795-maximum-number-of-expressions-in-a-list-is-1000/

[sunkara brahmam, modificado hace 12 años.]

Tnx Ozawa, here we were getting around 30000+ documents using dynamic query as setting into .in(30000+ list). But for blogs, using same type of query setting 27000+ into .in(2700+list) and getting sql exception.

Big confussion here why same query varieing with respect to Documents and Blogs.