参数:
- parameterType: 方法的入参类型
- resultType: 返回值类型
- useGeneratedKeys : 返回插入id, keyProperty : 数据库主键映射到类对象的字段
- 多个参数传递的时候使用 @Param注解
动态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>