Announcement Announcement Module
Collapse
No announcement yet.
declarative transaction via JDBC Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • declarative transaction via JDBC

    I am trying to setup declarative transaction with spring. Hence I am not really familiar with the concept I may done stupid mistakes. But any way it is not working. Could anybody help me:

    This is my application context:
    Transaction Proxy :
    <bean id="pmis1" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
    <property name="transactionManager"><ref local="transactionManager"/></property>
    <property name="target"><ref bean="userLogic"/></property>
    <property name="transactionAttributes">
    <props>
    <prop key="storeUser">PROPAGATION_REQUIRED,-DataAccessException</prop>
    </props>
    </property>
    </bean>


    userLogic bean:
    -----------------------------------
    <bean id="userLogic" class="com.tarhyar.pmis.jdbc.JdbcUser" >
    <property name="dataSource"><ref local="dataSource"/></property>
    </bean>

    and the method I expect to be transactional is :
    ---------------------------------------
    public void storeUser(User user) throws DataAccessException {
    insertUser.insert(user);
    insertUser1.insert(user);
    }

    protected class InsertUser extends SqlUpdate {

    protected InsertUser(DataSource ds) {
    super(ds, "INSERT INTO users VALUES(?,?,true)");
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.VARCHAR));
    compile();
    }

    protected void insert(User user) {
    Object[] objs =
    new Object[]{
    user.getUsername(),
    user.getPassword()
    };
    try {
    super.update(objs);
    } catch (DataAccessException e) {
    throw e;
    }
    //retrieveIdentity(owner);
    }
    }
    protected class InsertUser1 extends SqlUpdate {

    protected InsertUser1(DataSource ds) {
    super(ds,"INSERT INTO users1 VALUES (?,?)");
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.VARCHAR));
    compile();
    }

    protected void insert(User user) throws DataAccessException {
    Object[] objs =
    new Object[]{
    user.getUsername(),
    user.getPassword()
    };
    try {
    super.update(objs);
    } catch (DataAccessException e) {
    throw e;
    }
    --------------------------------------------------------
    Help Please!

  • #2
    But any way it is not working
    could you say what happens? also, how do you access your DAO?

    Comment


    • #3
      declarative Transaction via JDBC

      What kind of Transaction Manager are you using?

      You need to have a Bean with the id=transactionManager declared, where you say what TransactionManager you are using, something like for a single JDBC DataSource:

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

      See also the jpetstore example, the files:
      applicationContext.xml, dataAccessContext-jta.xml and dataAccessContext-local.xml.

      Hope it helps
      Christoph

      Comment


      • #4
        Originally posted by irbouho
        But any way it is not working
        could you say what happens? also, how do you access your DAO?
        I prepare a situation where second fails, I suppose the transaction will roll back and the first insert should also be discarded. But in the practice it gives me an error about the second insert( which is a right report) but looking at the database, the first insert is commited. This is what is happening

        Comment


        • #5
          Re: declarative Transaction via JDBC

          Originally posted by chenrici
          What kind of Transaction Manager are you using?

          You need to have a Bean with the id=transactionManager declared, where you say what TransactionManager you are using, something like for a single JDBC DataSource:

          <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
          <property name="dataSource"><ref local="dataSource"/></property>
          </bean>
          Hope it helps
          Christoph
          I have this bean and jdbc is working fine, it is the transactional behavior that is not working

          Comment


          • #6
            declarative Transaction via JDBC

            I guess your are spawing two transactions on:

            public void storeUser(User user) throws DataAccessException {
            insertUser.insert(user);
            insertUser1.insert(user);
            }

            What DataSource Implementation are you using? Possibly one that closes the connection after the first insert?

            Christoph

            Comment


            • #7
              Originally posted by arashbi
              I prepare a situation where second fails, I suppose the transaction will roll back and the first insert should also be discarded. But in the practice it gives me an error about the second insert( which is a right report) but looking at the database, the first insert is commited. This is what is happening
              Sounds to me like auto-commit is turned on for the Connection you are using.

              Regards,
              Andreas

              Comment


              • #8
                Originally posted by Andreas Senft
                Originally posted by arashbi
                I prepare a situation where second fails, I suppose the transaction will roll back and the first insert should also be discarded. But in the practice it gives me an error about the second insert( which is a right report) but looking at the database, the first insert is commited. This is what is happening
                Sounds to me like auto-commit is turned on for the Connection you are using.

                Regards,
                Andreas
                I anm using Postgresql. I don't know how to controll its properties inside the spring.
                I am using DriverManagerDataSource!
                Thank you

                Comment


                • #9
                  declarative Transaction via JDBC

                  I am using DriverManagerDataSource!
                  That's probably your problem.
                  The following is taken from the Spring documentation:
                  <snip>
                  The DriverManagerDataSource is a Implementation of SmartDataSource that configures a plain old JDBC Driver via bean properties, and returns a new connection every time.
                  <snip>
                  And
                  <snip>
                  Pool-assuming Connection.close() calls will simply close the connection....
                  <snip>
                  So if getConnection() always creates a new connection, the behaviour you encountered could be explained.
                  Check : http://www.springframework.org/docs/...agerDataSource
                  In a similiar situation i use : org.apache.commons.dbcp.BasicDataSource, see also: http://jakarta.apache.org/commons/dbcp/
                  Christoph

                  Comment


                  • #10
                    Re: declarative Transaction via JDBC

                    Originally posted by chenrici
                    I am using DriverManagerDataSource!
                    That's probably your problem.
                    The following is taken from the Spring documentation:
                    <snip>

                    So if getConnection() always creates a new connection, the behaviour you encountered could be explained.
                    Check : http://www.springframework.org/docs/...agerDataSource
                    In a similiar situation i use : org.apache.commons.dbcp.BasicDataSource, see also: http://jakarta.apache.org/commons/dbcp/
                    Christoph
                    I chanegd to org.apache.commons.dbcp.BasicDataSource but still not working, I also couldn't find out how can I enable poolong in dataSource.

                    Dows anybody have experience with JDBC and transactio in Spring?

                    Comment


                    • #11
                      Question: how are you testing?
                      The reason i am asking = the Transactional Proxy - your case "pmis1" - needs to be referenced, where the bean "userLogic", if you want transactional behaviour.

                      In the following a TestCase, which works fine ... eventually selecting from the database manually, to check if anything is there ;-)
                      So the connection stuff was really misleading, sorry about that...
                      The testcase works both against SingleConnectionDataSource and DriverManagerDataSource.


                      import junit.framework.TestCase;

                      import org.springframework.context.support.FileSystemXmlA pplicationContext;
                      import org.springframework.dao.DataAccessException;

                      public class TestInsert extends TestCase
                      {

                      private FileSystemXmlApplicationContext ctx;
                      private JdbcUser userLogic;
                      /*
                      * @see TestCase#setUp()
                      */
                      protected void setUp() throws Exception
                      {
                      super.setUp();
                      String path[] = {"/war/WEB-INF/applicationContext.xml"};
                      ctx = new FileSystemXmlApplicationContext(path);
                      ctx.refresh();
                      Object obj = ctx.getBean("pmis1"); // and not userLogic
                      if (!(obj instanceof JdbcUser))
                      {
                      throw new Exception("Bean Configuration wrong");
                      }
                      userLogic = (JdbcUser) obj;
                      }

                      public void testInsert() throws Exception
                      {
                      try {
                      final User aUser = new User();
                      aUser.setName("Some name");
                      aUser.setPasswd("Some Passwd");
                      userLogic.storeUsers(aUser);
                      fail("Unexpected");
                      } catch (DataAccessException e)
                      {
                      System.out.println(e.toString());
                      // Ok, but test some more
                      }
                      }

                      /*
                      * @see TestCase#tearDown()
                      */
                      protected void tearDown() throws Exception
                      {
                      super.tearDown();
                      ctx = null;
                      userLogic = null;
                      }

                      }

                      Comment


                      • #12
                        BTW: the test output looked as follows
                        <snip>
                        [junit] 01.11.2004 14:52:14 org.springframework.jdbc.object.RdbmsOperation compile
                        [junit] INFO: RdbmsOperation with SQL [INSERT INTO testusers VALUES(?,?)] compiled
                        [junit] 01.11.2004 14:52:14 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
                        [junit] INFO: Looking up default SQLErrorCodes for DataSource
                        [junit] 01.11.2004 14:52:14 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
                        [junit] INFO: Database product name found in cache for DataSource [org.springframework.jdbc.datasource.DriverManagerD ataSource@9be79a]. Name is 'Oracle'.
                        [junit] 01.11.2004 14:52:14 org.springframework.jdbc.object.RdbmsOperation compile
                        [junit] INFO: RdbmsOperation with SQL [INSERT INTO testusers1 VALUES(?,?)] compiled
                        [junit] 01.11.2004 14:52:14 org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator logTranslation
                        [junit] WARNUNG: Translating SQLException with SQLState '42000' and errorCode '942' and message [ORA-00942: table or view does not exist
                        [junit] ]; SQL was [INSERT INTO testusers1 VALUES(?,?)] for task [executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementC reatorImpl: sql=[INSERT INTO testusers1 VALUES(?,?)]: params=[Some name,Some Passwd]]]
                        [junit] .From prgm: org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [INSERT INTO testusers1 VALUES(?,?)] in task 'executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementC reatorImpl: sql=[INSERT INTO testusers1 VALUES(?,?)]: params=[Some name,Some Passwd]]'; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist
                        [junit] 01.11.2004 14:52:14 org.springframework.transaction.interceptor.Transa ctionInterceptor onThrowable
                        [junit] INFO: Invoking rollback for transaction on method 'storeUsers' in class [JdbcUser] due to throwable [org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [INSERT INTO testusers1 VALUES(?,?)] in task 'executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementC reatorImpl: sql=[INSERT INTO testusers1 VALUES(?,?)]: params=[Some name,Some Passwd]]'; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist
                        [junit] ]
                        [junit] 01.11.2004 14:52:14 org.springframework.transaction.support.AbstractPl atformTransactionManager rollback
                        [junit] INFO: Initiating transaction rollback
                        [junit] org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [INSERT INTO testusers1 VALUES(?,?)] in task 'executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementC reatorImpl: sql=[INSERT INTO testusers1 VALUES(?,?)]: params=[Some name,Some Passwd]]'; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist
                        [junit] Time: 1,344
                        [junit] OK (1 test)
                        <snip>

                        Comment

                        Working...
                        X