10 Replies Latest reply on Mar 17, 2002 11:49 AM by Jürgen Zeller

    findByPrimaryKeys with collection as argument

    Jürgen Zeller Newbie

      Hi,

      i'm trying to formulate a query in EJB QL that returns
      a number of Beans from their primary keys.

      Basically, i want a "findByPrimaryKeys" instead
      of a "findByPrimaryKey" for better performance.

      Unfortunately, it seams that EJB QL doesn't support
      that?!
      Any hints are welcome!

      Bye,

      Jürgen

        • 1. Re: findByPrimaryKeys with collection as argument
          Armin Newbie

          You could create a an home method, maybe called collectionByPKS(Collection col). The spec prohibits using find as a prefix for methods other than finders, so unfortunately it won't look like a finder. In the ejbHome method just iterate through the collection and call findByPrimaryKey(), adding the results to an ArrayList. This could be really slow, however if the beans are cached it might not be that bad. I tried something like this with weblogic, as a workaround for a problem with the cache. The performance was acceptable. Kind of kludgy though.

          • 2. Re: findByPrimaryKeys with collection as argument
            Mac Rinehart Newbie

            Another angle is to do the home method, and then provide an ejbSelect method for the underlying implementation of the query. I'm not sure if that will give you a performance boost.

            • 3. Re: findByPrimaryKeys with collection as argument
              Jürgen Zeller Newbie

              From our experience, the most important point for
              good CMP performance is to avoid multiple round
              trips to the SQL server.

              When there is no way to have a findByPrimaryKeys(),
              as soon as you get "real" data, you get performance
              problems.

              I'll check on the weekend for a solution, perhaps
              with using jboss-sql instead of EJB QL.

              • 4. Re: findByPrimaryKeys with collection as argument
                Mac Rinehart Newbie

                You might also think of this as a data model problem. If you want to get a set of entities and you have to do it with a collection value, then a simple solution is to find a term that meaningfully describes the set and create a column in your table that contains that term. Then you can use the value of that term as an argument for returning a set.

                Perhaps you can create a "categories" column, or a "types" column in your table that organizes the contents of the table and eliminates the need for a collection value argument. Just and thought. - Mac

                • 5. Re: findByPrimaryKeys with collection as argument
                  Dain Sundstrom Master

                  You can use dynamic-ql in JBoss 3.0 to do this. I have an example in the new yet-to-be-finished docs along these lines.

                  • 6. Re: findByPrimaryKeys with collection as argument
                    Jürgen Zeller Newbie

                    > You might also think of this as a data model problem.
                    > [..]
                    you're right, but in many cases the data model is
                    "historical" and can't be changed :-(

                    I'm very curious for Dain's example!

                    • 7. Re: findByPrimaryKeys with collection as argument
                      Dain Sundstrom Master

                      It's not that difficult.

                      Note: this will only work in 3.0beta2

                      Here is some code I wrote from rote, so it may not even compile, but you'll get the idea.

                      public abstract class OrderBean implements EntityBean {
                       public Set getOrdersToStates(Collection states) {
                       StringBuffer jbossQl = new StringBuffer();
                       jbossQl.append("SELECT OBJECT(o) ");
                       jbossQl.append("FROM OrderEJB o ");
                       jbossQl.append("WHERE o.state IN (");
                       for(Iterator iter=states.iterator(); iter.hasNext(); ) {
                       jbossQL.append("'").append(iter.next()).append("'");
                       if(iter.hasNext()) {
                       jbossQL.append(", ");
                       }
                       }
                       jbossQL.append(") ORDER BY o.state");
                      
                       return ejbSelectGeneric(jbossQL.toString(), new Object[] {});
                       }
                      
                       public abstract Set ejbSelectGeneric(String jbossQl, Object[] arguments)
                       throws FinderException;
                      }
                      

                      The declaration in the jbosscmp-jdbc.xml
                      <query>
                       <query-method>
                       <method-name>ejbSelectGeneric</method-name>
                       <method-params>
                       <method-param>java.lang.String</method-param>
                       <method-param>java.lang.Object[]</method-param>
                       </method-params>
                       </query-method>
                      
                       <declared-ql/>
                      
                       <read-ahead><strategy>on-load</strategy></read-ahead>
                      </query>
                      


                      As I said this may not be correct, but you get the idea.

                      • 8. Re: findByPrimaryKeys with collection as argument
                        Jürgen Zeller Newbie

                        Hi,

                        i tried the finder, but failed due to a strange bug. As it turned out,
                        the bug is even triggered when i don't use the JBossQL, but any
                        standard EJB finder.

                        I used JBoss 3.0.0b2, CVS version of today.

                        Bug description
                        ===============
                        From the client/a session bean, i do the following:
                        [...]
                        List al= new ArrayList(n);
                        for (int j= 0; j < n; j++)
                        {
                        Integer a= new Integer(1*10000000+i*10000000+0*10000+n*100+j);
                        al.add(a);
                        }
                        [...]
                        Context context= new InitialContext();
                        result = context.lookup("java:comp/env/ejb/e1LocalHome");
                        mE1Home= (e1Home)result;
                        [...]
                        results= mE1Home.findByPrimaryKeys(al);

                        In results are now the (right) beans, but when i try to access a bean, like in

                        Iterator i= results.iterator();
                        while (i.hasNext())
                        {
                        e1 result= (e1) i.next();

                        s1 r= new s1();
                        r.setA(result.getA().intValue());
                        r.setB(result.getB());
                        rl.add(r);
                        }

                        i get an SQLException, triggerd by the "getA()" call:

                        2002-03-17 15:48:02,380 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=m3
                        2002-03-17 15:48:02,380 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is null
                        2002-03-17 15:48:02,380 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for m3
                        2002-03-17 15:48:02,380 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx null
                        2002-03-17 15:48:02,390 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Starting new tx TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,460 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=findByPrimaryKeys
                        2002-03-17 15:48:02,460 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,460 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for findByPrimaryKeys
                        2002-03-17 15:48:02,470 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,470 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=findByPrimaryKey
                        2002-03-17 15:48:02,500 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,510 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for findByPrimaryKey
                        2002-03-17 15:48:02,510 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,540 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.e1EJB.findByPrimaryKey] Executing SQL: SELECT a FROM TAB1 WHERE a=?
                        2002-03-17 15:48:02,640 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.e1EJB.findByPrimaryKey] Set parameter: index=1, jdbcType=INTEGER, value=10000100
                        2002-03-17 15:48:02,720 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.e1EJB.a] Get result: index=1, javaType=java.lang.Integer, Simple, value=10000100
                        2002-03-17 15:48:02,770 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
                        2002-03-17 15:48:02,770 TRACE [org.jboss.ejb.plugins.LogInterceptor] End method=findByPrimaryKey
                        2002-03-17 15:48:02,770 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
                        2002-03-17 15:48:02,770 TRACE [org.jboss.ejb.plugins.LogInterceptor] End method=findByPrimaryKeys
                        2002-03-17 15:48:02,780 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=getA
                        2002-03-17 15:48:02,780 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,780 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for getA
                        2002-03-17 15:48:02,780 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,780 TRACE [org.jboss.ejb.plugins.EntityLockInterceptor] Begin invoke, key=e1EJB:10000100
                        2002-03-17 15:48:02,800 TRACE [org.jboss.ejb.plugins.AbstractInstanceCache] Activated bean e1EJB with id = e1EJB:10000100
                        2002-03-17 15:48:02,800 TRACE [org.jboss.ejb.plugins.EntityInstanceInterceptor] Begin invoke, key=e1EJB:10000100
                        2002-03-17 15:48:02,810 TRACE [org.jboss.ejb.plugins.EntitySynchronizationInterceptor] invoke called for ctx org.jboss.ejb.EntityEnterpriseContext@ff555b88, tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,810 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.e1EJB] RESET PERSISTENCE CONTEXT: id=e1EJB:10000100
                        2002-03-17 15:48:02,820 TRACE [org.jboss.ejb.plugins.cmp.jdbc.ReadAheadCache.e1EJB] load data: entity=e1EJB pk=e1EJB:10000100
                        2002-03-17 15:48:02,830 TRACE [org.jboss.ejb.plugins.cmp.jdbc.ReadAheadCache.e1EJB] No preload data found: entity=e1EJB pk=e1EJB:10000100
                        2002-03-17 15:48:02,830 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.e1EJB] Default eager-load for entity
                        2002-03-17 15:48:02,840 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.e1EJB] Executing SQL: SELECT b FROM TAB1 WHERE (a=?)
                        2002-03-17 15:48:02,840 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.e1EJB.a] Set parameter: index=1, jdbcType=INTEGER, value=e1EJB:10000100
                        2002-03-17 15:48:02,850 TRACE [org.jboss.ejb.plugins.EntitySynchronizationInterceptor] loadEntity Exception, clear tx for ctx=org.jboss.ejb.EntityEnterpriseContext@ff555b88, tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=saudorn//1, BranchQual=]
                        2002-03-17 15:48:02,850 TRACE [org.jboss.ejb.plugins.EntityInstanceInterceptor] Ending invoke, exceptionThrown, ctx=org.jboss.ejb.EntityEnterpriseContext@ff555b88
                        2002-03-17 15:48:02,850 TRACE [org.jboss.ejb.plugins.EntityInstanceInterceptor] End invoke, key=e1EJB:10000100, ctx=org.jboss.ejb.EntityEnterpriseContext@ff555b88
                        2002-03-17 15:48:02,860 TRACE [org.jboss.ejb.plugins.EntityLockInterceptor] End invoke, key=e1EJB:10000100
                        2002-03-17 15:48:02,860 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
                        2002-03-17 15:48:02,870 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackException, causedBy:
                        java.sql.SQLException: ERROR: parser: parse error at or near ":"



                        the entity bean hoam is very simple:
                        public interface e1Home extends EJBLocalHome
                        {
                        public e1 create(Integer pA, String pB);
                        public e1 findByPrimaryKey (Integer pKey) throws FinderException;
                        public Collection findByPrimaryKeys(Collection pKeys) throws FinderException;
                        }

                        and the only real method in e1Bean is:

                        //
                        // simulate a "real" findByPrimaryKeys(), which is not possible with standard EJB 2.0
                        //
                        public Collection ejbFindByPrimaryKeys(Collection pKeys) throws FinderException
                        {
                        Collection beans= new ArrayList(pKeys.size());
                        e1Home home = (e1Home) mEntityContext.getEJBLocalHome();

                        for (Iterator i= pKeys.iterator(); i.hasNext(); )
                        {
                        Integer key= (Integer)i.next();
                        e1 bean= home.findByPrimaryKey(key);
                        beans.add(bean);
                        }

                        return beans;
                        }

                        What's going on? I first thougt that ejbFindByPrimaryKeys() doesn't work, but it
                        does; it seams that the loading durch getA() fails.

                        Bye,

                        Jürgen

                        • 9. Re: findByPrimaryKeys with collection as argument
                          Dain Sundstrom Master

                          First off, I said that I did't actually run this my self.

                          Also, I haven't checked in the code that allows parameters in the IN clause. Switch it to use the = operator instead. Also, you should be using the cvs source.

                          • 10. Re: findByPrimaryKeys with collection as argument
                            Jürgen Zeller Newbie

                            Dain,

                            i use the CVS source, but the bug i triggered is _not_
                            related to your suggestion at all.

                            I seams to be a general bug, i'm currently searching
                            the source.
                            For some unknown reason the code takes the bean objekt,
                            not the PK objekt (Integer) as a parameter.

                            Bye,

                            Jürgen