Transaction Rollback not working with iBatis + Spring

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Transaction Rollback not working with iBatis + Spring

Jasmin Mehta

Hi,

I have 2 tables CUSTOMER and DEPENDENT.

In CUSTOMER table the primary key is cust_id which is foreign key to DEPENDENT table.

I would like to make insert to both tables together as a batch and also if one fails than other must roll back.

If I remove the constraints of having cust_id as a foreign key to DEPENDENT table than both insert works fine as required, it gets committed together and if any of it fails than none of the transaction will commit.

But as soon as I have two tables related, it works fine for first insert  to CUSTOMER table, when I make a call to insert() statement, it does not commit yet, but when it reaches second insert() stmt  to DEPENDENT table it throws below exception:

-- The error occurred in org/nexweb/qol/gcc/ibatis/dao/xml/Customer.xml.  
--- The error occurred while applying a parameter map.  
--- Check the insertCustomerDependent-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: java.sql.SQLException: ORA-02291: integrity constraint (DEPENDENT_CUST_ID_FK) violated - parent key not found

Can you check what am I missing ? I have also tried using SqlMapSession explicitly opening session and do all transaction but did not work.
I have been trying since last 2 days, please help me out.

Here is my code:

I am using JDeveloper 10.1.3 as my IDE which has embedded OC4J application server.

SqlMapConfig.xml

<properties resource="jdbc.properties"/>

<transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
      <property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
      <property name="JDBC.Username" value="${jdbc.username}"/>
      <property name="JDBC.Password" value="${jdbc.password}"/>      
      <property name="JDBC.Class" value="oracle.jdbc.pool.OracleDataSource"/>
      <property name="JDBC.DefaultAutoCommit" value="false" />
      <property name="Pool.MaximumActiveConnections" value="10"/>
      <property name="Pool.MaximumIdleConnections" value="5"/>
      <property name="Pool.MaximumCheckoutTime" value="120000"/>
      <property name="Pool.TimeToWait" value="500"/>
    </dataSource>
  </transactionManager>

<sqlMap resource="org/nexweb/qol/gcc/ibatis/dao/xml/Customer.xml"/>

spring.xml

<beans>

    <bean id="sqlMapClient"
          class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation">
            <value>classpath:org/nexweb/qol/gcc/ibatis/config/SqlMapConfig.xml</value>
        </property>
    </bean>

    <bean id="sqlMapClientTemplate"
          class="org.springframework.orm.ibatis.SqlMapClientTemplate">
        <property name="sqlMapClient">
            <ref bean="sqlMapClient"/>
        </property>
    </bean>    
  <bean id="customerDao" class="org.nexweb.qol.gcc.ibatis.dao.sqlmaps.CustomerSqlMapDAO">
        <property name="sqlMapClient">
            <ref bean="sqlMapClient"/>
        </property>
    </bean>    

    <bean id="customerService" class="org.nexweb.qol.gcc.ibatis.services.CustomerService">
        <constructor-arg index="0" ref="customerDao"/>
    </bean>

CustomerSqlMapDAO

public class CustomerSqlMapDAO extends SqlMapClientTemplate implements CustomerDAO
{
  public Integer insertCustomer(CustomerVO customerVo) throws DataAccessException {
        return (Integer)insert("insertCustomer", customerVo);
    }

    public Integer insertCustomerDependent(CustomerDependentVO customerDependentVo) throws DataAccessException {
        return (Integer)insert("insertCustomerDependent", customerDependentVo);
    }

CustomerService

public class CustomerService extends CommonService
{
    private CustomerDAO customerDAO;

    public CustomerService(CustomerDAO customerDAO)
    {
        super(customerDAO);
        this.customerDAO = customerDAO;
    }  

public Integer insertCustomer(CustomerVO customerVo) throws DataAccessException  {
        return customerDAO.insertCustomer(customerVo);
    }

