mybatis—mapper.xml里的多种写法举例
目录向一个表里插入另一个表的一些数据并进行取舍
insert into 表 (id, alid, mnjssj, zdbc, sjbc, zxbc, px, cfltl, qjq, wtycl, ddcs, sljd)
select SYS_GUID(),#{newAlid,jdbcType = VARCHAR}, mnjssj, zdbc, sjbc, zxbc, px, cfltl, qjq, wtycl, ddcs, sljd from al_zt_exp_mx where alid = #{alid,jdbcType = VARCHAR}
- mysql 的话换 SYS_GUID() 为 REPLACE(UUID(), '-', '')
<insert id="insertGdCalcParamEx" parameterType="map">
insert into gd_calc_param_ex (id, gdid, wd,
qxyl, qyb, hsl, px
)
<foreach collection="gdCalcParamExes" item="item" separator="union all">
select REPLACE(UUID(), '-', ''), #{item.gdid}, #{item.wd},
#{item.qxyl}, #{item.qyb}, #{item.hsl}, #{item.px}
from dual
</foreach>
</insert>
需要查询的 select 后的字段如果有不少语句都一致的话可以考虑单独提出来
<sql id="Base_Column_List">
ID, ALID, MNJSSJ, ZDBC, ZXBC, SJBC,CFLTL,QJQ,WTYCL,ddcs,sljd
</sql>
<select id="xxx" parameterType="cn.com.xxx.xxx.xxx.entity.xxx" resultMap="yyy">
select
<include refid="Base_Column_List"/>
from 表
where ALID = #{alid,jdbcType=VARCHAR}
</select>
更新
-
循环更新
<update id="updatePx"> <foreach collection="list" item="item" separator=";"> update 表 set px=#{item.px} where id = #{item.id} </foreach> </update>
-
循环更新多字段
<update id="updateByPriKey" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> update 表 <set> JH = #{item.jh,jdbcType=VARCHAR}, LYMC = #{item.lymc,jdbcType=VARCHAR}, ZJ = #{item.zj,jdbcType=DECIMAL}, DS = #{item.ds,jdbcType=DECIMAL}, </set> where ID = #{item.id} </foreach> </update>
删除
-
循环删除
<delete id="deleteBatchCalculationResult" parameterType="map"> delete from 表 where alid in <foreach collection="alidArr" item="item" index="index" separator="," close=")" open="("> #{item} </foreach> and cjr = #{cjr} </delete>
in 语句面对集合和字符串
-
例如:
<if test="nodeIdListStr != null and nodeIdListStr != ''"> AND A.LX IN (SELECT NODE FROM CY_HXJSJ_TREE WHERE ID IN (${nodeIdListStr}) AND NODE IS NOT NULL)</if>
-
因为这里传来的不是集合而是一个字符串,所以才用的这种写法,不推荐,最好还是传换成 List 集合的形式,那样就应该把语句更改成:
<if test="nodeIdListStr != null and nodeIdListStr != ''"> AND A.LX IN ( SELECT NODE FROM CY_HXJSJ_TREE WHERE ID IN <foreach collection="nodeIdListStr" item="item" index="index" open="(" close=")" separator=","> #{item} </foreach> AND NODE IS NOT NULL ) </if>
- 这里就是传来了集合类型的 nodeIdListStr
-
现举一些 mysql 中的例子:
select * from 表名 where 某字段 in ('B', 'BA', 'DS')
语句正确select * from 表名 where 某字段 in ("B", "BA", "DS")
语句正确select * from 表名 where 某字段 in (B, BA, DS)
语句不正确select * from 表名 where 某字段 in ('B, BA, DS')
不报错但句意改变
<trim>
标签
-
<trim prefix="" suffix="" suffixOverrides="" prefixOverrides=""></trim>
-
属性解释:
- prefix:在 trim 标签内 sql 语句加上前缀
- suffix:在 trim 标签内 sql 语句加上后缀
- suffixOverrides:指定去除多余的后缀内容
- prefixOverrides:指定去除多余的前缀内容
-
情景举例(仅是为了用法而举例,没写更简洁的写法):
-
在写查询语句时,就需要写:
<select> select * from xxx <trim prefix="where" prefixOverrides="and"> <if test="id != null"> and id = #{id} </if> <if test="name != null"> and name= #{name} </if> </trim> </select>
- 当然直接用
<where>
的话会自行去除 and,更简洁
- 当然直接用
-
在编写插入语句时,又写为:
<insert> insert into xxx <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> name, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id}, </if> <if test="name != null"> #{name}, </if> </trim> </insert>
-
在编写更新语句时:
<update> update xxx <trim prefix="set" suffixOverrides=","> <if test="id != null">id = #{id},</if> <if test="name != null">name= #{name},</if> </trim> where id = #{id} </update>
- 当然直接用
<set>
的话会自行去除逗号,更简洁
- 当然直接用
-
<choose>
标签判断 <when>
、<otherwise>
-
类似于 if else 的感觉了:
<select id="queryOnlineDuration" parameterType="Map" resultType="Map"> SELECT sum(DATE_PART('epoch', LOGOUT_TIME)-DATE_PART('epoch', LOGIN_TIME)) loggingTime, <choose> <when test="type == 'user'"> USER_ID userId, USER_NAME userName </when> <otherwise> ORGNA_ID orgnaId </otherwise> </choose> FROM user_online_duration WHERE USER_ID IS NOT NULL GROUP BY <choose> <when test="type == 'user'"> USER_ID,USER_NAME </when> <otherwise> ORGNA_ID </otherwise> </choose> </select>