[MySql]MySql中外键设置 以及Java/MyBatis程序对存在外键关联无法删除的规避

package com.hy;import java.io.Reader;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.apache.log4j.Logger;public class DeleteProductTest { private static Logger logger = Logger.getLogger(DeleteProductTest.class); public static void main(String[] args) throws Exception{ long startTime = System.currentTimeMillis(); Reader reader=Resources.getResourceAsReader("mybatis-config.xml"); SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader); reader.close(); SqlSession session=ssf.openSession(); logger.info("Commit Status="+session.getConnection().getAutoCommit()); try { ProductMapper pm=session.getMapper(ProductMapper.class); int changed=pm.deleteById(1); session.commit(); logger.info("Committed! Chenged Record Num="+changed); long endTime = System.currentTimeMillis(); logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + "."); }catch(Exception ex) { logger.error("Error/Exception happened when executing the meothod'ProductMapper.deleteById(1)' because '"+ex.getMessage()+"'."); session.rollback(); logger.info("Rollbacked."); } finally { session.close(); } } // format seconds to day hour minute seconds style // Example 5000s will be formatted to 1h23m20s public static String toDhmsStyle(long allSeconds) { String DateTimes = null; long days = allSeconds / (60 * 60 * 24); long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60); long minutes = (allSeconds % (60 * 60)) / 60; long seconds = allSeconds % 60; if (days > 0) { DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { DateTimes = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { DateTimes = minutes + "m" + seconds + "s"; } else { DateTimes = seconds + "s"; } return DateTimes; }}

package com.hy;public interface ProductMapper { int deleteById(long id);}

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="com.hy.ProductMapper"> <delete id="deleteById"> delete from product where id=#{id} </delete></mapper>

INFO [main] - Commit Status=falseERROR [main] - Error/Exception happened when executing the meothod'ProductMapper.deleteById(1)' because '### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orderTb`, CONSTRAINT `orderTb_ibfk_1` FOREIGN KEY (`productid`) REFERENCES `product` (`id`))### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: delete from product where id=?### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orderTb`, CONSTRAINT `orderTb_ibfk_1` FOREIGN KEY (`productid`) REFERENCES `product` (`id`))'. INFO [main] - Rollbacked.

package com.hy;import java.io.Reader;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.apache.log4j.Logger;public class DeleteProductTest2 { private static Logger logger = Logger.getLogger(DeleteProductTest2.class); public static void main(String[] args) throws Exception{ long startTime = System.currentTimeMillis(); Reader reader=Resources.getResourceAsReader("mybatis-config.xml"); SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader); reader.close(); SqlSession session=ssf.openSession(); logger.info("Commit Status="+session.getConnection().getAutoCommit()); int totalChanged=0; try { ProductMapper pm=session.getMapper(ProductMapper.class); long[] arr= {1,2,3,4,5}; for(long id:arr) { logger.info("deleteById id="+id+" started!"); try { int changed=pm.deleteById(id); session.commit(); totalChanged+=changed; logger.info("Committed! Chenged Record Num="+changed); }catch(Exception ex) { if(ex.getMessage().contains("foreign key constraint fails")){ // 用 ex instanceof 识别不出来，故而用这种方式 logger.error("ForeignKey collide Conflict happened when executing the meothod'ProductMapper.deleteById("+id+")'."); continue; }else { logger.error("Other Error/Exception happened when executing the meothod'ProductMapper.deleteById("+id+")' because '"+ex.getMessage()+"'."); session.rollback(); logger.info("Rollbacked."); } } logger.info("deleteById id="+id+" finished!"); } }catch(Exception ex) { logger.error("Error/Exception happened when executing the meothod'ProductMapper.deleteById(1)' because '"+ex.getMessage()+"'."); session.rollback(); logger.info("Rollbacked."); } finally { session.close(); } logger.info("Changed recoed count="+totalChanged); long endTime = System.currentTimeMillis(); logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + "."); } // format seconds to day hour minute seconds style // Example 5000s will be formatted to 1h23m20s public static String toDhmsStyle(long allSeconds) { String DateTimes = null; long days = allSeconds / (60 * 60 * 24); long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60); long minutes = (allSeconds % (60 * 60)) / 60; long seconds = allSeconds % 60; if (days > 0) { DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { DateTimes = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { DateTimes = minutes + "m" + seconds + "s"; } else { DateTimes = seconds + "s"; } return DateTimes; }}

INFO [main] - Commit Status=false INFO [main] - deleteById id=1 started!ERROR [main] - ForeignKey collide Conflict happened when executing the meothod'ProductMapper.deleteById(1)'. INFO [main] - deleteById id=2 started!ERROR [main] - ForeignKey collide Conflict happened when executing the meothod'ProductMapper.deleteById(2)'. INFO [main] - deleteById id=3 started!ERROR [main] - ForeignKey collide Conflict happened when executing the meothod'ProductMapper.deleteById(3)'. INFO [main] - deleteById id=4 started! INFO [main] - Committed! Chenged Record Num=1 INFO [main] - deleteById id=4 finished! INFO [main] - deleteById id=5 started! INFO [main] - Committed! Chenged Record Num=1 INFO [main] - deleteById id=5 finished! INFO [main] - Changed recoed count=2 INFO [main] - Time elapsed:10s.