【mybatis学习记录】mybatis的各种查询 一对一关联查询（4种方式） 一对多（2种方式）

---

一对一

如：通过订单id查询订单的时候，将订单关联的用户信息也返回。

首先建库建表：



新建实体类：

@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Data
public class OrderModel {
    private Integer id;
    private Integer userId;
    private Long createTime;
    private Long upTime;
    private UserModel userModel;
}


@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class UserModel {
    private Long id;
    private String name;
    private Integer age;
    private Double salary;
    private Integer sex;
}

mybatis全局配置：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties>
        <property name="jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="jdbc.url"
                  value="jdbc:mysql://localhost:3308/data1?characterEncoding=UTF-8"/>
        <property name="jdbc.username" value="heziyi"/>
        <property name="jdbc.password" value="123456"/>
    </properties>
    <!-- 环境配置，可以配置多个环境 -->
    <environments default="test">
        <!--
        environment用来对某个环境进行配置
        id：环境标识，唯一
        -->
        <environment id="test">
            <!-- 事务管理器工厂配置 -->
            <transactionManager type="JDBC"/>
            <!-- 数据源工厂配置，使用工厂来创建数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.example.demomybatis.mapper"/>
    </mappers>
</configuration>

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demomybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demomybatis</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>

                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>
</project>

方式一：

@Mapper
public interface OrderMapper {
    OrderModel getById(int id);
    OrderModel getById1(int id);
}

OrderMapper.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.example.demomybatis.mapper.OrderMapper">
<select id="getById"
        resultType="com.example.demomybatis.model.OrderModel">
    <![CDATA[
SELECT a.id,a.user_id as userId,a.create_time createTime,a.up_time upTime
FROM t_order a WHERE a.id = #{value}
]]>
</select>

    <resultMap id="orderModelMap1"
               type="com.example.demomybatis.model.OrderModel">
        <id column="id" property="id" />
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
        <result column="up_time" property="upTime"/>
        <result column="user_id" property="userModel.id"/>
        <result column="name" property="userModel.name"/>
    </resultMap>
    <select id="getById1" resultMap="orderModelMap1">
<![CDATA[
        SELECT
            a.id,
            a.user_id,
            a.create_time,
            a.up_time,
            b.name,
               b.sex,
               b.salary,
               b.age
        FROM
            t_order a,
            t_user b
        WHERE
            a.user_id = b.id
          AND a.id = #{value}
        ]]>
</select>
</mapper>

运行启动类：

@Slf4j
public class Demo3Test {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void before() throws IOException {
//指定mybatis全局配置文件
        String resource = "mybatis-config.xml";
//读取全局配置文件
        InputStream inputStream =Resources.getResourceAsStream(resource);
//构建SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new
                SqlSessionFactoryBuilder().build(inputStream);
        this.sqlSessionFactory = sqlSessionFactory;
    }
    @Test
    public void test() {
        try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
            OrderModel orderModel = mapper.getById1(1);
            log.info("{}", orderModel);

        }
        }
}

结果：

方式2

方式2
这次我们需要使用mapper xml中另外一个元素association ，这个元素可以配置关联对象的映射关系

<resultMap id="orderModelMap2"
type="com.javacode2018.chat05.demo2.model.OrderModel">
<id column="id" property="id" />
<result column="user_id" property="userId"/>
<result column="create_time" property="createTime"/>
<result column="up_time" property="upTime"/>
<association property="userModel">
<id column="user_id" property="id"/>
<result column="name" property="name" />
</association>
</resultMap>
<select id="getById2" resultMap="orderModelMap2">
<![CDATA[
SELECT
a.id,
a.user_id,
a.create_time,
a.up_time,
b.name
FROM
t_order a,
t_user b
WHERE
a.user_id = b.id
AND a.id = #{value}
]]>
</select>

添加接口：

    OrderModel getById2(int id);

测试类修改：

  OrderModel orderModel = mapper.getById2(1);

运行：

方式3

先按照订单id查询订单数据，然后在通过订单中user_id 去用户表查询用户数据，通过两次查询，组合成目标结果，mybatis已经内置了这种操作
我们先定义一个通过用户id查询用户信息的select元素，如下

    UserModel getById(int id);

    <select id="getById" resultType="com.example.demomybatis.model.UserModel">
