MyBatis详解(5)--MyBatis 高级查询

需求缘起

之前几节的示例,都是单表操作,但实际项目中,还有很多时候会用到多表查询,主要体现在一对一,一对多和多对多查询。这一节就来介绍下多表查询的用法。
本节将在上一节的基础上开发。

本小节代码
mybatis-demo-article(5)

1.一对一查询

1.1 创建一个订单表

# 创建表
CREATE TABLE orders
(
    id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id bigint(20) NOT NULL,
    number int(11) DEFAULT '1' NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';

# 向表中添加数据:
insert into `orders`(user_id,number) values
  (2,'2'),
  (4,'4'),
  (2,'3');

1.2 创建订单实体类

@Data
public class Orders {
    //订单ID
    private int id;
    //用户ID
    private int userId;
    //订单数量
    private String number;
    //和用户表构成一对一的关系,即一个订单只能由一个用户创建
    private UserInfo user;
}

1.3 编写接口 OrdersMapper.java

package com.mybatis.demo.mapper;

import com.mybatis.demo.model.Orders;
import com.mybatis.demo.model.UserInfo;

/**
 * @auther kklu
 * @date 2019/10/15 15:44
 * @describe
 */

public interface OrdersMapper {

    /**
     * 方式一:嵌套结果
     * select * from orders o,user u where o.user_id=u.id and o.id=#{id}
     *
     * @param orderId
     * @return
     */
    //根据订单ID查询订单和用户信息
    Orders selectOrdersAndUserInfoByOrderID(int orderId);

    /**
     * 方式二:嵌套查询
     * select * from order WHERE id=1;//得到user_id
     * select * from user WHERE id=1   //1 是上一个查询得到的user_id的值
     *
     * @param orderId
     * @return
     */
    //根据订单ID得到订单信息(包含user_id)
    Orders getOrdersByOrderId(int orderId);

    //根据用户ID查询用户信息
    UserInfo getUserByUserId(int userID);
}

1.3 编写 OrdersMapper.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.mybatis.demo.mapper.OrdersMapper">
    <!--
    嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
                               封装联表查询的数据(去除重复的数据)
     select * from order o,user_inf u where o.user_id=u.id and o.id=#{id}
     -->
    <select id="selectOrderAndUserInfoByOrderID" resultMap="getOrderAndUserInfo">
        select * from order o,user_info u where o.user_id=u.id and o.id=#{id}
    </select>

    <resultMap type="com.mybatis.demo.model.Order" id="getOrderAndUserInfo">
        <!--
            id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
            column:数据库对应的列
            property:实体类对应的属性名
          -->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <!--association:用于映射关联查询单个对象的信息
            property:实体类对应的属性名
            javaType:实体类对应的全类名
          -->
        <association property="userInfo" javaType="com.mybatis.demo.model.UserInfo">
            <!--
                id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
                column:数据库对应的列
                property:实体类对应的属性名
              -->
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="high" property="high"/>
            <result column="age" property="age"/>
            <result column="sex" property="sex"/>
        </association>
    </resultMap>


    <!--
         方式二:嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型
         select user_id from order WHERE id=1;//得到user_id
         select * from user WHERE id=1   //1 是上一个查询得到的user_id的值
         property:别名(属性名)    column:列名 -->
    <select id="getOrderByOrderId" resultMap="getOrderMap">
        select * from order where id=#{id}
    </select>
    <resultMap type="com.mybatis.demo.model.Order" id="getOrderMap">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        
        <!--property="userInfo" : orders 中要查询的属性-->
        <!--column="user_id" : user_id 要查询的条件属性-->
        <association property="userInfo"  column="user_id" select="getUserInfoByUserId">

        </association>
    </resultMap>

    <select id="getUserInfoByUserId" resultType="com.mybatis.demo.model.UserInfo">
        select * from user where id=#{id}
    </select>

</mapper>

1.4 在配置文件 mybatis-configuration.xml 中注册 OrderMapper.xml 文件

    <mappers>
        <!--注册多个接口的方式,该包名下所有的接口-->
        <package name="com.mybatis.demo.mapper"/>
    </mappers>

1.5 测试

package com.mybatis.demo.mapper;

import com.mybatis.demo.model.Orders;
import com.mybatis.demo.model.UserInfo;
import lombok.extern.slf4j.Slf4j;
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.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;
import java.io.Reader;

/**
 * @auther kklu
 * @date 2019/10/15 16:18
 * @describe
 */

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class OrdersMapperTest {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession = null;
    private static OrderMapper orderMapper;

    @BeforeClass
    public static void init() {
        try {
            //将工具类读入 reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            sqlSession = sqlSessionFactory.openSession();
            orderMapper = sqlSession.getMapper(OrderMapper.class);
            reader.close();
        } catch (IOException ignore) {
            ignore.printStackTrace();
        }
    }


    @Test
    public void selectOrderAndUserInfoByOrderID() {
        try {
            Orders result = orderMapper.selectOrdersAndUserInfoByOrderID(1);
            log.info("result={}", result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void getOrdersByOrderId() {
        try {
            Orders result = orderMapper.getOrdersByOrderId(1);
            log.info("result={}", result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void getUserInfoByUserId() {
        try {
            UserInfo result = orderMapper.getUserInfoByUserId(2);
            log.info("result={}", result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
}

2.一对多查询

2.1 添加 orders 属性

以用户表 user_info 和 订单表 orders 为例,一个用户能创建多个订单。故用户和订单构成一对多的关联。
我们在 userInfo.java 中添加一个属性 public List orders;

@Data
public class UserInfo {
    private Long id;
    private String username;
    private String password;
    private Integer high;
    private Integer age;
    private Integer sex;
    //一个用户能创建多个订单,用户和订单构成一对多的关系
    private List<Orders> orders;
}

2.2 创建 UserInfoMapper.java 接口

将上一节的接口 UserInfoMapper.java 中的方法全部删除,然后添加新方法如下:

public interface UserInfoMapper {

    //根据用户id查询用户信息,以及用户下面的所有订单信息
    UserInfo selectUserInfoAndOrdersByUserId(int UserId);

}

2.3 修改 UserInfoMapper.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.mybatis.demo.mapper.UserInfoMapper">

    <!--
   方式一:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
                              封装联表查询的数据(去除重复的数据)
    select * from user u,orders o where u.id=o.user_id and u.id=#{id}
    -->
    <select id="selectUserInfoAndOrdersByUserId" resultMap="getUserInfoAndOrders">
        select u.*,o.id oid,o.number number,o.user_id userId from user_info u,orders o where u.id=o.user_id and u.id=#{id}
    </select>
    <resultMap type="com.mybatis.demo.model.UserInfo" id="getUserInfoAndOrders">
        <!--id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
            column:数据库对应的列
            property:实体类对应的属性名 -->
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="high" property="high"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        <!--
            property:实体类中定义的属性名
            ofType:指定映射到集合中的全类名
          -->
        <collection property="orders" ofType="com.mybatis.demo.model.Orders">
            <id column="oid" property="id"/>
            <result column="number" property="number"/>
            <result column="userId" property="userId"/>
        </collection>
    </resultMap>

    <!--
     方式二:嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型
    -->
    <select id="getUserInfoAndOrdersByUserInfoId" parameterType="int" resultMap="getOrderMap">
        select * from user_info where id=#{id}
    </select>

    <resultMap type="com.mybatis.demo.model.UserInfo" id="getOrderMap">
        <id column="id" property="id"/>
        <id column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>

        <!--property="orders" : userInfo 被查询表中要查询的属性-->
        <!--column="id" : id 被查询表中的条件属性-->
        <collection property="orders"  column="id" select="getOrdersByOrderId">

        </collection>
    </resultMap>

    <select id="getOrdersByOrderId" parameterType="int" resultType="com.mybatis.demo.model.Orders">
        select * from orders where user_id=#{id}
    </select>
</mapper>

2.4 测试

package com.mybatis.demo.mapper;

import com.mybatis.demo.model.UserInfo;
import com.mybatis.demo.form.UserInfoForm;
import lombok.extern.slf4j.Slf4j;
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.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

/**
 * @auther kklu
 * @date 2019/9/29 15:49
 * @describe
 */

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession = null;
    private static UserInfoMapper userInfoMapper;

    @BeforeClass
    public static void init() {
        try {
            //将工具类读入 reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            sqlSession = sqlSessionFactory.openSession();
            userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
            reader.close();
        } catch (IOException ignore) {
            ignore.printStackTrace();
        }
    }


    @Test
    public void testSelectUserInfoByUsernameAndSex() {
        try {
            UserInfo result = userInfoMapper.selectUserInfoAndOrdersByUserId(2);
            log.info("result={}", result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

}

3.多对多查询

为了更好的演示示例,我们增加一个 角色表 sys_role,一个用户可以有多个角色(比如管理员,会员等),同时一个角色也可以属于多个用户(比如一个admin角色可有属于多个用户),这就是多对多的关系。
需求:

  • 给定角色id,查询这个角色所属的所有用户信息
  • 给定用户id,查询这个用户所拥有的角色信息

3.1 创建角色表 sys_role 和用户角色关系表

用户表接着使用上面的表。

# 创建角色表
CREATE TABLE sys_role
(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(64) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '角色表';

# 创建用户角色关系表
CREATE TABLE user_role
(
    id int PRIMARY KEY AUTO_INCREMENT,
    user_id int NOT NULL,
    role_id int NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户角色关系表';
# 添加角色信息
insert into sys_role(name) values
  ('admin1'),
  ('admin2'),
  ('member1'),
  ('member2'),
  ('member3');
# 添加用户和角色关系信息
insert into user_role(user_id,role_id) values
  (2,'1'),
  (2,'3'),
  (4,'3'),
  (4,'4');

3.2 创建实体类

  • 创建角色实体
@Data
public class SysRole {
    private Long id;
    private String name;
    //一个角色可以包含多个用户,即一个角色可以属于多个用户
    private List<UserInfo> userInfoList;
}
  • 创建用户实体
    在上一节的 UserInfo.java 中添加角色信息
@Data
public class UserInfo {
    private Long id;
    private String username;
    private String password;
    private Integer high;
    private Integer age;
    private Integer sex;
    //一个用户能创建多个订单,用户和订单构成一对多的关系
    private List<Orders> orders;
    //一个用户可以有多个角色,即一个用户可以是会员同时也是管理员
    private List<SysRole> sysRoleList;
}

3.3 添加接口


public interface UserInfoMapper {

    //多对多查询--给定角色id,查询这个角色所属的所有用户信息
    List<UserInfo> selectUserInfoByRoleId(int roleId);
    //多对多查询--给定用户id,查询这个用户所拥有的角色信息
    List<SysRole> selectSysRoleByUserInfoId(int userId);

}

3.4 编写 UserInfoMapper.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.mybatis.demo.mapper.UserInfoMapper">

    <!--多对多查询:给定角色id,查询这个角色所属的所有用户信息-->
    <select id="selectUserInfoByRoleId" resultMap="getUserInfoMap">
        select u.id,u.username,u.age,u.sex from user_role ur,user_info u where ur.user_id=u.id and ur.role_id=#{id}
    </select>

    <resultMap type="com.mybatis.demo.model.UserInfo" id="getUserInfoMap">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
    </resultMap>

    <!--多对多查询:给定用户id,查询这个用户所拥有的角色信息-->
    <select id="selectSysRoleByUserInfoId" resultMap="getSysRoleMap">
        select r.id,r.name from user_role ur,sys_role r where ur.role_id=r.id and ur.user_id=#{id}
    </select>

    <resultMap type="com.mybatis.demo.model.SysRole" id="getSysRoleMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
    </resultMap>
    
</mapper>

3.5 测试

package com.mybatis.demo.mapper;

import com.mybatis.demo.model.SysRole;
import com.mybatis.demo.model.UserInfo;
import com.mybatis.demo.form.UserInfoForm;
import lombok.extern.slf4j.Slf4j;
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.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

/**
 * @auther kklu
 * @date 2019/9/29 15:49
 * @describe
 */

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession = null;
    private static UserInfoMapper userInfoMapper;

    @BeforeClass
    public static void init() {
        try {
            //将工具类读入 reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            sqlSession = sqlSessionFactory.openSession();
            userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
            reader.close();
        } catch (IOException ignore) {
            ignore.printStackTrace();
        }
    }

    //多对多查询--给定角色id,查询这个角色所属的所有用户信息
    @Test
    public void testSelectUserInfoByRoleId() {
        try {
            List<UserInfo> result = userInfoMapper.selectUserInfoByRoleId(3);
            log.info("result={}", result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

    //多对多查询--给定用户id,查询这个用户所拥有的角色信息
    @Test
    public void testSelectSysRoleByUserInfoId() {
        try {
            List<SysRole> result = userInfoMapper.selectSysRoleByUserInfoId(4);
            log.info("result={}", result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
}

4. 总结

在大型系统上,由于分库分表的原因,由逻辑来控制比价好,减少表之间的关联查询,方便系统进行扩展。
但是在一般的企业级应用中,可使用MyBatis的高级映射结果来处理一对一,一对多和多对多的关系。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值