iBATIS在使用过程中SQL遇到的一些问题,记录以供学习!
<resultMap id="POMap"
type="PO">
<result property="name" column="NAMW" />
<result property="age" column="AGE" />
</resultMap>
<select id="aa"
parameterType="VO"
resultMap="POMap">
select name,age from
user p
where
<![CDATA[
p.chg_time >= #{fromDate}
and p.chg_time < #{toDate}
]]>
and p.seat_status in
<foreach collection="b" item="age" separator=","
open="(" close=")">
'${age}'
</foreach>
</select>
其中id="aa"代表mapper中方法的别名,parameterType是传递的参数类型,resultMap是查询结果(一般都为bean),参数可以使用EL表达式#{}在传递参数
#{}可以直接吧类型
<insert id="bb"
parameterType="Vo" >
INSERT INTO db_user (
NAME,
AGE
)
(
select t.name as name,
t.age as age
from sys_user t
where
t.age is not null
<if test="fromDate != null">
<![CDATA[
and t.tkt_time >= #{fromDate}
]]>
</if>
<if test="toDate != null">
<![CDATA[
and t.tkt_time < #{toDate}
]]>
</if>
)
</insert>
<insert id="cc" parameterType="PO">
INSERT INTO db_user (
name,
time,
age
)
VALUES (
#{name javaType=String, jdbcType=VARCHAR},
#{time javaType=java.sql.Timestamp, jdbcType=TIMESTAMP},
#{age javaType=int, jdbcType=NUMERIC},
)
</insert>
<update id="dd" parameterType="PO">
update db_user
set NAME = #{name javaType=java.sql.Timestamp, jdbcType=TIMESTAMP},
AGE = #{age javaType=int, jdbcType=NUMERIC},
where ID=#{id}
</update>
<delete id="ee" parameterType="String">
delete from db_user where ID = #{id}
</delete>
还有一些循环,判断的写法:
if判断加foreach循环:
<if test="status != null">
WHERE t.status in
<foreach collection="status" item="s" separator="," open="(" close=")">
${s}
</foreach>
</if>
choose用法:
<choose>
<when test="orderKey == 'age'">
t.age
</when>
<otherwise>
t.age
</otherwise>
</choose>
以上只是简单的列举了iBATIS中用到的一些SQL和条件语句,做个备忘!