Hi Marius,
I don't know how to explain. It is an UPDATE query, not a SELECT query that
you are sending to the database, so all records in your subquery will be
updated, none will be returned. Calling setMaxResults has no effect (it will
be ignored).
If you want to both select records and update them, you'll need to break it
up and make several round trips.
Another way might be by using a native SQL query that can both update and
return queries in one operation. (I vaguely remember Oracle introducing such
feature in Oracle 9 or 10.)
Regards,
Dies
MariusW wrote:
>
> Thanks for answering, both Michael and Dies.
>
> I can't just skip the ORDER BY, as I need to be able to pick just a subset
> of the messages, and this subset MUST include the messages with the
> highest priority.
>
> Skipping setMaxResults is not an option, as tens of thousands of messages
> might be placed in this queue, and I need to be sure that each thread
> dispatching these messages only has a limited number of messages to
> handle.
>
> I could, of course, solve this by contacting the database twice. First do
> a SELECT with ORDER BY and setMaxResults to get a list of IDs, and then a
> second trip to the database to execute the UPDATE query, using the list
> from the SELECT. I was hoping there was a more elegant solution, though.
> This is the simplified process in my message queue manager:
>
> 1. Get the (e.g.) 100 messages (id's) with the highest priority (SELECT)
> 2. Flag these messages as being in process, and tag them with a unique
> value (UPDATE)
> 3. Get the 100 messages (the whole record) that were just updated in a
> List (SELECT)
> 4. Create a worker thread (using a thread pool), give it the List of
> messages and execute it (this worker will do the dispatch and subsequent
> updates to the records)
> 5. Continue from 1 until there are no pending messages.
>
> I need to tag the records so that the message queue manager does not pick
> the same messages again while a worker thread is dispatching them. Three
> roundtrips to the database seems excessive, but maybe there is no other
> way if I cannot use ORDER BY in a subquery.
>
> //Marius
>
>
> Michael Bouschen-2 wrote:
>>
>> Hi,
>>
>> I agree with Dies. The Java Persistence query language does not support
>> ORDER BY in a subquery.
>>
>> I understand you call Query.setMaxResults in order to limit the result
>> of the subquery, correct? I think that does not work, because
>> setMaxResult applies to the entire query which is an UPDATE query in
>> your case. I propose to skip the ORDER BY clause in the subquery.
>>
>> Regards Michael
>>
>>> I need it because I use setMaxResults to get only a subset of the
>>> records.
>>> And this subset must always be ordered by priority so that the messages
>>> with
>>> the highest priority is selected first. In this way, if there are 1000
>>> messages currently queued, and setMaxResults is 100, I must be sure that
>>> the
>>> most important messages are picked up first, no matter how long
>>> low-priority
>>> messages have been in the queue.
>>>
>>> If you have ideas on other ways to solve this, they are most welcome!
>>>
>>> //Marius
>>>
>>>
>>> Dies wrote:
>>>
>>>> JPQL does not allow the use of ORDER BY in a subquery:
>>>>
>>>> subquery_from_clause ::=
>>>> FROM subselect_identification_variable_declaration
>>>> {, subselect_identification_variable_declaration}*
>>>>
>>>> Why do you need it?
>>>>
>>>>
>>>>
>>>> MariusW wrote:
>>>>
>>>>> Internal Exception: line 1:273: unexpected token: ORDER
>>>>>
>>>>> In a more readable format, the EJB QL looks like this:
>>>>> UPDATE MobileMessage x
>>>>> SET x.statusmsg = 'PROCESSING',x.transporttime = :transporttime
>>>>> WHERE x.id IN (
>>>>> SELECT y.id FROM MobileMessage y
>>>>> WHERE y.gateway = :gateway
>>>>> AND y.statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
>>>>> AND ((y.timetosend IS NULL) OR (y.timetosend < :cutoff ))
>>>>> ORDER BY y.priority
>>>>> )
>>>>>
>>>>> If I translate it to standard SQL and execute it, it works fine:
>>>>> UPDATE bb_core.bb_message
>>>>> SET statusmsg = 'PROCESSING'
>>>>> WHERE id IN (
>>>>> SELECT id FROM bb_core.bb_message
>>>>> WHERE gateway = 1
>>>>> AND statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
>>>>> AND ((timetosend IS NULL) OR (timetosend < now() ))
>>>>> ORDER BY priority
>>>>> )
>>>>>
>>>>> Any tips would be greatly appreciated!
>>>>>
>>>>> // Marius
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>> --
>> *Michael Bouschen*
>> *Prokurist*
>>
>> akquinet tech_at_spree GmbH
>> Tempelhofer Ufer 23-24, D-10963 Berlin
>> Bülowstr. 66, D-10783 Berlin
>>
>> Fon: +49 30 235 520-33
>> Fax: +49 30 217 520-12
>> Email: michael.bouschen_at_akquinet.de
>> Url: www.akquinet.de <http://www.akquinet.de>
>>
>> akquinet tech_at_spree GmbH, Berlin
>> Geschäftsführung: Prof. Dr. Christian Roth, Hendrik Saly, Martin Weber
>> Amtsgericht Berlin-Charlottenburg HRB 86780
>> USt.-Id. Nr.: DE 225 964 680
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Unexpected-token%3A-ORDER-tp16582059p16584177.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.