mybatis-SQL语句

一、驼峰映射，写在mybits配置文件中

<setting name="mapUnderscoreToCamelCase" value="true"/>

二、别名

 //查询
    @Select("select id, username, password, name, gender, image, job, entrydate as ed, dept_id, create_time, update_time from emp")
    List<Emp> findAll2();

三、自定义映射

//查询
@Results({//@Results: 自定义结果映射
    @Result(column = "entrydate", property = "ed"),//@Result一条映射规则 (column = "数据表中字段名", property = "实体类属性名")
    @Result(column = "dept_id", property = "deptId")
})	
    @Select("select * from emp")
    List<Emp> findAll3();

四、模糊查询

where name like concat('%',#{name},'%')

五、动态SQL

<!--
    字符串需要跟null和空串比较
    其他类型只要跟null比较

    if：使用test进行条件判断，只有条件成立，条件中的sql才会生效
    where：只会在<where>标签内部有内容的情况下才插入where子句，而且会自动去除子句的开头的AND或OR。
-->
<select id="findByCondition" resultType="com.itheima.domain.Emp">
    select * from emp
    <where>
        <if test="name != null and name != ''">
            name like concat('%',#{name},'%')
        </if>
        <if test="gender != null">
            and gender = #{gender}
        </if>
        <if test="begin != null and end != null">
            and entrydate between #{begin} and #{end}
        </if>
    </where>
</select>

<update id="update">
    update emp
    <set>
          <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="password != null and password != ''">
            password = #{password},
        </if>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="gender != null">
            gender = #{gender},
        </if>
        <if test="image != null and image != ''">
            image = #{image},
        </if>
        <if test="job != null">
            job = #{job},
        </if>
        <if test="ed != null">
            entrydate = #{ed},
        </if>
        <if test="deptId != null">
            dept_id = #{deptId},
        </if>
        <if test="createTime != null">
            create_time = #{createTime},
        </if>
        <if test="updateTime != null">
            update_time = #{updateTime},
        </if>
    </set>
    where id = #{id}
</update>

* collection：集合名称
* item：集合遍历出来的元素
* separator：每一次遍历使用的分隔符
* open： 遍历开始前拼接的片段
* close：遍历结束后拼接的片段

  <delete id="deleteByIds">
      delete from emp where id in
      <foreach collection="ids" item="id" separator="," open="(" close=")">
          #{id}
      </foreach>
  </delete>

可能用到Arrays.asList()方法。

六、SQL片段

* 定义SQL片段:  <sql id="selectUser"></sql> 
* 引用SQL片段:  <include refid="selectUser"></include>