<![CDATA[
        SELECT id,name FROM t_user where id = #{value}
        ]]>
</select>

OrderMapper.xml

<mapper namespace="com.example.demomybatis.mapper.OrderMapper">


    <resultMap id="orderModelMap3"
               type="com.example.demomybatis.model.OrderModel">
        <id column="id" property="id" />
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
        <result column="up_time" property="upTime"/>
        <association property="userModel"
                     select="com.example.demomybatis.mapper.UserMapper.getById"
                     column="user_id" />
    </resultMap>
    <select id="getById3" resultMap="orderModelMap3">
<![CDATA[
        SELECT
            a.id,
            a.user_id,
            a.create_time,
            a.up_time
        FROM
            t_order a
        WHERE
            a.id = #{value}
        ]]>
</select>
</mapper>

运行：

方式4

方式3中给第二个查询传递了一个参数，如果需要给第二个查询传递多个参数怎么办呢？可以这么写

<association property="属性" select="查询对应的select的id" column="{key1=父查询字段
1,key2=父查询字段2,key3=父查询字段3}" />

这种相当于给子查询传递了一个map，子查询中 需要用过map的key获取对应的条件，：

OrderMapper.xml：

  <resultMap id="orderModelMap4"
               type="com.example.demomybatis.model.OrderModel">
               <!-- property：主键在pojo中的属性名 -->
        <!-- column：主键在数据库中的列名 -->

        <id column="id" property="id" />
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
        <result column="up_time" property="upTime"/>
        <association property="userModel"
                     select="com.example.demomybatis.mapper.UserMapper.getById1" column="
{uid1=user_id,uid2=create_time}" />
    </resultMap>
    <select id="getById4" resultMap="orderModelMap4">
<![CDATA[
        SELECT
            a.id,
            a.user_id,
            a.create_time,
            a.up_time
        FROM
            t_order a
        WHERE
            a.id = #{value}
        ]]>
</select>

    OrderModel getById4(int id);

UserMapper.xml

    <select id="getById1"
            resultType="com.example.demomybatis.model.UserModel">
<![CDATA[
        SELECT id,name FROM t_user where id = #{uid1} and id = #{uid2}
        ]]>
</select>

UserModel getById1(int id);

结果：

传过来的是第一个查询的user_id和create_time

关于resultmap resulttype:

resultType可以把查询结果封装到pojo类型中，但必须pojo类的属性名和查询到的数据库表的字段名一致。
如果sql查询到的字段与pojo的属性名不一致，则需要使用resultMap将字段名和属性名对应起来，进行手动配置封装，将结果映射到pojo中

一对多查询(2种方式)

根据订单id查询出订单信息，并且查询出订单明细列表。
一对多关系:collection标签

   <resultMap id="orderModelMap1"
               type="com.example.demomybatis.model.OrderModel">
    <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
        <result column="up_time" property="upTime"/>
        <collection property="orderDetailModelList"
                    ofType="com.example.demomybatis.model.OrderDetailModel">
            <id column="orderDetailId" property="id"/>
            <result column="order_id" property="orderId"/>
            <result column="goods_id" property="goodsId"/>
            <result column="num" property="num"/>
            <result column="total_price" property="totalPrice"/>
        </collection>
    </resultMap>
    <select id="getById1" resultMap="orderModelMap1">
<![CDATA[
        SELECT
            a.id ,
            a.user_id,
            a.create_time,
            a.up_time,
            b.id orderDetailId,
            b.order_id,
            b.goods_id,
            b.num,
            b.total_price
        FROM
            t_order a,
            t_order_detail b
        WHERE
            a.id = b.order_id
          AND a.id = #{value}
        ]]>
</select>

这个sql中使用到了t_order和t_order_detail 连接查询，这个查询会返回多条结果，但是最后结果按照orderModelMap1 进行映射，最后只会返回一个OrderModel 对象，关键在于collection 元素，这个元素用来定义集合中元素的映射关系，有2个属性需要注意
property：对应的属性名称
ofType：集合中元素的类型，此处是OrderDetailModel
注意这个：

<id column="id" property="id"/>

