1.1 Mybatis的常用注解
这几年来注解开发越来越流行,Mybatis也可以使用注解开发方式,这样我们就可以减少编写Mapper映射文件了。我们先围绕一些基本的CRUD来学习,再学习复杂映射多表操作。
@Insert:实现新增
@Update:实现更新
@Delete:实现删除
@Select:实现查询
@Result:实现结果集封装
@Results:可以与@Result 一起使用,封装多个结果集
@One:实现一对一结果集封装
@Many:实现一对多结果集封装
1.2 Mybatis基础的CURD
接下来我们完成对User表基础的增删改查操作
在UserMapper接口添加增删改查代码,用注解进行开发操作
@Select("select * from tb_user")
public List<User> findAll();
@Select("select * from tb_user where id = #{id}")
public User findById(int id);
@Update("update tb_user set username = #{username}, password = #{password} where id = #{id}")
public void update(User user);
@Delete("delete from tb_user where id = #{id}")
public void delete(int id);
@Insert("insert into tb_user (username,password,birthday)values(#{username},#{password},#{birthday})")
public void insert(User user);
添加测试代码
public class TestCRUD {
private UserMapper mapper;
@Before
public void preTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void test01_findAll(){
List<User> userList = mapper.findAll();
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void test02_findById(){
User user = mapper.findById(3);
System.out.println(user);
}
@Test
public void test03_update(){
User user = new User();
user.setUsername("lisi");
user.setPassword("1234");
user.setId(80);
user.setBirthday(new Date());
mapper.update(user);
}
@Test
public void test04_delete(){
mapper.delete(78);
}
@Test
public void test05_insert(){
User user = new User();
user.setUsername("hahaha");
user.setPassword("12345");
mapper.insert(user);
}
}
我们简单看一下查询全部的运行结果
1.3 Mybatis使用注解实现复杂的映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置来实现,使用注解开发后,我们可以使用@Results注解,@Result注解,@One注解,@Many注解组合完成复杂关系的配置
1.4 一对一查询
使用注解开发前面几个步骤跟上一篇博客大同小异,接下来我们只演示与xml配置文件有差异的地方。
-
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
-
使用注解开发,首先我们要得到订单表中的uid,然后用uid去寻找user表中对应的用户信息。
select * from orders;
select * from tb_user where id=查询出订单的uid
-
创建User实体和Order实体
-
在OrderMapper中定义接口
-
使用注解配置
@Select("select *, o.id oid from orders o,tb_user u where o.uid = u.id")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "total",property = "total"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "uid",property = "users.id"),
@Result(column = "username",property = "users.username"),
@Result(column = "password",property = "users.password"),
@Result(column = "brithday",property = "users.brithday")
})
public List<Order> queryAll();
使用注解我们还有另一种方法
@Select("select *from orders")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "total",property = "total"),
@Result(column = "ordertime",property = "ordertime"),
@Result(
column = "uid",//要根据哪个字段去查询user表的数据
property = "users",//封装的属性名称
javaType = User.class,//要封装的实体类型
one = @One(select = "ex1.mapper.UserMapper.findById")
)
})
public List<Order> queryAll();
这里需要注意,我们是利用查询order表获得的uid去寻找user表中的用户,因此,这里需要调用UserMapper中的findById方法来返回User对象。
@Select("select * from tb_user where id = #{id}")
public User findById(int id);
- 添加测试
public class TestDemo2 {
private OrderMapper mapper;
@Before
public void preTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(OrderMapper.class);
}
@Test
public void test01_one_one(){
List<Order> orderList = mapper.queryAll();
for (Order order : orderList) {
System.out.println(order);
}
}
}
1.5 一对多查询
- 需求:查询用户对应的所有订单
- 查询语句
select * from tb_user
select * from order where uid = “用户表查询出的id”
- 在User实体中添加List<Order> orderList 属性
- 在UserMapper中定义相关接口
- 使用注解代替xml进行开发
@Select("select * from tb_user")
@Results({
@Result(id = true,column = "uid",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(column = "birthday",property = "birthday"),
@Result(
column = "id",//根据User的id属性去寻找orders当中的订单
property = "orderList",//实体中的成员变量
javaType = List.class,
many = @Many(select = "ex1.mapper.OrderMapper.findById")
)
})
public List<User> queryAllOrders();
这里说明一下,column中的id是从user表中查出的id,根据id去寻找order表中对应的uid
这里需要调用OrderMapper中的findById方法。
@Select("select * from orders where id = #{id}")
public Order findById(int id)
- 添加测试
private UserMapper mapper;
@Before
public void preTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void test_queryAllOrders(){
List<User> userList = mapper.queryAllOrders();
for (User user : userList) {
System.out.println(user);
}
}
1.6多对多查询
- 需求分析:查询用户对应的所有角色信息
- 查询语句
select * from tb_user
select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=用户的id
- 在User实体中添加List<Role> roleList成员变量
- 在UserMapper中添加findRoles接口
- 配置注解
@Select("select * from tb_user")
@Results({
@Result(id = true,column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(column = "birthday",property = "birthday"),
@Result(
column = "id",//根据User的id属性去寻找orders当中的订单
property = "orderList",//实体中的成员变量
javaType = List.class,
many = @Many(select = "ex1.mapper.OrderMapper.findById")
)
})
public List<User> queryAllOrders();
注意,这里需要调用RoleMapper接口中的findById方法,通过将tb_user表查询到的id传给RoleMapper接口中的findById方法,然后返回Role对象。
@Select("SELECT * from sys_user_role ur , sys_role r where ur.roleId = r.id and ur.userId = #{uid}")
public Role findByUid(int uid);
- 添加测试
private UserMapper mapper;
@Before
public void preTest() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void test_many_many(){
List<User> userList = mapper.queryAllRoles();
for (User user : userList) {
System.out.println(user);
}
}
ok,至此,通过注解进行简单的单表CURD和多表查询已经演示完毕。