Mybatis大神之路(1) 注解配置SQL映射器

Mybatis注解配置SQL映射器

Mybatis提供了多种注解来支持不同类型的SQL语句，在微服务的架构体系下，流行的做法是将SQL脚本与JAVA代码分离，这也是Mybatis的区别于其他ORM框架的一个重要特性。随着Springboot为服务框架的的流行，注解式开发也越来越多，而一些简单的SQL同样可以使用Mybatis的注解式SQL映射器实现，本文将对Mybatis的常用注解做详细的说明。

---

@Insert注解用来定义Insert映射语句

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Insert("insert into teacher(teacher_name,sex,age,telphone,email,address_id)"
            + "values(#{teacherName},#{sex},#{age},#{telphone},#{email},#{addressId})")
    public int insertTeacher(TeacherInfoBO teacherInfoBO);
}  

同时可以使用@Options注解的userGeneratedKeys和keyProperty标签 让数据库生成的auto_increment自增主键列值设置到入参对象的指定属性上。如下将插入数据的主键值设置到teacherInfoBO的teacherId属性。

@Options(useGeneratedKeys=true, keyProperty="teacherId")

然而，一些数据库，如Oracle并不支持auto_increment列属性，一般使用序列产生主键值，此时我们可以使用@SelectKey注解为对象属性指定主键值，用法如下：

@SelectKey(statement="select teacher_id_seq.nextval from dual", keyProperty="teacherId",resutType=int.class,before=true)

@Delete注解用来定义DELETE映射语句

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Delete("delete from teacher where teacher_id=#{teacherId}")
    public int deleteTeacherById(int studentId);
}

@Update注解用来定义UPDATE映射语句

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Update("update teacher set teacher_name=#{teacherName},sex=#{sex},age=#{age},"
            + "telphone=#{telphone},email=#{email},address_id=#{addressId} "
            + "where teacher_id=#{teacherId}")
    public int updateTeacherById(Teacher teacher);
}

@Select注解用来定义SELETE映射语句

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select * from teacher where teacher_id=#{teacherId}")
    public TeacherInfoBO selectTeacherById(int teacherId);
}

为了将列名与TeacherInfoBO中属性名匹配，需要将teacher_id ，teacher_name，address_id分别起一个与TeacherInfoBO匹配的别名。

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select teacher_id teacherId,teacher_name teacherName, sex,age,telphone,email,address_id addressId from teacher where teacher_id=#{teacherId}")
    public TeacherInfoBO selectTeacherById2(int teacherId);
}

同样可以将列名与属性名的映射关系在注解@Results中定义，定义如下：

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select * from teacher where teacher_id=#{teacherId}")
    @Results({
        @Result(id=true,column="teacher_id", property="teacherId"),
        @Result(column="teacher_name", property="teacherName"),
        @Result(column="address_id", property="addressId")
    })
    public TeacherInfoBO selectTeacherById3(int teacherId);
}

当相同的结果映射需要在不同的方法的注解上进行定义的时候，重复代码显然是我们不能容忍的，此时我们可以考虑将结果集的映射放在特定的Mapper.xml配置文件中，然后在方法的注解上使用@ResultMap引用即可。

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select * from teacher where teacher_id=#{teacherId}")
    @ResultMap("TeacherResultMap")
    public TeacherInfoBO selectTeacherById4(int teacherId);
}

以下为TeacherMapper.xml配置文件

  <resultMap id="TeacherResultMap" type="com.paic.smart.pojo.bo.TeacherInfoBO">
    <id column="teacher_id" jdbcType="INTEGER" property="teacherId" />
    <result column="teacher_name" jdbcType="VARCHAR" property="teacherName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="telphone" jdbcType="VARCHAR" property="telphone" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="address_id" jdbcType="INTEGER" property="addressId" />
  </resultMap>

非常重要的一点是采用Mapper.xml配置文件的时候，必须在配置文件中指定mapper-locations和，即配置文件的位置，不然无法找到注解中指定的TeacherResultMap。

mybatis: 
  config-location: classpath:mybatis-config.xml   
  mapper-locations: 
  - classpath:mapper/**/*.xml 

一对一结果映射

