mybatis spring配置SqlSessionTemplate的使用方式

mybatis spring配置SqlSessionTemplate的使用方式

mybatis spring配置SqlSessionTemplate使用

1.application.xml配置

xmlns:xsi="http://w3.org/2001/XMLSchema-instance"

xmlns:aop="http://springframework.org/schema/aop"

xmlns:tx="http://springframework.org/schema/tx"

xmlns:context="http://springframework.org/schema/context"

xmlns:dwr="http://directwebremoting.org/schema/spring-dwr"

xmlns:lang="http://springframework.org/schema/lang"

xmlns:top="http://comtop.org/schema/spring-top"

xsi:schemaLocation="

http://springframework.org/schema/beans

http://springframework.org/schema/beans/spring-beans-3.2.xsd

http://springframework.org/schema/tx

http://springframework.org/schema/tx/spring-tx-3.2.xsd

http://springframework.org/schema/aop

http://springframework.org/schema/aop/spring-aop-3.2.xsd

http://springframework.org/schema/context

http://springframework.org/schema/context/spring-context-3.2.xsd

http://directwebremoting.org/schema/spring-dwr

http://directwebremoting.org/schema/spring-dwr-3.0.xsd

http://springframework.org/schema/lang

http://springframework.org/schema/lang/spring-lang-3.2.xsd

http://comtop.org/schema/spring-top

http://comtop.org/schema/top/spring-top.xsd">

destroy-method="close">

xmlns:xsi="http://w3.org/2001/XMLSchema-instance"

xmlns:aop="http://springframework.org/schema/aop"

xmlns:tx="http://springframework.org/schema/tx"

xmlns:context="http://springframework.org/schema/context"

xmlns:dwr="http://directwebremoting.org/schema/spring-dwr"

xmlns:lang="http://springframework.org/schema/lang"

xmlns:top="http://comtop.org/schema/spring-top"

xsi:schemaLocation="

http://springframework.org/schema/beans

http://springframework.org/schema/beans/spring-beans-3.2.xsd

http://springframework.org/schema/tx

http://springframework.org/schema/tx/spring-tx-3.2.xsd

http://springframework.org/schema/aop

http://springframework.org/schema/aop/spring-aop-3.2.xsd

http://springframework.org/schema/context

http://springframework.org/schema/context/spring-context-3.2.xsd

http://directwebremoting.org/schema/spring-dwr

http://directwebremoting.org/schema/spring-dwr-3.0.xsd

http://springframework.org/schema/lang

http://springframework.org/schema/lang/spring-lang-3.2.xsd

http://comtop.org/schema/spring-top

http://comtop.org/schema/top/spring-top.xsd">

destroy-method="close">

destroy-method="close">

2.MyBatisDAO ：

package com.dwr;

import java.util.List;

import javax.annotation.Resource;

import org.apache.ibatis.session.RowBounds;

import org.mybatis.spring.SqlSessionTemplate;

import org.springframework.stereotype.Repository;

@Repository

public class MyBatisDAO{

@Resource

private SqlSessionTemplate sqlSessionTemplate;

public MyBatisDAO() {

}

public void setSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) {

this.sqlSessionTemplate = sqlSessionTemplate;

}

public void delete(String statementId, Object deleteObject) {

try {

this.sqlSessionTemplate.delete(statementId, deleteObject);

} catch (Exception e) {

}

}

public void insert(String statementId, Object insertObject) {

try {

this.sqlSessionTemplate.insert(statementId, insertObject);

} catch (Exception e) {

}

}

public void update(String statementId, Object updateObject) {

try {

this.sqlSessionTemplate.update(statementId, updateObject);

} catch (Exception e) {

}

}

public Object getObject(String statementId, Object selectParamObject) {

return this.sqlSessionTemplate

.selectOne(statementId, selectParamObject);

}

@SuppressWarnings("all")

public List queryList(String statementId, Object queryParamObject) {

return this.sqlSessionTemplate

.selectList(statementId, queryParamObject);

}

@SuppressWarnings("all")

public List queryList(String statementId, Object queryParamObject,

int pageNo, int pageSize) {

RowBounds objRowBounds;

int iOffset = (pageNo - 1) * pageSize;

objRowBounds = new RowBounds(iOffset, pageSize);

return this.sqlSessionTemplate.selectList(statementId,

queryParamObject, objRowBounds);

}

}

3.mybatis配置文件

PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"

"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

parameterType="com.mybatis.Test"

resultType="int">

SELECT COUNT(1) FROM INF_CONSUMER T WHERE T.UUID = #{uuid}

parameterType="com.mybatis.Test"

resultType="int">

SELECT COUNT(1) FROM INF_CONSUMER T WHERE T.UUID = #{uuid}

4.具体DAO配置

package com.dwr;

import org.springframework.stereotype.Repository;

import com.mybatis.Test;

@Repository

public class TestDAO extends MyBatisDAO {

public int getCount(Test test) {

return (Integer) this.getObject("Test.queryDataCount", test);

}

}

MyBatis+SpringBoot整合 注入SqlSessionTemplate

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

工具类接口层：

package com.miaosuan.dao;

import java.util.List;

import com.miaosuan.dao.dbenums.NameSpaceEnum;

/**

* 数据库操作接口

*

* @param 传入参数

* @param 返回结果

* @author qin_wei

*/

