1. OGNL表達(dá)式
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
1.1 <where> 標(biāo)簽
<where>元素只在子元素有內(nèi)容的情況下才插入 WHERE子句捌显；而且注盈，若子句的開(kāi)頭為 AND 或OR, <where>元素也會(huì)將它們?nèi)コ?/p>
<where>
<if test="name != null and name != '' ">
and name = #{name}
</if>
</where>
1.2 <foreach> 標(biāo)簽
<where>
<if test="names != null and names.size() > 0">
and name in
<foreach item="item" collection="names" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
1.3 <choose> 標(biāo)簽
類比 Java 中的 switch 語(yǔ)句马澈，只會(huì)進(jìn)入其中一個(gè)
<sql id="queryCondition">
<where>
<choose>
<when test="name != null and name != ''">
and name = #{name}
</when>
<when test="status != null">
and status = #{status}
</when>
<otherwise>
and age = 20
</otherwise>
</choose>
</where>
</sql>
1.4 <trim> 標(biāo)簽
四個(gè)屬性：
prefix蔑匣，suffix 表示拼接
prefixOverrides螃概，suffixOverrides 表示刪除
<trim prefix="and" prefixOverrides="and | or" suffix="," suffixOverrides=","></trim>
2. ${} VS #{}
-
${}拼接符- 對(duì)傳入的參數(shù)不會(huì)做任何的處理佑菩，傳遞什么就是什么
- 應(yīng)用場(chǎng)景：設(shè)置動(dòng)態(tài)表名或列名
- 缺點(diǎn)：${} 可能導(dǎo)致 SQL 注入
-
#{}占位符- 對(duì)傳入的參數(shù)會(huì)預(yù)編譯處理秘狞，被當(dāng)做字符串使用
- 比如解析后的參數(shù)值會(huì)有引號(hào)
select * from user where name = 'tinyspot'
- 兩者之間的關(guān)系可類比：JDBC 使用 PreparedStatement 代替 Statement
2.1 示例
<select id="propertyCount" resultType="UserCount">
select ${property} as property, count(1) as total
from user
group by ${property}
</select>
解析后：
==> Preparing: select name as property, count(1) as total from user group by name
==> Parameters:
<== Columns: property, total
若改為 group by #{property}
會(huì)報(bào)錯(cuò)：
### SQL: select name as property, count(1) as total from user group by ?
### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
3. 標(biāo)簽 <sql> <include> <bind>
3.1 <include> 標(biāo)簽 - SQL復(fù)用
<select id="findUsersByCondition" resultType="User">
select
<include refid="columns"/>
from user
<include refid="queryByPage"/>
</select>
4. 小問(wèn)題
查詢報(bào)錯(cuò)：Expected one result (or null) to be returned by selectOne(), but found: 3
解決方式一：用 List<User> 接收
解決方式二：加 limit 1 限制
<select id="findUser" resultType="User">
select * from user
<include refid="queryCondition" />
limit 1
</select>
5. 實(shí)戰(zhàn)
5.1 分頁(yè)查詢
@RequestMapping("/findUsersByCondition")
public String findUsersByCondition(UserDO userDO) {
userDO.resetOffset();
return JSON.toJSONString(userDao.findUsersByCondition(userDO));
}
請(qǐng)求地址：http://localhost:8080/findUsersByCondition?names=tinyspot,xing&page=3&pageSize=10
@Data
public class UserDO extends BaseQuery {
private static final long serialVersionUID = 6521438352578419238L;
private Integer id;
private String name;
private Integer age;
private Integer status;
private List<String> names;
private boolean forbiddenStatus = false;
private boolean filterForbiddenStatus = true;
}
@Data
public class BaseQuery implements Serializable {
private static final long serialVersionUID = 1604477692747202533L;
public static final int DEFAULT_PAGE_SIZE = 20;
public static final int MAX_PAGE_SIZE = 100;
private int pageSize = DEFAULT_PAGE_SIZE;
private int page = 1;
private int offset = 0;
public void resetOffset() {
this.offset = pageSize * (page - 1);
}
}
<sql id="columns">
id, name, age, status, extend, create_time, update_time
</sql>
<sql id="queryByPage">
<include refid="queryCondition"/>
<include refid="orderBy"/>
limit #{offset}, #{pageSize}
</sql>
<sql id="orderBy">
order by id desc
</sql>
<sql id="queryCondition">
<where>
<if test="name != null and name != '' ">
and name = #{name}
</if>
<if test="status != null ">
and status = #{status}
</if>
<if test="names != null and names.size() > 0">
and name in
<foreach item="item" collection="names" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<choose>
<when test="forbiddenStatus==true">
AND status in (-1)
</when>
<otherwise>
<choose>
<when test="filterForbiddenStatus==true">
AND status not in (-1, -4)
</when>
<otherwise>
AND status != -4
</otherwise>
</choose>
</otherwise>
</choose>
</where>
</sql>
解析后：
==> Preparing: select id , name, age, status, create_time, update_time from user WHERE name in ( ? , ? ) AND status not in (-1, -4) order by id desc limit ?, ?
==> Parameters: tinyspot(String), xing(String), 20(Integer), 10(Integer)
<== Total: 0
5.2 更新
請(qǐng)求地址 http://localhost:8080/update?id=3&status=2
請(qǐng)求地址 http://localhost:8080/updateUser?id=3
@RequestMapping("update")
public Integer update(UserDO userDO) {
return userDao.update(userDO);
}
@RequestMapping("updateUser")
public Integer updateUser(UserDO userDO) {
userDO.setExtend("{\"time\":\"2022-10-01\"}");
UpdateOption option = new UpdateOption();
option.setUpdateExtend(true);
return userDao.updateUser(userDO, option);
}
更新模型
public interface UserDao {
Integer update(UserDO userDO);
Integer updateUser(@Param("userDO") UserDO userDO, @Param("option") UpdateOption option);
}
@Data
public class UpdateOption implements Serializable {
private static final long serialVersionUID = 4974510783148384827L;
private boolean updateExtend = false;
}
<update id="update">
update user set
<if test="status != null">
status = #{status}
</if>
where id = #{id}
</update>
<update id="updateUser">
update user set
<if test="userDO.status != null">
status = #{userDO.status}
</if>
<if test="option.updateExtend == true">
<if test="userDO.extend != null and userDO.extend != ''">
extend = #{userDO.extend}
</if>
</if>
where id = #{userDO.id}
</update>
5.3 插入
<insert id="insert">
insert into user (name, age, status, extend)
values (#{name}, #{age}, #{status}, #{extend})
</insert>
<insert id="insertUser">
insert into user
<trim prefix='(' suffix=')' suffixOverrides=','>
create_time, update_time,
<if test="name != null and name != ''">
name,
</if>
<if test="status != null">
status,
</if>
</trim>
<trim prefix='values (' suffix=')' suffixOverrides=','>
now(),
now(),
<if test="name != null and name != ''">
#{name},
</if>
<if test="status != null">
#{status},
</if>
</trim>
</insert>
