mybatis spring配置SqlSessionTemplate的使用方法(mybatis,spring,开发技术)

mybatis spring配置SqlSessionTemplate使用

1.application.xml配置

<?xmlversion="1.0"encoding="UTF-8"?><beansxmlns="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/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-3.2.xsdhttp://www.directwebremoting.org/schema/spring-dwrhttp://www.directwebremoting.org/schema/spring-dwr-3.0.xsdhttp://www.springframework.org/schema/langhttp://www.springframework.org/schema/lang/spring-lang-3.2.xsdhttp://www.comtop.org/schema/spring-tophttp://www.comtop.org/schema/top/spring-top.xsd"><!--spring注解--><context:component-scanbase-package="com.dwr"/><!--这句的作用是表示允许DWR访问Spring的Context--><dwr:annotation-configid="dwr_as"/><!--扫描加了注解@RemoteProxy&@RemoteMethod的对象--><dwr:annotation-scanscanRemoteProxy="false"base-package="com.dwr"/><!--dwr初始化配置--><dwr:configuration></dwr:configuration><beanid="dataSource"class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"><propertyname="driverClassName"value="oracle.jdbc.driver.OracleDriver"/><propertyname="url"value="jdbc:oracle:thin:@10.10.15.29:1521:xxxx"/><propertyname="username"value="edmp"/><propertyname="password"value="edmp"/></bean><beanid="sqlSessionFactory"class="org.mybatis.spring.SqlSessionFactoryBean"><propertyname="dataSource"ref="dataSource"/><propertyname="mapperLocations"value="classpath:com/mybatis/*.xml"/></bean><beanid="sqlSessionTemplate"class="org.mybatis.spring.SqlSessionTemplate"><propertyname="sqlSessionFactory"ref="sqlSessionFactory"/></bean><importresource="RESTful.xml"/></beans>

2.MyBatisDAO ：

packagecom.dwr;importjava.util.List;importjavax.annotation.Resource;importorg.apache.ibatis.session.RowBounds;importorg.mybatis.spring.SqlSessionTemplate;importorg.springframework.stereotype.Repository;@RepositorypublicclassMyBatisDAO{@ResourceprivateSqlSessionTemplatesqlSessionTemplate;publicMyBatisDAO(){}publicvoidsetSqlSessionTemplate(SqlSessionTemplatesqlSessionTemplate){this.sqlSessionTemplate=sqlSessionTemplate;}publicvoiddelete(StringstatementId,ObjectdeleteObject){try{this.sqlSessionTemplate.delete(statementId,deleteObject);}catch(Exceptione){}}publicvoidinsert(StringstatementId,ObjectinsertObject){try{this.sqlSessionTemplate.insert(statementId,insertObject);}catch(Exceptione){}}publicvoidupdate(StringstatementId,ObjectupdateObject){try{this.sqlSessionTemplate.update(statementId,updateObject);}catch(Exceptione){}}publicObjectgetObject(StringstatementId,ObjectselectParamObject){returnthis.sqlSessionTemplate.selectOne(statementId,selectParamObject);}@SuppressWarnings("all")publicListqueryList(StringstatementId,ObjectqueryParamObject){returnthis.sqlSessionTemplate.selectList(statementId,queryParamObject);}@SuppressWarnings("all")publicListqueryList(StringstatementId,ObjectqueryParamObject,intpageNo,intpageSize){RowBoundsobjRowBounds;intiOffset=(pageNo-1)*pageSize;objRowBounds=newRowBounds(iOffset,pageSize);returnthis.sqlSessionTemplate.selectList(statementId,queryParamObject,objRowBounds);}}

3.mybatis配置文件

<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//ibatis.apache.org//DTDMapper3.0//EN""http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"><mappernamespace="Test"><selectid="queryDataCount"parameterType="com.mybatis.Test"resultType="int">SELECTCOUNT(1)FROMINF_CONSUMERTWHERET.UUID=#{uuid}</select></mapper>

4.具体DAO配置

packagecom.dwr;importorg.springframework.stereotype.Repository;importcom.mybatis.Test;@RepositorypublicclassTestDAOextendsMyBatisDAO{publicintgetCount(Testtest){return(Integer)this.getObject("Test.queryDataCount",test);}}

MyBatis+SpringBoot整合 注入SqlSessionTemplate

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

工具类接口层：

packagecom.miaosuan.dao;importjava.util.List;importcom.miaosuan.dao.dbenums.NameSpaceEnum;/***数据库操作接口**@param<T>传入参数*@param<E>返回结果*@authorqin_wei*/publicinterfaceDBDao{<T,E>Eselect(NameSpaceEnumnamespace,Stringid,Tparams);<T,E>List<E>selectList(NameSpaceEnumnamespace,Stringid,Tparams);<T>intupdate(NameSpaceEnumnamespace,Stringid,Tparams);<T>List<Long>updateList(NameSpaceEnumnamespace,Stringid,List<T>list);<T>longinsert(NameSpaceEnumnamespace,Stringid,Tparams);<T>List<Long>insertList(NameSpaceEnumnamespace,Stringid,List<T>list);<T>intdelete(NameSpaceEnumnamespace,Stringid,Tparams);<T>List<Long>deleteList(NameSpaceEnumnamespace,Stringid,List<T>list);<T>voidbatchALL(NameSpaceEnumnamespace,Stringid,List<T>params,Integerbathcount);}

