mybatis mapper详解

mybatis鼓励我们自己写sql，而不是自动生成sql，这样的好处是我们可以对sql进行管理和优化。如果我们懂sql开发和调优，那么这种方式是一个很好的方式。
我们可以定义一个map xml文件，来指定具体要执行的sql语句。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
  <mapper namespace="com.mybatis3.mappers.StudentMapper">
  <select id="findStudentById" parameterType="int" resultType="Student">
  select stud_id as studId, name, email, dob from Students wherestud_id=#{studId}
  </select>
</mapper>

如上所示，我们定义了一个select语句，然后我们可以定义一个interface

package com.mybatis3.mappers;
public interface StudentMapper
{
  Student findStudentById(Integer id);
}

这个interface的全名（包名+类名）必须和上面mapper的 namespace相同。接口中的方法名要和select语句的id相同，参数类型，和返回值类型也必须相同。
我们在configuration文件中注册了该mapper

<mappers>
  <mapper resource="com/mybatis3/mappers/StudentMapper.xml"/>
  </mappers>

然后就可以通过调用这个接口中的方法就可以执行对应的sql语句了。

public Student findStudentById(Integer studId)
{
  SqlSession sqlSession = MyBatisUtil.getSqlSession();
  try {
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    return studentMapper.findStudentById(studId);
  } finally {
    sqlSession.close();
  }
}

mybatis mapper不仅可以配置select语句，还可以配置update,insert, delete 语句

//对于想mysql这样的，支持自动增加key值得可以使用如下方式
<insert id="insertStudent" parameterType="Student"useGeneratedKeys="true" keyProperty="studId">
  INSERT INTO STUDENTS(NAME, EMAIL, PHONE)
  VALUES(#{name},#{email},#{phone})
</insert>
//对于像oracle这样的不支持的，需要通过某个queue来获取的，可以使用下面两种方式
<insert id="insertStudent" parameterType="Student">
  <selectKey keyProperty="studId" resultType="int" order="BEFORE">
    SELECT ELEARNING.STUD_ID_SEQ.NEXTVAL FROM DUAL
  </selectKey>

  INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL, PHONE)
  VALUES(#{studId},#{name},#{email},#{phone})
</insert>

//下面这种方式通过在表上建trigger来插入key,然后通过selectKey获取
<insert id="insertStudent" parameterType="Student">
  INSERT INTO STUDENTS(NAME,EMAIL, PHONE)
  VALUES(#{name},#{email},#{phone})
  <selectKey keyProperty="studId" resultType="int" order="AFTER">
    SELECT ELEARNING.STUD_ID_SEQ.CURRVAL FROM DUAL
  </selectKey>
</insert>

<update id="updateStudent" parameterType="Student">
  UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email}, PHONE=#{phone}
  WHERE STUD_ID=#{studId}
</update>

<delete id="deleteStudent" parameterType="int">
  DELETE FROM STUDENTS WHERE STUD_ID=#{studId}
</delete>

返回值均为收此次sql语句执行影响到的数据行数

当我们的对象和数据库table之间不是简单的一对一对应的时候，我们可能需要指定对象的property和表的column之间的对应关系.
如下所示，当column和property的name不同时，我们可以通过定义resultMap的形式来定义他们之间的对应关系。

<resultMap id="StudentResult" type="com.mybatis3.domain.Student">
  <id property="studId" column="stud_id"/>
  <result property="name" column="name"/>
  <result property="email" column="email"/>
  <result property="phone" column="phone"/>
</resultMap>

<select id="findAllStudents" resultMap="StudentResult" >
  SELECT * FROM STUDENTS
</select>

当涉及到嵌套时，可以使用如下方式

<resultMap type="Address" id="AddressResult">
  <id property="addrId" column="addr_id"/>
  <result property="street" column="street"/>
  <result property="city" column="city"/>
  <result property="state" column="state"/>
  <result property="zip" column="zip"/>
  <result property="country" column="country"/>
</resultMap>

<resultMap type="Student" id="StudentWithAddressResult">
  <id property="studId" column="stud_id"/>
  <result property="name" column="name"/>
  <result property="email" column="email"/>
  <association property="address" resultMap="AddressResult"/>
</resultMap>

<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
  SELECT STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY
  FROM STUDENTS S LEFT OUTER JOIN ADDRESSES A ON S.ADDR_ID=A.ADDR_ID
  WHERE STUD_ID=#{studId}
</select>

当是一对多时，可以使用如下方式：

<resultMap type="Course" id="CourseResult">
  <id column="course_id" property="courseId"/>
  <result column="name" property="name"/>
  <result column="description" property="description"/>
  <result column="start_date" property="startDate"/>
  <result column="end_date" property="endDate"/>
</resultMap>
  
<resultMap type="Tutor" id="TutorResult">
  <id column="tutor_id" property="tutorId"/>
  <result column="tutor_name" property="name"/>
  <result column="email" property="email"/>
  <collection property="courses" resultMap="CourseResult"/>
</resultMap>
  
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
  SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID, C.NAME, DESCRIPTION, START_DATE, END_DATE
  FROM TUTORS T LEFT OUTER JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID
  LEFT OUTER JOIN COURSES C ON T.TUTOR_ID=C.TUTOR_ID
  WHERE T.TUTOR_ID=#{tutorId}
</select>

动态sql

当我们需要根据用户的输入条件动态啊生产sql的时候，则需要如下动态方式生成sql语句

choose 该方式适用于多个条件中选择一个满足条件的来生成sql

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
  SELECT * FROM COURSES
  <choose>
    <when test="searchBy == 'Tutor'">
      WHERE TUTOR_ID= #{tutorId}
    </when>
    <when test="searchBy == 'CourseName'">
      WHERE name like #{courseName}
    </when>
    <otherwise>
      WHERE TUTOR start_date  &gt;= now()
    </otherwise>
  </choose>
</select>

where 适用于从多个条件中选择所有满足条件的来构成condtions

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
  SELECT * FROM COURSES
  <where>  
    <if test=" tutorId != null ">
      TUTOR_ID= #{tutorId}
    </if>
    <if test="courseName != null">
      AND name like #{courseName}
    </if>
    <if test="startDate != null">
      AND start_date  &gt;= #{startDate}
    </if>
    <if test="endDate != null">
      AND end_date  &lt;= #{endDate}
    </if>
  </where>
</select>

foreach

<select id="searchCoursesByTutors" parameterType="map" resultMap="CourseResult">
  SELECT * FROM COURSES
  <if test="tutorIds != null">
    <where>
      tutor_id IN
      <foreach item="tutorId" collection="tutorIds" 
        open="(" separator="," close=")">
        #{tutorId}
      </foreach>
    </where>
  </if>
</select>