6 Replies Latest reply on Dec 6, 2001 4:51 PM by Dain Sundstrom

    EJB QL and In expression

    Taepper Taepper Newbie

      Configuration: JBoss 3.0 , jdk 1.4.0-beta3 ( Windows ), Oracle 8.1.6

      We tried to execute following finder-method to prevent calling ten times findByPrimaryKey()


      Collection findByKeys( java.lang.String pk1,
      java.lang.String pk2,
      java.lang.String pk3,
      java.lang.String pk4,
      java.lang.String pk5,
      java.lang.String pk6,
      java.lang.String pk7,
      java.lang.String pk8,
      java.lang.String,pk9,
      java.lang.String pk10)


      <query-method>
      <method-name>findByKeys</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <ejb-ql><![CDATA[ SELECT OBJECT(o) FROM Fir o WHERE o.pK IN ( ?1,?2,?3,?4,?5,?6,?7,?8,?9,?10 ) ]]></ejb-ql>



      It seems that the query returns all rows of the table.
      Anyone encountered similiar problems and has a solution ?

        • 1. Re: EJB QL and In expression
          David Jencks Master

          Can you do this in plain oracle sql?? I never could get anything resembling this to work.

          Try

          o.Pk = ?1 or o.Pk = ?2 or ...

          • 2. Re: EJB QL and In expression
            Taepper Taepper Newbie

            In plain Oracle you can use an IN-clause. You also can use an IN-clause whith JDBC on various Databases i. e. ORACLE, MS-SQL, MS-ACCESS. Whith a testversion of BEA Weblogic 6.1 it works fine too.
            As a workaround the suggestion of an joined query by OR will work. Thanks.

            • 3. Re: EJB QL and In expression
              Dain Sundstrom Master

              What sql is the engine generating? If you turn on debugging in the jbosscmp-jdbc.xml file, JBossCMP will log (debug) the sql generated from the EJB-QL.

              -dain

              • 4. Re: EJB QL and In expression
                Taepper Taepper Newbie

                The log file contains following entries:
                [2001-12-05 12:04:51,248,CMP,DEBUG] EQL-QL: SELECT OBJECT(o) FROM Fir o WHERE o.pk in ( ?1, ?2 , ?3 , ?4 , ?5 ,?6 ,?7 ,?8 ,?9 ,?10 )
                [2001-12-05 12:04:51,418,CMP,DEBUG] Assembly: []SELECT/OBJECT/(/o/)/FROM/Fir/o^WHERE/o/./pk/in/(/?1/,/?2/,/?3/,/?4/,/?5/,/?6/,/?7/,/?8/,/?9/,/?10/)
                [2001-12-05 12:04:51,418,CMP,DEBUG] findByKeys SQL: SELECT t1_o.pk FROM Fir t1_o



                ...

                [2001-12-05 11:39:46,068,CMP,DEBUG] findByKeys command executing: SELECT t1_o.pk FROM Fir t1_o
                [2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954324229Fir-6878221951898700712
                [2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954354192Fir3300652343385115741
                [2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954394095Fir-3951422115451076574
                [2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954452113Fir2256422688763234438

                Changing to the or-clause creates following :

                [2001-12-05 11:58:13,772,CMP,DEBUG] EQL-QL: SELECT OBJECT(o) FROM Fir o WHERE o.pk = ?1 or o.pk = ?2 or o.pk =?3 or o.pk =?4 or o.pk =?5 or o.pk =?6 or o.pk =?7 or o.pk =?8 or o.pk =?9 or o.pk =?10
                [2001-12-05 11:58:14,533,CMP,DEBUG] Assembly: []SELECT/OBJECT/(/o/)/FROM/Fir/o/WHERE/o/./pk/=/?1/or/o/./pk/=/?2/or/o/./pk/=/?3/or/o/./pk/=/?4/or/o/./pk/=/?5/or/o/./pk/=/?6/or/o/./pk/=/?7/or/o/./pk/=/?8/or/o/./pk/=/?9/or/o/./pk/=/?10^
                [2001-12-05 11:58:14,533,CMP,DEBUG] findByKeys SQL: SELECT t1_o.pk FROM Fir t1_o WHERE t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ?

                • 5. Re: EJB QL and In expression
                  Dain Sundstrom Master

                  Ok, this is obviously a bug. I'll look into it.

                  • 6. Re: EJB QL and In expression
                    Dain Sundstrom Master

                    Can you post a bug report at sourceforge?