Announcement Announcement Module
Collapse
No announcement yet.
When using JdbcTemplate, how do I access the connection? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • When using JdbcTemplate, how do I access the connection?

    In an EJB app, I'm using JdbcTemplate to simplify some of the SQL coding
    I must do. Basically, I've got a session bean which uses JdbcTemplate, and
    the datasource that the session bean uses is an XA datasource which
    defaults to auto-commit off.

    For various reasons, I need to turn auto-commit on in the connection that
    JdbcTemplate ends up using, and possibly invoke commit or rollback on
    the connection as well, presumably prior to closing the connection.

    How can I gain access to the connection that JdbcTemplate uses?

    Thanks!

    : jay

  • #2
    Short answer - you should not mess with the connection. Use some kind of transaction management that is available for your application server. You can use Bean Managed transactions if you want and you can also use Spring's transaction framework to demarcate your transactions. I can't see any good reasons for manipulating the connection in an EJB environment.

    Here is an example of a transaction setup where the methods in the target class are all executed within a new transaction.

    Code:
    <!--  transaction manager -->
    <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
      <property name="transactionManagerName">
        <value>javax.transaction.TransactionManager</value>
      </property>
    </bean>
    
    <!-- factory bean with all methods executed in new transaction. -->
    <bean id="senderNewTx" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
      <property name="transactionManager">
      </property>
      <property name="target">
        <bean class="org.buggybean.MyTargetClass"/>
      </property>
      <property name="transactionAttributes">
        <props>
          <prop key="*">PROPAGATION_REQUIRES_NEW</prop>
        </props>
      </property>
    </bean>
    Having said that, if you really need the Connection you can use
    Code:
    jdbcTemplate.getDataSource&#40;&#41;.getConnection&#40;&#41;
    to get hold of the COnnection object.

    Comment


    • #3
      jdbcTemplate.getDataSource().getConnection()
      I have a small question with regards to this: Does jdbcTemplate use the same connection?
      I mean, if I call getDataSource().getConnection() twice in my jdbcTemplate, am I garanteed to get the same connecition?

      Comment


      • #4
        We are using WebLogic 8.1 as our appserver. We are using distributed
        transactions as our app uses both Informix and Oracle. We are runnning
        into a situation where we receive the following exceptionr:

        java.sql.SQLException: SQL operations are not allowed with no global
        transaction by default for XA drivers. If the XA driver supports performing
        SQL operations with no global transaction, explicitly allow it by setting
        "SupportsLocalTransaction" JDBC connection pool property to true. In this
        case, also remember to complete the local transaction before using the
        connection again for global transaction, else a XAER_OUTSIDE
        XAException may result. To complete a local transaction, you can either
        set auto commit to true or call Connection.commit() or Connection.rollback().

        The basic scenario is client makes a remote method call on a session bean
        A. Bean A then makes a local method call on session bean B. B makes use
        of JdbcTemplate to do some read-only JDBC things and returns the results
        to A. A then goes on to retrieve some entity beans and possibly update
        them.

        It seemed plausible to me that perhaps the local transaction was being
        started because of the read-only JDBC operations, and so acquiring the
        connection from JdbcTemplate and invoking commit or rollback on it
        might solve the problem. Hence the original question.

        However jdbcTemplate.getDataSource().getConnection() will give me a
        brand new connection, won't it? I'll need the connection used to by
        JdbcTemplate to perform the query in order to do a commit or rollback
        on it.

        : jay

        Comment


        • #5
          Sounds like you are trying to operate outside of a global transaction. Have you tried setting B's transaction property to NotSupported and then use a non-XA JDBC driver?

          If you want to be sure to use the same connection for some processing then you can create a SingleConnectionDataSource like this:
          Code:
          		Connection singleConn = dataSource.getConnection&#40;&#41;;
          		singleConn.setAutoCommit&#40;true&#41;;
          		SingleConnectionDataSource singleDs = new SingleConnectionDataSource&#40;singleConn, true&#41;;
          		JdbcTemplate jt2 = new JdbcTemplate&#40;singleDs&#41;;
          		Number n = &#40;Number&#41; jt2.queryForObject&#40;"select count&#40;*&#41; from booking", Number.class&#41;; 
          		singleDs.destroy&#40;&#41;;
          You need to call destroy() to properly return the connection to the pool.

          Comment


          • #6
            Also, silly question but you didn't mention it....you are using the JTA transactionManager in your spring configuration, correct?

            Comment


            • #7
              Originally posted by irbouho
              jdbcTemplate.getDataSource().getConnection()
              I have a small question with regards to this: Does jdbcTemplate use the same connection?
              I mean, if I call getDataSource().getConnection() twice in my jdbcTemplate, am I garanteed to get the same connecition?
              That's a good question. No, you would actually get another connection, unless the DataSource itself is some sort of special DS (like the test one) which always returns the same connection. And you would in fact be responsible for properly closing that connection. If you want to just reuse the connection that may already be bound to the thread, and ensure that if there is none already bound there a new one is created and bound, you should actually use:

              Connection con = DataSourceUtils.getConnection(myJdbcTemplate.getDa taSource());

              That's basically what JdbcTemplate does itself, internally. Any connection you get like this will also properly be closed by the template itself (if you are using it inside the template in an execute callback) or the enclosing transaction, depending on where the connection is actually synchronized.

              Regards,

              Comment


              • #8
                Originally posted by ryan.tyer
                Also, silly question but you didn't mention it....you are using the JTA transactionManager in your spring configuration, correct?
                No, that's not a silly question. I have no spring configuration other than its
                default. I started using Spring because I had started going down the path
                of developing something very much like JdbcTemplate, and then stumbled
                across Spring and began using its JDBC support because it did almost
                everything I needed, and it seemed to work "out-of-the-box".

                Should I be using the JTA transaction manager? Judging by its name, I
                think so. How do I configure it? To this point, I've really made use of very
                little of Spring other than JdbcTemplate.

                : jay

                Comment


                • #9
                  Originally posted by trisberg
                  Code:
                  <!--  transaction manager -->
                  <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
                    <property name="transactionManagerName">
                      <value>javax.transaction.TransactionManager</value>
                    </property>
                  </bean>
                  I think there is some typo here... transactionManagerName property expects the JNDI name of the TM of the given container... no?

                  Code:
                  <!--  transaction manager -->
                  <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
                    <property name="transactionManagerName">
                      <value>java&#58;/TransactionManager</value>
                    </property>
                  </bean>
                  on JBoss, and see Spring JavaDoc JtaTransactionManager for other containers.

                  Bye,
                  cstamas

                  Comment

                  Working...
                  X