MyBatis实现一对多多层Collection集合嵌套关系实现

MyBatis实现一对多多层Collection集合嵌套关系实现

1、Mybatis连接工具类

package com.cn.fb.utils;

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 java.io.IOException;
import java.io.InputStream;

/**
 * @className: MybatisUtils
 * @Description: MybatisUtils
 * @Author: liangyongyong
 * @Date: 2022/2/12 9:34
 * @Version: 1.0.0
 */
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory = null;
    private static final ThreadLocal<SqlSession> t = new ThreadLocal<>();

    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取SqlSession
     *
     * @return SqlSession
     */
    public static SqlSession openSession() {
        SqlSession sqlSession = t.get();
        if (sqlSession == null) {
            sqlSession = sqlSessionFactory.openSession();
            t.set(sqlSession);
        }
        return sqlSession;
    }

    /**
     * 关闭sqlSession
     */
    public static void closeSqlSession() {
        SqlSession sqlSession = t.get();
        if (sqlSession != null) {
            sqlSession.close();
            t.remove();
        }
    }

    /**
     * 提交事务
     */
    public static void commit() {
        SqlSession sqlSession = openSession();
        sqlSession.commit();
        closeSqlSession();
    }

    /**
     * 事务回滚
     */
    public static void rollback() {
        SqlSession sqlSession = openSession();
        sqlSession.rollback();
        closeSqlSession();
    }

    /**
     * 获取接口的实例对象
     *
     * @param clazz 接口类对象
     * @param <T>   接口类
     * @return 接口的实例对象
     */
    public static <T extends Object> T getMapper(Class<T> clazz) {
        SqlSession sqlSession = openSession();
        return sqlSession.getMapper(clazz);
    }
}

2、实体

UsersEntity

package com.cn.fb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * @className: Users
 * @Description: Users
 * @Author: liangyongyong
 * @Date: 2022/2/12 9:46
 * @Version: 1.0.0
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UsersEntity implements Serializable {
    private Integer userId;
    private String userName;
    private String password;
    private String nickName;
    private String realName;
    private String userImg;
    private String userMobile;
    private String userEmail;
    private String userSex;
    private Date userBirth;
    private Date userRegisterTime;
    private Date userUpdateTime;
    private List<OrdersEntity> ordersEntityList;
}

OrdersEntity

package com.cn.fb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * @className: OrdersEntity
 * @Description: OrdersEntity
 * @Author: liangyongyong
 * @Date: 2022/2/12 9:50
 * @Version: 1.0.0
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrdersEntity implements Serializable {
    private String orderId;
    private Integer userId;
    private String untitled;
    private String receiverName;
    private String receiverMobile;
    private String receiverAddress;
    private BigDecimal totalAmount;
    private BigDecimal actualAmount;
    private Integer payType;
    private String orderRemark;
    private Integer status;
    private String deliveryType;
    private String deliveryNum;
    private BigDecimal orderFreight;
    private Integer deleteStatus;
    private Date createTime;
    private Date updateTime;
    private Date payTime;
    private Date deliveryTime;
    private Date finishTime;
    private Date cancelTime;
    private Integer closeType;
    private List<OrdersDetailsEntity> ordersDetailsEntityList;
}

OrdersDetailsEntity

package com.cn.fb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @className: OrdersDetailsEntity
 * @Description: OrdersDetailsEntity
 * @Author: liangyongyong
 * @Date: 2022/2/12 9:52
 * @Version: 1.0.0
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrdersDetailsEntity implements Serializable {
    private Integer id;
    private String orderId;
    private Integer productId;
    private String productName;
    private String productImg;
    private BigDecimal productPrice;
    private Integer count;
    private BigDecimal totalAmount;
    private Date buyTime;
    private Integer commentStatus;
}

3、mybatis核心配置文件

mybatis-config.xml

<?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 resource="db.properties"/>
    <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>
        <mapper resource="mapper/UsersMapper.xml"/>
    </mappers>
</configuration>

