4 Replies Latest reply on Jul 23, 2002 2:53 PM by justin

    Question/Issue on Too Many Number of Select Statements Being

    Ben Wong Newbie

      Hi everyone,

      We are doing some prototyping with CMP entity bean 2.0 where we have a simple entity bean going against one table. We also set the loading strategy to read-ahead on-load where the page-size is set to 10. However, when we run the code below, it looks like that the SELECT statement is executed for every get method that is called (e.g. one for taMinSale.getOrganization(), one for taMinSale.getIdPartition()) resulting in 10 SELECT statements for every iteration in the collection. We are seeing these SELECT statements in the log using DEBUG mode. My question is whether these SELECT statements are actually being called (to the database) or just to show what SQL would have run?

      If you look at the code, we are only iterating through 10 elements in the collection. So I would expect only seeing one select statement being called (since the page-size is set to 10).

      Anyway help is greatly appreciated.

      Here is the Java client code:

      import cdas.type.*;
      import javax.servlet.*;
      import javax.servlet.http.*;
      import java.util.*;
      import java.io.*;
      import javax.naming.*;
      import javax.naming.NamingException;
      import java.lang.reflect.*;

      public class ControllerServlet extends HttpServlet {
      public void service(HttpServletRequest request, HttpServletResponse response) {
      try {
      InitialContext ctx = new InitialContext();
      TaMinSaleHome taMinSaleHome = (TaMinSaleHome)ctx.lookup("TaMinSale");
      System.out.println("sreeee");
      Collection collection = taMinSaleHome.findBySalesRecType();
      System.out.println(collection.size());
      long t1 = System.currentTimeMillis();
      useTypeObject(collection);
      long t2 = System.currentTimeMillis();
      System.out.println("for object = " + (t2-t1));
      useReflection(collection);
      long t3 = System.currentTimeMillis();
      System.out.println("for reflection = " + (t3-t1));
      } catch (Exception e) {
      e.printStackTrace();
      }

      }

      private void useTypeObject(Collection collection) {
      int i = 0;
      try {
      Iterator iterator = collection.iterator();
      TaMinSale taMinSale = null;
      Sale sale = null;
      while (iterator.hasNext()) {
      taMinSale = (TaMinSale)iterator.next();
      sale = new Sale(taMinSale.getIdOrganization(),
      taMinSale.getIdPartition(),
      taMinSale.getIdDateCalendar(),
      taMinSale.getIdTimeMinEnd(),
      taMinSale.getIdServiceType(),
      taMinSale.getIdSalesRecType(),
      taMinSale.getAmtSale(),
      taMinSale.getQtySale(),
      taMinSale.getSysDateCreate(),
      taMinSale.getSysDateMod());
      i++;
      if (i % 2 == 0 ) {
      System.out.println("object = " + i);
      }
      if ( i==10)
      break;
      }
      } catch (Exception e) {
      e.printStackTrace();
      }

      }

      ...

      Here is the jbosscmp-jdbc.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <jbosscmp-jdbc>

      java:/OraclePoolDS
      <datasource-mapping>Oracle8</datasource-mapping>
      <create-table>false</create-table>
      <remove-table>false</remove-table>
      <read-only>false</read-only>
      <pk-constraint>true</pk-constraint>
      <fk-constraint>false</fk-constraint>
      <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
      <read-ahead>
      on-load
      <page-size>10</page-size>
      <eager-load-group>*</eager-load-group>
      </read-ahead>
      <list-cache-max>1000</list-cache-max>

      <enterprise-beans>

      <ejb-name>TaMinSale</ejb-name>
      <table-name>ta_min_sale_test</table-name>
      <ejb-designer-id>TaMinSale</ejb-designer-id>
      <cmp-field>
      <field-name>idDateCalendar</field-name>
      <column-name>ID_DATE_CALENDAR</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>idTimeMinEnd</field-name>
      <column-name>ID_TIME_MIN_END</column-name>
      </cmp-field>

      <cmp-field>
      <field-name>idServiceType</field-name>
      <column-name>ID_SERVICE_TYPE</column-name>
      </cmp-field>

      <cmp-field>
      <field-name>idSalesRecType</field-name>
      <column-name>ID_SALES_REC_TYPE</column-name>
      </cmp-field>

      <cmp-field>
      <field-name>idOrganization</field-name>
      <column-name>ID_ORGANIZATION</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>idPartition</field-name>
      <column-name>ID_PARTITION</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>amtSale</field-name>
      <column-name>AMT_SALE</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>qtySale</field-name>
      <column-name>QTY_SALE</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>sysDateCreate</field-name>
      <column-name>SYS_DATE_CREATE</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>sysDateMod</field-name>
      <column-name>SYS_DATE_MOD</column-name>
      </cmp-field>

      <query-method>
      <method-name>findBySalesRecType</method-name>
      <method-params />
      </query-method>
      <ejb-ql>SELECT OBJECT(g) FROM ta_min_sale_test g
      </ejb-ql>
      <read-ahead>
      on-load
      <page-size>10</page-size>
      <eager-load-group>*</eager-load-group>
      </read-ahead>


      </enterprise-beans>
      </jbosscmp-jdbc>

      I have also attached the log file if you want to see the output. Notice there are 20 select statements between the "object = " output statements.

      Thanks,
      Ben