Announcement Announcement Module
Collapse
No announcement yet.
Handling errors due to foreign key constraints.. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Handling errors due to foreign key constraints..

    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:

    Code:
    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?

  • #2
    btw, this is my dao code:

    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);
    	}
    
    }

    Comment

    Working...
    X