实现类：

packagecom.miaosuan.dao;importjava.lang.reflect.Method;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importorg.apache.commons.lang3.StringUtils;importorg.apache.ibatis.mapping.BoundSql;importorg.apache.ibatis.mapping.MappedStatement;importorg.apache.ibatis.mapping.ParameterMapping;importorg.apache.ibatis.mapping.SqlCommandType;importorg.mybatis.spring.SqlSessionTemplate;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.context.annotation.Scope;importorg.springframework.stereotype.Repository;importcom.miaosuan.common.util.NullEmptyUtil;importcom.miaosuan.common.util.StringUtil;importcom.miaosuan.dao.dbenums.NameSpaceEnum;importcom.miaosuan.logger.Log;@Repository("dbDao")@Scope("prototype")publicclassBaseDaoimplementsDBDao{@AutowiredSqlSessionTemplatesqlSessionTemplate;@Overridepublic<T,E>Eselect(NameSpaceEnumnamespace,Stringid,Tparams){if(params==null){returnsqlSessionTemplate.selectOne(namespace.mapper+"."+id);}else{returnsqlSessionTemplate.selectOne(namespace.mapper+"."+id,params);}}//这个主要用来批量操作@Overridepublic<T,E>List<E>selectList(NameSpaceEnumnamespace,Stringid,Tparams){if(params==null){returnsqlSessionTemplate.selectList(namespace.mapper+"."+id);}else{returnsqlSessionTemplate.selectList(namespace.mapper+"."+id,params);}}@Overridepublic<T>intupdate(NameSpaceEnumnamespace,Stringid,Tparams){if(params==null){returnsqlSessionTemplate.update(namespace.mapper+"."+id);}else{returnsqlSessionTemplate.update(namespace.mapper+"."+id,params);}}@SuppressWarnings("unchecked")@Overridepublic<T>List<Long>updateList(NameSpaceEnumnamespace,Stringid,List<T>list){try{if(list==null||list.isEmpty()){returnnull;}MappedStatementms=sqlSessionTemplate.getConfiguration().getMappedStatement(namespace.mapper+"."+id);SqlCommandTypesqlCommandType=ms.getSqlCommandType();BoundSqlboundSql=ms.getSqlSource().getBoundSql(list.get(0));Stringsql=boundSql.getSql();List<ParameterMapping>list2=boundSql.getParameterMappings();Connectionconnection=sqlSessionTemplate.getConnection();PreparedStatementstatement=null;if(sqlCommandType==SqlCommandType.INSERT){statement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);}else{statement=connection.prepareStatement(sql);}for(Titem:list){if(NullEmptyUtil.isEmpty(item)){continue;}if(iteminstanceofMap){Map<String,Object>map=(Map<String,Object>)item;for(intindex=0;index<list2.size();index++){ParameterMappingpm=list2.get(index);Objectvalue=map.get(pm.getProperty());statement.setObject(index+1,value);}}elseif(iteminstanceofLong||iteminstanceofString||iteminstanceofInteger){statement.setObject(1,item);}else{for(intindex=0;index<list2.size();index++){ParameterMappingpm=list2.get(index);StringmethodName=StringUtil.hump("get_"+pm.getProperty(),"_");Methodmethod=item.getClass().getMethod(methodName);Objectvalue=method.invoke(item);statement.setObject(index+1,value);}}statement.addBatch();}List<Long>resultList=newArrayList<Long>();int[]resultArray=statement.executeBatch();if(sqlCommandType!=SqlCommandType.INSERT){for(intintval:resultArray){resultList.add(Long.valueOf(intval+""));}}else{ResultSetresultSet=statement.getGeneratedKeys();while(resultSet.next()){resultList.add(resultSet.getLong(0));}}returnresultList;}catch(Exceptione){thrownewRuntimeException(e.getMessage());}}@Overridepublic<T>longinsert(NameSpaceEnumnamespace,Stringid,Tparams){returnupdate(namespace,id,params);}@Overridepublic<T>List<Long>insertList(NameSpaceEnumnamespace,Stringid,List<T>list){returnupdateList(namespace,id,list);}@Overridepublic<T>intdelete(NameSpaceEnumnamespace,Stringid,Tparams){returnupdate(namespace,id,params);}@Overridepublic<T>List<Long>deleteList(NameSpaceEnumnamespace,Stringid,List<T>list){returnupdateList(namespace,id,list);}//所有的批量都可以用这个方法，它识别的是xml的sql，与方法无关;bathcount指的是没多少条提交一次事物@Overridepublic<T>voidbatchALL(NameSpaceEnumnamespace,StringsqlId,List<T>list,Integerbathcount){List<T>data=newArrayList<>();for(inti=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>voidbatchUtil(NameSpaceEnumnamespace,StringsqlId,List<T>list){try{if(list==null||list.isEmpty()){return;}MappedStatementms=sqlSessionTemplate.getConfiguration().getMappedStatement(namespace.mapper+"."+sqlId);SqlCommandTypesqlCommandType=ms.getSqlCommandType();BoundSqlboundSql=ms.getSqlSource().getBoundSql(list.get(0));Stringsql=boundSql.getSql();List<ParameterMapping>list2=boundSql.getParameterMappings();Connectionconnection=sqlSessionTemplate.getSqlSessionFactory().openSession().getConnection();PreparedStatementstatement=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(Titem:list){if(NullEmptyUtil.isEmpty(item)){continue;}StringBuffervalues=newStringBuffer();if(iteminstanceofMap){Map<String,Object>map=(Map<String,Object>)item;for(intindex=0;index<list2.size();index++){ParameterMappingpm=list2.get(index);Objectvalue=map.get(pm.getProperty());values.append(value).append("(").append(value.getClass()).append("),");statement.setObject(index+1,value);}}elseif(iteminstanceofLong||iteminstanceofString||iteminstanceofInteger){statement.setObject(1,item);values.append(item).append("(").append(StringUtils.substringAfterLast(item.getClass().toString(),".")).append("),");}else{List<String>params=newArrayList<>();for(intindex=0;index<list2.size();index++){ParameterMappingpm=list2.get(index);StringmethodName=StringUtil.hump("get_"+pm.getProperty(),"_");Methodmethod=item.getClass().getMethod(methodName);Objectvalue=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=newArrayList<>();int[]resultArray=statement.executeBatch();if(sqlCommandType!=SqlCommandType.INSERT){for(intintval:resultArray){resultList.add(Long.valueOf(intval+""));}}else{ResultSetresultSet=statement.getGeneratedKeys();while(resultSet.next()){try{resultList.add(resultSet.getLong(1));}catch(Exceptione){Log.error("错误："+e.toString());}}}return;}catch(Exceptione){Log.error("错误："+e.toString());thrownewRuntimeException(e.toString());}}@SuppressWarnings("unchecked")protected<T>voidprintSql(Stringid,Tparams){try{MappedStatementms=sqlSessionTemplate.getConfiguration().getMappedStatement(id);BoundSqlboundSql=ms.getSqlSource().getBoundSql(params);Stringsql=boundSql.getSql();sql=sql.replaceAll("\\n","");sql=sql.replaceAll("\\t","");sql=sql.replaceAll("[[]]{2,}","");List<ParameterMapping>list2=boundSql.getParameterMappings();if(params==null){}elseif(paramsinstanceofMap){Map<String,Object>map=(Map<String,Object>)params;for(intindex=0;index<list2.size();index++){ParameterMappingpm=list2.get(index);Objectvalue=map.get(pm.getProperty());sql=sql.replaceFirst("[?]",value+"");}}elseif(paramsinstanceofLong||paramsinstanceofString||paramsinstanceofInteger){sql=sql.replaceFirst("[?]",params+"");}else{for(intindex=0;index<list2.size();index++){ParameterMappingpm=list2.get(index);StringmethodName=StringUtil.hump("get_"+pm.getProperty(),"_");Methodmethod=params.getClass().getMethod(methodName);Objectvalue=method.invoke(params);sql=sql.replaceFirst("[?]",value+"");}}Log.info(sql);}catch(Exceptione){e.printStackTrace();}}}

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

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

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

具体使用：

接口定义：

接口实现类：

xml：

<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTDMapper3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.miaosuan.mapper.shop.shopimageinfo">//这里的路径随便写不要重复就可以<sqlid="tableName">shop_image_info</sql><sqlid="where_sql"><where><iftest="id!=null">andid=#{id}</if><iftest="spuId!=null">andspu_id=#{spuId}</if></where></sql><sqlid="update_sql"><set><iftest="imageName!=nullandimageName!=''">image_name=#{imageName},</if><iftest="imageSuffix!=nullandimageSuffix!=''">image_suffix=#{imageSuffix},</if><iftest="url!=nullandurl!=''">url=#{url},</if><iftest="zcyUrl!=nullandzcyUrl!=''">zcy_url=#{zcyUrl},</if><iftest="zcyStatus!=null">zcy_status=#{zcyStatus},</if><iftest="imgType!=nullandimgType!=''">img_type=#{imgType},</if><iftest="status!=null">status=#{status},</if><iftest="mainImg!=null">main_img=#{mainImg},</if></set></sql><selectid="list"resultType="DBMap"parameterType="DBMap">select*from<includerefid="tableName"/><includerefid="where_sql"/></select><selectid="get"resultType="DBMap"parameterType="DBMap">select*from<includerefid="tableName"/><includerefid="where_sql"/>limit1</select><updateid="update"parameterType="DBMap">update<includerefid="tableName"/><includerefid="update_sql"/><includerefid="where_sql"/></update><deleteid="delete"parameterType="DBMap">deletefrom<includerefid="tableName"/><includerefid="where_sql"/></delete><insertid="insert"parameterType="DBMap"keyProperty="id"useGeneratedKeys="true">insertinto<includerefid="tableName"/>(image_name,image_suffix,spu_id,url,zcy_url,zcy_status,img_type<iftest="status!=null">,status</if>,main_img)values(#{imageName},#{imageSuffix},#{spuId},#{url},#{zcyUrl},#{zcyStatus},#{imgType}<iftest="status!=null">,#{status}</if>,#{mainImg})</insert><selectid="selectBySpuId"resultType="DBMap"parameterType="java.lang.Long">select*from<includerefid="tableName"></include><where>andspu_id=#{spuId,jdbcType=BIGINT}andimg_type=0orderbymain_imgdesc,iddesc</where></select><selectid="selectIdsByShopId"resultType="java.lang.Long"parameterType="java.lang.Long">selectidfromshop_image_info<where>spu_id=#{spuId}andimg_type=0</where></select><updateid="updateByPrimaryKeySelective"parameterType="DBMap">updateshop_image_info<set><iftest="imageName!=null">image_name=#{imageName},</if><iftest="imageSuffix!=null">image_suffix=#{imageSuffix},</if><iftest="spuId!=null">spu_id=#{spuId},</if><iftest="url!=null">url=#{url},</if><iftest="zcyUrl!=null">zcy_url=#{zcyUrl},</if><iftest="zcyStatus!=null">zcy_status=#{zcyStatus},</if><iftest="imgType!=null">img_type=#{imgType},</if><iftest="status!=null">status=#{status},</if><iftest="mainImg!=null">main_img=#{mainImg},</if></set>whereid=#{id}</update><insertid="insertSelective"parameterType="DBMap">insertintoshop_image_info<trimprefix="("suffix=")"suffixOverrides=","><iftest="id!=null">id,</if><iftest="imageName!=null">image_name,</if><iftest="imageSuffix!=null">image_suffix,</if><iftest="spuId!=null">spu_id,</if><iftest="url!=null">url,</if><iftest="zcyUrl!=null">zcy_url,</if><iftest="zcyStatus!=null">zcy_status,</if><iftest="imgType!=null">img_type,</if><iftest="status!=null">status,</if><iftest="mainImg!=null">main_img,</if></trim><trimprefix="values("suffix=")"suffixOverrides=","><iftest="id!=null">#{id},</if><iftest="imageName!=null">#{imageName},</if><iftest="imageSuffix!=null">#{imageSuffix},</if><iftest="spuId!=null">#{spuId},</if><iftest="url!=null">#{url},</if><iftest="zcyUrl!=null">#{zcyUrl},</if><iftest="zcyStatus!=null">#{zcyStatus},</if><iftest="imgType!=null">#{imgType},</if><iftest="status!=null">#{status},</if><iftest="mainImg!=null">#{mainImg},</if></trim></insert><deleteid="batchDeleteByIds"parameterType="java.util.List">deletefrom<includerefid="tableName"/>whereimg_type=0andidin<foreachcollection="list"item="params"open="("separator=","close=")">#{params}</foreach></delete><deleteid="deleteById"parameterType="java.lang.Long">deletefrom<includerefid="tableName"/>whereimg_type=0andid=#{params}</delete><deleteid="deleteWithoutByIds"parameterType="DBMap">deletefrom<includerefid="tableName"/>whereimg_type=0andspu_id=#{spuId}<iftest="ids!=null">andidnotin<foreachcollection="list"item="ids"open="("separator=","close=")">#{ids}</foreach></if></delete><!--批量添加标准库数据照片--><insertid="insertImage"parameterType="com.miaosuan.dao.entity.standard.StandardImageInfo">insertinto<includerefid="tableName"></include><trimprefix="("suffix=")"suffixOverrides=","><iftest="imageName!=null">image_name,</if><iftest="imageSuffix!=null">image_suffix,</if><iftest="spuId!=null">spu_id,</if><iftest="url!=null">url,</if><iftest="imgType!=null">img_type,</if><iftest="status!=null">status,</if><iftest="mainImg!=null">main_img,</if></trim><trimprefix="values("suffix=")"suffixOverrides=","><iftest="imageName!=null">#{imageName,jdbcType=VARCHAR},</if><iftest="imageSuffix!=null">#{imageSuffix,jdbcType=VARCHAR},</if><iftest="spuId!=null">#{spuId,jdbcType=INTEGER},</if><iftest="url!=null">#{url,jdbcType=VARCHAR},</if><iftest="imgType!=null">#{imgType,jdbcType=TINYINT},</if><iftest="status!=null">#{status,jdbcType=TINYINT},</if><iftest="mainImg!=null">#{mainImg,jdbcType=TINYINT},</if></trim></insert>

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

<insertid="insertOrUpdate"parameterType="DBMap"useGeneratedKeys="true"keyProperty="id"keyColumn="id">insertinto<includerefid="tableName"></include><trimprefix="("suffix=")"suffixOverrides=","><iftest="id!=null">id,</if><iftest="imageName!=nullandimageName!=''">image_name,</if><iftest="imageSuffix!=nullandimageSuffix!=''">image_suffix,</if><iftest="spuId!=null">spu_id,</if><iftest="url!=nullandurl!=''">url,</if><iftest="imgType!=null">img_type,</if><iftest="status!=null">status,</if><iftest="mainImg!=null">main_img,</if></trim><trimprefix="values("suffix=")"suffixOverrides=","><iftest="id!=null">#{id},</if><iftest="imageName!=null">#{imageName,jdbcType=VARCHAR},</if><iftest="imageSuffix!=null">#{imageSuffix,jdbcType=VARCHAR},</if><iftest="spuId!=null">#{spuId,jdbcType=INTEGER},</if><iftest="url!=null">#{url,jdbcType=VARCHAR},</if><iftest="imgType!=null">#{imgType,jdbcType=TINYINT},</if><iftest="status!=null">#{status,jdbcType=TINYINT},</if><iftest="mainImg!=null">#{mainImg,jdbcType=TINYINT},</if></trim>ONDUPLICATEKEYUPDATE<trimsuffixOverrides=","><iftest="imageName!=nullandimageName!=''">image_name=#{imageName,jdbcType=VARCHAR},</if><iftest="imageSuffix!=nullandimageSuffix!=''">image_suffix=#{imageSuffix,jdbcType=VARCHAR},</if><iftest="spuId!=null">spu_id=#{spuId,jdbcType=INTEGER},</if><iftest="url!=nullandurl!=''">url=#{url,jdbcType=VARCHAR},</if><iftest="imgType!=null">img_type=#{imgType,jdbcType=TINYINT},</if><iftest="status!=null">status=#{status,jdbcType=TINYINT},</if><iftest="mainImg!=null">main_img=#{mainImg,jdbcType=TINYINT},</if></trim></insert></mapper>

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