2 Replies Latest reply on May 12, 2005 2:59 AM by Harry

    Mixed JDBC and CMP access within a single session method

    Nick Ryan Newbie

      We've got a situation where we need to update entity values using JDBC statements and then access them for reading via CMP methods. This is to be done within a single stateless session bean method.

      I think this is a fairly odd requirement but from what I understand it should be possible. Please correct me if I am wrong.

      I thought that within a single transacted session method and using the same datasource used for CMP when a connection is opened it should be the same as used for CMP updates as it should be registered with the method's transaction.

      From the debug logs it doesn't seem to work like this. And to be honest from what I see there I feel I may be missing something fundamental.

      We're using JBoss 3.2.2 and Oracle 8.1.7 but have also tried with JBoss 3.2.6

      We are using the local-tx-datasource definition for Oracle that comes with JBoss.
      We have also tried using Hypersonic as a datasource with the same results.

      Here is the session method code we're trying to run:

      public void testConnections() {
      
       Connection conn = null;
      
       try {
      
       Context ctx = new InitialContext();
       Object o = ctx.lookup("java:comp/env/TestEntityLocal");
       TestEntityLocalHome home = (TestEntityLocalHome)o;
      
      
       TestEntityLocal entity = home.create();
       System.out.println("testField before setting : " + entity.getTestField());
       entity.setTestField(new Integer(1000));
       System.out.println("testField after setting, before JDBC update (CMP view) : " + entity.getTestField());
      
       DataSource ds = (DataSource)ctx.lookup("java:DefaultDS");
       conn = ds.getConnection();
      
       // JDBC select
       int jdbcView = -1;
       PreparedStatement statement = conn.prepareStatement("select testfield from testentity where id = ?");
       statement.setLong(1, entity.getId().longValue());
       ResultSet rs = statement.executeQuery();
       if (rs.next()) {
       jdbcView = rs.getInt(1);
       }
      
       System.out.println("testField after setting, before JDBC update (JDBC view) : " + jdbcView);
       rs.close();
       statement.close();
      
       // JDBC update
       statement = conn.prepareStatement("update testentity set testfield = ? where id = ?");
       statement.setLong(1, 9999);
       statement.setLong(2, entity.getId().longValue());
       statement.executeUpdate();
      
       // JDBC select
       jdbcView = -1;
       statement = conn.prepareStatement("select testfield from testentity where id = ?");
       statement.setLong(1, entity.getId().longValue());
       rs = statement.executeQuery();
       if (rs.next()) {
       jdbcView = rs.getInt(1);
       }
      
       System.out.println("testField after JDBC update (JDBC view) : " + jdbcView);
       rs.close();
       statement.close();
      
       System.out.println("testField after JDBC update (CMP view) : " + entity.getTestField());
      
       entity.remove();
      
       conn.close();
      
       } catch (Exception e) {
       e.printStackTrace();
       }
       finally {
       try {
       conn.close();
       } catch (Exception e1) {}
       }
      
       System.out.println("End Test");
       }
      



      The end result is:
      20:20:47,094 INFO [STDOUT] testField before setting : null
      20:20:47,101 INFO [STDOUT] testField after setting, before JDBC update (CMP view) : 1000
      20:20:47,111 INFO [STDOUT] testField after setting, before JDBC update (JDBC view) : 0
      20:20:47,149 INFO [STDOUT] testField after JDBC update (JDBC view) : 9999
      20:20:47,150 INFO [STDOUT] testField after JDBC update (CMP view) : 1000
      20:20:47,173 INFO [STDOUT] End Test
      


      Obviously we would want the values to be consistent regardless of the access method.

      Can anyone give us some insight on this? (before I go mad)

      Thanks!



      Here is the datasource definition used with Hypersonic:

      <?xml version="1.0" encoding="UTF-8"?>
      
      <datasources>
       <local-tx-datasource>
      
       <jndi-name>DefaultDS</jndi-name>
       <connection-url>jdbc:hsqldb:hsql://localhost:1701</connection-url>
       <driver-class>org.hsqldb.jdbcDriver</driver-class>
       <user-name>sa</user-name>
       <password></password>
       <min-pool-size>5</min-pool-size>
       <max-pool-size>20</max-pool-size>
       <idle-timeout-minutes>5</idle-timeout-minutes>
       <track-statements/>
       <security-domain>HsqlDbRealm</security-domain>
       <prepared-statement-cache-size>32</prepared-statement-cache-size>
       </local-tx-datasource>
       <mbean code="org.jboss.jdbc.HypersonicDatabase"
       name="jboss:service=Hypersonic">
       <attribute name="Port">1701</attribute>
       <attribute name="Silent">true</attribute>
       <attribute name="Database">default</attribute>
       <attribute name="Trace">false</attribute>
       <attribute name="No_system_exit">true</attribute>
       </mbean>
      </datasources>
      


      Here is the ejb-jar.xml :

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">
      
      <ejb-jar >
      
       <description>[CDATA[No Description.]]</description>
       <display-name>Generated by XDoclet</display-name>
      
       <enterprise-beans>
      
       <!-- Session Beans -->
       <session >
       <description>[CDATA[Description for TestStatelessSession]]</description>
       <display-name>Name for TestStatelessSession</display-name>
      
       <ejb-name>TestStatelessSession</ejb-name>
      
       <home>test.interfaces.TestStatelessSessionHome</home>
       <remote>test.interfaces.TestStatelessSession</remote>
       <ejb-class>test.ejb.TestStatelessSessionBean</ejb-class>
       <session-type>Stateless</session-type>
       <transaction-type>Container</transaction-type>
      
       <ejb-local-ref >
       <ejb-ref-name>TestEntityLocal</ejb-ref-name>
       <ejb-ref-type>Entity</ejb-ref-type>
       <local-home>test.interfaces.TestEntityLocalHome</local-home>
       <local>test.interfaces.TestEntityLocal</local>
       <ejb-link>TestEntity</ejb-link>
       </ejb-local-ref>
      
       </session>
      
       <!-- Entity Beans -->
       <entity >
       <description>[CDATA[Description for TestEntity]]</description>
       <display-name>Name for TestEntity</display-name>
      
       <ejb-name>TestEntity</ejb-name>
      
       <local-home>test.interfaces.TestEntityLocalHome</local-home>
       <local>test.interfaces.TestEntityLocal</local>
      
       <ejb-class>test.ejb.TestEntityBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Long</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>TestEntity</abstract-schema-name>
       <cmp-field >
       <description>[CDATA[Getter for CMP Field TestField]]</description>
       <field-name>testField</field-name>
       </cmp-field>
       <cmp-field >
       <description>[CDATA[Getter for CMP Field id]]</description>
       <field-name>id</field-name>
       </cmp-field>
       <primkey-field>id</primkey-field>
      
       </entity>
      
       </enterprise-beans>
      
       <assembly-descriptor >
       <container-transaction >
       <method >
       <ejb-name>TestEntity</ejb-name>
       <method-name>*</method-name>
       </method>
       <trans-attribute>Required</trans-attribute>
       </container-transaction>
       <container-transaction >
       <method >
       <ejb-name>TestStatelessSession</ejb-name>
       <method-name>*</method-name>
       </method>
       <trans-attribute>Required</trans-attribute>
       </container-transaction>
       </assembly-descriptor>
      
      </ejb-jar>


      And here is the jboss.xml :
      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE jboss PUBLIC "-//JBoss//DTD JBOSS 3.0//EN" "http://www.jboss.org/j2ee/dtd/jboss_3_0.dtd">
      
      <jboss>
      
       <enterprise-beans>
      
       <entity>
       <ejb-name>TestEntity</ejb-name>
       <local-jndi-name>TestEntityLocal</local-jndi-name>
       <configuration-name>Standard CMP 2.x EntityBean</configuration-name>
      
       </entity>
      
       <session>
       <ejb-name>TestStatelessSession</ejb-name>
       <jndi-name>ejb/TestStatelessSession</jndi-name>
       <configuration-name>Standard Stateless SessionBean</configuration-name>
      
       </session>
      
       </enterprise-beans>
      
       <resource-managers>
       </resource-managers>
      
      </jboss>