一、Mybatis多表查询

1.1 一对一查询

1.2 一对多查询

1.3 多对多查询

1.4 知识小结

二、Mybatis注解开发

2.1 MyBatis的常用注解

2.2 MyBatis的增删改查

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

2.4 一对一查询

2.5 一对多查询

2.6 多对多查询

一、Mybatis多表查询

1.1 一对一查询

1. 一对多查询的模型

2. 一对多查询的语句

3. 修改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;
}

4. 创建OrderMapper接口

public interface OrderMapper {
    List findAll();
}

5. 配置OrderMapper.xml



    
        
        
        
        
      

        
        
            
            
            
            
        

    

    
        SELECT *,o.id oid FROM orders o,USER u WHERe o.uid=u.id

6. 测试

    @Test
    public void test1() throws IOException {
        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获得MyBatis框架生成的UserMapper接口的实现类
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }

        sqlSession.close();
    }

1.2 一对多查询

1. 一对多查询的模型

2. 一对多查询的语句

3. 修改User实体

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List orderList;
}

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //代表当前订单从属于哪一个客户
    private User user;
}

4. 创建UserMapper接口

public interface UserMapper {
    List findAll();
}

5. 配置UserMapper.xml



    
        
        
        
        
        
        
            
            
            
            
        
    

    
        SELECT * FROM USER u,sys_user_role ur,sys_role r WHERe u.id=ur.userId AND ur.roleId=r.id

6. 测试

    @Test
    public void test3() throws IOException {
        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获得MyBatis框架生成的UserMapper接口的实现类
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }
        sqlSession.close();
    }

1.4 知识小结

二、Mybatis注解开发

2.1 MyBatis的常用注解
Mybatis可以使用注解开发方式来减少编写Mapper 映射文件。先围绕一些基本的CRUD来学习，再学习复杂映射多表操作

@Insert：实现新增

@Update：实现更新

@Delete：实现删除

@Select：实现查询

@Result：实现结果集封装

@Results：可以与@Result 一起使用，封装多个结果集

@One：实现一对一结果集封装

@Many：实现一对多结果集封装

2.2 MyBatis的增删改查

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("233");
        mapper.save(user);
    }

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

    @Test
    public void testDelete(){
        User user = new User();
        mapper.delete(7);
    }

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

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

修改MyBatis的核心配置文件，使用了注解替代的映射文件，所以只需要加载使用了注解的Mapper接口即可

或者指定扫描包含映射关系的接口所在的包也可以

2.3 MyBatis的注解实现复杂映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置来实现，使用注解开发后，我们可以使用@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）（）））

2.4 一对一查询

1. 一对一查询的模型

2. 一对一查询的语句

3. 创建Order和User实体

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //代表当前订单从属于哪一个客户
    private User user;
}

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

4. 创建OrderMapper接口

public interface OrderMapper {
    List findAll();
}

5. 使用注解配置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(
                    property = "user", //要封装的属性名称
                    column = "uid", //根据哪个字段去查询user表的数据
                    javaType = User.class, //要封装的实体类型
                    //select属性 代表查询哪个接口的方法来获得数据
                    one = @One(select = "com.learn.mapper.UserMapper.findById")
            )
    })
    public List findAll();


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

}

public interface UserMapper {

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

6. 测试

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 testSave(){
        List all = mapper.findAll();
        for (Order order : all) {
            System.out.println(order);
        }
    }
}

2.5 一对多查询

1. 一对一查询的模型

2. 一对一查询的语句

3. 修改User实体

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //代表当前订单从属于哪一个客户
    private User user;
}

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List orderList;
}

4. 创建UserMapper接口

List findAllUserAndOrder();

5. 使用注解配置Mapper

public interface UserMapper {

    @Select("select * from user")
    @Results({
            @Result(id=true,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.learn.mapper.OrderMapper.findByUid")
            )
    })
    public List findUserAndOrderAll();
}

public interface OrderMapper {

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

}

6. 测试

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 testSave(){
       List userAndOrderAll = mapper.findUserAndOrderAll();
        for (User user : userAndOrderAll) {
            System.out.println(user);
        }
    }
}

2.6 多对多查询

1. 一对一查询的模型

2. 一对一查询的语句

3. 创建Role实体，修改User实体

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List orderList;
    //代表当前用户具备哪些角色
    private List roleList;
}

public class Role {
    private int id;
    private String rolename;
}

4. 添加UserMapper接口方法

List findAllUserAndRole();

5. 使用注解配置Mapper

public interface UserMapper {

    @Select("select * from user")
    @Results({
            @Result(id=true,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.learn.mapper.RoleMapper.findByUid")
            )
    })
    public List 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 findByUid(int uid);

}

6. 测试

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 testSave(){
       List userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }
    }
}

Mybatis (3)—多表操作、注解开发

一、Mybatis多表查询

1.4 知识小结

二、Mybatis注解开发

Java相关栏目本月热门文章