Announcement Announcement Module
Collapse
No announcement yet.
Spring Transaction with simple JDBC. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring Transaction with simple JDBC.

    Hi,

    I have an old application which is using the simple JDBC, means Statements and PreparedStatments, for the Database query and update.
    Now I am integrating this application with REST web service. For this I am using Spring's REST implementation and I also want to use the Spring's Transaction management. For that I have configured everything like transactionManager bean with the existing Datasource (which is being used in whole application), transaction annotation driven tag, and declared the @Transaction annotation on the service methods.

    But when I invoke the service methods those do not take part in transaction, I mean some table got updated even some statements throw exception. I have not changed anything in my DAOs and I can not change those as because same are being used in other part of application.

    I searched everywhere but what I got is, they are using some sort of Templates like JDBCTemplate, which are provided by the Spring itself. Is it necessary to use JDBCTemplate to take part into a transaction? If no, then where am I mistaking?

    Any help regarding this will be appropriated.
    Thax

  • #2
    No you don't... That is why the TransactionAwareDataSourceProxy exists... I suggest a read of chapter 12 of the reference guide...

    Wrap the DataSource with this class and let all your daos use the wrapped datasource and presto they should participate in the spring transaction.

    Originally posted by springWSDev
    I searched everywhere
    Apparently not (or at least not in the most obvious location) .

    Comment


    • #3
      Hi,

      Thanks for reply. Could you give me some link or reference for that, because I am new to these things. Any example or sample code would help.

      Thanks

      Comment


      • #4
        Have you actually read my post..

        Originally posted by mdeinum
        I suggest a read of chapter 12 of the reference guide...
        I expect that you know the existence of the springsource.org website.

        Comment


        • #5
          Hi,

          I did read that chapter. But that has nothing much just information about the class:-
          Here is the link from where I read.
          http://static.springsource.org/sprin...ence/jdbc.html

          And I did use this but I dont know whether I am doing right way.

          That is why asked for any example.

          Thnx

          Comment


          • #6
            As I stated simply wrap your datasource with that class and use it as the datasource... Nothing more, nothing less. That way your existing (old-style) jdbc code will participate in a spring managed transaction.

            Comment


            • #7
              Hi,

              I have been doing same as you stated. I am attaching my code

              these are the configuration files.
              Code:
              	<bean id="proxyDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
              		<property name="targetDataSource" ref="dataSource" />
              	</bean>
              	
              	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
              		<property name="dataSource" ref="dataSource"/>
              	</bean>
              	
              	<bean class="com.test.common.RestUtil" name="CommonComponent">
                    	<property name="serviceComponent">
                       	<value>${component.name}</value>
                    	</property>
                    	<property name="authenticator">
              			<ref bean="Authenticator" />
              		</property>
                    	<property name="componentOracleMap">
              			<map>
              				<entry key="GUI">
              					<ref bean="guiOracle" />
              				</entry>
              			</map>
              		</property>
              		<!-- For all requests from components not in the map above, use defaultDataSource below -->
              		<property name="defaultDataSource">
              			<ref bean="oracle" />
              		</property>
              	</bean>
              Code:
              	<bean name="oracle" class="com.test.Oracle">
              		<property name="dataSource">
              			<ref bean="dataSource" />
              		</property>
              		<property name="batchLimit">
              			<value>0</value>
              		</property>
              		<property name="writeDB">
              			<value>true</value>
              		</property>
              		<property name="schemaOwner">
              			<value>${schema}</value>
              		</property>
              	</bean>
              	
              	<bean id="dataSource" class="com.test.OracleDataSource">
              		<property name="URL">
              			<value>${datasource.url}</value>
              		</property>
              		<property name="user">
              			<value>${datasource.user}</value>
              		</property>
              		<property name="password">
              			<value>${datasource.password}</value>
              		</property>
              		<property name="useConnectionCache">
              			<value>true</value>
              		</property>
              		<property name="minConnections">
              			<value>${datasource.minConnections}</value>
              		</property>
              		<property name="maxConnections">
              			<value>${datasource.maxConnections}</value>
              		</property>
              		<property name="initialConnections">
              			<value>${datasource.initialConnections}</value>
              		</property>
              		<property name="loginTimeout">
              			<value>5</value>
              		</property>
              		<property name="inactivityTimeout">
              			<value>1800</value>
              		</property>
              		<property name="propertyCheckInterval">
              			<value>900</value>
              		</property>
              	</bean>
              If you see the above configuration, I have datasource class which is extending the oracle.jdbc.pool.OracleDataSource Class. There we are managing connection cache, so that is injected in the Oracle class where I have methods which does all the stuff over connection. Those methods are being called in RestUtill class. In RestUtill Class I am getting new connection and putting into the some Object which I am passing to DAOs to handle the DB.

              Code:
              		conn = oracleToUse.getConnection();
              		try {
              			RequestArguments requestArgs = 
              					(RequestArguments) myBeanFactory.getBean("requestArgs");
              			
              			//Create the request arguments we need
              			requestArgs.setConnection(conn);
              			requestArgs.setRequestId(request.getId());
              			requestArgs.setComponent(componentName);
              			
              			return requestArgs;
              I am getting Connection from following method of Oracle class.

              Code:
                  Connection conn = DataSourceUtils.getConnection(getDataSource());
                  if (conn == null) {
                      throw new SQLException("No database connection is currently available. Please retry your request at a later time.");
                  }
              After this I am altering the schema name, which is dynamic and coming from properties file.
              So I dont know, where am I doing mistake? Could please see the code and let me know any mistake I am doing.

              Thax

              Comment


              • #8
                So I dont know, where am I doing mistake? Could please see the code and let me know any mistake I am doing.
                You aren't using the proxy... You are injecting the plain datasource and not the proxy... Your bean named oracle should reference the proxies datasource NOT the plain datasource.

                Comment


                • #9
                  But the Datasource I am injecting in Oracle that should be 'oracle.jdbc.pool.OracleDataSource', otherwise it will throw ClassCastException in the Oracle class where I am configuring the cache for that.

                  Code:
                      myCacheManager = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
                      myCacheManager.createCache((OracleDataSource)myDataSource, 
                              ((OracleDataSource)myDataSource).getConnectionCacheProperties());
                  So, can I do any work around for that?

                  Thax

                  Comment


                  • #10
                    In general when you need such thing you are on a slippery slope...

                    have datasource class which is extending the oracle.jdbc.pool.OracleDataSource Class
                    What I don't really get is why you are extending the DataSource (as you mentioned judging from the quote above).

                    Basically I'm a bit lost on what you try to accomplish (or at least how you are trying to accomplish it) it looks like you are trying to hack your way and implement some ConnectionPooling yourself (whereas oracle already provides that) although not sure about that . Anywayz the class that really uses the datasource and not depending on some custom implementation (I suspect that is the RestUtil class) should use the proxied instance instead of the plain datasource.

                    Comment


                    • #11
                      Hi,

                      Thanks for the help. actually my most of the code is legacy code and I have to use the existing code and cant change that as well. But I got a way to accommodate your suggestion and it did not resolve the problem.

                      Thnx again...

                      Comment


                      • #12
                        Hi Marten,

                        How should I take Connection from the Datasource.
                        Code:
                        private DataSource myDataSource;
                        Connection conn = myDataSource.getConnection();
                        Here DataSource is the TransactionAwareDataSourceProxy type, which is being injected by Spring. Configuration for the same is as following.

                        Code:
                        	<bean id="proxyDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
                        		<property name="targetDataSource" ref="dataSource" />
                        	</bean>
                        
                        	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                        		<property name="dataSource" ref="dataSource"/>
                        	</bean>
                        
                        	<bean name="oracleRest" class="com.test.Oracle">
                                        <property name="dataSource">
                        			<ref bean="proxyDataSource" />
                        		</property>
                        	</bean>
                        I debug my code and what I found that in connection Object autoCommit is true and transaction level is 2. I do not where I am missing a thing or two.

                        Thanks for the help.

                        Comment


                        • #13
                          That should do the trick... The connection should be a dynamic object if correctly configured the Connection should be of the type ConnectionProxy. Next to that you should configure transactions which is more then adding a TransactionManager. YOu have to tell where to start/end transactions (I suggest a read of the transaction chapter in the reference guide).

                          Comment


                          • #14
                            Hi Marten,

                            I think, I doing something wrong for sure. Because the connection type I am getting is 'oracle.jdbc.driver.LogicalConnection', that seems to be Oracle specific.
                            And I have put annotation @Tansactional for the transaction starting point. I think I doing something very wrong. Anyway thanks.

                            Comment


                            • #15
                              Are you using the correct datasource (you don't do a lookup in your code?) also make sure that you use the configured instances from your application context and that you aren't using different instances. Also @Transactional needs a tx:annotation-driven (only annotations don't do much)..

                              If possible zip your project and attach here, if that is problematic I can send you a PM with my email so you can send it over email.

                              Comment

                              Working...
                              X