一、MyBatis的常⽤注解
Mybatis也可以使⽤注解开发⽅式，这样我们就可以减少编写Mapper映射⽂件了，我们先围绕⼀些基本的CRUD来学习，之后再编写复杂的映射
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result ⼀起使⽤，封装多个结果集
@One：实现⼀对⼀结果集封装
@Many：实现⼀对多结果集封装
二、MyBatis的增删改查
User 实体类
package study.lagou.com.pojo;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:13
*/
public class User {
/**
* 用户ID
*/
private Integer id;
/**
* 用户名称
*/
private String username;
/**
* 用户密码
*/
private String password;
/**
* 出生日期
*/
private String birthday;
/**
* 用户拥有的订单信息
*/
private List<Orders> orderList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday='" + birthday + '\'' +
", orderList=" + orderList +
'}';
}
}
用户增删改查接口IUserMapper
package study.lagou.com.mapper;
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 study.lagou.com.pojo.User;
import java.util.List;
public interface IUserMapper {
/**
* 添加用户
* @param user
*/
@Insert("insert into user values (#{id},#{username},#{password},#{birthday})")
public void addUser(User user);
/**
* 修改用户
* @param user
*/
@Update("update user set username = #{username},password = #{password},birthday = #{birthday} where id = #{id}")
public void updateUser(User user);
/**
* 查询用户
*/
@Select("select * from user")
public List<User> listUser();
/**
* 删除用户
* @param id
*/
@Delete("delete from user where id = #{id}")
public void deleteUser(Integer id);
}
修改MyBatis的核⼼配置⽂件，使⽤了注解替代的映射⽂件，担是仍然需要配置SQL文件扫描的位置，需要加载使⽤了注解的Mapper接⼝
<mappers>
<mapper class="study.lagou.com.mapper.IUserMapper"/>
</mappers>
或者基于包扫描进行配置
<mappers>
<package name="study.lagou.com.mapper"/>
</mappers>
测试类MyBatisMulitiTableAnnotationTest
package test;
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 study.lagou.com.mapper.IUserMapper;
import study.lagou.com.pojo.Orders;
import study.lagou.com.pojo.User;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:51
*/
public class MyBatisMulitiTableAnnotationTest {
private IUserMapper userMapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
userMapper = sqlSession.getMapper(IUserMapper.class);
}
@Test
public void testAddUser() throws IOException {
User user = new User();
user.setId(5);
user.setUsername("测试用户");
user.setPassword("123");
user.setBirthday("1987-09-09");
userMapper.addUser(user);
}
@Test
public void testUpdateUser(){
User user = new User();
user.setId(5);
user.setUsername("数据信息");
user.setPassword("111");
user.setBirthday("1991-09-09");
userMapper.updateUser(user);
}
@Test
public void testListUser(){
List<User> users = userMapper.listUser();
users.forEach(user -> {
System.out.println(user);
});
}
@Test
public void testDeleteUser(){
userMapper.deleteUser(5);
}
}
三、MyBatis的注解实现复杂映射开发
实现复杂关系映射之前我们可以在映射⽂件中通过配置来实现，使⽤注解开发后，我们可以使⽤@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置
image.png
四、注解一对一查询
以⽤户表和订单表的关系为例进行查询，⼀个⽤户有多个订单，⼀个订单只属于⼀个⽤户
查询需求：查询⼀个订单，与此同时查询出该订单所属的⽤户
image.png
创建订单实体
package study.lagou.com.pojo;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:15
*/
public class Orders {
/**
* 订单ID
*/
private Integer id;
/**
* 下单时间
*/
private String ordertime;
/**
* 订单金额
*/
private Double total;
/**
*订单所属用户信息
*/
private User user;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrdertime() {
return ordertime;
}
public void setOrdertime(String ordertime) {
this.ordertime = ordertime;
}
public Double getTotal() {
return total;
}
public void setTotal(Double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordertime='" + ordertime + '\'' +
", total=" + total +
", user=" + user +
'}';
}
}
基于注解编写IOrderMapper接口
package study.lagou.com.mapper;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import study.lagou.com.pojo.Orders;
import study.lagou.com.pojo.User;
import java.util.List;
public interface IOrderMapper {
@Select("select * from orders")
@Results({
@Result(id = true,property = "id", column = "id"),
@Result(property = "ordertime", column = "ordertime"),
@Result(property = "total", column = "total"),
@Result(property = "user", column = "uid",javaType = User.class
,one = @One(select = "study.lagou.com.mapper.IUserMapper.selectUserById"))
})
List<Orders> listAll();
}
IUserMapper接口当中增加selectUserById方法，上述代码中的one = @One(select = "study.lagou.com.mapper.IUserMapper.selectUserById"代码段会调用到此方法
/**
* 通过id查询用户信息
* @param id
* @return
*/
@Select("select * from user where id = #{id}")
public User selectUserById(String id);
编写测试类，查看测试结果
package test;
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 study.lagou.com.mapper.IOrderMapper;
import study.lagou.com.pojo.Orders;
import study.lagou.com.pojo.User;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:51
*/
public class OrderTest {
private IOrderMapper orderMapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
orderMapper = sqlSession.getMapper(IOrderMapper.class);
}
@Test
public void testList(){
List<Orders> orders = orderMapper.listAll();
orders.forEach(order -> System.out.println(order));
}
}
运行输出结果：
Orders{id=1, ordertime='2019-12-12', total=3000.0, user=User{id=1, username='lucy', password='123', birthday='2019-12-12', orderList=null}}
Orders{id=2, ordertime='2019-12-12', total=4000.0, user=User{id=1, username='lucy', password='123', birthday='2019-12-12', orderList=null}}
Orders{id=3, ordertime='2019-12-12', total=5000.0, user=User{id=2, username='tom', password='123', birthday='2019-12-12', orderList=null}}
Disconnected from the target VM, address: '127.0.0.1:53416', transport: 'socket'
五、 注解⼀对多查询
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对多查询的需求：查询⼀个⽤户，与此同时查询出该⽤户具有的订单
image.png
调整IUserMapper，在IUserMapper接口中添加findUserAndOrder方法，一个用户对应多个订单，映射关系通过@Result(property = "orderList",column = "id",javaType = List.class,many = @Many(select = "study.lagou.com.mapper.IOrderMapper.listOrderByUserId"))，注意此处的column="id",此处的Id是用户的ID
/**
* 查询用户并返回用户的订单信息
* @return
*/
@Select("select * from user")
@Results({
@Result(property = "id",column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "orderList",column = "id",
javaType = List.class,
many = @Many(select = "study.lagou.com.mapper.IOrderMapper.listOrderByUserId"))
})
public List<User> findUserAndOrder();
在IOrderMapper中添加listOrderByUserId通过用户ID查询订单方法
/**
* 根据用户ID查询订单信息
* @param userId
* @return
*/
@Select("select * from orders where uid = #{userId}")
List<Orders> listOrderByUserId(String userId);
一对多测试方法，通过OrderTest中testOneToMany进行测试
package test;
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 study.lagou.com.mapper.IOrderMapper;
import study.lagou.com.mapper.IUserMapper;
import study.lagou.com.pojo.Orders;
import study.lagou.com.pojo.User;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:51
*/
public class OrderTest {
private IOrderMapper orderMapper;
private IUserMapper userMapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
orderMapper = sqlSession.getMapper(IOrderMapper.class);
userMapper = sqlSession.getMapper(IUserMapper.class);
}
@Test
public void testOneToOne(){
List<Orders> orders = orderMapper.listAll();
orders.forEach(order -> System.out.println(order));
}
@Test
public void testOneToMany(){
List<User> users = userMapper.findUserAndOrder();
users.forEach(user -> System.out.println(user));
}
}
测试运行结果
User{id=1, username='lucy', password='123', birthday='2019-12-12', orderList=[Orders{id=1, ordertime='2019-12-12', total=3000.0, user=null}, Orders{id=2, ordertime='2019-12-12', total=4000.0, user=null}]}
User{id=2, username='tom', password='123', birthday='2019-12-12', orderList=[Orders{id=3, ordertime='2019-12-12', total=5000.0, user=null}]}
User{id=5, username='测试用户', password='123', birthday='1987-09-09', orderList=[]}
六、注解多对多查询
1、多对多查询的模型
⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊
image.png
2、创建Role实体，修改User实体，增加roleList属性
Role 实体类
package study.lagou.com.pojo;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-26 0:54
*/
public class Role {
/**
* 角色ID
*/
private Integer id;
/**
* 角色名称
*/
private String rolename;
/**
* 角色描述信息
*/
private String roleDesc;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
User实体
package study.lagou.com.pojo;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:13
*/
public class User {
/**
* 用户ID
*/
private Integer id;
/**
* 用户名称
*/
private String username;
/**
* 用户密码
*/
private String password;
/**
* 出生日期
*/
private String birthday;
/**
* 用户拥有的订单信息
*/
private List<Orders> orderList;
/**
* 用户菜单列表
*/
private List<Role> roleList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday='" + birthday + '\'' +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
}
3、创建IRoleMapper接口，添加getRoleByUserId方法，实现通过用户ID查询出角色信息
package study.lagou.com.mapper;
import org.apache.ibatis.annotations.Select;
import study.lagou.com.pojo.Role;
import java.util.List;
public interface IRoleMapper {
/**
* 通过用户ID查询角色信息
* @param userId
* @return
*/
@Select("select b.* from sys_user_role a,sys_role b where a.roleid = b.id and a.userid = #{userId}")
public List<Role> listRoleByUserId(String userId);
}
4、在IUserMapper中添加查询用户角色方法findUserAndRole
/**
* 查询用户和用户角色信息
* @return
*/
@Select("select * from user")
@Results({
@Result(property = "id",column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "roleList",column = "id",
javaType = List.class,
many = @Many(select = "study.lagou.com.mapper.IRoleMapper.listRoleByUserId"))
})
public List<User> findUserAndRole();
6、在OrderTest中编写测试方法
@Test
public void testManyToMany(){
List<User> users = userMapper.findUserAndRole();
users.forEach(user -> System.out.println(user));
}
测试结果
User{id=1, username='lucy', password='123', birthday='2019-12-12', orderList=null, roleList=[Role{id=1, rolename='CTO', roleDesc='CTO'}, Role{id=2, rolename='CEO', roleDesc='CEO'}]}
User{id=2, username='tom', password='123', birthday='2019-12-12', orderList=null, roleList=[Role{id=1, rolename='CTO', roleDesc='CTO'}, Role{id=2, rolename='CEO', roleDesc='CEO'}]}
User{id=5, username='测试用户', password='123', birthday='1987-09-09', orderList=null, roleList=[]}
