[MySql]MySql中外键设置 以及Java/MyBatis程序对存在外键关联无法删除的规避
在MySql设定两张表，其中product表的主键设定成orderTb表的外键，具体如下：
产品表：
create table product(id INT(11) PRIMARY KEY,name VARCHAR(32) );
订单表：
create table orderTb(id INT(11) PRIMARY KEY,productid INT(11), FOREIGN KEY(productid) REFERENCES product(id) )；
给产品表插入数据如下：
给订单表插入数据如下：
在MySql-Front工具中写SQL文“DELETE from product where id=1”，由于主外键关联，工具会如下报错：
如果用java程序去删（工程下载：https://files.cnblogs.com/files/xiandedanteng/product191006_2.rar ）
删除代码：
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; } }
Mapper接口类
package com.hy; public interface ProductMapper { int deleteById(long id); }
Mapper.xml
<?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>
用程序强行去删，会出现异常：
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
执行下来，控制台输出会是：
INFO [main] - Commit Status=false ERROR [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.
--END-- 2019年10月6日14:52:46
