mybatis spring配置SqlSessionTemplate使用
1.application.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:dwr="http://www.directwebremoting.org/schema/spring-dwr"
xmlns:lang="http://www.springframework.org/schema/lang"
xmlns:top="http://www.comtop.org/schema/spring-top"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.directwebremoting.org/schema/spring-dwr
http://www.directwebremoting.org/schema/spring-dwr-3.0.xsd
http://www.springframework.org/schema/lang
http://www.springframework.org/schema/lang/spring-lang-3.2.xsd
http://www.comtop.org/schema/spring-top
http://www.comtop.org/schema/top/spring-top.xsd">
<!-- spring 注解 -->
<context:component-scan base-package="com.dwr"/>
<!-- 这句的作用是表示允许DWR访问Spring的Context -->
<dwr:annotation-config id="dwr_as"/>
<!-- 扫描加了注解@RemoteProxy & @RemoteMethod 的对象 -->
<dwr:annotation-scan scanRemoteProxy="false" base-package="com.dwr" />
<!-- dwr初始化配置 -->
<dwr:configuration></dwr:configuration>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@10.10.15.29:1521:xxxx" />
<property name="username" value="edmp" />
<property name="password" value="edmp" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:com/mybatis
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调用就可以直接获取数据库数据。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
