玩转mybatis之mapper.xml
前言
这篇文档使用于刚工作的朋友、或者说喜欢ctrlC+V的伙伴,会介绍一些基本的传参、然后会有一个通用的接口,适用于一些简单的查询,当然这个会有sql注入风险,被老板骂了可不要找我。下面的介绍都是以oracle数据库为主的,这点要注意,像一些分页、日期获取、日期转换都是有所不同的,不过大部分都是相同的。
查询
精确查询(以下传参皆为map即parameterType=“map”)
<if test="column != null and column != ''">
and COLUMN = #{column,jdbcType=VARCHAR}
</if>
上面代码小写column 为map的key,大写的COLUMN 为数据库对应的字段java的命名规范都是见名知意,这个代码意思大概就是如果你传入的参数map中含有column这个key并且它不等于“”,那么查询就会拼接上着句语句。
小贴士
jdbcType=VARCHAR ,在oracle对应的字段是 varchar2(*),除此之外jbdcType还有给你们个传送门吧 jdbctype有哪些类型 我是能复制的坚决不手敲…
模糊查询
<if test="column != null and column != ''">
and COLUMN like '%' || #{column ,jdbcType=VARCHAR} || '%'
</if>
其实最后目的都一样就是拼接为 and COLUMN like ‘%模糊匹配关键词%’
处理日期的范围查询
<if test="createTimeFrom != null and createTimeFrom != ''">
<![CDATA[ AND TO_CHAR(CREATE_TIME,'yyyy-MM-dd') >= #{createTimeFrom,jdbcType=VARCHAR} ]]>
</if>
<if test="createTimeTo != null and createTimeTo != ''">
<![CDATA[ AND TO_CHAR(CREATE_TIME,'yyyy-MM-dd') <= #{createTimeTo,jdbcType=VARCHAR} ]]>
</if>
细心的朋友就发现了外面包了一层什么玩楞?,其实就是增加了
<![CDATA[]]> ,其实不增加也能用只不过需要特殊字符<(小于)、>(大于),less的英文是小于,greater 是 大于,这样记忆会清晰些小贴士<![CDATA[]]>是什么?
这是XML语法。在CDATA内部的所有内容都会被解析器忽略。
被<![CDATA[]]>这个标记所包含的内容将表示为纯文本,比如<![CDATA[<]]>表示文本内容“<”。
不管怎么样,转义前的字符也好,转义后的字符也好,都会被xml解析器解析,为了方便起见,使用<![CDATA[]]>来包含不被xml解析器解析的内容。
in,某些数据之内的查询
<if test="column != null and column.size() > 0">
and COLUMN in
<foreach item="item" index="index" collection="column" open="(" separator="," close=")">
#{item,jdbcType=VARCHAR}
</foreach>
</if>
这边foreach 就是循环list,最终目的也是拼接为in(‘’,‘’,‘’) ,这种传参的话其实也是map类型只不过key为column的value的值必须为list类型
通用查询值
大家都知道${},直接传入个sql语句,完美!!!当然如果你在的公司老板允许这么写sql,赶紧辞职吧,哥们哥们。
<select id="commonSingleQuery" resultType="map" parameterType="map">
SELECT * FROM (
SELECT A.*,ROWNUM RN FROM (
select * from ${tableName} C
WHERE 1=1
<if test="businessColumn != null">
<foreach collection="businessColumn" item="map" index="key">
<if test= "businessColumn[key] != null and businessColumn[key] != ''">
and ${key} = #{businessColumn.${key}}
</if>
</foreach>
</if>
<if test="indistinctColumn != null">
<foreach collection="indistinctColumn" item="map" index="key">
<if test= "indistinctColumn[key] != null and indistinctColumn[key] != ''">
and ${key} like '%'||#{indistinctColumn.${key}}||'%'
</if>
</foreach>
</if>
<if test="collectColumn != null and collectList != null and collectList.size()>0">
and ${collectColumn} IN
<foreach item="item" index="index" collection="collectList" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test = "gtColumn != null">
<if test = "gtColumnVal != 'null' and gtColumnVal !=''">
AND ${gtColumn} > ${gtColumnVal}
</if>
</if>
<if test = "ltColumn != null">
<if test = "ltColumnVal != 'null' and ltColumnVal !=''">
AND ${ltColumn} < ${ltColumnVal}
</if>
</if>
<if test="nullColumn != null and nullColumn.size()>0">
<foreach item="item" index="index" collection="nullColumn">
<if test= "item != null and item != ''">
and ${item} is null
</if>
</foreach>
</if>
<if test = "dateField != null and dateField !=''">
<if test="createTimeFrom != null and createTimeFrom != ''">
<![CDATA[ AND TO_CHAR(${dateField},'yyyy-MM-dd') >= #{createTimeFrom,jdbcType=VARCHAR} ]]>
</if>
<if test="createTimeTo != null and createTimeTo != ''">
<![CDATA[ AND TO_CHAR(${dateField},'yyyy-MM-dd') <= #{createTimeTo,jdbcType=VARCHAR} ]]>
</if>
</if>
<if test="customSql != null and customSql != ''">
${customSql}
</if>
<if test="orderColumn != null and orderColumn != ''">
order by ${orderColumn}
</if>
)A
<if test="isPaging != null and isPaging == true">
WHERE ROWNUM <= #{endNum}
</if>
)
<if test="isPaging != null and isPaging == true">
WHERE RN > #{startNum}
</if>
</select>
上面其实是一个单表的查询,当然也是oracle的,什么你说我复制的,我恭喜你答对了,记得我们的宗旨,能复制就不要手敲~~,公司用了很稳定的东西直接拿来用不香吗?
回到正题,这个查询乍一看好TM长啊,其实你心慢慢看看也是非常好理解的,什么?你还是不会?
var params = {
"tableName" : "表名",
"businessColumn":{ //精确查询字段
"字段1": "字段1的值",
"字段2": "字段2的值"
} ,
"indistinctColumn":{ //模糊查询字段
"字段1": "字段1的值",
"字段2": "字段2的值"
},
"collectColumn":"需要范围查询的字段"
"collectList":[ //具体的查询范围
"1","2"
] ,
"gtColumn":"需要大于的某个字段",
"gtColumnVal":"大于的具体值",
"ltColumn":"需要小于的某个字段",
"ltColumnVal":"小于的具体值",
"nullColumn":["字段1","字段2"], //为空的字段
"dateField":"日期字段",
"createTimeFrom":"日期格式为yyyy-mm-dd的日期",
"createTimeTo":"日期格式为yyyy-mm-dd的日期",
"customSql":"",//自己额外增加的sql,当然你可以写“OR 1=1”
"orderColumn" : "排序的数据库字段,可以多个,正常sql拼接"
};
这如果你还是看不懂的话直接拨打114,该找谁找谁吧。
通用查询数据条数
也就是 select count(*) 道理一致,
<select id="commonSingleQueryCnt" resultType="int" parameterType="map">
select count(*) from ${tableName} C WHERE 1=1
<if test="businessColumn != null">
<foreach collection="businessColumn" item="map" index="key">
<if test= "businessColumn[key] != null and businessColumn[key] != ''">
and ${key} = #{businessColumn.${key}}
</if>
</foreach>
</if>
<if test="indistinctColumn != null">
<foreach collection="indistinctColumn" item="map" index="key">
<if test= "indistinctColumn[key] != null and indistinctColumn[key] != ''">
and ${key} like '%'||#{indistinctColumn.${key}}||'%'
</if>
</foreach>
</if>
<if test="collectColumn != null and collectList != null and collectList.size()>0">
and ${collectColumn} IN
<foreach item="item" index="index" collection="collectList" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test = "gtColumn != null">
<if test = "gtColumnVal != 'null' and gtColumnVal !=''">
AND ${gtColumn} > ${gtColumnVal}
</if>
</if>
<if test = "ltColumn != null">
<if test = "ltColumnVal != 'null' and ltColumnVal !=''">
AND ${ltColumn} < ${ltColumnVal}
</if>
</if>
<if test="customSql != null and customSql != ''">
${customSql}
</if>
</select>
新增
<insert id="saveCommonSingleCfg" parameterType="java.util.Map" >
insert into ${tableName}
<foreach collection="businessColumn" item="map" index="key" open="(" separator="," close=")">
<if test= "businessColumn[key] != null and businessColumn[key] != ''">
${key}
</if>
</foreach>
values
<foreach collection="businessColumn" item="map" index="key" open="(" separator="," close=")">
<if test= "businessColumn[key] != null and businessColumn[key] != ''">
#{businessColumn.${key}}
</if>
</foreach>
</insert>
删除
<delete id = "delCommonSingleCfg" parameterType="map">
DELETE FROM ${tableName} WHERE
<foreach collection="businessColumn" item="map" index="key" separator=" and ">
<if test= "businessColumn[key] != null and businessColumn[key] != ''">
${key} = #{businessColumn.${key}}
</if>
</foreach>
<if test="collectColumn != null and collectList != null and collectList.size()>0">
and ${collectColumn} IN
<foreach item="item" index="index" collection="collectList" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</delete>
小贴士
这玩意慎用啊,你的系统如果登录安全什么的都没做,如果被我看见,那你数据库数据可能全没了
修改
<update id="updateCommonSingleCfg">
update ${tableName} set
<foreach collection="businessColumn" item="map" index="key" separator=",">
${key} = #{businessColumn.${key}}
</foreach>
<if test="sysdateColumn != null and sysdateColumn.size()>0">
<if test="businessColumn != null">,</if>
<foreach collection="sysdateColumn" item="item" index="index" separator=",">
<if test="item != null and item != ''">
${item} = sysdate
</if>
</foreach>
</if>
where ${conditionColumn} = #{conditionVal,jdbcType=VARCHAR}
</update>
好了这次的内容到这里就结束了,内容很简单,但是对于一些人来说也很实用,这么久没更新是因为最近工作事情很多,还有目前的项目是前后端都有的,jQuery、js、新框架属实很头疼,想了解工作中更多搬运即用的知识记得关注我 555~~