SqlSessionTemplate批量增删改查

实际开发中我们操作数据库持久化，总是需要写重复的mapper，service，浪费了我们大量的时间，在这里推荐大家使用SqlSessionTemplate废话不多说直接上代码

public class BaseDao {

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    private final Logger logger = LoggerFactory.getLogger(this.getClass());


    void batchInsert(String statement, List parameterList, int pageSize) {
        if (parameterList == null || parameterList.size() == 0) {
            return;
            ;
        }
        int size = parameterList.size();
        if (size <= pageSize) {
            batchInsert(statement, parameterList);
        }
    }

    void batchInsert(String statement, List parameterList) {
        if (parameterList == null || parameterList.size() == 0) {
            return;
        }
        SqlSession batchSqlSession = null;
        try {
            batchSqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            for (Object obj : parameterList) {
                batchSqlSession.insert(statement, obj);
            }
            batchSqlSession.commit();
            //清理缓存防止溢出
            batchSqlSession.clearCache();
        } catch (Exception e) {
            logger.error("batch insert error");
            if (batchSqlSession != null) {
                batchSqlSession.rollback();
            }
        } finally {
            if (batchSqlSession != null) {
                batchSqlSession.close();
            }
        }
    }
}

用法：

       1.写个接口继承这个类，在接口实现类上加入@component注解

       2. 填写SQL   ID 

  

2.通过接口的方式

（1）定义数据库操作接口

import java.util.List;

/**
 * @author liuminglin
 * @date 2021/8/23 17:01
 * @desc: 定义数据库操作接口
 */



public interface BaseDao {


    /**
     * @Desc
     * @Param 单个查询
     * @Return
     */
    <T, E> E select(String namespace, String id, T params);

    /**
     * @Desc
     * @Param 所有的批量都可以用这个方法，它识别的是xml的sql，与方法无关;bathcount指的是没多少条提交一次事物
     * @Return
     */
    <T, E> List<E> selectList(String namespace, String id, T params);

    /**
     * @Desc
     * @Param 查询所有
     * @Return
     */
    <T> void batchALL(String namespace, String id, List<T> params, Integer bathcount);

    /**
     * @Desc
     * @Param 单个修改
     * @Return
     */
    <T> int update(String namespace, String id, T params);

    /**
     * @Desc
     * @Param 批量修改
     * @Return
     */
    <T> List<Long> updateList(String namespace, String id, List<T> list);

    /**
     * @Desc
     * @Param 单个插入
     * @Return
     */
    <T> long insert(String namespace, String id, T params);

    /**
     * @Desc
     * @Param 批量差入
     * @Return
     */
    <T> List<Long> insertList(String namespace, String id, List<T> list);

    /**
     * @Desc
     * @Param 单个删除
     * @Return
     */
    <T> int delete(String namespace, String id, T params);

    /**
     * @Desc
     * @Param 批量删除
     * @Return
     */
    <T> List<Long> deleteList(String namespace, String id, List<T> list);
}

（2）定义数据库操作接口实现类

NameSpace指的是你的xml的映射路径，sqlid指的是你xml中方法的名字,批量操作，xml中的sql参数都是单个，不需要使用foeach标签，这里的批量用的并不是mybatis的foreach操作而是通过传进来的集合批量提交事务到数据库
 

import com.example.recordlog.service.BaseDao;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
@Component
public class BaseDaoImpl implements BaseDao {

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    //protected String nameSpace = this.getClass().getName() + ".";

    @Override
    public <T, E> E select(String nameSpace, String id, T params) {
        if (params == null) {
            return sqlSessionTemplate.selectOne(nameSpace + "." + id);
        } else {
            return sqlSessionTemplate.selectOne(nameSpace + "." + id, params);
        }
    }

    @Override
    public <T, E> List<E> selectList(String nameSpace, String id, T params) {
        if (params == null) {
            return sqlSessionTemplate.selectList(nameSpace + "." + id);
        } else {
            return sqlSessionTemplate.selectList(nameSpace + "." + id, params);
        }
    }

