标签
1.insert
<insert id="insertTest" parameterType="java.util.HashMap">
insert into table_test (field1,field2) values (value1,value2)
</insert>
2.delete
<delete id="deleteTest" parameterType="java.util.HashMap">
delete from table_test where id="id"
</delete>
3.update
<update id="updateTest" parameterType="java.util.HashMap">
update table_test set value1="valueNew1",value1="valueNew1" where id="id"
</update>
4.select
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
select field1,field2 from table_test
</select>
5.if
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
select field1,field2 from table_test
where 1=1
<if test="value1!=null and test!=''">
and field1 = #{value1}
</if>
<if test="value2!=null and test!=''">
and field2 = #{value2}
</if>
</select>
6.trim
<insert id="insertTest" parameterType="java.util.HashMap">
insert into table_test
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="value1!=null and test!=''">
field1,
</if>
<if test="value2!=null and test!=''">
field2,
</if>
<if test="value3!=null and test!=''">
field2,
</if>
</trim>
<trim prefix="values(" suffix=")" suffixOverrides="," >
<if test="value1!=null and test!=''">
#{value1},
</if>
<if test="value2!=null and test!=''">
#{value2},
</if>
<if test="value3!=null and test!=''">
#{value3},
</if>
</trim>
</insert>
7.include
<!-- 和sql标签一起用 -->
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
select
<include refid= "field"/>
from
<include refid= "table"/>
where
id=#{id}
</select>
8.sql
<!-- 和标签一起用 -->
<sql id ="field">
field1,
field2,
field3
</sql>
<sql id ="table">
table_test
</sql>
9.foreach
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
select field1,field2 from table_test
where 1=1
<if test="value1!=null and test!=''">
and field1 = #{value1}
</if>
<if test="value2!=null and test!=''">
and field2 = #{value2}
</if>
<if test="idlist!=null and idlist!=''">
and id in (
<foreach collection="idlist" item="id" index="id" separator=",">
#{id}
</foreach>
)
</if>
</select>
10.choose-when-otherwise
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
select field1,field2 from table_test
where 1=1
<if test="value1!=null and test!=''">
and field1 = #{value1}
</if>
<if test="value2!=null and test!=''">
and field2 = #{value2}
</if>
<choose>
<when test="value3 !=null and value3 !=''">
and field3=#{value3}
</when>
<otherwise>
and field3 = #{value4}
</otherwise>
</choose>
</select>
关键字
1.case when
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
select case when field1 is null then '1'
case when field2 is not null then '2'
else '3' end
from table_test where id = #{id}
</select>
2.to_char / to_date 日期类型转换
3.with table_temp 临时表使用
4.sum(filed1+field2)
5.merge into
6.instr(string,’’)>0 包含某个字符个数
7.substr(string,’[^]+’,2) 第一第二个*字符之间的字符串
8.start within connect by prior