mybatis鼓勵我們自己寫sql，而不是自動生成sql，這樣的好處是我們可以對sql進行管理和優(yōu)化。如果我們懂sql開發(fā)和調優(yōu)，那么這種方式是一個很好的方式。
我們可以定義一個map xml文件，來指定具體要執(zhí)行的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相同，參數(shù)類型，和返回值類型也必須相同。
我們在configuration文件中注冊了該mapper

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

然后就可以通過調用這個接口中的方法就可以執(zhí)行對應的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語句執(zhí)行影響到的數(shù)據(jù)行數(shù)

當我們的對象和數(shù)據(jù)庫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>

動態(tài)sql

當我們需要根據(jù)用戶的輸入條件動態(tài)啊生產sql的時候，則需要如下動態(tài)方式生成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>