目錄
- 動态SQL
-
- if
- where
- trim
- choose
- set
- foreach
-
- 查詢
- 插入
- _databaseId
- bind
- sql片段
動态SQL
if
請求位址: http://localhost:8080/dept/dynamic/condition/if
如果不帶id會報錯
解決:添加 where 1=1 或者 使用where标簽
where
where标簽會替換掉第一個and,如果and在後邊就會說sql語句有錯誤,這個時候就要和trim标簽一起來使用mapper.xml
<select id="getEmpsConditionWhere" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,email,gender
FROM
tbl_employee
<where>
<if test="id != null">
id = #{id} AND
</if>
<if test="lastName != null and lastName != ''">
last_name LIKE #{lastName} AND
</if>
<if test="email != null and email != ''">
email = #{email} AND
</if>
<if test="gender == 1 or gender == 0">
gender = #{gender}
</if>
</where>
</select>
請求位址:http://localhost:8080/dept/dynamic/condition/where
請求參數:{“id”:1}
sql執行出錯,執行的sql語句為
可以明顯看到sql出了問題,是以我們需要把後邊的and去掉,這時我們就可以使用到trim标簽,把and去掉
trim
trim共有四個屬性:
prefix:加字首,
prefixOverrides:去字首,
suffix:加字尾,
suffixOverrides:去字尾
在where标簽内添加一個trim标簽,标明我們要去掉and字尾即可,sql執行就不會出錯了
<select id="getEmpsConditionWhere" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,email,gender
FROM
tbl_employee
<where>
<trim suffixOverrides="AND">
<if test="id != null">
id = #{id} AND
</if>
<if test="lastName != null and lastName != ''">
last_name LIKE #{lastName} AND
</if>
<if test="email != null and email != ''">
email = #{email} AND
</if>
<if test="gender == 1 or gender == 0">
gender = #{gender}
</if>
</trim>
</where>
</select>
choose
按照條件查詢,相當于switch case,從前向後判斷,如果有id,就直接按id查,如果有lastName,就按lastName查,不多贅述
<select id="getEmpsConditionChoose" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,email,gender
FROM
tbl_employee
WHERE
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="lastName != null and lastName != ''">
last_name LIKE #{lastName}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
gender = #{gender}
</otherwise>
</choose>
</select>
set
set用于更新的sql語句中
在update時,多條件更新,每個屬性後面要加逗号“,”,這個時候可能會出現多一個“,”的情況,此時我們就可以使用set去掉後邊的“,”,除此之外我們使用前面說的trim也可實作目前的操作
<update id="updateEmpsConditionSet">
UPDATE tbl_employee
<set>
<if test="id != null">
id = #{id},
</if>
<if test="lastName != null and lastName != ''">
last_name LIKE #{lastName},
</if>
<if test="email != null and email != ''">
email = #{email},
</if>
<if test="gender == 1 or gender == 0">
gender = #{gender}
</if>
</set>
</update>
foreach
查詢
foreach用來查參數是一個清單,比如說使用IN操作符就會用到
foreach的幾個屬性:
collection:元素集合,
item_id:取出來的每一項,
separator:間隔符,
open:字首,close:字尾,
index:周遊list的時候是索引,周遊map的時候是key
<select id="getEmpsConditionForeach" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,gender,email
FROM
tbl_employee
WHERE id IN
<foreach collection="ids" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
插入
插入多條記錄
<insert id="insertEmps">
insert into tbl_employee(last_name,gender,email,d_id)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>
_databaseId
可以通過這個參數來判斷目前是何種環境或者種類的資料庫,然後據此來執行對應資料庫的sql
bind
可以修改OGNL表達式的值,下面這個例子解釋了_databasedId和bind,如果使用了bind,建立一個新的變量_lastName在lastName的基礎上做修改,達到想要的形式
<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
<!-- bind:可以将OGNL表達式的值綁定到一個變量中,友善後來引用這個變量的值 -->
<bind name="_lastName" value="'%'+lastName+'%'"/>
<if test="_databaseId=='mysql'">
select * from tbl_employee
<if test="_parameter!=null">
where last_name like #{_lastName}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from employees
<if test="_parameter!=null">
where last_name like #{_parameter.lastName}
</if>
</if>
</select>
sql片段
添加sql片段,後續的xml中的标簽用到時就可以直接引入
<sql id="cols">
id,last_name,email,gender
</sql>
<select id="getEmpsConditionIf" resultType="com.banana.mybatis.bean.Employee">
SELECT
<include refid="cols"/>
FROM
tbl_employee
WHERE
<if test="id != null">
id = #{id}
</if>
<if test="lastName != null and lastName != ''">
AND last_name LIKE #{lastName}
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
<if test="gender == 1 or gender == 0">
AND gender = #{gender}
</if>
</select>