Hello Marius,
To me your main problem seems to be the JPQL does not support a way to
update and fetch records at the same time. A native query could do it.
I didn't understand your 3rd step; if you load the entities at step 1,
update them at 2, why do you need to fetch them again at step 3?
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-tp16582059p16584566.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.