笔记：mybatis笔记

1.注解insert

加@insert注解

参数是实体类；

   @Insert("insert into mcLog values(#{logDate},#{logContent})")
    public boolean insertLog(McLog mcLog);

占位符内容设置实体类的属性；

2.查询Select多参数

@Select("select * from mcUser where userId like #{userId} and userWord like #{userWord}")
public McUser login(@Param("userId") String userId,
                    @Param("userWord") String userWord);

@Param注解绑定占位符参数

3.子查询Select

    @Select("select * from mcSong where songName " +
            "in " +
            "(select songName from userSong where userName like #{userName})")
    public List<McSong> selectBuy(String userName);

子查询也支持select

4.模糊查询小技巧

注解语句

    @Select("select * from mcLog where logContent like #{logContent}")
    public List<McLog> selectUserLog(String logContent);

调用**参数加%%**进行模糊查询件

adminMapper.selectUserLog("%" + userName + "%");

5.不定参数update的xml使用set拼接写法

参数是int类型使用==0判断

    <update id="songUpdate" parameterType="McSong">
        update mcSong
        <set>
            <if test="songBuy != 0">songBuy=#{songBuy},</if>
            <if test="songCollect != 0">songCollect=#{songCollect},</if>
            <if test="songLike != 0">songLike=#{songLike},</if>
            <if test="songDown != 0">songDown=#{songDown},</if>
            <if test="songListen != 0">songListen=#{songListen},</if>
        </set>
        where songName like #{songName}
    </update>

参数类型是string判空

    <update id="update_1" parameterType="Employee">
        update EMPLOYEE
        <set>
            <if test="ename != null and ename.trim().length>0">ENAME=#{ename},</if>
            <if test="job != null and job.trim().length>0">JOB=#{job},</if>
            <if test="mgr != null">MGR=#{mgr},</if>
            <if test="hiredate != null">HIREDATE=#{hiredate},</if>
            <if test="sal != null">SAL=#{sal},</if>
            <if test="comm != null">COMM=#{comm},</if>
            <if test="deptno != null">DEPTNO=#{deptno},</if>
        </set>
       <!-- <where>如果带多条件的更依然可以使<where>元素动态生成where子句</where> -->
       where EMPNO=#{empno}
    </update>

6.不定参数update的xml使用trim拼接写法

    <update id="update_2" parameterType="Employee">
        update EMPLOYEE
        <trim prefix="set" suffixOverrides=",">
            <if test="ename != null and ename.trim().length>0">ENAME=#{ename},</if>
            <if test="job != null and job.trim().length>0">JOB=#{job},</if>
            <if test="mgr != null">MGR=#{mgr},</if>
            <if test="hiredate != null">HIREDATE=#{hiredate},</if>
            <if test="sal != null">SAL=#{sal},</if>
            <if test="comm != null">COMM=#{comm},</if>
            <if test="deptno != null">DEPTNO=#{deptno},</if>
        </trim>
       <!-- <where>如果带多条件的更依然可以使<where>元素动态生成where子句</where> -->
       where EMPNO=#{empno}
    </update>

7.delete的where语句

<delete id="delete" parameterType="Employee">
        delete EMPLOYEE
        <where>
            <if test="empno != null">
                and EMPNO=#{empno}
            </if>
            <if test="ename != null">
                and ENAME=#{ename}
            </if>
            <if test="job != null and job.trim().length>0">
                and JOB=#{job}
            </if>
            <if test="loSal != null">
                and SAL>=#{loSal}
            </if>
            <if test="hiSal != null">
                <![CDATA[ and SAL<=#{hiSal} ]]>
            </if>
            <if test="deptno != null">
                and DEPTNO=#{deptno}
            </if>
        </where>
    </delete>