mysql外键的mapper配置_[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”，由于主外键关联，工具会如下报错：

删除代码：

packagecom.hy;importjava.io.Reader;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.apache.log4j.Logger;public classDeleteProductTest {private static Logger logger = Logger.getLogger(DeleteProductTest.class);public static void main(String[] args) throwsException{long startTime =System.currentTimeMillis();

Reader reader=Resources.getResourceAsReader("mybatis-config.xml");

SqlSessionFactory ssf=newSqlSessionFactoryBuilder().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(longallSeconds) {

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";

}returnDateTimes;

}

}

Mapper接口类

packagecom.hy;public interfaceProductMapper {int deleteById(longid);

}

Mapper.xml

/p>

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

delete from product where id=#{id}

用程序强行去删，会出现异常：

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.

因此，在删除时，应该有选择地辨认并跳过这种异常才行。具体程序如下：

packagecom.hy;importjava.io.Reader;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.apache.log4j.Logger;public classDeleteProductTest2 {private static Logger logger = Logger.getLogger(DeleteProductTest2.class);public static void main(String[] args) throwsException{long startTime =System.currentTimeMillis();

Reader reader=Resources.getResourceAsReader("mybatis-config.xml");

SqlSessionFactory ssf=newSqlSessionFactoryBuilder().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(longid: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(longallSeconds) {

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";

}returnDateTimes;

}

}

执行后输出如下：

INFO [main] - Commit Status=falseINFO [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=1INFO [main]- deleteById id=4 finished!INFO [main]- deleteById id=5 started!INFO [main]- Committed! Chenged Record Num=1INFO [main]- deleteById id=5 finished!INFO [main]- Changed recoed count=2INFO [main]- Time elapsed:10s.

--END-- 2019年10月6日14:52:46