MyBatis多表关联查询

客官°小女子只卖身不卖艺 2022-12-25 00:58 157阅读 0赞

# 1、多表关联查询关系分析 #

多表关联：至少两个表关联。分析多表关系的技巧：从一条记录出发，不要从表整体去分析，比如分析A表和B表的关系，A表中的一条记录对应B表中的几条记录，如果是一条，那么A到B就是一对一；如果多条，那么A到B就是一对多

**一对一**，从订单出发，到用户表，是一对一的关系

**一对多**，从用户表出发，到订单表，一条用户数据可以在订单表中存在多条记录，这就是一对多。通过主外键来体现一对多结构。一的一方是主表，多的一方是从表，从表当中有一个字段是外键，指向类主表的主键。

**多对多**，用户和角色表，一个用户可以有很多角色，一个角色有很多用户，多对多通过中间表来体现

# 2 、表关联的SQL语句 #

关联查询:

●内连接 inner join on  
  ○SELECT \* FROM USER u inner join ORDERS o on u.id=o.uid;

●左外连接 left join on

●右外连接 right join on

●交叉连接(笛卡尔积)  
  ○select \* from 表名1，表名2;

# 3、一对一查询 #

需求 ：查询订单表全部数据，关联查询出订单对应的用户数据

SQL语句自己编写，MyBatis只是帮我们执行SQL语句同时封装结果集。

## 3.1、SQL语句 ##

select s.id,s.user_id,s.username,s.number,s.price,u.id,u.name from shop s join User u on s.user_id=u.id;

对于查询结果集，没有对应的POJO对象，因此MyBatis框架也不能封装结果集，但是可以将User对象作为一个成员属性放在Orders类中，因为一对一关系，一个Orders对象可以对应一个User对象，然后再通过MyBatis框架提供的手动映射方式，可以实现将查询结果分别封装到对应的对象中。

## 3.2、POJO类 ##

package com.bdit.pojo;
    
    import java.io.Serializable;
    
    public class Shop implements Serializable {
        
    
        private static final long serialVersionUID = 2550483658181847727L;
        private int id;
        private int user_id;
        private String username;
        private double number;
        private int price;
    
        //添加一个对User对象的引用
        private User user;
    
        public Shop(){
        }
    
        public int getId() {
        
            return id;
        }
    
        public void setId(int id) {
        
            this.id = id;
        }
    
        public int getUser_id() {
        
            return user_id;
        }
    
        public void setUser_id(int user_id) {
        
            this.user_id = user_id;
        }
    
        public String getUsername() {
        
            return username;
        }
    
        public void setUsername(String username) {
        
            this.username = username;
        }
    
        public double getNumber() {
        
            return number;
        }
    
        public void setNumber(double number) {
        
            this.number = number;
        }
    
        public int getPrice() {
        
            return price;
        }
    
        public void setPrice(int price) {
        
            this.price = price;
        }
    
        public User getUser() {
        
            return user;
        }
    
        public void setUser(User user) {
        
            this.user = user;
        }
    
        @Override
        public String toString() {
        
            return "Shop{" +
                    "id=" + id +
                    ", user_id=" + user_id +
                    ", username='" + username + '\'' +
                    ", number=" + number +
                    ", price=" + price +
                    ", user=" + user +
                    '}';
        }
    }

## 3.3、ShopMapper.xml ##

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.bdit.dao.IShopMapper">
    
        
        <select id="findShopUser" resultMap="map" >
            select s.id,s.user_id,s.username,s.number,s.price,u.id,u.name from shop s join User u on s.user_id=u.id;
        </select>
    
        <resultMap id="map" type="shop">
            <id column="id" property="id"/>
            <result column="user_id" property="user_id"/>
            <result column="username" property="username"/>
            <result column="number" property="number"/>
            <result column="price" property="price"/>
            
            <association property="user" javaType="user">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
            </association>
    
        </resultMap>
    </mapper>

@Test
        public void findShopUser(){
        
            IShopMapper iShopMapper=sqlSession.getMapper(IShopMapper.class);
            List<Shop> list=iShopMapper.findShopUser();
            for(Shop l:list){
        
                System.out.println("商品信息--->"+l);
                System.out.println("用户名称--->"+l.getUser().getName());
            }
        }

# 4、一对多查询 #

## 4.1、SQL语句 ##

select u.id as userid ,u.name,u.keyword,u.test,s.id,s.user_id,s.username,s.number,s.price from shop s join user u on s.user_id=u.id

一个用户对应多个订单数据，因此在User类中，添加一个对订单对象集合的引用。

## 4.2、POJO类 ##

