Announcement Announcement Module
Collapse
No announcement yet.
Legacy JDBC and Datatsource with JUNT Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Legacy JDBC and Datatsource with JUNT

    Objective is to use Autowire in a JUnit to get a handle on the Datasource within a legacy DAOImpl, but the DAOImpl can not use any Spring in the DAOImpl

    My current DAO impl retrives data source like below:

    InitialContext ic = new InitialContext();
    DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/testDb");

    1) Can not use Spring in the DAO impl but can add a Datasource attribute
    2) If I add the following attribute to the DAO Imp

    private DataSource dataSource;

    public DataSource getDataSource() {
    return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
    }


    3) Then put the original DataSource statements in the getDataSource() method and use this method to get the connection for all JDBC connections

    public DataSource getDataSource() {

    InitialContext ic = new InitialContext();
    return (DataSource) ic.lookup("java:comp/env/jdbc/testDb");

    }

    4) If the DAOImpl is Autowired in JUnit(DataSource id is added to the Spring-Module.xml file), will Spring ignore the original DataSource statments in the getDataSource() method (in the DAOImpl) and get the DataSource settings from Spring-module xml file(i.e. basically an override)? In otherwords, Junit will call a method in the DAOImpl(i.e. insert), which calls the getDataSource() method to get a DataSource Connection, will Spring retrieve Spring DataSource setting/configuration or the legacy configuration(ie. (DataSource) ic.lookup("java:comp/env/jdbc/testDb")?

    5) If Spring can not retrieve the Spring DataSource configuration, how can the DAOImpl(no Spring imports) or Junit be updated to retrieve the Spring DataSource configuration info in the DAOImpl?
    Last edited by java2design; Jun 8th, 2013, 12:38 AM.

  • #2
    Why so complex...

    1. Add property for datasource
    2. Let spring inject datasource for production code use a jndi-lookup for test override the datasource with a test one.

    No need to do an additional lookup in the code.

    Comment


    • #3
      Thanks for responding..override the datasource worked fine..

      Next issue..JDBC commit and rollback transaction via Junit4

      I have a DAO, which includes many JDBC statement updates, and all have to be successful before the transaction it is commited. Therefore, set JDBC Connection autoCommit(false) and explicit added a commit() statement to the DAO method. Can not add any Spring to DAO..

      The only issue is after the JDBC commit() is executed in the DAO method the transaction is not rolled back after returning back teh the test Junit method. Junit4/Spring rollbacks the transaction successfully when autoCommit is not set - i.e. default setting.

      How can I force a JDBC transaction to RollBack, even after a JDBC commit() is executed? Already extended AbstractTransactionalJUnit4SpringContextTests for Junit test class, but the method still did not rollback.

      Comment


      • #4
        Spring will automaticaly disable autocommit so no need to mess around with that.

        A commit is final and cannot be rolledback. Also make sure that you use a database which supports transactions (MySQL with MyISAM tables does not support transactions).

        Comment


        • #5
          Thanks for responding!

          1) Using MySQL and it is using InnoDB
          2) A commit is final - this explains when this DAO method, which includes the following JDBC statememts below, is called by the Junit 4 Test method, the JDBC transaction is not rolled back.

          conn.setAutoCommit(false);
          .
          .
          pstmt.executeUpdate();
          .
          .
          pstmt.executeUpdate();
          conn.commit();
          conn.setAutoCommit(true);

          Comment


          • #6
            If you have setup spring correctly and use the correct datasource in your dao (no lookup but the TransactionAwareDataSourceProxy) the call to commit should be intercepted and basically do nothing. So if it is committing there must eb either something wrongin your code, in your configuration or your testcase.

            Comment


            • #7
              Thanks for responding Marten!

              This is the Spring config:

              <beans xmlns="http://www.springframework.org/schema/beans"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://www.springframework.org/schema/beans
              http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
              <bean id="dataSource"
              class="org.springframework.jdbc.datasource.Transac tionAwareDataSourceProxy">
              <constructor-arg ref="dataSourceOrg" />
              </bean>

              <bean id="transactionManager"
              class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
              <property name="dataSource" ref="dataSourceOrg" />
              </bean>

              <bean id="dataSourceOrg"
              class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
              <property name="driverClassName" value="com.mysql.jdbc.Driver" />
              <property name="url" value="jdbc:mysql://localhost:3306/testMicrotipper" />
              <property name="username" value="root" />
              <property name="password" value="GO2FSU!" />
              </bean>

              </beans>

              Junit 4 Test case class annotations:

              @RunWith(SpringJUnit4ClassRunner.class)
              @Transactional(propagation = Propagation.REQUIRED)
              @ContextConfiguration(locations = { "classpath:resources/Spring-Module.xml" })
              @TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
              public class TestWallet extends AbstractTransactionalJUnit4SpringContextTests{

              Junit 4 test method:

              @Test
              public void testInsertWallet() {

              try {

              walletGlobal.setAccount("123456");
              new TestDatabaseUserManager().insertWallet(walletGloba l);
              } catch (DAOException e) {
              Assert.fail(e.getLocalizedMessage());
              }

              }

              The above method does not Rollback when explicitly commiting in JDBC(i.e. second execution fails). However, once the explicit JDBC statements are removed, the Junit test case above is rolled back successfully.
              Last edited by java2design; Jun 10th, 2013, 10:19 AM.

              Comment


              • #8
                Please use [ code][/code ] tags when posting code/xml/stacktraces that way they remain readable.

                Your configuration is basically useless... Your TestDatabaseUserManager instance should be a spring configured one not a newly constructed one, you should have spring inject the datasource into it. Currently it probably uses its own datasource or is constructing a new one which is outside the scope of spring.

                Comment


                • #9
                  Thanks for responding..apologies copied the wrong class. Below is the correct test and DAO class:

                  1) DAO Spring config
                  Code:
                  <bean id="testDatabaseUserManager" class="com.iwm.micro.test.customer.dao.impl.TestDatabaseUserManager">
                    <property name="dataSource" ref="dataSource" />
                  </bean>
                  2) Datasource Spring-config
                  Code:
                  <beans xmlns="http://www.springframework.org/schema/beans"
                  	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  	xsi:schemaLocation="http://www.springframework.org/schema/beans
                  http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
                  	<bean id="dataSource"
                  		class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
                  		<constructor-arg ref="dataSourceOrg" />
                  	</bean>
                  
                  	<bean id="transactionManager"
                  		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                  		<property name="dataSource" ref="dataSourceOrg" />
                  	</bean>
                  
                  	<bean id="dataSourceOrg"
                  		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
                  		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
                  		<property name="url" value="jdbc:mysql://localhost:3306/testmicro" />
                  		<property name="username" value="root" />
                  		<property name="password" value="GO2FSU!" />
                  	</bean>
                  
                  </beans>
                  3) DAO to accept datasource from Spring config via Autowire
                  Code:
                  private  DataSource dataSource;
                  
                  public DataSource getDataSource() {
                  		return dataSource;
                  	}
                  
                  	public void setDataSource(DataSource dataSource) {
                  		this.dataSource = dataSource;
                  	}
                  
                  	public void insertWallet(Wallet wallet) throws DAOException {
                  		Connection conn = null;
                  		PreparedStatement pstmt = null;
                  		ResultSet rs = null;
                  
                  		try {
                  			conn = dataSource.getConnection();
                  			conn.setAutoCommit(false);                                       
                                                         jdbc updates
                                                         .
                                                         .
                                                         .
                                                         conn.commit() 
                  		             conn.setAutoCommit(true);
                  4) Test case
                  Code:
                  @RunWith(SpringJUnit4ClassRunner.class)
                  @Transactional(propagation = Propagation.REQUIRED)
                  @ContextConfiguration(locations = { "classpath:resources/Spring-Module.xml" })
                  @TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
                  public class TestWallet extends AbstractTransactionalJUnit4SpringContextTests{
                  
                  	private static Wallet walletGlobal = new Wallet();
                  	
                  	@Autowired
                  	private TestDatabaseUserManager testDatabaseUserManager;
                  
                  	@BeforeClass
                  	public static void setUpBeforeClass() throws Exception {
                  		walletGlobal.setWalletId("walletId");
                  		walletGlobal.setPasscode("walletPasscode");
                  	}
                  	@Before
                  	public void setUp() {
                  	}
                  
                  	@Test
                  	public void testInsertWallet() {
                  		try {
                  			walletGlobal.setAccount("123456");
                  			testDatabaseUserManager.insertWallet(walletGlobal);
                  		} catch (DAOException e) {
                  			Assert.fail(e.getLocalizedMessage());
                  		}
                  	}
                  }
                  This is the Spring exception being returned:

                  Code:
                  org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:286)
                  	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:846)
                  	at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:823)
                  	at org.springframework.test.context.transaction.TransactionalTestExecutionListener$TransactionContext.endTransaction(TransactionalTestExecutionListener.java:501)
                  	at org.springframework.test.context.transaction.TransactionalTestExecutionListener.endTransaction(TransactionalTestExecutionListener.java:277)
                  	at org.springframework.test.context.transaction.TransactionalTestExecutionListener.afterTestMethod(TransactionalTestExecutionListener.java:170)
                  	at org.springframework.test.context.TestContextManager.afterTestMethod(TestContextManager.java:344)
                  	at org.springframework.test.context.junit4.SpringMethodRoadie.runAfters(SpringMethodRoadie.java:307)
                  	at org.springframework.test.context.junit4.SpringMethodRoadie$RunBeforesThenTestThenAfters.run(SpringMethodRoadie.java:338)
                  	at org.springframework.test.context.junit4.SpringMethodRoadie.runWithRepetitions(SpringMethodRoadie.java:217)
                  	at org.springframework.test.context.junit4.SpringMethodRoadie.runTest(SpringMethodRoadie.java:197)
                  	at org.springframework.test.context.junit4.SpringMethodRoadie.run(SpringMethodRoadie.java:143)
                  	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.invokeTestMethod(SpringJUnit4ClassRunner.java:160)
                  	at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
                  	at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
                  	at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
                  	at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
                  	at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
                  	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:97)
                  	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
                  	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
                  	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
                  	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
                  	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
                  	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
                  Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true
                  	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
                  	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:44)
                  	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:39)
                  	at java.lang.reflect.Constructor.newInstance(Constructor.java:516)
                  	at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
                  	at com.mysql.jdbc.Util.getInstance(Util.java:382)
                  	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
                  	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
                  	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
                  	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
                  	at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4732)
                  	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:283)
                  	... 24 more
                  Last edited by Marten Deinum; Jun 11th, 2013, 12:50 AM. Reason: Added code tags.

                  Comment


                  • #10
                    Again use [ code][/code ] tags when posting code that way it remains readable. (I've edited your post and added the tags)

                    You shouldn't be messing around with the autocommit setting of the connection, springs transaction management takes care of that. It automatically sets the autocommit to false (if it is true) and after commit/rollback of the transaction it will be returned to its original state.
                    Last edited by Marten Deinum; Jun 11th, 2013, 12:52 AM.

                    Comment


                    • #11
                      Thanks for responding..will use tags in the future.

                      This JDBC legacy code can not be changed(i.e. can not reference any Spring jars to the code), so there is no way to Rollback using Spring and Junit 4. However, if it were rewritten in Spring, this would not be a problem.

                      Is this correct?

                      Comment


                      • #12
                        Please READ...
                        You don't need to reference any spring code you just need to remove the messing around with the auto commit... SPring will automatically handle this for you!!!

                        Code:
                        public void insertWallet(Wallet wallet) throws DAOException {
                        		Connection conn = null;
                        		PreparedStatement pstmt = null;
                        		ResultSet rs = null;
                        
                        		try {
                        			conn = dataSource.getConnection();
                        			conn.setAutoCommit(false);                                       
                                                               jdbc updates
                                                               .
                                                               .
                                                               .
                                                               conn.commit() 
                        		             conn.setAutoCommit(true);
                        
                        So basically remove the code in red at least the setAutoCOmmit to true as that disables springs transaction management (you cannot commit a connection that has autocommit set to true)
                        Last edited by Marten Deinum; Jun 11th, 2013, 09:42 AM.

                        Comment


                        • #13
                          Thanks for responding

                          The datasource management for the production code is not managed by Spring, so I do not understand the following comment:

                          "Spring will automatically handle this for you"

                          Yes..for testing purposes I can extend the production DAO class, oveeride the insertWallet method, and remove the autocommits. This is the only way I can get it to work using Junit 4 and Spring; in this case Spring will manage the connection and rollback when an exception occurs.

                          Is this correct?

                          BTW:
                          I am using Junit to reference a production project, which includes DAO - not copying it to a local test project/package
                          Last edited by java2design; Jun 11th, 2013, 10:54 AM.

                          Comment


                          • #14
                            Please be more specific in what you ask... Why on earth are you using spring if you don't use it in production?! Also IMHO the production code is flawed as it is messing around with the auto-commit settings where it shouldn't (at least not setting it to true it should only set it to false but that is just IMHO).

                            For your testing purpose create your own implementation of the TransactionAwareDataSourceProxy in this extend the TransactionAwareInvocationHandler to override the setAutoCommit method and make it do nothing (maybe log a warning or something like that).

                            Code:
                            /**
                             * Invocation handler that delegates close calls on JDBC Connections
                             * to DataSourceUtils for being aware of thread-bound transactions.
                             */
                            private class TransactionAwareInvocationHandler implements InvocationHandler {
                            	
                            	private final Log log = LogFactory.getLog(TransactionAwareInvocationHandler.class);
                            
                            	private final DataSource targetDataSource;
                            
                            	private Connection target;
                            
                            	private boolean closed = false;
                            
                            	public TransactionAwareInvocationHandler(DataSource targetDataSource) {
                            		this.targetDataSource = targetDataSource;
                            	}
                            
                            	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                            		// Invocation on ConnectionProxy interface coming in...
                            
                            		if (method.getName().equals("equals")) {
                            			// Only considered as equal when proxies are identical.
                            			return (proxy == args[0]);
                            		}
                            		else if (method.getName().equals("hashCode")) {
                            			// Use hashCode of Connection proxy.
                            			return System.identityHashCode(proxy);
                            		}
                            		else if (method.getName().equals("toString")) {
                            			// Allow for differentiating between the proxy and the raw Connection.
                            			StringBuilder sb = new StringBuilder("Transaction-aware proxy for target Connection ");
                            			if (this.target != null) {
                            				sb.append("[").append(this.target.toString()).append("]");
                            			}
                            			else {
                            				sb.append(" from DataSource [").append(this.targetDataSource).append("]");
                            			}
                            			return sb.toString();
                            		}
                            		else if (method.getName().equals("unwrap")) {
                            			if (((Class) args[0]).isInstance(proxy)) {
                            				return proxy;
                            			}
                            		}
                            		else if (method.getName().equals("isWrapperFor")) {
                            			if (((Class) args[0]).isInstance(proxy)) {
                            				return true;
                            			}
                            		}
                            		else if (method.getName().equals("close")) {
                            			// Handle close method: only close if not within a transaction.
                            			DataSourceUtils.doReleaseConnection(this.target, this.targetDataSource);
                            			this.closed = true;
                            			return null;
                            		}
                            		else if (method.getName().equals("isClosed")) {
                            			return this.closed;
                            		} else if (method.getName().equals("setAutoCommit")) {
                            			// Do nothing only log.
                            			log.warn("JDBC code called 'setAutoCommit', ignoring call as the PlatformTransactionManager will manage auto-commit settings");
                            		}
                            
                            		if (this.target == null) {
                            			if (this.closed) {
                            				throw new SQLException("Connection handle already closed");
                            			}
                            			if (shouldObtainFixedConnection(this.targetDataSource)) {
                            				this.target = DataSourceUtils.doGetConnection(this.targetDataSource);
                            			}
                            		}
                            		Connection actualTarget = this.target;
                            		if (actualTarget == null) {
                            			actualTarget = DataSourceUtils.doGetConnection(this.targetDataSource);
                            		}
                            
                            		if (method.getName().equals("getTargetConnection")) {
                            			// Handle getTargetConnection method: return underlying Connection.
                            			return actualTarget;
                            		}
                            
                            		// Invoke method on target Connection.
                            		try {
                            			Object retVal = method.invoke(actualTarget, args);
                            
                            			// If return value is a Statement, apply transaction timeout.
                            			// Applies to createStatement, prepareStatement, prepareCall.
                            			if (retVal instanceof Statement) {
                            				DataSourceUtils.applyTransactionTimeout((Statement) retVal, this.targetDataSource);
                            			}
                            
                            			return retVal;
                            		}
                            		catch (InvocationTargetException ex) {
                            			throw ex.getTargetException();
                            		}
                            		finally {
                            			if (actualTarget != this.target) {
                            				DataSourceUtils.doReleaseConnection(actualTarget, this.targetDataSource);
                            			}
                            		}
                            	}
                            }

                            Comment


                            • #15
                              Thanks for responding..

                              Let me review and if I have any question, I'll post..

                              Comment

                              Working...
                              X