MyBatis 注解开发

MyBatis的常用注解

• @Insert：实现新增
• @Update：实现更新
• @Delete：实现删除
• @Select：实现查询
• @Result：实现结果集封装
• @Results：可以与@Result 一起用，封装多个结果集
• @One：实现一对一结果集封装
• @Many：实现一对多结果集封装

MyBatis基于注解的增删改查

使用注解开发的好处就是：可以直接在Mapper接口写sql语句，无需编写mapper.xml文件，在业务简单的情况下，方便快速开发

• 新建UserMapper类：

  package com.demo.mapper;import com.demo.pojo.User;
  import org.apache.ibatis.annotations.Delete;
  import org.apache.ibatis.annotations.Insert;
  import org.apache.ibatis.annotations.Select;
  import org.apache.ibatis.annotations.Update;import java.io.IOException;
  import java.util.List;public interface UserMapper {/*** 添加用户*/@Insert("insert into user values (#{id},#{username})")void addUser(User user);/*** 更新用户*/@Update("update user set username = #{username} where id = #{id}")void updateUser(User user);/*** 删除用户*/@Delete("delete from user where id = #{id}")void deleteUser(Integer id);/*** 查询全部*/@Select("select * from user")List<User> selectAll();}

• 新建测试类

  package com.demo.test;import com.demo.mapper.UserMapper;
  import com.demo.pojo.User;
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.SqlSession;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  import org.junit.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.List;public class AnnotationTest {private UserMapper userMapper;@Beforepublic void before() throws IOException {// 1.Resources工具类，配置文件的加载，把配置文件加载成字节输入流InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");// 2.解析了配置文件，并创建了sqlSessionFactorySqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);// 3.生成sqlSession （默认开启一个事务，但是该事务不会自动提交， 参数为true为自动提交）SqlSession sqlSession = sqlSessionFactory.openSession(true);userMapper = sqlSession.getMapper(UserMapper.class);}@Testpublic void testSelect(){List<User> users = userMapper.selectAll();for (User user : users) {System.out.println(user);}}@Testpublic void testInsert(){User user = new User();user.setId(6);user.setUsername("测试数据");userMapper.addUser(user);testSelect();}@Testpublic void testUpdate(){User user = new User();user.setId(6);user.setUsername("修改测试数据");userMapper.updateUser(user);testSelect();}@Testpublic void testDelete(){userMapper.deleteUser(6);testSelect();}
  }

• 使用注解开发时，sqlMapConfig.xml无需引入mapper.xml文件，但是仍需要引入Mapper接口类，

  <!-- 直接使用package扫描mapper接口 -->
  <mappers><package name="com.demo.mapper"/>
  </mappers>或者<!-- 直接使引入指定的mapper接口 -->
  <mappers><mapper name="com.demo.mapper.UserMapper"/>
  </mappers>

MyBatis基于注解实现复杂映射开发

实现复杂关系映射之前我们可以在映射文件通过配置resultMap实现，使用注解开发后，我们可以使用@Results、@Result、@One、@Many 注解组合完成复杂关系的配置

注解	说明
@Results	代替的标签是，该注解中可以使用单个@Result注解，也可以使用@Result集合。使用格式：@Results({@Result( ), @Result( ) }) 或者@Results(@Result() )
@Result	代替了标签和标签 @Result中属性介绍 column：数据库中的列名 property ：实体类中的属性名 one：需要使用的@One注解 (@Result (one = @One) ()) many：需要使用的@Many注解 (@Result (many = @Many) ())

一对一查询

查询需求：查询订单，并同时把订单所属的用户一起查出来

对应的SQL语句：

select * from orders;
select * from user where id=查询出的订单uid;

原先用mapper.xml写的resultMap与sql语句

<!-- 手动配置实体属性与表字段的映射关系 -->
<resultMap id="orderAndUser" type="com.demo.pojo.Order"><result property="id" column="id"/><result property="orderTime" column="order_time"/><result property="total" column="total"/><association property="user" javaType="com.demo.pojo.User"><result property="id" column="uid"/><result property="username" column="username"/></association>
</resultMap><select id="findAll" resultMap="orderAndUser">select * from `order`,user where `order`.uid=user.id
</select>

现在在mapper接口使用注解：

public interface OrderMapper {@Results({@Result(property = "id", column = "id"),@Result(property = "orderTime", column = "order_time"),@Result(property = "total", column = "total"),@Result(property = "user", column = "uid", javaType = User.class, one = @One(select = "com.demo.mapper.UserMapper.findById")),})@Select("select * from `order`")List<Order> findOrderAndUser();
}

对应的UserMapper：

public interface UserMapper {/*** 根据id查用户* @param id 用户id* @return*/@Select("select * from user where id = #{id}")User findById(Integer id);}

这里执行的sql语句如下：

==>  Preparing: select * from `order` 
==> Parameters: 
<==    Columns: id, order_time, total, uid
<==        Row: 1, 2019-12-12, 3000, 1
====>  Preparing: select * from user where id = ? 
====> Parameters: 1(Integer)
<====    Columns: id, username
<====        Row: 1, 张三
<====      Total: 1
<==        Row: 2, 2019-12-12, 4000, 1
<==        Row: 3, 2019-12-12, 5000, 2
====>  Preparing: select * from user where id = ? 
====> Parameters: 2(Integer)
<====    Columns: id, username
<====        Row: 2, coco
<====      Total: 1
<==      Total: 3
Order{id=1, orderTime='2019-12-12', total=3000.0, uid=null, user=User{id=1, username='张三', orderList=[]}}
Order{id=2, orderTime='2019-12-12', total=4000.0, uid=null, user=User{id=1, username='张三', orderList=[]}}
Order{id=3, orderTime='2019-12-12', total=5000.0, uid=null, user=User{id=2, username='coco', orderList=[]}}

MyBatis底层是先把所有订单查出来，再用订单的uid，使用@One 调用 com.demo.mapper.UserMapper.findById 接口。

一对多

查询需求：查询用户，并同时把用户的所有订单一起查出来

对应的SQL语句：

select * from user; 
select * from orders where uid=查询出用户的id;

原先用mapper.xml写的resultMap与sql语句

<resultMap id="userMap" type="com.demo.pojo.User"><result property="id" column="id"/><result property="username" column="username"/><collection property="orderList" ofType="com.demo.pojo.Order"><result property="id" column="id(1)"/><result property="orderTime" column="order_time"/><result property="total" column="total"/></collection>
</resultMap><select id="findUserOrders" resultMap="userMap">select * from user u left join `order` o on u.id=o.uid
</select>

现在在mapper接口使用注解：

public interface UserMapper {@Results({@Result(property = "id", column = "id"),@Result(property = "username", column = "username"),@Result(property = "orderList", column = "id", javaType = List.class,many = @Many(select = "com.demo.mapper.OrderMapper.findOrderByUserId"))})@Select("select * from user")List<User> findUserOrderList();
}

对应的UserMapper：

public interface UserMapper {/*** 根据uid查订单* @param id 用户id* @return*/@Select("select * from `order` where uid = #{uid}")List<Order> findOrderByUserId(Integer uid);}

这里执行的sql语句如下：

==>  Preparing: select * from user 
==> Parameters: 
<==    Columns: id, username
<==        Row: 1, 张三
====>  Preparing: select * from `order` where uid = ? 
====> Parameters: 1(Integer)
<====    Columns: id, order_time, total, uid
<====        Row: 1, 2019-12-12, 3000, 1
<====        Row: 2, 2019-12-12, 4000, 1
<====      Total: 2
<==        Row: 2, coco
====>  Preparing: select * from `order` where uid = ? 
====> Parameters: 2(Integer)
<====    Columns: id, order_time, total, uid
<====        Row: 3, 2019-12-12, 5000, 2
<====      Total: 1
<==        Row: 3, kk
====>  Preparing: select * from `order` where uid = ? 
====> Parameters: 3(Integer)
<====      Total: 0
<==        Row: 4, ccham
====>  Preparing: select * from `order` where uid = ? 
====> Parameters: 4(Integer)
<====      Total: 0
<==        Row: 5, zz
====>  Preparing: select * from `order` where uid = ? 
====> Parameters: 5(Integer)
<====      Total: 0
<==      Total: 5
User{id=1, username='张三', orderList=[Order{id=1, orderTime='null', total=3000.0, uid=1, user=null}, Order{id=2, orderTime='null', total=4000.0, uid=1, user=null}]}
User{id=2, username='coco', orderList=[Order{id=3, orderTime='null', total=5000.0, uid=2, user=null}]}
User{id=3, username='kk', orderList=[]}
User{id=4, username='ccham', orderList=[]}
User{id=5, username='zz', orderList=[]}

MyBatis底层是先把所有用户查出来，再用用户的id，使用@Many 调用 com.demo.mapper.OrderMapper.findOrderByUserId 接口。

多对多查询

以用户和角色为例，一个用户可以有多个角色，一个角色被授权给多个用户。

多对多查询需求：查询所有的用户，同时查询出该用户的所有角色

对应的sql语句：

select * from user;
select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=用户id;

期望查询结果：

mapper接口使用注解：

public interface UserMapper {@Results({@Result(property = "id", column = "id"),@Result(property = "username", column = "username"),@Result(property = "roleList", column = "id", javaType = List.class,many = @Many(select = "com.demo.mapper.RoleMapper.findRoleByUid")),})@Select("select * from user")List<User> findUserAndRole();
}

对应的RoleMapper：

public interface RoleMapper {/*** 根据用户ID查询拥有的角色* @param uid* @return*/@Select("select * from role r,user_role ur where r.id=ur.role_id and user_id=#{uid}")List<Role> findRoleByUid(Integer uid);
}

这里执行的sql语句如下：

==>  Preparing: select * from user 
==> Parameters: 
<==    Columns: id, username
<==        Row: 1, 张三
====>  Preparing: select * from role r,user_role ur where r.id=ur.role_id and user_id=? 
====> Parameters: 1(Integer)
<====    Columns: id, role_name, user_id, role_id
<====        Row: 1, admin, 1, 1
<====        Row: 2, root, 1, 2
<====      Total: 2
<==        Row: 2, coco
====>  Preparing: select * from role r,user_role ur where r.id=ur.role_id and user_id=? 
====> Parameters: 2(Integer)
<====    Columns: id, role_name, user_id, role_id
<====        Row: 3, CEO, 2, 3
<====        Row: 4, CTO, 2, 4
<====      Total: 2
<==        Row: 3, kk
====>  Preparing: select * from role r,user_role ur where r.id=ur.role_id and user_id=? 
====> Parameters: 3(Integer)
<====      Total: 0
<==        Row: 4, ccham
====>  Preparing: select * from role r,user_role ur where r.id=ur.role_id and user_id=? 
====> Parameters: 4(Integer)
<====    Columns: id, role_name, user_id, role_id
<====        Row: 4, CTO, 4, 4
<====      Total: 1
<==        Row: 5, zz
====>  Preparing: select * from role r,user_role ur where r.id=ur.role_id and user_id=? 
====> Parameters: 5(Integer)
<====      Total: 0
<==      Total: 5
User{id=1, username='张三', orderList=[], roleList=[Role{id=1, roleName='null'}, Role{id=2, roleName='null'}]}
User{id=2, username='coco', orderList=[], roleList=[Role{id=3, roleName='null'}, Role{id=4, roleName='null'}]}
User{id=3, username='kk', orderList=[], roleList=[]}
User{id=4, username='ccham', orderList=[], roleList=[Role{id=4, roleName='null'}]}
User{id=5, username='zz', orderList=[], roleList=[]}

MyBatis底层是先把所有用户查出来，再用用户的id，使用@Many 调用 com.demo.mapper.RoleMapper.findRoleByUid 接口。