One注解来使用嵌套select语句，加载一对一关联数据查询。注解的 select属性来指定一个使用完全限定名的方法上，下列方法返回一个Address对象，其中column=”address_id”，将作为selectAddressById方法的入参

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select * from address where address_id=#{addressId}")
    @Results({  
        @Result(id=true,column="address_id", property="addressId")
    })
    public Address selectAddressById(int addressId);
    @Select("select * from teacher where teacher_id=#{teacherId}")
    @Results({
        @Result(id=true,column="teacher_id", property="teacherId"),
        @Result(column="teacher_name", property="teacherName"),
        @Result(column="address_id", property="addressId"),
        @Result(property="address",column="address_id",one=@One(select="selectAddressById"))
    })
    public TeacherInfoBO selectTeacherWithAddressById(int teacherId);
}

同样也可以使用基于xml的映射器配置，使用嵌套结果来加载一对一关系查询，接口和TeacherMapper.xml信息如下，TeacherWithAddressResultMap使用<association>标签进行一对一映射。

  <resultMap id="TeacherWithAddressResultMap" type="com.paic.smart.pojo.bo.TeacherInfoBO">
    <id column="teacher_id" jdbcType="INTEGER" property="teacherId" />
    <result column="teacher_name" jdbcType="VARCHAR" property="teacherName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="telphone" jdbcType="VARCHAR" property="telphone" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="address_id" jdbcType="INTEGER" property="addressId" />
    <association property="address" resultMap="com.paic.smart.dao.mapper.AddressMapper.AddressBaseResultMap"/> 
  </resultMap>

在接口中加入@ResultMap("TeacherWithAddressResultMap")注解来引用它。

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select a.*,b.* from teacher a left join address b on a.address_id=b.address_id where teacher_id=#{teacherId}")
    @ResultMap("TeacherWithAddressResultMap")
    public TeacherInfoBO selectTeacherWithAddressById2(int teacherId);  
}

一对多结果映射

Many注解来使用嵌套select语句，加载一对多关联数据查询。注解的 select属性来指定一个使用完全限定名的方法上，该方法将返回一个List<Course>对象，其中column=”teacher_id”，将作为selectCourseById()方法的入参。

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select * from address where address_id=#{addressId}")
    @Results({  
        @Result(id=true,column="address_id", property="addressId")
    })
    public Address selectAddressById(int addressId);
    @Select("select * from course where teacher_id=#{teacherId}")
    @Results({  
        @Result(id=true,column="course_id", property="courseId"),
        @Result(column="course_name", property="courseName"),
        @Result(column="teacher_id", property="teacherId"),
    })
    public List<Course> selectCourseById(int courseId);
    @Select("select * from  teacher where teacher_id=#{teacherId}")
    @Results({
        @Result(id=true,column="teacher_id", property="teacherId"),
        @Result(column="teacher_name", property="teacherName"),
        @Result(column="address_id", property="addressId"),
        @Result(property="address",column="address_id",one=@One(select="selectAddressById")),
        @Result(property="course",column="teacher_id",many=@Many(select="selectCourseById"))
    })
    public TeacherInfoBO selectTeacherAllInfoById(int teacherId);
}

同样也可以使用基于xml的映射器配置，使用嵌套结果来加载一对多关系查询，接口和TeacherMapper.xml信息如下，TeacherAllInfoResultMap使用<collection>标签进行一对一映射。

  <resultMap id="TeacherAllInfoResultMap" type="com.paic.smart.pojo.bo.TeacherInfoBO">
    <id column="teacher_id" jdbcType="INTEGER" property="teacherId" />
    <result column="teacher_name" jdbcType="VARCHAR" property="teacherName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="telphone" jdbcType="VARCHAR" property="telphone" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="address_id" jdbcType="INTEGER" property="addressId" />
    <association property="address" resultMap="com.paic.smart.dao.mapper.AddressMapper.AddressBaseResultMap"/> 
    <collection property="course" resultMap="com.paic.smart.dao.mapper.CourseMapper.CourseBaseResultMap"/>
  </resultMap>

在接口中加入@ResultMap("TeacherAllInfoResultMap")注解来引用它。

public interface TeacherMapper extends CommonMapper<Teacher> {
    @Select("select a.*,b.*,c.* from teacher a left join address b on a.address_id=b.address_id "
            + "left join course c on a.teacher_id=c.teacher_id where a.teacher_id=#{teacherId}")
    @ResultMap("TeacherAllInfoResultMap")
    public TeacherInfoBO selectTeacherAllInfoById2(int teacherId);
}