Mybatis复杂映射开发

刺骨的言语ヽ痛彻心扉 2022-10-25 12:52 119阅读 0赞

# Mybatis复杂映射开发 #

## 一对一 ##

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户  
⼀对⼀查询的需求：查询⼀个订单，与此同时查询出该订单所属的⽤户

用户表  
![在这里插入图片描述][20210131190437863.png]

订单表 uid表示用户id  
![在这里插入图片描述][20210131190506849.png]

#### ⼀对⼀查询的语句 ####

对应的sql语句：

> select \* from orders o,user u where o.uid=u.id;

查询结果

![在这里插入图片描述][20210131190609894.png]

实际开发需要将其封装在Java对象里

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

Order

public class Order { 
    
        private Integer id;
        private String orderTime;
        private Double total;
    
        // 表明该订单属于哪个用户
        private User user;
    
        public User getUser() { 
            return user;
        }
    //get set 省略。。

User

public class User implements Serializable { 
    
        private Integer id;
    
        private String username;
    // get set 省略。。。
    }

#### 创建OrderMapper接⼝ ####

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

#### IOrderMapper.xml ####

<mapper namespace="com.lagou.mapper.IOrderMapper">
        
        <resultMap id="orderMap1" type="com.lagou.pojo.Order">
            <result property="id" column="id"></result>
            <result property="orderTime" column="orderTime"></result>
            <result property="total" column="total"></result>
            
            
            <association property="user" javaType="com.lagou.pojo.User">
                <result property="id" column="uid"></result>
                <result property="username" column="orderTime"></result>
            </association>
        </resultMap>
    
        <select id="findOrder2User" resultMap="orderMap1">
            select * from orders o,user u where o.uid = u.id
        </select>
    </mapper>

#### 测试 ####

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

可以看到结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]

## 一对多映射 ##

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

对应的sql语句：

> select \*,o.id oid from user u left join orders o on u.id=o.uid;

可以看到用户id为1 的有两个订单  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 1]

#### User实体 ####

我们用 orderList 来表示接收多个的订单集合即一个用户对应多个订单

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

#### 创建UserMapper接⼝ ####

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

#### UserMapper.xml ####

<mapper namespace="com.mapper.IUserMapper">
    
    
        <resultMap id="userMap2" type="com.lagou.pojo.User">
            <result column="id" property="id"></result>
            <result column="username" property="username"></result>
            
            <collection property="orderList" ofType="com.lagou.pojo.Order">
                <result property="id" column="oid"></result>
                <result property="orderTime" column="ordertime"></result>
                <result property="total" column="total"></result>
            </collection>
        </resultMap>
    
        <select id="findAll2" resultMap="userMap2">
           select u.*,o.id oid,o.ordertime,o.total,o.uid from user u left join orders o on o.uid = u.id
       </select>
    </mapper>

#### 测试 ####

@Test
        public void findAll2() throws IOException { 
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
    
            List<User> users = mapper.findAll2();
            for (User user : users) { 
                System.out.println(user.getUsername());
                System.out.println(user.getOrderList());
                System.out.println("============");
    
            }
        }

可以看到lucy 有两个订单，tom有一个  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 2]

## 多对多 ##

⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤  
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊

用户表  
![在这里插入图片描述][20210131203519129.png]  
用户角色关联表

![在这里插入图片描述][20210131203529702.png]

角色表  
![在这里插入图片描述][20210131203510528.png]

对应的sql语句：

SELECT
    	u.*,
    	r.rolename,
    	r.id as rid 
    FROM
    	USER u
    	LEFT JOIN sys_user_role ur ON u.id = ur.userid
    	INNER JOIN sys_role r ON ur.roleid = r.id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 3]

添加UserMapper接⼝⽅法

public List<User>  manyUser2ManyRole();

xml

<mapper namespace="com.lagou.mapper.IUserMapper">
        <resultMap id="userMap2" type="com.lagou.pojo.User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
    
            <collection property="roleList" ofType="com.lagou.pojo.Role">
    
                <id property="id" column="rid"/>
                <result column="rolename" property="rolename"></result>
            </collection>
        </resultMap>
    
       <select id="manyUser2ManyRole" resultMap="userMap2" >
        SELECT
            u.*,
            r.rolename,
            r.id as rid
        FROM
            USER u
            LEFT JOIN sys_user_role ur ON u.id = ur.userid
            INNER JOIN sys_role r ON ur.roleid = r.id;
       </select>
    </mapper>

#### 测试 ####

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

可以看到结果

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 4]

[20210131190437863.png]: /images/20221024/29295381b87b4f5b8f0a0cd33d98b431.png
[20210131190506849.png]: /images/20221024/2a1d716e67bd4ea78cede966292c588d.png
[20210131190609894.png]: /images/20221024/aa228c0c93cc4bac85fa892e5868b60e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]: /images/20221024/42196e664ed34747a74bf396ab69a241.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 1]: /images/20221024/166903ef970c482297998e4b4fc6df1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 2]: /images/20221024/2d022c40cdd1483ca1a398acaff3108b.png
[20210131203519129.png]: /images/20221024/4c9ba60a153b4e3ba91cededc4f1d764.png
[20210131203529702.png]: /images/20221024/d6b5d2fcbbfe409b8d256271dab96259.png
[20210131203510528.png]: /images/20221024/215bbb27091f4261a35aba0a2b51a655.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 3]: /images/20221024/20c8d4fbea344dd48d5d379ec28f3511.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 4]: /images/20221024/5d2519ea0f654477afa809def95b9360.png