今天遇到了一个尴尬的问题,在使用mybatis的时候发现事务回滚不了,然后查找了一下资料才知道是mysql引擎的问题:
mysql有几种引擎,当使用InnoDB的时候,才可以进行事务处理
案列如下:
userMapper.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">
<!-- 如果需要访问这条sql语,需要通过其唯一标识访问到 唯一标识就是 namespace+id的组合 -->
<mapper namespace="mybatis_example01.com.dao.UserDao">
<!-- 批量插入 -->
<insert id="insertUsersGetCount" useGeneratedKeys="true" keyProperty="id">
insert into tb_user(id,username,password) values
<foreach item="user" index="index" collection="list" open="(" separator="),(" close=")">
#{user.id},#{user.username},#{user.password}
</foreach>
</insert>
</mapper>
UserDao.java
package mybatis_example01.com.dao;
import java.util.List;
import mybatis_example01.com.pojo.User;
public interface UserDao {
int insertUsersGetCount(List<User> user);
}
SqlSessionFactoryUtils.java
package mybatis_example01.com.sqlSessionFactory;
import java.io.IOException;
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.ibatis.session.SqlSessionManager;
public class SqlSessionFactoryUtils {
private static SqlSessionFactory sessionFactory;
private static SqlSession sqlSession;
static {
String resource = "mybatis-config.xml";
try {
sessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resource));
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession() {
sqlSession = SqlSessionManager.newInstance(sessionFactory).openSession();
return sqlSession;
}
}
UserService.java
package mybatis_example01.com.service;
import java.util.List;
import mybatis_example01.com.pojo.User;
public interface UserService {
int insertUsersGetCount(List<User> userList);
}
UserServiceImpl.java
package mybatis_example01.com.service.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import mybatis_example01.com.dao.UserDao;
import mybatis_example01.com.pojo.User;
import mybatis_example01.com.service.UserService;
import mybatis_example01.com.sqlSessionFactory.SqlSessionFactoryUtils;
public class UserServiceImpl implements UserService {
SqlSession session=SqlSessionFactoryUtils.getSqlSession();
UserDao userDao = session.getMapper(UserDao.class);
@Override
public int insertUsersGetCount(List<User> userList){
SqlSession session=SqlSessionFactoryUtils.getSqlSession();
int i=0;
try {
i = userDao.insertUsersGetCount(userList);
session.commit();
} catch (Exception e) {
session.rollback();
e.printStackTrace();
}finally{
session.close();
}
return i;
}
public static void main(String[]args){
UserServiceImpl userServiceImpl=new UserServiceImpl();
List<User> userList=new ArrayList<User>();
for(int i=4;i<20;i++)
{
userList.add(new User(i,"username"+i,"password"+i));
}
//userList.add(new User(1,"username","password"));//①数据表中已经存在主键id为1,2,3的user记录
int i = userServiceImpl.insertUsersGetCount(userList);
System.out.println("成功插入"+i+"记录!");
}
}
数据表初始数据如下
下面是将UserServiceImpl.java中①处注释去掉 的运行结果:
如上图片所示:在批量插入记录报错时,并没有产生事务回滚。
接下来改变mysql引擎,为InnoDB,保存:
恢复数据表初始数据 再重新运行一遍,结果如下:
这时可以看到报错并且产生事务回滚了。