查询出来的结果会按照这个配置中指定的column 进行分组，即按照订单id 进行分组，每个订单对应多个订单明细，订单明细会按照collection 的配置映射为ofType元素指定的对象。
实际resultMap元素中的id元素可以使用result 元素代替，只是用id 可以提升性能，mybatis可以通过id元素配置的列的值判断唯一一条记录，如果我们使用result 元素，那么判断是否是同一条记录的时候，需要通过所有列去判断了，所以通过id 可以提升性能，使用id元素在一对多中可以提升性能，在单表查询中使用id元素还是result元素，性能都是一样的。

    OrderModel getById1(Integer id);

  <resultMap id="orderModelMap1"
               type="com.example.demomybatis.model.OrderModel">
    <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
        <result column="up_time" property="upTime"/>
        <collection property="orderDetailModelList"
                    ofType="com.example.demomybatis.model.OrderDetailModel">
            <id column="orderDetailId" property="id"/>
            <result column="order_id" property="orderId"/>
            <result column="goods_id" property="goodsId"/>
            <result column="num" property="num"/>
            <result column="total_price" property="totalPrice"/>
        </collection>
    </resultMap>
    <select id="getById1" resultMap="orderModelMap1">
<![CDATA[
        SELECT
            a.id ,
            a.user_id,
            a.create_time,
            a.up_time,
            b.id orderDetailId,
            b.order_id,
            b.goods_id,
            b.num,
            b.total_price
        FROM
            t_order a,
            t_order_detail b
        WHERE
            a.id = b.order_id
          AND a.id = #{value}
        ]]>
</select>

@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Data
public class OrderDetailModel {
    private Integer  id;
    private Integer goodsId;
    private Integer num;
    private Integer orderId;
    private Double totalPrice;
}

    @Test
    public void test() {
        try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
            Integer id = 1;
            OrderModel orderModel = mapper.getById1(id);
            log.info("{}", orderModel);

        }

结果：

20:13:12.153 [main] DEBUG
com.example.demomybatis.mapper.OrderMapper.getById1 - ==> Preparing:
SELECT a.id , a.user_id, a.create_time, a.up_time, b.id orderDetailId,
b.order_id, b.goods_id, b.num, b.total_price FROM t_order a,
t_order_detail b WHERE a.id = b.order_id AND a.id = ? 20:13:12.181
[main] DEBUG com.example.demomybatis.mapper.OrderMapper.getById1 - >
Parameters: 1(Integer) 20:13:12.205 [main] DEBUG
com.example.demomybatis.mapper.OrderMapper.getById1 - < Total:
2 20:13:12.205 [main] INFO com.example.demomybatis.Demo3Test -
OrderModel(id=1, userId=1, createTime=1624088102, upTime=1624088102,
userModel=null, orderDetailModelList=[OrderDetailModel(id=1,
goodsId=1, num=2, orderId=1, totalPrice=17.76), OrderDetailModel(id=2,
goodsId=1, num=1, orderId=1, totalPrice=16.66)])

方式2

通过2次查询，然后对结果进行分装，先通过订单id查询订单信息，然后通过订单id查询订单明细列表，
然后封装结果。mybatis中默认支持这样，还是通过collection 元素来实现的。
OrderDetailMapper.xml

<select id="getListByOrderId1"
        resultType="com.example.demomybatis.model.OrderDetailModel"
        parameterType="int">
    <![CDATA[
        SELECT
            a.id,
            a.order_id AS orderId,a.goods_id AS goodsId,
            a.num,
            a.total_price AS totalPrice
        FROM
            t_order_detail a
        WHERE
            a.order_id = #{value}
        ]]>
</select>

Ordermapper.xml

  <resultMap id="orderModelMap2"
               type="com.example.demomybatis.model.OrderModel">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
        <result column="up_time" property="upTime"/>
        <collection property="orderDetailModelList"
                    select="com.example.demomybatis.mapper.OrderDetailMapper.getListByOrderId1
" column="id"/>
    </resultMap>
    <select id="getById2" resultMap="orderModelMap2">
<![CDATA[
        SELECT
            a.id ,
            a.user_id,
            a.create_time,
            a.up_time
        FROM
            t_order a
        WHERE
            a.id = #{value}
        ]]>
</select>

重点：

        <collection property="orderDetailModelList"
                    select="com.example.demomybatis.mapper.OrderDetailMapper.getListByOrderId1
" column="id"/>

表示orderDetailModelList 属性的值通过select 属性指定的查询获取，查询参数是通过column 属性指定的，此处使用getById2 sql中的id 作为条件，即订单id