Mybatis实现多表查询(一对一、一对多、多对多)

迷南。 2022-10-09 12:20 352阅读 0赞

### 文章目录 ###

*   *  一、一对一查询
     *   *  1.1 模型
         *  1.2 SQL语句
         *  1.3 创建Order和User实体类
         *  1.4 创建OrderMapper接口
         *  1.5 配置OrderMapper.xml
         *  1.6 测试
     *  二、一对多查询
     *   *  2.1 模型
         *  2.2 SQL语句
         *  2.3 修改User和Order实体类
         *  2.4 创建UserMapper接口
         *  2.5 创建UserMapper.xml
         *  2.6 测试
     *  三、多对多查询
     *   *  3.2 SQL语句
         *  3.3 修改User类，创建Role类
         *  3.4 UserMapper接口添加方法
         *  3.5 配置UserMapper.xml
         *  3.6 测试
     *  四、小结

## 一、一对一查询 ##

### 1.1 模型 ###

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70]

### 1.2 SQL语句 ###

对应的sql语句：`select * from orders o,user u where o.uid=u.id`

查询结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 1]

### 1.3 创建Order和User实体类 ###

`Order.java`

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

`User.java`

public class User { 
       private int id;    
       private String username;    
       private String password;    
       private Date birthday;
       // 省略get set方法
    }

### 1.4 创建OrderMapper接口 ###

public interface OrderMapper {     
    	List<Order> findAll();
    }

### 1.5 配置OrderMapper.xml ###

通过`sql`查询并封装数据，此处返回类型使用的`resultMap`，在这里完成数据的封装。

<mapper namespace="com.happy.mapper.OrderMapper">
        <resultMap id="orderMap" type="order">
            
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
            <result column="uid" property="user.id"></result>
            <result column="username" property="user.username"></result>
            <result column="password" property="user.password"></result>
            <result column="birthday" property="user.birthday"></result>
        </resultMap>   
        <select id="findAll" resultMap="orderMap">        
        	select * from orders o,user u where o.uid=u.id    
        </select>
    </mapper>

当然，除了改方式外，还可通过`association`完成对象封装，如下：

### 1.6 测试 ###

执行测试代码，如下：

@Test
    public void test1() throws IOException { 
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
    
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) { 
            System.out.println(order);
        }
    
        sqlSession.close();
    }

输出结果为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 2]

## 二、一对多查询 ##

### 2.1 模型 ###

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对多查询的需求：`查询一个用户，与此同时查询出该用户具有的订单`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 3]

### 2.2 SQL语句 ###

对应的sql语句：`select *,o.id oid from user u left join orders o on u.id=o.uid;`

查询的结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 4]

### 2.3 修改User和Order实体类 ###

`Order.java`

`User.java`

public class User {         
    	private int id;    
    	private String username;    
    	private String password;    
    	private Date birthday;
        //代表当前用户具备哪些订单 
        private List<Order> orderList;
        // 省略get set方法
    }

### 2.4 创建UserMapper接口 ###

public interface UserMapper {     
    	List<User> findAll();
    }

### 2.5 创建UserMapper.xml ###

<resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        
        <collection property="orderList" ofType="order">
            
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
    
    <select id="findAll" resultMap="userMap">
        SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
    </select>

### 2.6 测试 ###

执行如下测试代码：

@Test
    public void test2() throws IOException { 
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
    
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> all = mapper.findAll();
        for(User user : all){     
        	System.out.println(user.getUsername());    
        	List<Order> orderList = user.getOrderList();    
        	for(Order order : orderList){         
        		System.out.println(order);    
        	}    
        	System.out.println("----------------------------------");
        }
        sqlSession.close();
    }

输出结果为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 5]

## 三、多对多查询 ##

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

多对多查询的需求：`查询用户同时查询出该用户的所有角色`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 6]

### 3.2 SQL语句 ###

对应的sql语句：`select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id;`

查询的结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 7]

### 3.3 修改User类，创建Role类 ###

public class Role {     
    	private int id;    
    	private String rolename;
    	// 省略get set方法
    }

public class User {     
    	private int id;    
    	private String username;    
    	private String password;    
    	private Date birthday;
        //代表当前用户具备哪些订单 
        private List<Order> orderList;
        //代表当前用户具备哪些角色 
        private List<Role> roleList;
        // 省略get set方法
    }

### 3.4 UserMapper接口添加方法 ###

添加查询方法，如下：

List<User> findAllUserAndRole();

### 3.5 配置UserMapper.xml ###

<resultMap id="userRoleMap" type="user">
            
            <id column="userId" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
            
            <collection property="roleList" ofType="role">
                <id column="roleId" property="id"></id>
                <result column="roleName" property="roleName"></result>
                <result column="roleDesc" property="roleDesc"></result>
            </collection>
        </resultMap>
    
        <select id="findUserAndRoleAll" resultMap="userRoleMap">
            SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
        </select>

### 3.6 测试 ###

执行如下测试代码：

@Test
    public void test3() throws IOException { 
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
    
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> all = mapper.findAllUserAndRole();
        for(User user : all){     
        	System.out.println(user.getUsername());    
        	List<Role> roleList = user.getRoleList();    
        	for(Role role : roleList){         
        		System.out.println(role);    
        	}    
        System.out.println("----------------------------------");}
        sqlSession.close();
    }

输出结果为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 8]

## 四、小结 ##

`Mybatis`多表配置方式：

*  一对一，使用`<resultMap>`做配置
 *  一对多，使用`<resultMap>`和`<collection>`做配置
 *  多对多，使用`<resultMap>`和`<collection>`做配置

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70]: /images/20221005/410d36a2158547bda89bb8e07cbbda5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 1]: /images/20221005/118713b9b9a14ac5af62eff35b809bf3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 2]: /images/20221005/5358e30ea6c841c9a73647ea409e0251.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 3]: /images/20221005/83d9f11ba36a4f76b4f7b61962a5517d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 4]: /images/20221005/3f03d63c377741bea4965544b3b2ffb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 5]: /images/20221005/bf741480401c442dae2068d3f3cb2c41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 6]: /images/20221005/91a3118b5c3c48c59a1004de2418da7d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 7]: /images/20221005/4cc56ab918c44eb893c3b3243c9cf745.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAzNTgxNjg_size_16_color_FFFFFF_t_70 8]: /images/20221005/d69c32f9f8a04e3bab85863369cebe9b.png