文章目录

1. MyBatis的常用注解

  • 这几年来注解开发越来越流行,Mybatis也可以使用注解开发方式,这样我们就可以减少编写Mapper映射文件了。我们先围绕一些基本的CRUD来学习,再学习复杂映射多表操作。
@Insert:实现新增
@Update:实现更新
@Delete:实现删除
@Select:实现查询
@Result:实现结果集封装
@Results:可以与@Result 一起使用,封装多个结果集
@One:实现一对一结果集封装
@Many:实现一对多结果集封装

2. MyBatis的增删改查

完成简单的user表的增删改查的操作

  • 修改MyBatis的核心配置文件,使用了注解替代的映射文件,所以只需要加载使用了注解的Mapper接口即可
<mappers>
<!--扫描使用注解的类-->
<mapper class="com.itheima.mapper.UserMapper">
</mapper>
</mappers>
  • 或者指定扫描包含映射关系的接口所在的包也可以
<!--加载映射关系-->
<mappers>
<!--指定接口所在的包-->
<package name="com.xdr630.mapper"/>
</mappers>
public interface UserMapper {

@Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
public void save(User user);

@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);

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

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

@Select("select * from user")
public List<User> findAll();

}
public class MyBatisTest {

private UserMapper mapper;


@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}


@Test
public void testSave(){
User user = new User();
user.setUsername("tom");
user.setPassword("abc");
mapper.save(user);
}

@Test
public void testUpdate(){
User user = new User();
user.setId(8);
user.setUsername("lucy");
user.setPassword("123");
mapper.update(user);
}

@Test
public void testDelete(){
mapper.delete(18);
}

@Test
public void testFindById(){
User user = mapper.findById(2);
System.out.println(user);
}

@Test
public void testFindAll(){
List<User> all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
}

}
  • 增加
    Mybatis注解开发_一对多

  • 更新
    Mybatis注解开发_一对多_02

  • 删除
    Mybatis注解开发_sql_03

  • 查询单条数据
    Mybatis注解开发_一对一_04

  • 查询全部数据
    Mybatis注解开发_一对多_05

3. MyBatis的注解实现复杂映射开发

  • 实现复杂关系映射之前可以在映射文件中通过配置​​<resultMap>​​​来实现,使用注解开发后,可以使用​​@Results​​​注解,​​@Result​​​注解,​​@One​​​注解,​​@Many​​​注解组合完成复杂关系的配置
    Mybatis注解开发_一对一_06
    Mybatis注解开发_一对多_07

4. 一对一查询

4.1 一对一查询的模型

  • 用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
  • 一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
    Mybatis注解开发_sql_08

4.2 一对一查询的语句

SELECT *,o.id oid 
FROM orders o, user u
WHERE o.uid=u.id

Mybatis注解开发_Mybatis注解开发_09

4.3 创建Order和User实体

public class User {
private int id;
private String username;
private String password;
private Date birthday;
}
public class Order {
private int id;
private Date ordertime;
private double total;
//当前订单属于哪一个用户
private User user;
}
public interface UserMapper {

@Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
public void save(User user);

@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);

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

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

@Select("select * from user")
public List<User> findAll();

}

4.4 使用注解配置Mapper接口

public interface OrderMapper {

@Select("SELECT *,o.id oid FROM orders o, user u WHERE o.uid=u.id")
@Results({
@Result(column = "oid", property = "id"),
@Result(column = "ordertime", property = "ordertime"),
@Result(column = "total", property = "total"),
@Result(column = "uid", property = "user.id"),
@Result(column = "username", property = "user.username"),
@Result(column = "password", property = "user.password"),
})
public List<Order> findAll();

}
  • 也可以这样写:
public interface OrderMapper {

@Select("SELECT * from orders")
@Results({
@Result(column = "id", property = "id"),
@Result(column = "ordertime", property = "ordertime"),
@Result(column = "total", property = "total"),
@Result(
property = "user", //要封装的属性名
column = "uid", //根据哪个字段去查询user表的数据
javaType = User.class, //要封装的实体类型
// select 属性 代表哪个接口方法获得的数据
one = @One(select = "com.xdr630.mapper.UserMapper.findById")
)
})
public List<Order> findAll();

}

Mybatis注解开发_一对多_10

4.5 测试结果

public class MyBatisTest2 {

private OrderMapper mapper;


@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(OrderMapper.class);
}


@Test
public void testSelectOrderAndUser(){
List<Order> all = mapper.findAll();
for (Order order : all) {
System.out.println(order);
}
}
}

Mybatis注解开发_Mybatis注解开发_11

5. 一对多查询

5.1 一对多查询的模型

  • 用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
  • 一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
    Mybatis注解开发_一对多_12

5.2 一对多查询的语句

对应的sql语句:

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

查询的结果如下:

Mybatis注解开发_Mybatis注解开发_13

5.3 修改User实体

public class User {
private int id;
private String username;
private String password;
private Date birthday;

//描述当前用户存在哪些订单
private List<Order> orderList;
}

5.4 使用注解配置Mapper

public interface UserMapper {

@Select("select * from user")
@Results({
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(
property = "orderList",
column = "id",
javaType = List.class,
many = @Many(select = "com.xdr630.mapper.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrderAll();

}
public interface OrderMapper {

@Select("select * from orders where uid=#{uid}")
public List<Order> findByUid(int id);

}

Mybatis注解开发_sql_14

5.5 测试结果

public class MyBatisTest3 {

private UserMapper mapper;


@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}


@Test
public void testfindUserAndOrderAll(){
List<User> userAndOrderAll = mapper.findUserAndOrderAll();
for (User user : userAndOrderAll) {
System.out.println(user);
}
}

}

Mybatis注解开发_一对多_15

6. 多对多查询

6.1 多对多查询的模型

  • 用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
  • 多对多查询的需求:查询用户同时查询出该用户的所有角色
    Mybatis注解开发_一对多_16

6.2 多对多查询的语句

  • 对应的sql语句:
select * from user;
select * from sys_user_role ur,sys_role r where ur.roleId=r.id AND ur.userId=用户id
  • 查询的结果如下:
    Mybatis注解开发_一对多_17

6.3 创建Role实体,修改User实体

public class Role {
private int id;
private String roleName;
private String roleDesc;
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;

//描述的是当前用户具备哪些角色
private List<Role> roleList;
}

6.4 使用注解配置Mapper

public interface UserMapper {

@Select("select * from user")
@Results({
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),

@Result(
property = "roleList",
column = "id",
javaType = List.class,
many = @Many(select = "com.xdr630.mapper.RoleMapper.findByUid")
)

})
public List<User> findUserAndRoleAll();
}
public interface RoleMapper {

@Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id AND ur.userId=#{uid}")
public List<Role> findByUid(int uid);
}

Mybatis注解开发_sql_18

6.5 测试结果

public class MyBatisTest4 {

private UserMapper mapper;

@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}

@Test
public void testfindByUid(){
List<User> userAndRoleAll = mapper.findUserAndRoleAll();
for (User user : userAndRoleAll) {
System.out.println(user);
}
}

}

Mybatis注解开发_Mybatis注解开发_19