    public Integer insertCustomerDependent(CustomerDependentVO customerDependentVo) throws DataAccessException  {
        return customerDAO.insertCustomerDependent(customerDependentVo);
    }


CustomerAction.java

CustomerService customerService = SpringBeans.getCustomerService();
try
{
                  // retrieve cust_id from QOL_CUSTOMER using qol_customer_seq.NEXTVAL
         custId = customerService.getCustomerId();              

        customerService.startTransaction();
         // start batch process
        customerService.startBatch();

        // insert record into QOL_CUSTOMER table (add to batch process)
         customerService.insertCustomer(customerVo);

         // retrive dependent first/last name for insertion check to QOL_DEPENDENT tabel
         String custDepFirstName = customerPersonalForm.getDepFirstName();
         String custDepLastName = customerPersonalForm.getDepLastName();
        // insert into DEPENDENT table only if dependent first/last name enterd on the form
         if (StringUtils.isNotBlank(custDepFirstName) || StringUtils.isNotBlank(custDepLastName))
         {
           customerDependentVo.setCustId(custId);
           // set CustomerDependentVO to CustomerVO only if dependent first/last name exist and
           // record to DEPENDENT is going to be inserted in for this customer id
           customerVo.setCustomerDependentVo(customerDependentVo);          
           // insert record into DEPENDENT table (add to batch process)
           customerService.insertCustomerDependent(customerDependentVo);
         }
         // insert record into VEHICLE table (add to batch process)
         customerVehicleService.insertCustomerVehicle(customerVehicleVo);       --> same issue with this insert, if i remove constraint from DEPENDENT
         // execute batch
         customerService.executeBatch();

         // end batch process
        customerService.commitTransaction();
     }
       catch(SQLException sqlEx)
       {  ...........
}
       finally
       {
           try
           {
             customerService.endTransaction();
           }
           catch(SQLException sqlEx)
           {
             errors.add(ActionErrors.GLOBAL_MESSAGE, new ActionMessage("gcc.error.db.exception"));
             logger.error(ExceptionUtils.getStackTrace(sqlEx));
           }
       }
}

******************************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or agency to whom they are addressed.
If you have received this email in error please notify the Navy
Exchange Service Command e-mail administrator. This footnote
also confirms that this email message has been scanned for the
presence of computer viruses.

Thank You!           
******************************************************************************

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Bruno Issenmann
Hi,

I'm facing the same problem. Did you solved it ?
Thanks



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Larry Meadors
You don't need the batch, and I'm pretty sure it won't ever work with
the batch because the inserts don't happen when you call them (they
happen when you call executeBatch()) so your ids don't get set when
you expect them to.

Remove the lines related to the batch, and I think it'll work.

Larry


On Wed, Jul 21, 2010 at 10:19 AM, Bruno Issenmann
<[hidden email]> wrote:

> Hi,
>
> I'm facing the same problem. Did you solved it ?
> Thanks
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Bruno Issenmann
Thanks for ansering.

I do not use the batch.
I'm just doing two inserts then i commit. I still have an integrity contraint
violation.



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Transaction Rollback not working with iBatis + Spring

Vinaya Tirikkovalluru
Sorry!! I missed the thread.
Does it give you a runtime exception? Only if the code throws a runtime
exception the transaction will be rolled back

Vinaya


-----Original Message-----
From: Bruno Issenmann [mailto:[hidden email]]
Sent: Wednesday, July 21, 2010 1:07 PM
To: [hidden email]
Subject: Re: Transaction Rollback not working with iBatis + Spring

Thanks for ansering.

I do not use the batch.
I'm just doing two inserts then i commit. I still have an integrity
contraint
violation.



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



This electronic message is intended only for the use of the individual(s) or entity(ies) named above and may contain information which is privileged and/or confidential.  If you are not the intended recipient, be aware that any disclosure, copying, distribution, dissemination or use of the contents of this message is prohibited.  If you received this message in error, please notify the sender immediately.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Bruno Issenmann
Vinaya Tirikkovalluru <vinaya.tirikkovalluru <at> converge.com> writes:

>
> Sorry!! I missed the thread.
> Does it give you a runtime exception? Only if the code throws a runtime
> exception the transaction will be rolled back
>
> Vinaya

I have a runtime exception, transaction is rolled back. When i run the SQL
manually on oracle it's ok.
But with the dao i got a constraint integrity violation. It's like the inserts
go in the wrong order (complains about the first table id missing).
Or i'm wrong with the dao.

Here is my code

public class LotTest extends DAOManagerTest {