4、配置UsersMapper.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.cn.fb.mapper.UsersMapper">

    <resultMap id="usersMap" type="com.cn.fb.entity.UsersEntity">
        <id column="user_id" property="userId"/>
        <result column="username" property="userName"/>
        <result column="password" property="password"/>
        <result column="nickname" property="nickName"/>
        <result column="realname" property="realName"/>
        <result column="user_img" property="userImg"/>
        <result column="user_mobile" property="userMobile"/>
        <result column="user_email" property="userEmail"/>
        <result column="user_sex" property="userSex"/>
        <result column="user_birth" property="userBirth"/>
        <result column="user_regtime" property="userRegisterTime"/>
        <result column="user_modtime" property="userUpdateTime"/>
        <collection property="ordersEntityList" ofType="com.cn.fb.entity.OrdersEntity">
            <id column="order_id" property="orderId"/>
            <result column="user_id" property="userId"/>
            <result column="untitled" property="untitled"/>
            <result column="receiver_name" property="receiverName"/>
            <result column="receiver_mobile" property="receiverMobile"/>
            <result column="receiver_address" property="receiverAddress"/>
            <result column="total_amount" property="totalAmount"/>
            <result column="actual_amount" property="actualAmount"/>
            <result column="pay_type" property="payType"/>
            <result column="order_remark" property="orderRemark"/>
            <result column="status" property="status"/>
            <result column="delivery_type" property="deliveryType"/>
            <result column="delivery_num" property="deliveryNum"/>
            <result column="order_freight" property="orderFreight"/>
            <result column="delete_status" property="deleteStatus"/>
            <result column="create_time" property="createTime"/>
            <result column="update_time" property="updateTime"/>
            <result column="pay_time" property="payTime"/>
            <result column="delivery_time" property="deliveryTime"/>
            <result column="flish_time" property="finishTime"/>
            <result column="cancel_time" property="cancelTime"/>
            <result column="close_type" property="closeType"/>
            <collection property="ordersDetailsEntityList" ofType="com.cn.fb.entity.OrdersDetailsEntity">
                <id column="id" property="id"/>
                <result column="order_id" property="orderId"/>
                <result column="product_id" property="productId"/>
                <result column="product_name" property="productName"/>
                <result column="product_img" property="productImg"/>
                <result column="product_price" property="productPrice"/>
                <result column="count" property="count"/>
                <result column="total_amount" property="totalAmount"/>
                <result column="buy_time" property="buyTime"/>
                <result column="comment_status" property="commentStatus"/>
            </collection>
        </collection>
    </resultMap>

    <sql id="selectUsersVO">
        SELECT u.user_id,
               u.username,
               u.PASSWORD,
               u.nickname,
               u.realname,
               u.user_img,
               u.user_mobile,
               u.user_email,
               u.user_sex,
               u.user_birth,
               u.user_regtime,
               u.user_modtime,
               o.order_id,
               o.user_id,
               o.untitled,
               o.receiver_name,
               o.receiver_mobile,
               o.receiver_address,
               o.total_amount,
               o.actual_amount,
               o.pay_type,
               o.order_remark,
               o.STATUS,
               o.delivery_type,
               o.delivery_num,
               o.order_freight,
               o.delete_status,
               o.create_time,
               o.update_time,
               o.pay_time,
               o.delivery_time,
               o.flish_time,
               o.cancel_time,
               o.close_type,
               d.id,
               d.order_id,
               d.product_id,
               d.product_name,
               d.product_img,
               d.product_price,
               d.count,
               d.total_amount,
               d.buy_time,
               d.comment_status
        FROM users u
                 LEFT JOIN orders o ON u.user_id = o.user_id
                 LEFT JOIN orders_details d ON o.order_id = d.order_id
    </sql>

    <select id="selectOne" parameterType="integer" resultMap="usersMap">
        <include refid="selectUsersVO"/>
        where u.user_id = #{userId};
    </select>

    <select id="selectAll" resultMap="usersMap">
        <include refid="selectUsersVO"/>
    </select>

</mapper>