5 Replies Latest reply on Aug 19, 2010 3:46 AM by wdfink

    JDBC inside CMT

    mda_dk

      Hi

      My question is probably quite trivial, anyway.

       

      I have an MDB (A) where transactionattribute = NOT_SUPPORTED is declared on class level.

       

      Bean A has an associated class (B) that uses JDBC with setAutoCommit = true.

       

      Question: What are the consequences of A calling a method in B?

       

      Best reagrds

      Mads M Andersen

        • 1. Re: JDBC inside CMT
          wdfink

          How do you connect?

          Do you use a connection out of JBoss connection pool?

           

          If you use plain JDBC, JDBC-connection-parameter and connection handling within the MDB, the DB access will be commited directly after the SQL-statement.

          This is without respect whether the MDB call is successful or not (and might not what you want).

           

          If you have to use plain JDBC, use a DataSource and set the MDB CMT to REQUIRED.

          In this case the action is only commited if the MDB return without an exception.

          • 2. Re: JDBC inside CMT
            mda_dk

            Hi Wolf-Dieter

             

            Thanks for your reply.

            The actual setup is somewhat more complex than my example.

             

            I reality my MDB need to use JDBC for queries on a number of tables that are not entities and where it makes no sense to define entity objects for these tables.

             

            The JDBC connection I get from a DS.

             

            My MDB also operates on entity objects, naturally through the entityManager.

             

            Everywhere I read about transactions I run into roughly the same text, following taken from:

            http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/Transaction3.html

             

            You should not invoke any method that might interfere with the  transaction boundaries set by the container. The list of prohibited  methods follows:
            • The commit, setAutoCommit, and rollback methods of java.sql.Connection
            • The getUserTransaction method of javax.ejb.EJBContext
            • Any method of javax.transaction.UserTransaction

             

            Since I basically work with a MDB with CMT I wonder about the consequences of mixing JDBC with CMT.

             

            It is not an option to have the MDB's transaction control the completion of the JDBC queries as their completion is a precondition for making the queries on the entity objects.

             

            I've taken the consequence of not finding any satisfying answers as to why it cannot be mixed and put all of my JDBC in a BMT SLSB with AutoCommit = true and where I open and (naturally) close connection, statements and ResultSets.

             

            Again.....nowhere I've been able to find a decent explanation as to why?

             

            Thanks again. Best regards

            Mads M Andersen

            • 3. Re: JDBC inside CMT
              wdfink

              Ok,

              if you are using a DataSource and set explicit autocommit the result after return connection into the pool might be surprisingly because the connection will be reused.

              We have some trouble in the past with 'non standard' solutions, it will become difficult in case of app-server upgrade or code changes.

               

               

              But if I understand your scenario correct you do only some SELECTs vie JDBC, right?

              If so, there is no necessity for autocommit.

               

              I use JDBC also within CMT beans, I'll leave the connection unchanged and delegate the rollback to the container.

              If a RuntimeException is thrown the container rollback for you.

              If an Exception should thrown I force the rollback via <context>.setRollbackOnly()

               

              So I prefere a transactional MDB and throw an Exception to rollback or delegate to a SLSB with Tx=RequiresNew.

               

              Remember that all entity access must have a transaction, if no available the container will open one explicit (this can cause less performance)

              • 4. Re: JDBC inside CMT
                mda_dk

                Hi Wolf-Dieter

                 

                I'm not only making selects I also do inserts. And it is essential to the application that these inserts have been committed before the application begins to operate on the entities. That's the reason for the AutoCommit.

                 

                Best regards

                Mads M Andersen

                • 5. Re: JDBC inside CMT
                  wdfink

                  If the preparation must be done and committed nevertheless whether  the Entity access succeeds or not I'll use a SLSB with Tx=RequiresNew  for the JDBC access.

                   

                  If the Entity manipulation failed after this it is a business logic decision to re-queue  the message or not.