MyBatis+SpringBoot整合 注入SqlSessionTemplate

MyBatis+SpringBoot整合 注入SqlSessionTemplate实际开发中我们操作数据库持久化,总是需要写重复的mapper,service,xml浪费了我们大量的时间,在这里推荐大家使用SqlSessionTemplate废话不多说直接上代码工具类接口层:packagecom.miaosuan.dao;importjava.util.List;importcom.miaosuan.dao.dbenums.NameSpaceEnum;…

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

实际开发中我们操作数据库持久化,总是需要写重复的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操作而是通过传进来的集合批量提交事务到数据库‘’MyBatis+SpringBoot整合 注入SqlSessionTemplate

 

具体使用:

接口定义:MyBatis+SpringBoot整合 注入SqlSessionTemplate

接口实现类:

MyBatis+SpringBoot整合 注入SqlSessionTemplate

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调用就可以直接获取数据库数据。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/138218.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • nginx配置端口_修改redis端口

    nginx配置端口_修改redis端口sudosu#进入nginx自己的配置文件cd/etc/nginx/sites-enabledvimdefault#修改默认监听端口server{ listen80default_server; #这是nginx的端口,可修改 listen[::]:80default_server; #这是ipv6端口…

  • access token

    最短时间来,打算把自己学习的东西都总结一遍,发到博客上面来。带大家来了解windows访问令牌0x00acesstoken的作用accesstoken的作用说简单点就是为了系统的安全0x

    2021年12月11日
  • unix grep命令_grep命令实例

    unix grep命令_grep命令实例grep一般格式为:grep[选项]基本正则表达式[文件]这里基本正则表达式可为字符串。单引号双引号在grep命令中输入字符串参数时,最好将其用双引号括起来。在调用模式匹配时,应使用单引号。 例如:“mystring”。这样做有两个原因

  • String类型数字与Integer最大值比较[通俗易懂]

    String类型数字与Integer最大值比较[通俗易懂]将String类型数字转为Integer类型时需要先判断范围是否超过Integer最大值,否则会报异常/***与最大值比较,大于返回1,等于返回0,小于返回-1*@paramval*@return*/publicintcomparetoMaxInt(Stringval){BigDecimalbd=newBigDecimal(val);BigDecimalmaxInt=ne

  • 详解神经网络的前向传播和反向传播(从头推导)

    详解神经网络的前向传播和反向传播(从头推导)详解神经网络的前向传播和反向传播本篇博客是对MichaelNielsen所著的《NeuralNetworkandDeepLearning》第2章内容的解读,有兴趣的朋友可以直接阅读原文NeuralNetworkandDeepLearning。  对神经网络有些了解的人可能都知道,神经网络其实就是一个输入XXX到输出YYY的映射函数:f(X)=Yf(X)=Yf(X)=Y,函…

  • 博科FC光纤交换机替换zone配置导入导出指导

    博科FC光纤交换机替换zone配置导入导出指导一、旧交换机配置导出。1、笔记本搭建ftp服务,可以在百度下载个ftp软件工具使用,保存的文件路径,用户名及密码例如2、老设备在命令界面操作导出命令switch:admin>configuploadProtocol(scporftp)[ftp]:ftpServerNameorIPAddress[host]:192.168.200.xxFileName[confi…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号