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!
******************************************************************************
