mybayis项目使用的Mapping文件使用总结参考(二)
针对in字句中的数组使用方法
<select id="getCpProfileNamesByIds" resultType="string"> select name from mega_mis_smpp where id in <foreach item="ids" index="index" collection="array" open="(" separator="," close=")"> #{ids} </foreach> </select>
针对in字句中的Collection使用方法
存储过程的使用：
<select id="FactoryNumberDao.getNewFactoryCode" resultMap="BaseResultMap" > DECLARE @factoryCode varchar(5) EXEC P_Factory_Number @factoryCode = @factoryCode OUTPUT SELECT @factoryCode as factoryCode </select>
Ognl的使用：
<select id="AreaDao.findByPageArea.count" resultType="java.lang.Long"> SELECT count(*) from gs_area <include refid="areaDaoDynamicWhere"/> </select> <sql id="areaDaoDynamicWhere"> <!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用框架中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 --> <where> <!-- del_flag = '0' --> <if test="@Ognl@isNotEmpty(areaCode)"> and area_code = #{areaCode} </if> <if test="@Ognl@isNotEmpty(areaName)"> and area_name like '%${areaName}%' </if> </where> </sql>
sql标签：
<sql id="Base_Column_List" > factory_code, factory_name, factory_user, factory_desc, tel, email, update_time, state_flag </sql> <select id="FactoryDao.selectByPrimaryKey" resultMap="FactoryInfoMap" parameterType="java.lang.String" > select <include refid="Base_Column_List" /> from dbo.gs_factory where factory_code = #{factoryCode,jdbcType=VARCHAR} </select> <select id="LineDao.findByPageLine.count" resultType="java.lang.Long"> SELECT count(*) from gs_line <include refid="lineDaoDynamicWhere"/> </select> <sql id="lineDaoDynamicWhere"> <!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用框架中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 --> <where> <!-- del_flag = '0' --> <if test="@Ognl@isNotEmpty(lineCode)"> and line_code = #{lineCode} </if> <if test="@Ognl@isNotEmpty(lineName)"> and line_name like '%${lineName}%' </if> </where> </sql> <sql id="stationTaskReportChartDynamicWhere"> <!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用框架中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 --> <choose> <when test="endDate!=null and startDate!=null"> and g.plan_task_date BETWEEN #{startDate,jdbcType=TIMESTAMP} AND #{endDate,jdbcType=TIMESTAMP} </when> <when test="endDate==null and startDate!=null"> and g.plan_task_date >= #{startDate,jdbcType=TIMESTAMP} </when> <when test="endDate!=null and startDate==null"> <![CDATA[ and g.plan_task_date <= #{startDate,jdbcType=TIMESTAMP} ]]> </when> </choose> <if test="stationCodeList!=null"> and g.station_code in <foreach item="stationCode" index="index" open="(" close=")" separator="," collection="stationCodeList"> #{stationCode} </foreach> </if> <if test="userCodeList!=null"> and g.user_code in <foreach item="userCode" index="index" open="(" close=")" separator="," collection="userCodeList"> #{userCode} </foreach> </if> </sql>
trim标签的使用：
<insert id="AssetTypeDao.insert" parameterType="com.easyway.eamsg.assetmgt.domain.AssetTypeInfo" > insert into dbo.gs_asset_type <trim prefix="(" suffix=")" suffixOverrides="," > <if test="assetTypeCode != null" > asset_type_code, </if> <if test="assetTypeName != null" > asset_type_name, </if> <if test="assetTypeDesc != null" > asset_type_desc, </if> <if test="natureFlag != null" > nature_flag, </if> <if test="featureFlag != null" > feature_flag, </if> <if test="codingFlag != null" > coding_flag, </if> <if test="matchFlag != null" > match_flag, </if> <if test="updateTime != null" > update_time, </if> <if test="stateFlag != null" > state_flag, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="assetTypeCode != null" > #{assetTypeCode,jdbcType=VARCHAR}, </if> <if test="assetTypeName != null" > #{assetTypeName,jdbcType=VARCHAR}, </if> <if test="assetTypeDesc != null" > #{assetTypeDesc,jdbcType=VARCHAR}, </if> <if test="natureFlag != null" > #{natureFlag,jdbcType=CHAR}, </if> <if test="featureFlag != null" > #{featureFlag,jdbcType=CHAR}, </if> <if test="codingFlag != null" > #{codingFlag,jdbcType=CHAR}, </if> <if test="matchFlag != null" > #{matchFlag,jdbcType=CHAR}, </if> <if test="updateTime != null" > #{updateTime,jdbcType=TIMESTAMP}, </if> <if test="stateFlag != null" > #{stateFlag,jdbcType=CHAR}, </if> </trim> </insert>
set标签的使用
<update id="AssetTypeDao.update" parameterType="com.easyway.eamsg.assetmgt.domain.AssetTypeInfo" > update dbo.gs_asset_type <set > <if test="assetTypeName != null" > asset_type_name = #{assetTypeName,jdbcType=VARCHAR}, </if> <if test="assetTypeDesc != null" > asset_type_desc = #{assetTypeDesc,jdbcType=VARCHAR}, </if> <if test="natureFlag != null" > nature_flag = #{natureFlag,jdbcType=CHAR}, </if> <if test="featureFlag != null" > feature_flag = #{featureFlag,jdbcType=CHAR}, </if> <if test="codingFlag != null" > coding_flag = #{codingFlag,jdbcType=CHAR}, </if> <if test="matchFlag != null" > match_flag = #{matchFlag,jdbcType=CHAR}, </if> <if test="updateTime != null" > update_time = #{updateTime,jdbcType=TIMESTAMP}, </if> <if test="stateFlag != null" > state_flag = #{stateFlag,jdbcType=CHAR}, </if> </set> where asset_type_code = #{assetTypeCode,jdbcType=VARCHAR} </update>
存储过程：
<select id="SiteAssetsSoftWareNumberDao.getNewsiteAssetsSoftwareCode" resultMap="BaseResultMap" parameterType="com.easyway.eamsg.assetmgt.domain.SiteAssetsSoftWareNumbeInfo" > DECLARE @siteAssetsSoftwareCode varchar(13) EXEC P_Site_Assets_Software_Number @stationCode = #{stationCode,jdbcType=VARCHAR}, @assetTypeCode = #{assetTypeCode,jdbcType=VARCHAR}, @siteAssetsSoftwareCode = @siteAssetsSoftwareCode OUTPUT SELECT @siteAssetsSoftwareCode as siteAssetsSoftwareCode </select>
存储过程2：
<resultMap id="BaseResultMap" type="com.easyway.eamsg.assetmgt.domain.SparePartsNumbeInfo" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="remarks" property="remarks" jdbcType="VARCHAR" /> <result column="sparePartsCode" property="sparePartsCode" jdbcType="VARCHAR" /> </resultMap> <select id="SparePartsNumberDao.getNewSparePartsCode" resultMap="BaseResultMap" > DECLARE @sparePartsCode varchar(17) EXEC P_Spare_Parts_Number @sparePartsCode = @sparePartsCode OUTPUT SELECT @sparePartsCode as sparePartsCode </select>
