文章目录
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);
}
}
}
-
增加
-
更新
-
删除
-
查询单条数据
-
查询全部数据
3. MyBatis的注解实现复杂映射开发
- 实现复杂关系映射之前可以在映射文件中通过配置
<resultMap>来实现，使用注解开发后，可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置
4. 一对一查询
4.1 一对一查询的模型
- 用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
- 一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
4.2 一对一查询的语句
SELECT *,o.id oid
FROM orders o, user u
WHERE o.uid=u.id
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();
}
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);
}
}
}
5. 一对多查询
5.1 一对多查询的模型
- 用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
- 一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
5.2 一对多查询的语句
对应的sql语句：
select * from user;
select * from orders where uid=查询出用户的id;
查询的结果如下：
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);
}
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);
}
}
}
6. 多对多查询
6.1 多对多查询的模型
- 用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
- 多对多查询的需求：查询用户同时查询出该用户的所有角色
6.2 多对多查询的语句
- 对应的sql语句：
select * from user;
select * from sys_user_role ur,sys_role r where ur.roleId=r.id AND ur.userId=用户id
- 查询的结果如下：
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);
}
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);
}
}
}
