一:Mybatis中的标签
1. if标签的用法
<select id="getGrap" parameterType="Object" resultType="com.jeecg.p3.deadbeat.order.entity.WxActDeadbeatOrderGrab">
SELECT
*
FROM wx_act_deadbeat_order_grab
where 1=1
<if test="debtNo !=null and debtNo!=''">
and debt_no=#{debtNo}
</if>
<if test="graborId !=null and graborId!=''">
and grabor_id=#{graborId}
</if>
</select>
(注:这种我们可以把所有的属性都加上,然后跟据不同需求使用,比较灵活)
2.where标签的用法
<!-- 查询学生list,like姓名,=性别、=生日、=班级,使用where,参数entity类型 -->
<select id="getStudentListWhereEntity" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<where>
<if test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
<if test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</if>
<if test="studentBirthday!=null">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday}
</if>
<if test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">
AND ST.CLASS_ID = #{classEntity.classID}
</if>
</where>
</select>
(注:这种写法和上面的where 1=1的用法一样)
3.set标签用法
<update id="updateStudent" parameterType="StudentEntity">
UPDATE STUDENT_TBL
<set>
<if test="studentName!=null and studentName!='' ">
STUDENT_TBL.STUDENT_NAME = #{studentName},
</if>
<if test="studentSex!=null and studentSex!='' ">
STUDENT_TBL.STUDENT_SEX = #{studentSex},
</if>
<if test="studentBirthday!=null ">
STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
</if>
<if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">
STUDENT_TBL.CLASS_ID = #{classEntity.classID}
</if>
</set>
WHERE STUDENT_TBL.STUDENT_ID = #{studentID};
</update>
(注:和if一起使用,在update语句中,如果存在null的字段,数据库就保持不更新)
4.trim的用法
<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
<if test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</if>
</trim>
</select>
(注:去掉多余的关键字标签,像select中的and或or;update中的,之类的)
5.choose的用法
<select id="getStudentListChooseEntity" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<where>
<choose>
<when test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</when>
<when test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</when>
<when test="studentBirthday!=null">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday}
</when>
<when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">
AND ST.CLASS_ID = #{classEntity.classID}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
(注:当when中有一个成立的话,那么choose就终止;如果when都不成立,就执行otherwise中的语句)
6.foreach的用法(暂时不太了解)
7.全局的动态SQL
<sql id="wherecontation">
<trim suffixOverrides="," >
<if test="query.isJudgment != null and query.isJudgment != ''" >
/* 是否判决 */
AND wado.is_judgment = #{query.isJudgment,jdbcType=VARCHAR}
</if>
<if test="query.focusNum != null and query.focusNum != ''" >
/* 关注人数 */
AND wado.focus_num = #{query.focusNum,jdbcType=INTEGER}
</if>
<if test="query.getNum != null and query.getNum != ''" >
/* 抢单人数 */
AND wado.get_num = #{query.getNum,jdbcType=INTEGER}
</if>
</trim>
</sql>
(注:截取的一部分,其中id定义的是后面需要引入该动态sql的名字;<include refid="wherecontation"/>引入的方式)