springboot整合mybatis使用

参数:

  • parameterType: 方法的入参类型
  • resultType: 返回值类型
  • useGeneratedKeys : 返回插入id, keyProperty : 数据库主键映射到类对象的字段
  • 多个参数传递的时候使用 @Param注解

动态SQL

  • 动态 SQL 使用
  • 可进行批量查询和更新

关联查询

  • 一对一方式一:association javaType 列出字段
  • 一对一方式二: association column select
  • 一对多方式一: collection ofType 列出字段
  • 一对多方式二: collection column select

参数传递

    <!--单个参数查询-->
    Member findMemberByName(@Param("name") String name);

    <select id="findMemberByName" parameterType="String" resultType="Member">
        SELECT * FROM member WHERE name = #{name}
    </select>

    <!--单个参数使用对象-->
    int addMember(Member member);

    <insert id="addMember" parameterType="Member" useGeneratedKeys="true" keyProperty="id">
        insert into member (name, age, money, create_time) values (#{name}, #{age}, #{money}, #{createTime})
    </insert>

    <!--多参数查询-->
    Member findByNameAndAge(@Param("name") String name, @Param("age") Integer age);

    <select id="findByNameAndAge" resultType="Member">
        select * from member where name=#{name} and age=#{age} order by id desc limit 1
    </select>

动态SQL

     <!--多条件 where if 查询-->
    <select id="getByNameAndAgeAndMoney" resultType="Member">
        select * from member
        <where>
            <if test="name != null">name=#{name}</if>
            <if test="age != null">age=#{age}</if>
            <if test="money != null">money=#{money}</if>
        </where>
    </select>

    <!--多条件choose 相当与switch-->
    <select id="getByMembersChoose" resultType="Member">
        select * from member where 
        <choose>
            <when test="name != null">name = #{name}</when>
            <when test="age != null">age = #{age}</when>
            <when test="money != null">money = #{money}</when>
            <otherwise>
                name=#{name}
            </otherwise>
        </choose>
    </select>

    <!--foreach 批量添加-->
    <insert id="batchAddMember" parameterType="Member">
        insert into member (name, age, money, create_time) values
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.age}, #{item.money}, #{item.createTime})
        </foreach>
    </insert>

    <!--foreach 批量查询-->
    <select id="getMemberByIds" parameterType="Integer" resultType="Member">
        select * from member where id in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

关联查询 一对一

<!-- 方式一 -->
    <resultMap id="empMap" type="employee">
        <id property="empId" column="emp_id" />
        <result property="empName" column="emp_name" />
        <association property="department" javaType="Department">
            <id property="deptId" column="dept_id" />
            <result property="deptName" column="dept_name" />
        </association>
    </resultMap>

    <select id="findEmployeeByEmpId" parameterType="Integer" resultMap="empMap">
        select * from employee e, department d where e.dept_id = d.dept_id and e.emp_id = #{empId}
    </select>

    <!-- 方式二 -->
    <resultMap id="empMap2" type="Employee">
        <id property="empId" column="emp_id" />
        <result property="empName" column="emp_name" />
        <association property="department" javaType="Department" column="dept_id" select="cn.emam.mybatis.dao.DepartmentMapper.findDepartmentByDeptId" />
    </resultMap>

    <select id="findEmployeeByEmpId2" parameterType="Integer" resultMap="empMap2">
        select * from employee where emp_id = #{empId}
    </select>

关联方式 一对多

     <!-- 一对多 方式一 -->
    <resultMap id="deptMap1" type="Department">
        <id property="deptId" column="dept_id" />
        <result property="deptName" column="dept_name" />
        <collection property="employees" ofType="Employee">
            <id property="empId" column="emp_id" />
            <result property="empName" column="emp_name" />
        </collection>
    </resultMap>

    <select id="findDepartmentByDeptIdType1" parameterType="Integer" resultMap="deptMap1">
        select * from employee e, department d where e.dept_id = d.dept_id and d.dept_id = #{deptId}
    </select>

 <!-- 一对多 方式二 -->
    <resultMap id="deptMap2" type="Department">
        <id property="deptId" column="dept_id" />
        <result property="deptName" column="dept_name" />
        <collection property="employees" ofType="Employee" column="dept_id" select="cn.emam.mybatis.dao.EmployeeMapper.getEmployeeByDeptId" />
    </resultMap>

    <select id="findDepartmentByDeptIdType2" parameterType="Integer" resultMap="deptMap2">
        select * from department where dept_id = #{deptId}
    </select>