【MyBatis学习18】使用注解配置Mapper

时间:2023-02-01 20:33:37

本文博客地址:http://blog.csdn.net/soonfly/article/details/67640653 (转载请注明出处)

MyBatis支持使用注解来配置映射的sql语句,这样可以省掉映射器xml文件。

一、映射语句

1、insert
比如入门实例中的:

public int insert(User user) throws Exception;
<insert id="insert" parameterType="twm.mybatisdemo.pojo.User" useGeneratedKeys="true" keyProperty="id">
<!-- 将插入数据的主键返回,返回到user对象中 -->
insert into user (username,address,email) values (#{username},#{address},#{email})
</insert>

改成注解来配置映射:

@Insert("insert into user (username,address,email) values (#{username},#{address},#{email})")
@Options(useGeneratedKeys = true, keyProperty = "id")
public int insert(User user) throws Exception;

这里使用@Insert注解来定义一个INSERT映射语句。
并且使用@Options注解的userGeneratedKeyskeyProperty属性,让数据库auto_increment生成的主键值,赋值到keyProperty标记的属性id中

还有一种获取主键的方法(oracle要用SELECT SEQ.NEXTVAL FROM DUAL ,且order设为before)

<insert id="insert" parameterType="twm.mybatisdemo.pojo.User" >
<!-- 将插入数据的主键返回,返回到user对象中 -->
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
select last_insert_id()
</selectKey>
insert into user (username,address,email) values (#{username},#{address},#{email})
</insert>

相应的注解是:

@Insert("insert into user (username,address,email) values (#{username},#{address},#{email})")
@SelectKey(statement="select last_insert_id()",keyProperty="id", resultType=int.class, before=true)
public int insert(User user) throws Exception;

2、select

@Select("select * from user where id=#{id}")
public User selectById(int id) throws Exception;

返回的是一个User对象,因此如果select语句返回多行记录,就会出现TooManyResultsException异常。

3、update

@Update("update user set username=#{username},address=#{address},email=#{email} where id=#{id}")
public int update(User user) throws Exception;

4、delete

@Delete("delete from user where id=#{id}")
public int delete(int id) throws Exception;

二、结果映射

在xml配置文件中,将查询结果和JavaBean属性映射起来的标签是<resultMap>。对应的是@Results注解

@Select("select * from user")
@Results({ @Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "user_name"),
@Result(column = "city", property = "city") })
public List<User> selectAll() throws Exception;

@Results注解没办法复用。譬如我们的public User selectById(int id) throws Exception也要用到同样的@Results注解,但还是要重新写一个一模一样的@Results

@Select("select * from user where id=#{id}")
@Results({ @Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "user_name"),
@Result(column = "city", property = "city") })
public User selectById(int id) throws Exception;

如果要想使用可以复用的映射器,那么就使用@ResultMap注解。该注解依赖一个xml配置文件。
在接口文件同目录下新建一个userMapper.xml文件,并定义一个名为userMap的resultMap。

<mapper namespace="twm.mybatisdemo.mapper.UserMapper">
<!-- 自定义返回结果集 -->
<resultMap id="userMap" type="twm.mybatisdemo.pojo.User">
<id column="id" property="id" jdbcType="INTEGER" />
<result property="user_name" column="username"></result>
<result property="city" column="city"></result>
</resultMap>
</mapper>

在userMapper.java中,使用@ResultMap引用名为userMap的resultMap,实现复用。

@Select("select * from user where id=#{id}")  
@ResultMap("twm.mybatisdemo.mapper.UserMapper.userMap")
public User selectById(int id) throws Exception;

@Select("select * from user")
@ResultMap("twm.mybatisdemo.mapper.UserMapper.userMap")
public List<User> selectAll() throws Exception;

三、关联关系

1、一对一关系
MyBatis提供了@One注解来配合@Result注解,从而实现一对一关联查询数据的加载。
比如所有user对象都有个配偶信息spouse(本例中配偶本身也是一个user对象)

