1.语句末尾不能有分号 ;
示例:
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from hkbi_meta_datasource
where id = #{id,jdbcType=INTEGER}
</select>
2.对 sql中有比较大小的需要转义 < , > 。 这里省略转义,使用<![CDATA[ sql ]]>。
示例:
< | <= | > | >= | & | ' | " |
< | <= | > | >= | & | ' | " |
示例:
select name from tbl_user where age > #{age}
<![CDATA[ select name from tbl_user where age > #{age} ]]>
age>= #{age}
使用<![CDATA[ sql ]]> 方法为xml中的语法。在CDATA内部的所有内容都会被解析器忽略. 但是因此会导致<if test=""></if> <where></where> <choose></choose> <trim></trim>等标签本身功能失效,所以使用时把带有特殊字符的语句或字符本身放在<![CDATA[ ]]>内即可,即尽量精确其作用范围.
3.sql语句存入的内容为拼接字符串, 对双引号,单引号等特殊符号做转义处理。
示例:使用org.apache.commons.lang3下的包
sql存库 : StringEscapeUtils.escapeJava(conent);
sql查询取出: StringEscapeUtils.unescapeJava(conent);
类似的方法还有 escapeSql, html,javascript,xml 等方法。大家可尝试使用
pom导包
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>
4.插入一段sql,记录下常用标签的使用
<sql id="Update_By_Example_Where_Clause">
<where>
<foreach collection="example.oredCriteria" item="criteria" separator="or">
<if test="criteria.valid">
<trim prefix="(" prefixOverrides="and" suffix=")">
<foreach collection="criteria.criteria" item="criterion">
<choose>
<when test="criterion.noValue">
and ${criterion.condition}
</when>
<when test="criterion.singleValue">
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue">
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue">
and ${criterion.condition}
<foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
<insert id="insertSelective" parameterType="org.triber.dghabi.model.ConfigDatasource">
insert into hki_meta_datasource
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="dName != null">
d_name,
</if>
<if test="dType != null">
d_type,
</if>
<if test="dUrl != null">
d_url,
</if>
<if test="dUsername != null">
d_username,
</if>
<if test="dPassword != null">
d_password,
</if>
<if test="dDriverClass != null">
d_driver_class,
</if>
<if test="dQueryClass != null">
d_query_class,
</if>
<if test="options != null">
options,
</if>
<if test="dDesc != null">
d_desc,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="dName != null">
#{dName,jdbcType=VARCHAR},
</if>
<if test="dType != null">
#{dType,jdbcType=VARCHAR},
</if>
<if test="dUrl != null">
#{dUrl,jdbcType=VARCHAR},
</if>
<if test="dUsername != null">
#{dUsername,jdbcType=VARCHAR},
</if>
<if test="dPassword != null">
#{dPassword,jdbcType=VARCHAR},
</if>
<if test="dDriverClass != null">
#{dDriverClass,jdbcType=VARCHAR},
</if>
<if test="dQueryClass != null">
#{dQueryClass,jdbcType=VARCHAR},
</if>
<if test="options != null">
#{options,jdbcType=VARCHAR},
</if>
<if test="dDesc != null">
#{dDesc,jdbcType=VARCHAR},
</if>
</trim>
</insert>