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; @Before public void before() throws IOException { // 1.Resources工具类,配置文件的加载,把配置文件加载成字节输入流 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); // 2.解析了配置文件,并创建了sqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // 3.生成sqlSession (默认开启一个事务,但是该事务不会自动提交, 参数为true为自动提交) SqlSession sqlSession = sqlSessionFactory.openSession(true); userMapper = sqlSession.getMapper(UserMapper.class); } @Test public void testSelect(){ List<User> users = userMapper.selectAll(); for (User user : users) { System.out.println(user); } } @Test public void testInsert(){ User user = new User(); user.setId(6); user.setUsername("测试数据"); userMapper.addUser(user); testSelect(); } @Test public void testUpdate(){ User user = new User(); user.setId(6); user.setUsername("修改测试数据"); userMapper.updateUser(user); testSelect(); } @Test public 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
接口。