package com.bdit.pojo;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class User implements Serializable {
        
    
        private static final long serialVersionUID = -4891296867479104581L;
        private Integer id;
        private String name;
        private String keyword;
        private String test;
    
        /**
         * 添加一个对订单对象集合的引用
         * */
        private List<Shop> shopList;
    
    
        public User(){
        }
    
        public Integer getId() {
        
            return id;
        }
    
        public void setId(Integer id) {
        
            this.id = id;
        }
    
        public String getName() {
        
            return name;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    
        public String getKeyword() {
        
            return keyword;
        }
    
        public void setKeyword(String keyword) {
        
            this.keyword = keyword;
        }
    
        public String getTest() {
        
            return test;
        }
    
        public void setTest(String test) {
        
            this.test = test;
        }
    
        public List<Shop> getShopList() {
        
            return shopList;
        }
    
        public void setShopList(List<Shop> shopList) {
        
            this.shopList = shopList;
        }
    
        @Override
        public String toString() {
        
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", keyword='" + keyword + '\'' +
                    ", test='" + test + '\'' +
                    ", shopList=" + shopList +
                    '}';
        }
    }

## 4.3、UserMapper.xml ##

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.bdit.dao.IUserMapper">
    
        
        <select id="findUserShop" resultMap="map">
            select u.id as userid ,u.name,u.keyword,u.test,s.id,s.user_id,s.username,s.number,s.price from shop s join user u on s.user_id=u.id
        </select>
        
        <resultMap id="map" type="User">
            <id column="userid" property="id"/>
            <result column="name" property="name"/>
            <result column="keyword" property="keyword"/>
            <result column="test" property="test"/>
    
            
            <collection property="shopList" ofType="shop">
                <id column="id" property="id"/>
                <result column="user_id" property="user_id"/>
                <result column="username" property="username"/>
                <result column="number" property="number"/>
                <result column="price" property="price"/>
            </collection>
        </resultMap>
    
    </mapper>

【如果在进行关联查询时，列名如果有相同的，有可能会出现映射时数据不匹配的问题，所以建议任何表的主键都不要直接定义为id】

@Test
        public void findUserShop(){
        
            IUserMapper iUserMapper=sqlSession.getMapper(IUserMapper.class);
            List<User> list=iUserMapper.findUserShop();
            for (User u:list){
        
                System.out.println("用户："+u);
                for(Shop s:u.getShopList()){
        
                    System.out.println("商品："+s);
                }
            }
        }

# 5、多对多查询 #

多对多：要提供一个中间表，A表和B表都和中间表建立一定的关联关系，多对多其实就是一个双向的一对多，从A表到中间表是一对多的关系，从B表到中间表也是一对的多的关系

## 5.1、SQL语句 ##

需求：查询所有的角色，并关联查询出拥有该角色的所有用户信息

select r.role_id,r.name,r.pass,r.depart,u.id,u.name as username,u.keyword,u.test  from role r join user_role ur on r.role_id=ur.role_id join user u on ur.user_id=u.id

## 5.2、POJO类 ##

package com.bdit.pojo;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Role implements Serializable {
        
        private static final long serialVersionUID = 7085601444722275361L;
        private Integer role_id;
        private String name;
        private String pass;
        private String depart;
    
        //添加对User对象集合的引用
        private  List<User> listUser;
    
        public Role(){
        };
    
        public Integer getRole_id() {
        
            return role_id;
        }
    
        public void setRole_id(Integer role_id) {
        
            this.role_id = role_id;
        }
    
        public String getName() {
        
            return name;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    
        public String getPass() {
        
            return pass;
        }
    
        public void setPass(String pass) {
        
            this.pass = pass;
        }
    
        public String getDepart() {
        
            return depart;
        }
    
        public void setDepart(String depart) {
        
            this.depart = depart;
        }
    
        public List<User> getListUser() {
        
            return listUser;
        }
    
        public void setListUser(List<User> listUser) {
        
            this.listUser = listUser;
        }
    
        @Override
        public String toString() {
        
            return "Role{" +
                    "role_id=" + role_id +
                    ", name='" + name + '\'' +
                    ", pass='" + pass + '\'' +
                    ", depart='" + depart + '\'' +
                    ", listUser=" + listUser +
                    '}';
        }
    }

## 5.3、RoleMapper.xml ##

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.bdit.dao.IRoleMapper">
    
        
        <select id="findUserRole" resultMap="map">
            select r.role_id,r.name,r.pass,r.depart,u.id,u.name as username,u.keyword,u.test  from role r join user_role ur on r.role_id=ur.role_id join user u on ur.user_id=u.id
        </select>
    
        <resultMap id="map" type="role">
            <id column="role_id" property="role_id"/>
            <result column="name" property="name"/>
            <result column="pass" property="pass"/>
            <result column="depart" property="depart"/>
    
            <collection property="listUser" ofType="user">
                <id column="id" property="id"/>
                <result column="username" property="name"/>
                <result column="keyword" property="keyword"/>
                <result column="test" property="test"/>
            </collection>
        </resultMap>
    </mapper>

package com.bdit;
    
    import com.bdit.dao.IRoleMapper;
    import com.bdit.pojo.Role;
    import com.bdit.pojo.User;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class RoleMyBatiesTesst {
        
    
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        @Before
        public void init() throws IOException {
        
            InputStream inputStream= Resources.getResourceAsStream("mybaties-config.xml");
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
            sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
            sqlSession=sqlSessionFactory.openSession();
        }
    
        @After
        public void desory(){
        
            sqlSession.commit();
            sqlSession.close();
        }
    
    
        @Test
        public void findUserRole(){
        
            IRoleMapper iRoleMapper=sqlSession.getMapper(IRoleMapper.class);
            List<Role> list=iRoleMapper.findUserRole();
            for(Role r:list){
        
                System.out.println("role: "+r);
                for(User u:r.getListUser()){
        
                    System.out.println("user: "+u);
                }
            }
        }
    
    
    }