    @Test
    public void testInsert(){
   
    DAOFactory.daoMgr.startTransaction();
    try {
        LotDAO dao = (LotDAO)DAOFactory.daoMgr.getDao(LotDAO.class);
        Lot lot = new Lot();
        lot.setIdlot("201000000008");
        //lot.setNumhistolot(new Short("1"));
        lot.setIdlotamm("20char");
        dao.insert(lot);
       
        HistoLotDAO dao2 =
(HistoLotDAO)DAOFactory.daoMgr.getDao(HistoLotDAO.class);
        HistoLot histo = new HistoLot();
        histo.setIdlot("201000000008");
        histo.setNumhistolot(new Short("1"));
        histo.setIdetatlot("EC");
        histo.setDateetatlot(new Date());
        histo.setInfoetatlot("20char");
        dao2.insert(histo);
       
        DAOFactory.daoMgr.commitTransaction();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DAOFactory.daoMgr.endTransaction();
        }
    }
}

Thanks,
Bruno


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Transaction Rollback not working with iBatis + Spring

Vinaya Tirikkovalluru
Does just the insert work on the table which is giving the issue without
the insert into the history?
 


-----Original Message-----
From: Bruno Issenmann [mailto:[hidden email]]
Sent: Wednesday, July 21, 2010 2:03 PM
To: [hidden email]
Subject: Re: Transaction Rollback not working with iBatis + Spring

Vinaya Tirikkovalluru <vinaya.tirikkovalluru <at> converge.com> writes:

>
> Sorry!! I missed the thread.
> Does it give you a runtime exception? Only if the code throws a
runtime
> exception the transaction will be rolled back
>
> Vinaya

I have a runtime exception, transaction is rolled back. When i run the
SQL
manually on oracle it's ok.
But with the dao i got a constraint integrity violation. It's like the
inserts
go in the wrong order (complains about the first table id missing).
Or i'm wrong with the dao.

Here is my code

public class LotTest extends DAOManagerTest {

    @Test
    public void testInsert(){
   
    DAOFactory.daoMgr.startTransaction();
    try {
        LotDAO dao =
(LotDAO)DAOFactory.daoMgr.getDao(LotDAO.class);
        Lot lot = new Lot();
        lot.setIdlot("201000000008");
        //lot.setNumhistolot(new Short("1"));
        lot.setIdlotamm("20char");
        dao.insert(lot);
       
        HistoLotDAO dao2 =
(HistoLotDAO)DAOFactory.daoMgr.getDao(HistoLotDAO.class);
        HistoLot histo = new HistoLot();
        histo.setIdlot("201000000008");
        histo.setNumhistolot(new Short("1"));
        histo.setIdetatlot("EC");
        histo.setDateetatlot(new Date());
        histo.setInfoetatlot("20char");
        dao2.insert(histo);
       
        DAOFactory.daoMgr.commitTransaction();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DAOFactory.daoMgr.endTransaction();
        }
    }
}

Thanks,
Bruno


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



This electronic message is intended only for the use of the individual(s) or entity(ies) named above and may contain information which is privileged and/or confidential.  If you are not the intended recipient, be aware that any disclosure, copying, distribution, dissemination or use of the contents of this message is prohibited.  If you received this message in error, please notify the sender immediately.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Bruno Issenmann
Both tables are linked by constraints on primary keys, so i can't make only one
insert.

I must have missed something with the ibatis config or the doa manager.

Thanks for your time.

Bruno



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Larry Meadors
Wait - I'm REALLY confused. Can you post a stack trace?

Doh, I just noticed - this conversation should be on the new list.
Maybe it's time to start this as a new thread and give us the stack
trace and the relevant code anew. :)

The ibatis project has moved and been renamed.

It is no longer being maintained as an Apache project, but has moved
(along with the development team) here:

http://www.mybatis.org/

Please join us at the new location by joining the mailing list here:

http://groups.google.com/group/mybatis-user

Larry

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transaction Rollback not working with iBatis + Spring

Bruno Issenmann
Larry Meadors <larry.meadors <at> gmail.com> writes:

>
> Wait - I'm REALLY confused. Can you post a stack trace?
>
> Doh, I just noticed - this conversation should be on the new list.
> Maybe it's time to start this as a new thread and give us the stack
> trace and the relevant code anew. :)
>
> The ibatis project has moved and been renamed.
>
> It is no longer being maintained as an Apache project, but has moved
> (along with the development team) here:
>
> http://www.mybatis.org/
>
> Please join us at the new location by joining the mailing list here:
>
> http://groups.google.com/group/mybatis-user
>
> Larry
>

My SqlMapConfig.xml wasn't ok, i was using an EXTERNAL transaction manager
instead of a JDBC... (sorry for that)
Next time i'll post on the new list.

Larry and Vinaya Thanks for your help !

Bruno




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...