public interface DBDao {

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

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

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

updateList(NameSpaceEnum namespace, String id, List list);

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

List insertList(NameSpaceEnum namespace, String id, List list);

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

List deleteList(NameSpaceEnum namespace, String id, List list);

void batchALL(NameSpaceEnum namespace, String id, List 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.mappinCqRLDQpWKg.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 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 List 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 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 List updateList(NameSpaceEnum namespace, String id, List 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 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 map = (Map) 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 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()) {

resultList.add(resultSet.getLong(0));

}

}

return resultList;

} catch (Exception e) {

throw new RuntimeException(e.getMessage());

}

}

@Override

public long insert(NameSpaceEnum namespace, String id, T params) {

return update(namespace, id, params);

}

@Override

public List insertList(NameSpaceEnum namespace, String id, List list) {

return updateList(namespace, id, list);

}

@Override

public int delete(NameSpaceEnum namespace, String id, T params) {

return update(namespace, id, params);

}

@Override

public List deleteList(NameSpaceEnum namespace, String id, List list) {

return updateList(namespace, id, list);

}

//所有的批量都可以用这个方法，它识别的是xml的sql，与方法无关;bathcount指的是没多少条提交一次事物

@Override

public void batchALL(NameSpaceEnum namespace, String sqlId, List list, Integer bathcount) {

List 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 void batchUtil(NameSpaceEnum namespace, String sqlId, List 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 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 map = (Map) 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 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 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 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 list2 = boundSql.getParameterMappings();

if (params == null) {

} else if (params instanceof Map) {

Map map = (Map) 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：

//这里的路径随便写不要重复就可以

shop_image_info

and id = #{id}

and spu_id = #{spuId}

image_name = #{imageName},

image_suffix = #{imageSuffix},

url = #{url},

zcy_url = #{zcyUrl},

zcy_status = #{zcyStatus},

img_type = #{imgType},

status = #{status},

main_img = #{mainImg},

select * from

select * from

limit 1

update

delete from

insert into

(image_name,image_suffix,spu_id,url,zcy_url,zcy_status,img_type

,status

,main_img

)

values

(#{imageName},#{imageSuffix},#{spuId},#{url},#{zcyUrl},#{zcyStatus},#{imgType}

,#{status}

,#{mainImg}

)

select * from

and spu_id = #{spuId,jdbcType=BIGINT}

and img_type = 0 order by main_img desc ,id desc

select id from shop_image_info

spu_id = #{spuId} and img_type = 0

update shop_image_info

image_name = #{imageName},

image_suffix = #{imageSuffix},

spu_id = #{spuId},

url = #{url},

zcy_url = #{zcyUrl},

zcy_status = #{zcyStatus},

img_type = #{imgType},

status = #{status},

main_img = #{mainImg},

where id = #{id}

insert into shop_image_info

id,

image_name,

image_suffix,

spu_id,

url,

zcy_url,

zcy_status,

img_type,

status,

main_img,

#{id},

#{imageName},

#{imageSuffix},

#{spuId},

#{url},

#{zcyUrl},

#{zcyStatus},

#{imgType},

#{status},

#{mainImg},

delete from

where img_type = 0 and id in

#{params}

delete from

where img_type = 0 and id = #{params}

delete from

where img_type = 0 and spu_id = #{spuId}

and id not in

#{ids}

insert into

image_name,

image_suffix,

spu_id,

url,

img_type,

status,

main_img,

#{imageName,jdbcType=VARCHAR},

#{imageSuffix,jdbcType=VARCHAR},

#{spuId,jdbcType=INTEGER},

#{url,jdbcType=VARCHAR},

#{imgType,jdbcType=TINYINT},

#{status,jdbcType=TINYINT},

#{mainImg,jdbcType=TINYINT},

无论批量还是单个都可以调用具体看你调用的dao里面的批量方法还是单个这个sql是如果数据库没有这条数据就添加，否则就修改，通过主键id判断，如果不喜欢这中sql可以自己用常规的update方法

keyProperty="id" keyColumn="id">

insert into

id,

image_name,

image_suffix,

spu_id,

url,

</if>

img_type,

status,

main_img,

#{id},

#{imageName,jdbcType=VARCHAR},

#{imageSuffix,jdbcType=VARCHAR},

#{spuId,jdbcType=INTEGER},

#{url,jdbcType=VARCHAR},

#{imgType,jdbcType=TINYINT},

#{status,jdbcType=TINYINT},

#{mainImg,jdbcType=TINYINT},

ON DUPLICATE KEY UPDATE

image_name = #{imageName,jdbcType=VARCHAR},

image_suffix = #{imageSuffix,jdbcType=VARCHAR},

spu_id = #{spuId,jdbcType=INTEGER},

url=#{url,jdbcType=VARCHAR},

img_type=#{imgType,jdbcType=TINYINT},

status=#{status,jdbcType=TINYINT},

main_img=#{mainImg,jdbcType=TINYINT},

keyProperty="id" keyColumn="id">

insert into

id,

image_name,

image_suffix,

spu_id,

url,

</if>

img_type,

status,

main_img,

#{id},

#{imageName,jdbcType=VARCHAR},

#{imageSuffix,jdbcType=VARCHAR},

#{spuId,jdbcType=INTEGER},

#{url,jdbcType=VARCHAR},

#{imgType,jdbcType=TINYINT},

#{status,jdbcType=TINYINT},

#{mainImg,jdbcType=TINYINT},

ON DUPLICATE KEY UPDATE

image_name = #{imageName,jdbcType=VARCHAR},

image_suffix = #{imageSuffix,jdbcType=VARCHAR},

spu_id = #{spuId,jdbcType=INTEGER},

url=#{url,jdbcType=VARCHAR},

img_type=#{imgType,jdbcType=TINYINT},

status=#{status,jdbcType=TINYINT},

main_img=#{mainImg,jdbcType=TINYINT},

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。