MyBatis动态SQL标签用法实例详解
程序员文章站
2023-11-21 09:16:46
1、动态sql片段
通过sql片段达到代码复用
1、动态sql片段
通过sql片段达到代码复用
<!-- 动态条件分页查询 -->
<sql id="sql_count">
select count(*)
</sql>
<sql id="sql_select">
select *
</sql>
<sql id="sql_where">
from icp
<dynamic prepend="where">
<isnotempty prepend="and" property="name">
name like '%$name$%'
</isnotempty>
<isnotempty prepend="and" property="path">
path like '%path$%'
</isnotempty>
<isnotempty prepend="and" property="area_id">
area_id = #area_id#
</isnotempty>
<isnotempty prepend="and" property="hided">
hided = #hided#
</isnotempty>
</dynamic>
<dynamic prepend="">
<isnotnull property="_start">
<isnotnull property="_size">
limit #_start#, #_size#
</isnotnull>
</isnotnull>
</dynamic>
</sql>
<select id="findbyparamsforcount" parameterclass="map" resultclass="int">
<include refid="sql_count"/>
<include refid="sql_where"/>
</select>
<select id="findbyparams" parameterclass="map" resultmap="icp.result_base">
<include refid="sql_select"/>
<include refid="sql_where"/>
</select>
2、数字范围查询
所传参数名称是捏造所得，非数据库字段，比如_img_size_ge、_img_size_lt字段
<isnotempty prepend="and" property="_img_size_ge">
<![cdata[
img_size >= #_img_size_ge#
]]>
</isnotempty>
<isnotempty prepend="and" property="_img_size_lt">
<![cdata[
img_size < #_img_size_lt#
]]>
</isnotempty>
多次使用一个参数也是允许的
<isnotempty prepend="and" property="_now">
<![cdata[
execplantime >= #_now#
]]>
</isnotempty>
<isnotempty prepend="and" property="_now">
<![cdata[
closeplantime <= #_now#
]]>
</isnotempty>
3、时间范围查询
<isnotempty prepend="" property="_starttime">
<isnotempty prepend="and" property="_endtime">
<![cdata[
createtime >= #_starttime#
and createtime < #_endtime#
]]>
</isnotempty>
</isnotempty>
4、in查询
<isnotempty prepend="and" property="_in_state">
state in ('$_in_state$')
</isnotempty>
5、like查询
<isnotempty prepend="and" property="chnameone">
(chnameone like '%$chnameone$%' or spellinitial like '%$chnameone$%')
</isnotempty>
<isnotempty prepend="and" property="chnametwo">
chnametwo like '%$chnametwo$%'
</isnotempty>
6、or条件
<isequal prepend="and" property="_exeable" comparevalue="n">
<![cdata[
(t.finished='11' or t.failure=3)
]]>
</isequal>
<isequal prepend="and" property="_exeable" comparevalue="y">
<![cdata[
t.finished in ('10','19') and t.failure<3
]]>
</isequal>
7、where子查询
<isnotempty prepend="" property="exprogramcode">
<isnotempty prepend="" property="isrational">
<isequal prepend="and" property="isrational" comparevalue="n">
code not in
(select t.contentcode
from cms_ccm_programcontent t
where t.contenttype='mznrlx_ma'
and t.programcode = #exprogramcode#)
</isequal>
</isnotempty>
</isnotempty>
<select id="findbyprogramcode" parameterclass="string" resultmap="cms_ccm_material.result">
select *
from cms_ccm_material
where code in
(select t.contentcode
from cms_ccm_programcontent t
where t.contenttype = 'mznrlx_ma'
and programcode = #value#)
order by updatetime desc
</select>
9、函数的使用
<!-- 添加 -->
<insert id="insert" parameterclass="rulemaster">
insert into rulemaster(
name,
createtime,
updatetime,
remark
) values (
#name#,
now(),
now(),
#remark#
)
<selectkey keyproperty="id" resultclass="long">
select last_insert_id()
</selectkey>
</insert>
<!-- 更新 -->
<update id="update" parameterclass="rulemaster">
update rulemaster set
name = #name#,
updatetime = now(),
remark = #remark#
where id = #id#
</update>
10、map结果集
<!-- 动态条件分页查询 -->
<sql id="sql_count">
select count(a.*)
</sql>
<sql id="sql_select">
select a.id vid,
a.img imgurl,
a.img_s imgfile,
b.vfilename vfilename,
b.name name,
c.id sid,
c.url url,
c.filename filename,
c.status status
</sql>
<sql id="sql_where">
from secfiles c, juji b, videoinfo a
where
a.id = b. videoid
and b.id = c.segmentid
and c.status = 0
order by a.id asc,b.id asc,c.sortnum asc
<dynamic prepend="">
<isnotnull property="_start">
<isnotnull property="_size">
limit #_start#, #_size#
</isnotnull>
</isnotnull>
</dynamic>
</sql>
<!-- 返回没有下载的记录总数 -->
<select id="getundownfilesforcount" parameterclass="map" resultclass="int">
<include refid="sql_count"/>
<include refid="sql_where"/>
</select>
<!-- 返回没有下载的记录 -->
<select id="getundownfiles" parameterclass="map" resultclass="java.util.hashmap">
<include refid="sql_select"/>
<include refid="sql_where"/>
</select>
11、trim
trim是更灵活的去处多余关键字的标签，他可以实践where和set的效果。
where例子的等效trim语句：
xml代码
<!-- 查询学生list，like姓名，=性别 -->
<select id="getstudentlistwhere" parametertype="studententity" resultmap="studentresultmap">
select * from student_tbl st
<trim prefix="where" prefixoverrides="and|or">
<if test="studentname!=null and studentname!='' ">
st.student_name like concat(concat('%', #{studentname}),'%')
</if>
<if test="studentsex!= null and studentsex!= '' ">
and st.student_sex = #{studentsex}
</if>
</trim>
</select>
set例子的等效trim语句：
xml代码
<!-- 更新学生信息 -->
<update id="updatestudent" parametertype="studententity">
update student_tbl
<trim prefix="set" suffixoverrides=",">
<if test="studentname!=null and studentname!='' ">
student_tbl.student_name = #{studentname},
</if>
<if test="studentsex!=null and studentsex!='' ">
student_tbl.student_sex = #{studentsex},
</if>
<if test="studentbirthday!=null ">
student_tbl.student_birthday = #{studentbirthday},
</if>
<if test="classentity!=null and classentity.classid!=null and classentity.classid!='' ">
student_tbl.class_id = #{classentity.classid}
</if>
</trim>
where student_tbl.student_id = #{studentid};
</update>
12、choose (when, otherwise)
有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。mybatis提供了choose 元素，按顺序判断when中的条件出否成立，如果有一个成立，则choose结束。当choose中所有when的条件都不满则时，则执行 otherwise中的sql。类似于java 的switch 语句，choose为switch，when为case，otherwise则为default。
if是与(and)的关系，而choose是或（or）的关系。
例如下面例子，同样把所有可以限制的条件都写上，方面使用。选择条件顺序，when标签的从上到下的书写顺序：
xml代码
<!-- 查询学生list，like姓名、或=性别、或=生日、或=班级，使用choose -->
<select id="getstudentlistchooseentity" parametertype="studententity" resultmap="studentresultmap">
select * from student_tbl st
<where>
<choose>
<when test="studentname!=null and studentname!='' ">
st.student_name like concat(concat('%', #{studentname}),'%')
</when>
<when test="studentsex!= null and studentsex!= '' ">
and st.student_sex = #{studentsex}
</when>
<when test="studentbirthday!=null">
and st.student_birthday = #{studentbirthday}
</when>
<when test="classentity!=null and classentity.classid !=null and classentity.classid!='' ">
and st.class_id = #{classentity.classid}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
以上所述是小编给大家介绍的mybatis动态sql标签用法实例详解，希望对大家有所帮助