動态sql介紹
由于在開發過程不同的業務中會用到不同的操作條件,如果每個業務都拼接不同sql語句的話會是一個龐大的工作量;此時動态sql就能解決這個問題,可以針對不确定的操作條件動态拼接sql語句,根據送出的條件來完成業務sql的執行!
sql根标簽
<insert>,<update>,<select>,<delete>
動态sql标簽
<if>,<choose>,<when>,<otherwise>,<trim>,<foreach>,<where>,<set>,<bind>
關聯關系标簽
<collection>,<association>
sql根标簽介紹
01<select>
<select id="getById" resultMap="orderMap"/>
02<insert>
<update id="update" parameterType="employee"/>
03<update>
<insert id="insert" puseGeneratedKeys="true" keyProperty="id"/>
04<delete>
<delete id="delete" parameterType="employee"/>
注意哦
parameterType與resultMap為查詢傳回結果,且不能同時存在,id不能重複,插入時候使用puseGeneratedKeys、keyProperty來保證主鍵生成
常用動态sql标簽介紹
01<foreach>
//循環插入等場景使用foreach
<select id="selectIDInEids" parameterType="java.util.List" resultType="com.atguigu.ssm.pojo.Employee">
select * from employee where id in
<foreach collection="eids" item="eid" open="(" separator="," close=")">
${eid}
</foreach>
</select>
02<if>
//通常用來處理條件語句的判斷
<select id="queryEmployeeIf" parameterType="employee" resultType="employee">
select * from employee where 1=1
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="gender != null and gender != '' ">
and gender like '%${gender}%'
</if>
</select>
03<choose><when><otherwise>
<select id="queryEmployeeChoose" parameterType="employee" resultType="employee">
select * from employee where 1=1
<choose>
<when test="empName != null and empName != ''">
and emp_name = #{empName}
</when>
<when test="gender != null and gender != '' ">
and gender like '%${gender}%'
</when>
<otherwise>
and id = 18
</otherwise>
</choose>
</select>
04<where>
// where标簽标明,有符合條件則主動拼接條件;不需要額外拼接where 1=1
<select id="queryEmployeeWhere" parameterType="employee" resultType="employee">
select * from employee
<where>
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
</select>
05<trim>
// 效果與 where類似,字首添加where,如果符合條件則提出預設and。
<select id="queryEmployeeTrim" parameterType="employee" resultType="employee">
select * from employee
<trim prefix="where" prefixOverrides="and" >
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</trim>
</select>
06<set>
// set标簽可以用來進行更新操作,mybatis操作更新隻需要指定對應元素即可進行局部更新,顯的更加靈活
<update id="updateEmployeeInfo" parameterType="employee" >
update employee
<set>
<if test="empName != null and empName != ''">
emp_name=#{empName},
</if>
<if test="gender != null">
gender=#{gender},
</if>
</set>
where id=#{id}
</update>
07<bind>
// 該标簽主要為了給傳遞的參數添加特色标簽而完成特殊的需求;
// 如模糊查詢可以通過該标簽添加%name%
<select id="queryEmployeeInfoByBind" parameterType="employee" resultType="employee">
<bind name="pattern" value="'%' + empName + '%'"/>
select * from student where empName like #{pattern}
</select>
注意哦
01字元串空值問題
字元串變量需要針對null值和空字元串判斷哦;
02基本類型轉換問題
針對基本的類型的變量如果實體與前台字段中存在空字元串會報錯哦,是以執行個體變量最好使用其包裝類作為基本類型的替換類型;
03xml解析&&問題
動态sql時解析時xml無法失敗&&,需要替換成為and,表示并且。
關聯關系标簽介紹
01<association>
// 該标簽專門用來映射一對一類型關聯關系,如每個人都擁有自己的身份辨別,且僅僅為一對一,不會存在多種的可能
<resultMap id="orderMap" type="com.atguigu.entity.Order">
<id column="order_id" property="orderId"></id>
<result column="order_name" property="orderName"></result>
<result column="customer_id" property="customerId"></result>
<association property="customer" javaType="com.atguigu.entity.Customer">
<id column="customer_id" property="customerId"></id>
<result column="customer_name" property="customerName"></result>
</association>
</resultMap>
02<collection>
//該标簽則能完成一對多的關聯關系,多對多則可以了解為倆次處理一對多即可
<resultMap id="customerMap" type="com.atguigu.entity.Customer">
<id property="customerId" column="customer_id"></id>
<result property="customerName" column="customer_name"></result>
<collection property="orderList" ofType="com.atguigu.entity.Order">
<id property="orderId" column="order_id"></id>
<result property="orderName" column="order_name"></result>
</collection>
</resultMap>
小結
總之,在您學完MyBatis中動态拼接sql技能後,針對不同業務場景,靈活運用動态标簽即可達到您想要的結果!常見的标簽用了、常見的坑也踩了,繼續開始内卷!