finder method driving me crazy!!!

Discussions

EJB programming & troubleshooting: finder method driving me crazy!!!

  1. finder method driving me crazy!!! (2 messages)

    I am developing a finder method for a CMP EJB (Oracle JDeveloper and 9iAS) which is supposed to retrieve a "page" of records from a database, this is its definition in my EJB Home interface:

    Collection findPage(Long p0, Long p1) throws RemoteException, FinderException;

    This is it's definition in my orion-ejb-jar.xml file:

    <finder-method partial="True" query="$id_usuario IN
    (SELECT id_usuario FROM
    (SELECT ROWNUM NUM, USUARIO.id_usuario FROM USUARIO ORDER BY USUARIO.CLAVE)
    WHERE NUM BETWEEN $1 AND ($1+($2-1)))">
                <method>
                   <ejb-name>ebUsuario</ejb-name>
                   <method-name>findAllPaged</method-name>
                   <method-params>
                      <method-param>java.lang.Long</method-param>
                      <method-param>java.lang.Long</method-param>
                   </method-params>
                </method>
             </finder-method>

    although it retrieves pages correctly, the records are not retrieved in the correct order (CLAVE)... I have tested the query and it works ok, but the bean does not do as expected...

    What am I doing wrong... I am almost desperate...
  2. Er ORDER BY is not actually in the ejb 2.0 spec. Maybe read your Orion manual and see if they have _actually_ implemented or not.

    Question: What does your finder return? A Collection? If so - Collections do not guarantee to maintain order.

    Cheers,

    Daniel.
  3. Well, I have solved the problem... and it had nothing to do with the J2EE server, or at least not with Oracle's.

    After testing the query outside the EJB I found that the query did not brought records in the expected order if I wrote the query as I did. I had to rewrite it to include a Sub-select from my source table ordering by the field I desired, like in:

    (SELECT id_usuario FROM
    (SELECT ROWNUM NUM, USUARIO.id_usuario FROM (SELECT USUARIO.id_usuario FROM USUARIO ORDER BY USUARIO.CLAVE))
    WHERE NUM BETWEEN $1 AND ($1+($2-1)))"

    See the inner sub-select instead of simply ordering.... well doing this either in any sql-tool or in my EJB the records were retrieved in the expected order.

    Thanks, for your help!