Handling errors due to foreign key constraints..

[wowiesy]

Using iBATIS/Spring/MySQL, I've managed to use SqlMapClientSupportDao for the DAOs I have. Given a column with a foreign key constraint such as ON UPDATE RESTRICT and/or ON DELETE RESTRICT on another table, I purposely updated this same column to a value that is not yet existing in the reference table / column and I get this error:

2007-07-12 00:38:20,281 main
 [INFO ] org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];   
--- The error occurred in ibatis/loan.xml.  
--- The error occurred while applying a parameter map.  
--- Check the updateLoan-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`pay/pay_loan`, CONSTRAINT `FK_pay_loan_freq` FOREIGN KEY (`frequency`) REFERENCES `pay_frequency` (`code`)); nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in ibatis/loan.xml.  
--- The error occurred while applying a parameter map.  
--- Check the updateLoan-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`pay/pay_loan`, CONSTRAINT `FK_pay_loan_freq` FOREIGN KEY (`frequency`) REFERENCES `pay_frequency` (`code`))
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in ibatis/loan.xml.  
--- The error occurred while applying a parameter map.  
--- Check the updateLoan-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`pay/pay_loan`, CONSTRAINT `FK_pay_loan_freq` FOREIGN KEY (`frequency`) REFERENCES `pay_frequency` (`code`))
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
	at org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInSqlMapClient(SqlMapClientTemplate.java:383)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:193)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:381)
	at pay.dao.ibatis.SqlMapClientLoanDao.updateLoan(SqlMapClientLoanDao.java:55)
	at pay.dao.ibatis.test.TestIbatisLoan.run(TestIbatisLoan.java:75)
	at pay.dao.ibatis.test.TestIbatisLoan.main(TestIbatisLoan.java:139)
Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`pay/pay_loan`, CONSTRAINT `FK_pay_loan_freq` FOREIGN KEY (`frequency`) REFERENCES `pay_frequency` (`code`))
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
	at $Proxy3.execute(Unknown Source)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:81)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
	... 8 more

Somehow, I expected that the Spring framework, or the iBatis framework can handle this error. Am I using the SqlMapClientDaoSupport properly?

[wowiesy]

btw, this is my dao code:

package pay.dao.ibatis;

import java.util.List;

import org.springframework.dao.DataAccessException;
import pay.dao.LoanDao;
import pay.domain.Loan;
import org.springframework.orm.ibatis.support.*;

public class SqlMapClientLoanDao extends SqlMapClientDaoSupport implements LoanDao {

	public void deleteLoan(Loan loan) throws DataAccessException {
		// TODO Auto-generated method stub
		getSqlMapClientTemplate().delete("deleteLoan", loan);
	}

	public List getLoan(String code) throws DataAccessException {
		// TODO Auto-generated method stub
		return getSqlMapClientTemplate().queryForList("getLoanByCode",new String(code));
	}

	public List getLoanCode(String code) throws DataAccessException {
		// TODO Auto-generated method stub
		return getSqlMapClientTemplate().queryForList("getLoanCodesByCode", new String(code));
	}

	public List getLoans() throws DataAccessException {
		// TODO Auto-generated method stub
		return getSqlMapClientTemplate().queryForList("getAllLoans");
	}

	public void insertLoan(Loan loan) throws DataAccessException {
		// TODO Auto-generated method stub
		
		if (getLoanCode(loan.getCode()).isEmpty()) {
			getSqlMapClientTemplate().insert("insertLoan",loan);			
		} 
		
		
		
	}

	public boolean isKeyExisting(Loan loan) throws DataAccessException {
		// TODO Auto-generated method stub
		if (getLoanCode(loan.getCode()).isEmpty()) {
			 return false;
		} else {
			return true;
		}
		
	}

	public void updateLoan(Loan loan) throws DataAccessException {
		// TODO Auto-generated method stub
		getSqlMapClientTemplate().update("updateLoan", loan);
	}

}