@Select("select * from user")
@Results({ @Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "user_name"),
@Result(column = "city", property = "city"),
@Result(column = "account_id", property = "account",one = @One(select = "twm.mybatisdemo.mapper.AccountMapper.selectById")) })
public List<User> selectAll() throws Exception;

在这里column = "account_id"指定了向twm.mybatisdemo.mapper.AccountMapper.selectById方法传递的参数。如果@OneSELECT查询返回了多行结果,则会抛出TooManyResultsException异常。

调用:

UserMapper userMapper =sqlSession.getMapper(UserMapper.class); 
List<User> userlist = userMapper.selectAll();
System.out.println(userlist[0].getAccount().getBalance());

如果结果映射使用的是xml文件,那么可以association 来配置映射:

<mapper namespace="twm.mybatisdemo.mapper.UserMapper">
<!-- 自定义返回结果集 -->
<resultMap id="userMapWithAccount" type="twm.mybatisdemo.pojo.User">
<id column="id" property="id" jdbcType="INTEGER" />
<result property="user_name" column="username"></result>
<result property="city" column="city"></result>
<association property="account" javaType="Account" column="account_id"
select="twm.mybatisdemo.mapper.AccountMapper.selectById" />

</resultMap>
</mapper>

接口如下:

@Select("select * from user")
@ResultMap("twm.mybatisdemo.mapper.UserMapper.userMapWithAccount")
public List<User> selectAll() throws Exception;

2、一对多关系
MyBatis提供了@Many注解来配合@Result注解,从而实现一对多关联查询数据的加载。
现在让我们看一下如何使用@Many注解获取一个讲师及其教授课程列表信息:

public interface TutorMapper
{
@Select("select * from courses where tutor_id=#{tutorId}")
@Results(
{
@Result(id = true, 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")
})
List<Course> findCoursesByTutorId(int tutorId);

@Select("SELECT tutor_id, name as tutor_name, email, addr_id
FROM tutors where tutor_id=#{tutorId}"
)
@Results(
{
@Result(id = true, column = "tutor_id", property = "tutorId"),
@Result(column = "tutor_name", property = "name"),
@Result(column = "email", property = "email"),
@Result(property = "address", column = "addr_id",
one = @One(select = " com.mybatis3.
mappers.TutorMapper.findAddressById"
)),
@Result(property = "courses", column = "tutor_id",
many = @Many(select = "com.mybatis3.mappers.TutorMapper.
findCoursesByTutorId"
))
})
Tutor findTutorById(int tutorId);
}

同样也可以采用配置xml中配置resultMaps的方式

<mapper namespace="com.mybatis3.mappers.TutorMapper">
<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>
</mapper>
public interface TutorMapper
{
@Select("SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL,
A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY, 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}"
)
@ResultMap("com.mybatis3.mappers.TutorMapper.TutorResult")
Tutor selectTutorById(int tutorId);
}

四、动态SQL

MyBatis提供了各种注解如@InsertProvider,@UpdateProvider,@DeleteProvider和@SelectProvider,来帮助构建动态SQL语句,然后让MyBatis执行这些SQL语句。

这里重点用@SelectProvider举例:

创建一个TutorDynaSqlProvider.java类,以及findTutorByIdSql()方法,如下所示:

package com.mybatis3.sqlproviders;
import org.apache.ibatis.jdbc.SQL;
public class TutorDynaSqlProvider
{
public String findTutorByIdSql(int tutorId)
{
return "SELECT TUTOR_ID AS tutorId, NAME, EMAIL FROM TUTORS
WHERE TUTOR_ID="
+ tutorId;
}
}

在TutorMapper.java接口中创建一个映射语句,如下:

@SelectProvider(type=TutorDynaSqlProvider.class, method="findTutorByIdSql") 
Tutor findTutorById(int tutorId);

本文博客地址:http://blog.csdn.net/soonfly/article/details/67640653 (转载请注明出处)