MyBatis+SpringBoot整合 注入SqlSessionTemplate

大家好，又见面了，我是你们的朋友全栈君。

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

工具类接口层：

package com.miaosuan.dao;

import java.util.List;

import com.miaosuan.dao.dbenums.NameSpaceEnum;

/**
 * 数据库操作接口
 *
 * @param <T> 传入参数
 * @param <E> 返回结果
 * @author qin_wei
 */
public interface DBDao {

    <T, E> E select(NameSpaceEnum namespace, String id, T params);

    <T, E> List<E> selectList(NameSpaceEnum namespace, String id, T params);

    <T> int update(NameSpaceEnum namespace, String id, T params);

    <T> List<Long> updateList(NameSpaceEnum namespace, String id, List<T> list);

    <T> long insert(NameSpaceEnum namespace, String id, T params);

    <T> List<Long> insertList(NameSpaceEnum namespace, String id, List<T> list);

    <T> int delete(NameSpaceEnum namespace, String id, T params);

    <T> List<Long> deleteList(NameSpaceEnum namespace, String id, List<T> list);

    <T> void batchALL(NameSpaceEnum namespace, String id, List<T> params, Integer bathcount);
}

实现类：

package com.miaosuan.dao;

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;

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.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Repository;

import com.miaosuan.common.util.NullEmptyUtil;
import com.miaosuan.common.util.StringUtil;
import com.miaosuan.dao.dbenums.NameSpaceEnum;
import com.miaosuan.logger.Log;

@Repository("dbDao")
@Scope("prototype")
public class BaseDao implements DBDao {

    @Autowired
    SqlSessionTemplate sqlSessionTemplate;

    @Override
    public <T, E> E select(NameSpaceEnum namespace, String id, T params) {
        if (params == null) {
            return sqlSessionTemplate.selectOne(namespace.mapper + "." + id);
        } else {
            return sqlSessionTemplate.selectOne(namespace.mapper + "." + id, params);
        }
    }
//这个主要用来批量操作
    @Override
    public <T, E> List<E> selectList(NameSpaceEnum namespace, String id, T params) {
        if (params == null) {
            return sqlSessionTemplate.selectList(namespace.mapper + "." + id);
        } else {
            return sqlSessionTemplate.selectList(namespace.mapper + "." + id, params);
        }
    }

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

    @SuppressWarnings("unchecked")
    @Override
    public <T> List<Long> updateList(NameSpaceEnum namespace, String id, List<T> list) {
        try {
            if (list == null || list.isEmpty()) {
                return null;
            }
            MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(namespace.mapper + "." + id);
            SqlCommandType sqlCommandType = ms.getSqlCommandType();
            BoundSql boundSql = ms.getSqlSource().getBoundSql(list.get(0));
            String sql = boundSql.getSql();
            List<ParameterMapping> list2 = boundSql.getParameterMappings();
            Connection connection = sqlSessionTemplate.getConnection();
            PreparedStatement statement = null;
            if (sqlCommandType == SqlCommandType.INSERT) {
                statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                statement = connection.prepareStatement(sql);
            }
            for (T item : list) {
                if (NullEmptyUtil.isEmpty(item)) {
                    continue;
                }
                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());
                        statement.setObject(index + 1, value);
                    }
                } else if (item instanceof Long || item instanceof String || item instanceof Integer) {
                    statement.setObject(1, item);

                } else {
                    for (int index = 0; index < list2.size(); index++) {
                        ParameterMapping pm = list2.get(index);
                        String methodName = StringUtil.hump("get_" + pm.getProperty(), "_");
                        Method method = item.getClass().getMethod(methodName);
                        Object value = method.invoke(item);
                        statement.setObject(index + 1, value);
                    }
                }
                statement.addBatch();
            }
            List<Long> resultList = new ArrayList<Long>();
            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()) {
                    resultList.add(resultSet.getLong(0));
                }
            }
            return resultList;
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

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

    @Override
    public <T> List<Long> insertList(NameSpaceEnum namespace, String id, List<T> list) {
        return updateList(namespace, id, list);
    }

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

    @Override
    public <T> List<Long> deleteList(NameSpaceEnum namespace, String id, List<T> list) {
        return updateList(namespace, id, list);
    }