    @Override
    public <T> void batchALL(String nameSpace, String sqlId, List<T> list, Integer bathcount) {

        List<T> data = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            data.add(list.get(i));
            if (data.size() == bathcount || i == list.size() - 1) {
                this.batchUtil(nameSpace, sqlId, data);
                data.clear();
            }
        }
    }

    @Override
    public <T> int update(String nameSpace, String id, T params) {
        if (params == null) {
            return sqlSessionTemplate.update(nameSpace + "." + id);
        } else {
            return sqlSessionTemplate.update(nameSpace + "." + id, params);
        }
    }

    @Override
    public <T> List<Long> updateList(String nameSpace, String id, List<T> list) {
        batchUpdate(nameSpace + "." + id, list);
        return null;
    }

    @Override
    public <T> long insert(String nameSpace, String id, T params) {
        return update(nameSpace, id, params);
    }

    @Override
    public <T> List<Long> insertList(String nameSpace, String id, List<T> list) {

        if (list == null || list.size() == 0) {
            return new ArrayList<>();
        }
        String statementId = nameSpace + "." + id;
        batchInsert(statementId, list);
        return null;
    }

    @Override
    public <T> int delete(String nameSpace, String id, T params) {
        return update(nameSpace, id, params);
    }

    @Override
    public <T> List<Long> deleteList(String nameSpace, String id, List<T> list) {
        return updateList(nameSpace, id, list);
    }

    void batchInsert(String statement, List parameterList) {
        if (parameterList == null || parameterList.size() == 0) {
            return;
        }
        SqlSession batchSqlSession = null;
        try {
            batchSqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            for (Object obj : parameterList) {
                batchSqlSession.insert(statement, obj);
            }
            batchSqlSession.commit();
            //清理缓存防止溢出
            batchSqlSession.clearCache();
        } catch (Exception e) {
            log.error("batch insert error");
            if (batchSqlSession != null) {
                batchSqlSession.rollback();
            }
        } finally {
            if (batchSqlSession != null) {
                batchSqlSession.close();
            }
        }
    }

    void batchUpdate(String statement, List parameterList) {
        if (parameterList == null || parameterList.size() == 0) {
            return;
        }
        SqlSession batchSqlSession = null;
        try {
            batchSqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            for (Object obj : parameterList) {
                batchSqlSession.insert(statement, obj);
            }
            batchSqlSession.commit();
            //清理缓存防止溢出
            batchSqlSession.clearCache();
        } catch (Exception e) {
            log.error("batch insert error");
            if (batchSqlSession != null) {
                batchSqlSession.rollback();
            }
        } finally {
            if (batchSqlSession != null) {
                batchSqlSession.close();
            }
        }
    }

    private <T> void batchUtil(String nameSpace, String sqlId, List<T> list) {
        try {
            if (list == null || list.isEmpty()) {
                return;
            }
            MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(nameSpace + "." + sqlId);
            SqlCommandType sqlCommandType = ms.getSqlCommandType();
            BoundSql boundSql = ms.getSqlSource().getBoundSql(list.get(0));
            String sql = boundSql.getSql();
            List<ParameterMapping> list2 = boundSql.getParameterMappings();
            Connection connection = sqlSessionTemplate.getSqlSessionFactory().openSession().getConnection();
            PreparedStatement statement = null;
            if (sqlCommandType == SqlCommandType.INSERT) {
                statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                statement = connection.prepareStatement(sql);
            }
            sql = sql.replaceAll("\\n", "");
            sql = sql.replaceAll("\\t", "");
            sql = sql.replaceAll("[[ ]]{2,}", " ");
            log.info("==>  Preparing：" + sql);
            for (T item : list) {
                if (item == null) {
                    continue;
                }
                StringBuffer values = new StringBuffer();
                if (item instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) item;
                    for (int index = 0; index < list2.size(); index++) {
                        ParameterMapping pm = list2.get(index);
                        Object value = map.get(pm.getProperty());
                        values.append(value).append("(").append(value.getClass()).append("),");
                        statement.setObject(index + 1, value);
                    }
                } else if (item instanceof Long || item instanceof String || item instanceof Integer) {
                    statement.setObject(1, item);
                    values.append(item).append("(").append(StringUtils.substringAfterLast(item.getClass().toString(), ".")).append("),");
                } else {
                    List<String> params = new ArrayList<>();
                    for (int index = 0; index < list2.size(); index++) {
                        ParameterMapping pm = list2.get(index);
//                        String methodName = StringUtil.hump("get_" + pm.getProperty(), "_");
                        String methodName = null;
                        Method method = item.getClass().getMethod(methodName);
                        Object value = method.invoke(item);
                        params.add(value.toString());
                        statement.setObject(index + 1, value);
                        values.append(value).append("(").append(StringUtils.substringAfterLast(value.getClass().toString(), ".")).append("),");
                    }
                }
                statement.addBatch();
                values.delete(values.length() - 1, values.length());
                log.info("==> Parameters：" + values);
            }
            List<Long> resultList = new ArrayList<>();
            int[] resultArray = statement.executeBatch();
            if (sqlCommandType != SqlCommandType.INSERT) {
                for (int intval : resultArray) {
                    resultList.add(Long.valueOf(intval + ""));
                }
            } else {
                ResultSet resultSet = statement.getGeneratedKeys();
                while (resultSet.next()) {
                    try {
                        resultList.add(resultSet.getLong(1));
                    } catch (Exception e) {
                        log.error("错误：" + e.toString());
                    }
                }
            }
            return;
        } catch (Exception e) {
            log.error("错误：" + e.toString());
            throw new RuntimeException(e.toString());
        }
    }
}

（3）定义mapper接口方法

Long batchdeleteByPrimaryKey(List<?> list);

（4）定义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.example.recordlog.mapper.UserInfoMapper">

    <delete id="batchdeleteByPrimaryKey" parameterType="java.util.List">
        delete
        from user_info
        where id = #{id,jdbcType=BIGINT}
    </delete>

</mapper>

（5）定义NameSpace常量类

public class NameSpaceEnum {


    public static final String USER_MAPPER = "com.example.recordlog.mapper.UserInfoMapper";

}

（6）定义controller方法调用

import com.example.recordlog.bean.UserInfo;
import com.example.recordlog.constant.NameSpaceEnum;
import com.example.recordlog.service.BaseDao;
import com.example.recordlog.service.UserInfoService;
import com.example.recordlog.tools.RestResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api")
public class OperateController {

    @Autowired
    private UserInfoService userInfoService;

    @Autowired
    private BaseDao baseDao;

  

    @RequestMapping(value = "/batchRemoveInfo", produces = "application/json;charset=utf-8", method = {RequestMethod.POST})
    public RestResponse batchRemoveInfo(@RequestBody List<UserInfo> list) {
        List deleteList = baseDao.deleteList(NameSpaceEnum.USER_MAPPER, "batchdeleteByPrimaryKey", list);
        RestResponse restResponse = RestResponse.success(deleteList);
        return restResponse;
    }

}