MyBatis多表映射

MrCollins

于 2023-10-17 22:17:04 发布

阅读量235

点赞数 2

文章标签： mybatis oracle 数据库

本文链接：https://blog.csdn.net/MrCollins/article/details/133895061

版权

一、准备环境

1.创建数据库表

CREATE TABLE `t_customer`(
    `customer_id`   INT NOT NULL AUTO_INCREMENT,
    `customer_name` CHAR(100),
    PRIMARY KEY (`customer_id`)
);

CREATE TABLE `t_order`(
    `order_id`    INT NOT NULL AUTO_INCREMENT,
    `order_name`  CHAR(100),
    `customer_id` INT,
    PRIMARY KEY (`order_id`)
);

INSERT INTO `t_customer` (`customer_name`)
VALUES ('c01');

INSERT INTO `t_order` (`order_name`, `customer_id`)
VALUES ('o1', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`)
VALUES ('o2', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`)
VALUES ('o3', '1');

2.准备实体类

package com.suchuanlin.pojo;

import lombok.Data;
import java.util.List;

@Data
public class Customer {
    private Integer customerId;
    private String customerName;
    private List<Order> orderList;/*一个客户对应多个订单，对多：装对方类型的集合即可*/
}

package com.suchuanlin.pojo;

import lombok.Data;

@Data
public class Order {
    private Integer orderId;
    private String orderName;
    private Integer customerId;
    private Customer customer;  /*客户信息*/
}

3.准备数据库配置文件jdbc.properties

jdbc.url=jdbc:mysql://localhost:3306/mybatis-example
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=1234

4.编写mybatis-config.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!--引入properties文件-->
    <properties resource="jdbc.properties"/>

    <settings>
        <!--开启mybatis的日志输出-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰命名自动映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--开启resultMap深层次自动映射-->
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>

    <typeAliases>
        <!--批量将包下的类起别名，别名为类的首字母小写-->
        <package name="com.suchuanlin.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <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/suchuanlin/mapper"/>
    </mappers>

</configuration>

二、一对一数据查询

5.编写OrderMapper接口

package com.suchuanlin.mapper;

import com.suchuanlin.pojo.Order;

public interface OrderMapper {

    //根据id查询订单信息和订单对应的用户
    Order queryOrderById(Integer id);
}

6.编写OrderMapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.suchuanlin.mapper.OrderMapper">

    <!--自定义映射，配置嵌套对象的映射关系-->
    <resultMap id="orderAndCustomerResultMap" type="order">
        <id column="order_id" property="orderId"/>
        <result column="order_name" property="orderName"/>
        <result column="customer_id" property="customerId"/>
        <!--使用association标签配置[一对一]关联关系-->
        <!-- property属性：在Order类中对一的一端进行引用时使用的属性名 -->
        <!-- javaType属性：一的一端类的全类名 -->
        <association property="customer" javaType="customer">
            <!-- 配置Customer类的属性和字段名之间的对应关系 -->
            <id column="customer_id" property="customerId"/>
            <result column="customer_name" property="customerName"/>
        </association>
    </resultMap>

    <!--根据id查询订单信息和订单对应的用户-->
    <select id="queryOrderById" resultMap="orderAndCustomerResultMap">
        select *
        from t_order o,t_customer c
        where o.customer_id = c.customer_id
          and o.order_id = #{id}
    </select>

</mapper>

三、一对多数据查询

7.编写CustomerMapper接口

package com.suchuanlin.mapper;

import com.suchuanlin.pojo.Customer;
import java.util.List;

public interface CustomerMapper {

    //查询所有客户信息以及对应的订单信息
    List<Customer> queryList();

}

8.编写CustomerMapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.suchuanlin.mapper.CustomerMapper">

    <!-- 配置resultMap实现从Customer到OrderList的“对多”关联关系 -->
    <resultMap id="customerAndOrderResultMap" type="customer">
        <id column="customer_id" property="customerId"/>
        <!--<result column="customer_name" property="customerName"/>-->
        <!--
        collection标签：映射“对多”的关联关系
        property属性：在Customer类中，关联“多”的一端的属性名
        ofType属性：集合属性中元素的类型 -->
        <collection property="orderList" ofType="order">
            <id column="order_id" property="orderId"/>
            <!--<result column="order_name" property="orderName"/>
            <result column="customer_id" property="customerId"/>-->
        </collection>
    </resultMap>

    <!--查询所有客户信息以及对应的订单信息-->
    <select id="queryList" resultMap="customerAndOrderResultMap">
        select *
        from t_customer
            left join t_order
                on t_customer.customer_id = t_order.customer_id
    </select>

</mapper>

四、测试程序

9.完整测试程序

package com.suchuanlin.test;

import com.suchuanlin.mapper.CustomerMapper;
import com.suchuanlin.mapper.OrderMapper;
import com.suchuanlin.pojo.Customer;
import com.suchuanlin.pojo.Order;
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.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MyBatisMultiTest {

    private SqlSession sqlSession;

    @BeforeEach  //每次走测试方法之前，先走的初始化方法
    public void before() throws IOException {
        //1.读取配置文件输入流
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        //2.创建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //3.获取SqlSession对象
        sqlSession = sqlSessionFactory.openSession();
    }

    @AfterEach  //每次走测试方法之后，调用的关闭资源方法
    public void clean(){
        //5.关闭资源/提交事务
        sqlSession.close();
    }

    @Test
    public void testQueryOrderById(){
        //4.获取mapper，执行方法
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        Order order = mapper.queryOrderById(1);
        System.out.println(order.getCustomer());
        System.out.println(order);
    }

    @Test
    public void testQueryList(){
        //4.获取mapper，执行方法
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customerList = mapper.queryList();
        for (Customer customer : customerList) {
            System.out.println(customer);
        }
    }
}

10.1.测试一对一

@Test
public void testQueryOrderById(){
    //4.获取mapper，执行方法
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    Order order = mapper.queryOrderById(1);
    System.out.println(order.getCustomer());
    System.out.println(order);
}

10.2.输出结果

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@64bc21ac]
==>  Preparing: select * from t_order o,t_customer c where o.customer_id = c.customer_id and o.order_id = ?
==> Parameters: 1(Integer)
<==    Columns: order_id, order_name, customer_id, customer_id, customer_name
<==        Row: 1, 订单1, 1, 1, 张三
<==      Total: 1
Customer(customerId=1, customerName=张三, orderList=null)
Order(orderId=1, orderName=订单1, customerId=1, customer=Customer(customerId=1, customerName=张三, orderList=null))

11.1.测试一对多

@Test
public void testQueryList(){
    //4.获取mapper，执行方法
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    List<Customer> customerList = mapper.queryList();
    for (Customer customer : customerList) {
        System.out.println(customer);
    }
}

11.2.输出结果

==>  Preparing: select * from t_customer left join t_order on t_customer.customer_id = t_order.customer_id
==> Parameters: 
<==    Columns: customer_id, customer_name, order_id, order_name, customer_id
<==        Row: 1, 张三, 3, 订单3, 1
<==        Row: 1, 张三, 2, 订单2, 1
<==        Row: 1, 张三, 1, 订单1, 1
<==      Total: 3
Customer(customerId=1, customerName=张三, orderList=[Order(orderId=3, orderName=订单3, customerId=1, customer=null), Order(orderId=2, orderName=订单2, customerId=1, customer=null), Order(orderId=1, orderName=订单1, customerId=1, customer=null)])