//所有的批量都可以用这个方法，它识别的是xml的sql，与方法无关;bathcount指的是没多少条提交一次事物
    @Override
    public <T> void batchALL(NameSpaceEnum 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();
            }
        }
    }

    @SuppressWarnings("unchecked")
    private <T> void batchUtil(NameSpaceEnum namespace, String sqlId, List<T> list) {
        try {
            if (list == null || list.isEmpty()) {
                return;
            }
            MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(namespace.mapper + "." + 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 (NullEmptyUtil.isEmpty(item)) {
                    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(), "_");
                        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());
        }
    }


    @SuppressWarnings("unchecked")
    protected <T> void printSql(String id, T params) {
        try {
            MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(id);
            BoundSql boundSql = ms.getSqlSource().getBoundSql(params);
            String sql = boundSql.getSql();
            sql = sql.replaceAll("\\n", "");
            sql = sql.replaceAll("\\t", "");
            sql = sql.replaceAll("[[ ]]{2,}", " ");
            List<ParameterMapping> list2 = boundSql.getParameterMappings();
            if (params == null) {

            } else if (params instanceof Map) {
                Map<String, Object> map = (Map<String, Object>) params;
                for (int index = 0; index < list2.size(); index++) {
                    ParameterMapping pm = list2.get(index);
                    Object value = map.get(pm.getProperty());
                    sql = sql.replaceFirst("[?]", value + "");
                }
            } else if (params instanceof Long || params instanceof String || params instanceof Integer) {
                sql = sql.replaceFirst("[?]", params + "");
            } else {
                for (int index = 0; index < list2.size(); index++) {
                    ParameterMapping pm = list2.get(index);
                    String methodName = StringUtil.hump("get_" + pm.getProperty(), "_");
                    Method method = params.getClass().getMethod(methodName);
                    Object value = method.invoke(params);
                    sql = sql.replaceFirst("[?]", value + "");
                }
            }
            Log.info(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

说明：NameSpaceEnum指的是你的xml的映射路径，不喜欢的可以写成自己的xml所在路径，我这边用的是枚举类

sqlid指的是你xml中方法的名字,

无论是单个操作还是批量操作，你的xml中的sql都是单个，这里的批量用的并不是mybatis的foreach操作而是通过传进来的集合批量提交事务到数据库‘’

具体使用:

接口定义：

接口实现类：

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.miaosuan.mapper.shop.shopimageinfo">//这里的路径随便写不要重复就可以
    <sql id="tableName">
       shop_image_info
    </sql>
    <sql id="where_sql">
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="spuId != null">
                and spu_id = #{spuId}
            </if>
        </where>
    </sql>

    <sql id="update_sql">
        <set>
            <if test="imageName != null and imageName != ''">
                image_name = #{imageName},
            </if>
            <if test="imageSuffix != null and imageSuffix != ''">
                image_suffix = #{imageSuffix},
            </if>
            <if test="url != null and url != ''">
                url = #{url},
            </if>
            <if test="zcyUrl != null and zcyUrl != ''">
                zcy_url = #{zcyUrl},
            </if>
            <if test="zcyStatus != null">
                zcy_status = #{zcyStatus},
            </if>
            <if test="imgType != null and imgType != ''">
                img_type = #{imgType},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="mainImg != null">
                main_img = #{mainImg},
            </if>
        </set>
    </sql>

    <select id="list" resultType="DBMap" parameterType="DBMap">
        select * from
        <include refid="tableName"/>
        <include refid="where_sql"/>
    </select>

    <select id="get" resultType="DBMap" parameterType="DBMap">
        select * from
        <include refid="tableName"/>
        <include refid="where_sql"/>
        limit 1
    </select>

    <update id="update" parameterType="DBMap">
        update
        <include refid="tableName"/>
        <include refid="update_sql"/>
        <include refid="where_sql"/>
    </update>

    <delete id="delete" parameterType="DBMap">
        delete from
        <include refid="tableName"/>
        <include refid="where_sql"/>
    </delete>

    <insert id="insert" parameterType="DBMap" keyProperty="id" useGeneratedKeys="true">
        insert into
        <include refid="tableName"/>
        (image_name,image_suffix,spu_id,url,zcy_url,zcy_status,img_type
        <if test="status != null">
            ,status
        </if>,main_img
        )
        values
        (#{imageName},#{imageSuffix},#{spuId},#{url},#{zcyUrl},#{zcyStatus},#{imgType}
        <if test="status != null">
            ,#{status}
        </if>
        ,#{mainImg}
        )
    </insert>
    <select id="selectBySpuId" resultType="DBMap" parameterType="java.lang.Long">
        select * from
        <include refid="tableName"></include>
        <where>
            and spu_id = #{spuId,jdbcType=BIGINT}
            and img_type = 0 order by main_img desc ,id desc
        </where>
    </select>
    <select id="selectIdsByShopId" resultType="java.lang.Long" parameterType="java.lang.Long">
        select id from shop_image_info
        <where>
            spu_id = #{spuId} and img_type = 0
        </where>
    </select>
    <update id="updateByPrimaryKeySelective" parameterType="DBMap">
        update shop_image_info
        <set>
            <if test="imageName != null">
                image_name = #{imageName},
            </if>
            <if test="imageSuffix != null">
                image_suffix = #{imageSuffix},
            </if>
            <if test="spuId != null">
                spu_id = #{spuId},
            </if>
            <if test="url != null">
                url = #{url},
            </if>
            <if test="zcyUrl != null">
                zcy_url = #{zcyUrl},
            </if>
            <if test="zcyStatus != null">
                zcy_status = #{zcyStatus},
            </if>
            <if test="imgType != null">
                img_type = #{imgType},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="mainImg != null">
                main_img = #{mainImg},
            </if>
        </set>
        where id = #{id}
    </update>
    <insert id="insertSelective" parameterType="DBMap">
        insert into shop_image_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="imageName != null">
                image_name,
            </if>
            <if test="imageSuffix != null">
                image_suffix,
            </if>
            <if test="spuId != null">
                spu_id,
            </if>
            <if test="url != null">
                url,
            </if>
            <if test="zcyUrl != null">
                zcy_url,
            </if>
            <if test="zcyStatus != null">
                zcy_status,
            </if>
            <if test="imgType != null">
                img_type,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="mainImg != null">
                main_img,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id},
            </if>
            <if test="imageName != null">
                #{imageName},
            </if>
            <if test="imageSuffix != null">
                #{imageSuffix},
            </if>
            <if test="spuId != null">
                #{spuId},
            </if>
            <if test="url != null">
                #{url},
            </if>
            <if test="zcyUrl != null">
                #{zcyUrl},
            </if>
            <if test="zcyStatus != null">
                #{zcyStatus},
            </if>
            <if test="imgType != null">
                #{imgType},
            </if>
            <if test="status != null">
                #{status},
            </if>
            <if test="mainImg != null">
                #{mainImg},
            </if>
        </trim>
    </insert>
    <delete id="batchDeleteByIds" parameterType="java.util.List">
        delete from
        <include refid="tableName"/>
        where img_type = 0 and id in
        <foreach collection="list" item="params" open="(" separator="," close=")">
            #{params}
        </foreach>
    </delete>
    <delete id="deleteById" parameterType="java.lang.Long">
        delete from
        <include refid="tableName"/>
        where img_type = 0 and id = #{params}
    </delete>
    <delete id="deleteWithoutByIds" parameterType="DBMap">
        delete from
        <include refid="tableName"/>
        where img_type = 0 and spu_id = #{spuId}
        <if test="ids != null">
            and id not in
            <foreach collection="list" item="ids" open="(" separator="," close=")">
                #{ids}
            </foreach>
        </if>
    </delete>
    <!--批量添加标准库数据照片-->
    <insert id="insertImage" parameterType="com.miaosuan.dao.entity.standard.StandardImageInfo">
        insert into
        <include refid="tableName"></include>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="imageName != null">
                image_name,
            </if>
            <if test="imageSuffix != null">
                image_suffix,
            </if>
            <if test="spuId != null">
                spu_id,
            </if>
            <if test="url != null">
                url,
            </if>
            <if test="imgType != null">
                img_type,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="mainImg != null">
                main_img,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="imageName != null">
                #{imageName,jdbcType=VARCHAR},
            </if>
            <if test="imageSuffix != null">
                #{imageSuffix,jdbcType=VARCHAR},
            </if>
            <if test="spuId != null">
                #{spuId,jdbcType=INTEGER},
            </if>
            <if test="url != null">
                #{url,jdbcType=VARCHAR},
            </if>
            <if test="imgType != null">
                #{imgType,jdbcType=TINYINT},
            </if>
            <if test="status != null">
                #{status,jdbcType=TINYINT},
            </if>
            <if test="mainImg != null">
                #{mainImg,jdbcType=TINYINT},
            </if>
        </trim>
    </insert>

无论批量还是单个都可以调用具体看你调用的dao里面的批量方法还是单个 这个sql是如果数据库没有这条数据就添加，否则就修改，通过主键id判断，如果不喜欢这中sql可以自己用常规的update方法
    <insert id="insertOrUpdate" parameterType="DBMap" useGeneratedKeys="true"
            keyProperty="id" keyColumn="id">
        insert into
        <include refid="tableName"></include>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="imageName != null and imageName!=''">
                image_name,
            </if>
            <if test="imageSuffix != null and imageSuffix!=''">
                image_suffix,
            </if>
            <if test="spuId != null">
                spu_id,
            </if>
            <if test="url != null and url!=''">
                url,
            </if>
            <if test="imgType != null">
                img_type,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="mainImg != null">
                main_img,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id},
            </if>
            <if test="imageName != null">
                #{imageName,jdbcType=VARCHAR},
            </if>
            <if test="imageSuffix != null">
                #{imageSuffix,jdbcType=VARCHAR},
            </if>
            <if test="spuId != null">
                #{spuId,jdbcType=INTEGER},
            </if>
            <if test="url != null">
                #{url,jdbcType=VARCHAR},
            </if>
            <if test="imgType != null">
                #{imgType,jdbcType=TINYINT},
            </if>
            <if test="status != null">
                #{status,jdbcType=TINYINT},
            </if>
            <if test="mainImg != null">
                #{mainImg,jdbcType=TINYINT},
            </if>
        </trim>
        ON DUPLICATE KEY UPDATE
        <trim suffixOverrides=",">
            <if test="imageName != null and imageName!=''">
                image_name = #{imageName,jdbcType=VARCHAR},
            </if>
            <if test="imageSuffix != null and imageSuffix!=''">
                image_suffix = #{imageSuffix,jdbcType=VARCHAR},
            </if>
            <if test="spuId != null">
                spu_id = #{spuId,jdbcType=INTEGER},
            </if>
            <if test="url != null and url!=''">
                url=#{url,jdbcType=VARCHAR},
            </if>
            <if test="imgType != null">
                img_type=#{imgType,jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status=#{status,jdbcType=TINYINT},
            </if>
            <if test="mainImg != null">
                main_img=#{mainImg,jdbcType=TINYINT},
            </if>
        </trim>
    </insert>
</mapper>

所有的接口层只需要定义xml，通过dao调用就可以直接获取数据库数据。