Mybatis 多表映射基于XML总结（一对一，一对多，多对多）

一、数据表

数据包括订单，产品，游客，会员。

1.1数据说明和数据关系：

产品表说明：

订单表说明：

会员表说明

旅客表说明

数据表的ER图

表与表之间的业务关系

旅客表（traverller）、订单表（orders）、会员表(member)、产品表（product）的id都是uuid（）下随机生成的

订单表（orders）->产品表（product）一对一的关系
订单表（orders）->会员表(member) 一对一的关系
订单表（orders）->旅客表（traverller）多对多的关系（在Mybatis时中和一对多的处理很像，只不过多对多需要一张中间表）

二、实体类建立

Product实体类：

package domain;

import org.springframework.format.annotation.DateTimeFormat;
import util.DateUtils;

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

public class Product implements Serializable {

    private String id; // 主键
    private String productNum; // 编号 唯一
    private String productName; // 名称
    private String cityName; // 出发城市
    //这个注解的目的是用spring来帮助处理这个属性从表单提交时的数据转换
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm")
    private Date departureTime; // 出发时间
    private String departureTimeStr;//出发时间格式化数据
    private double productPrice; // 产品价格
    private String productDesc; // 产品描述
    private Integer productStatus; // 状态 0 关闭 1 开启
    private String productStatusStr;//状态格式化

    //省略getter 和 setter 
}

Member实体类：

package domain;

import java.io.Serializable;

public class Member implements Serializable {
    private String id;
    private String name;
    private String nickname;
    private String phoneNum;
    private String email;

    //省略getter 和 setter 
}

Traveller实体类：

package domain;

import java.io.Serializable;

public class Traveller implements Serializable {
    private String id;
    private String name;
    private String sex;
    private String phoneNum;
    private Integer credentialsType;
    private String credentialsTypeStr;
    private String credentialsNum;
    private Integer travellerType;
    private String travellerTypeStr;

    //省略getter 和 setter 
    
}

Orders实体类：

package domain;

import org.springframework.format.annotation.DateTimeFormat;
import util.DateUtils;

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

public class Orders implements Serializable {

    private String id;
    private String orderNum;
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm")
    private Date orderTime;
    private String orderTimeStr; //订单时间格式化
    private int orderStatus;
    private String orderStatusStr; //订单状态格式化
    private int peopleCount;
    private Product product;
    private List<Traveller> travellers;
    private Member member;
    private Integer payType;
    private String payTypeStr; //支付状态格式化
    private String orderDesc;

 //省略getter 和 setter 

}

三、DAO层建立

使用Mybatis的动态代理，因此不需要自己实现DAO，因此要对DAO进行配置

IMemberDao接口:

package dao;

import domain.Member;

public interface IMemberDao {

    Member findMemberById(String id);
}

IProductDao接口：

package dao;

import domain.Product;

import java.util.List;

public interface IProductDao {

    //查询所有商品信息
    List<Product> findAll() throws Exception;

    void insert(Product product) throws Exception;

    Product findById(String id);
}

ITravellerDao接口：

package dao;

import domain.Traveller;

import java.util.List;

public interface ITraveller {

    /**
     * 根据订单查询旅客信息
     * @param id
     * @return
     */
    List<Traveller> findTravellerByOrderId(String id);

}

IOrderDao接口：

package dao;
import domain.Orders;
import java.util.List;

public interface IOrdersDao {

    /**
     * 嵌套查询全部信息
     * 包括旅客信息，会员信息，产品信息
     * @return
     * @throws Exception
     */
    List<Orders> findAll() throws Exception;

    /**
     * 嵌套结果查询全部
     * 包括旅客信息，会员信息，产品信息
     * @return
     * @throws Exception
     */
    List<Orders> findAll1() throws Exception;

    /**
     * 只查询订单
     * 不包括旅客信息，会员信息，产品信息
     * @return
     * @throws Exception
     */
    List<Orders> getAll() throws Exception;

    /**
     * 根据ID查询订单
     * 包括旅客信息，会员信息，产品信息
     * @param id
     * @return
     * @throws Exception
     */
    Orders findById(String id) throws Exception;
}

基于XML配置Mybatis

1、配置全局配置文件：

<?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>
    <!--使用typeAlias配置别名-->
    <typeAliases>
        <!--type指定全限定类名,alias指定别名，别名不区分大小写-->
        <typeAlias type="domain.Product" alias="product"/>
        <typeAlias type="domain.Orders" alias="orders"/>
        <typeAlias type="domain.Member" alias="member"/>
        <typeAlias type="domain.Traveller" alias="traveller"/>
    </typeAliases>
    <!--这个mapper必须在最下面！！ 映射dao的配置文件-->
    <mappers>
        <mapper resource="dao/ProductMapper"/>
        <mapper resource="dao/OrderMapper"/>
        <mapper resource="dao/MemberMapper"/>
        <mapper resource="dao/TravellerMapper"/>
    </mappers>
</configuration>

这里没有配置数据源信息的原因是已经在spring中配置了

2、将mybatis工厂对象交给Spring IOC容器管理

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!--开启注解扫描，只需要扫描Service层的注解即可，DAO层由xml配置-->
<!--    <context:component-scan base-package="dao"/>-->
    <context:component-scan base-package="service"/>


    <!--Spring整合mybatis-->
    <!--配置连接池-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!--注入数据库连接信息-->
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/ssm1?use Unicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
        <property name="user" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <!--配置sqlSession工厂对象-->
    <bean id="factory" class="org.mybatis.spring.SqlSessionFactoryBean">     
        <!--设置数据源-->
        <property name="dataSource" ref="dataSource"/>
        <!--映射mybatis全局配置文件-->
        <property name="configLocation" value="classpath:SqlMapConfig.xml"/>
    </bean>

    <!-- 配置Spring的声明式事务管理 -->
    <!-- 配置事务管理器 -->
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager"/>

    <!--映射mapper文件,获取生成代理Dao-->
    <bean id="productMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="dao.IProductDao"/>
        <property name="sqlSessionFactory" ref="factory"/>
    </bean>
    <bean id="orderMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="dao.IOrdersDao"/>
        <property name="sqlSessionFactory" ref="factory"/>
    </bean>



</beans>

3、配置每个DAO接口的Mapper文件

3.1MemberMapper

<?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="dao.IMemberDao">
    <select id="findMemberById" resultType="domain.Member">
        select * from member where id = #{id}
    </select>
</mapper>

3.2ProductMapper

<?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="dao.IProductDao">
    <select id="findAll" resultType="product">
        select * from product
    </select>
    <insert id="insert" parameterType="product">
        <selectKey keyProperty="id" keyColumn="id" resultType="java.lang.String" order="BEFORE">
            select replace(uuid(),'-','')
        </selectKey>
        insert into product(id,productNum,productName,departureTime,cityName,productPrice,productDesc,productStatus)
         values (#{id},#{productNum},#{productName},#{departureTime},#{cityName},#{productPrice},#{productDesc},#{productStatus})
    </insert>
    <select id="findById" resultType="product" parameterType="java.lang.String">
        select * from product where id = #{id}
    </select>
</mapper>

3.3TravellerMapper

<?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="dao.ITraveller">
    <select id="findTravellerByOrderId" resultType="traveller" parameterType="java.lang.String">
        select * from traveller where id in (select travellerId from order_traveller where #{oid} = orderId)
    </select>
</mapper>

3.4OrderMapper

<?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="dao.IOrdersDao">
    <!--嵌套结果查询-->
    <resultMap id="orderMap" type="domain.Orders">
        <id property="id" column="oid"/>
        <result property="orderNum" column="orderNum"/>
        <result property="orderTime" column="orderTime"/>
        <result property="orderStatus" column="orderStatus"/>
        <result property="peopleCount" column="peopleCount"/>
        <result property="payType" column="payType"/>
        <result property="orderDesc" column="orderDesc"/>
        <!--association:用于映射关联查询单个对象的信息
           property:要将关联查询的用户信息映射到Orders中那个属性-->
        <association property="product" javaType="domain.Product">
            <!-- id:关联查询的唯一标识
                column:指定唯一标识信息的列
                property:映射到product的哪个属性
            -->
            <id column="pid" property="id"/>
            <result column="productNum" property="productNum"/>
            <result column="productName" property="productName"/>
            <result column="cityName" property="cityName"/>
            <result column="departureTime" property="departureTime"/>
            <result column="productPrice" property="productPrice"/>
            <result column="productDesc" property="productDesc"/>
            <result column="productStatus" property="productStatus"/>
        </association>

        <!--查询映射会员信息-->
        <association property="member" javaType="domain.Member">
            <id column="mid" property="id"/>
            <result column="name" property="name"/>
            <result column="nickname" property="nickname"/>
            <result column="phoneNum" property="phoneNum"/>
            <result column="email" property="email"/>
        </association>

        <!--根据中间表查询旅客信息-->
        <!-- 关联旅客明细信息
           一个订单关联查询出了多条旅客,要使用collection映射
           collection:对关联查询到的多条记录映射到集合中
           property:将关联查询到的多条记录映射到orders类的那个属性
           ofType:指定映射的集合属性中pojo的类型
       -->
        <collection property="travellers"  ofType="domain.Traveller">
            <id column="tid" property="id"/>
            <result column="name" property="name"/>
            <result column="sex" property="sex"/>
            <result column="phoneNum" property="phoneNum"/>
            <result column="credentialsType" property="credentialsType"/>
            <result column="credentialsNum" property="credentialsNum"/>
            <result column="travellerType" property="travellerType"/>
        </collection>
    </resultMap>

    <!--定义Order的ResultMap懒加载模式映射,嵌套查询-->
    <resultMap id="OrderMapLazy" type="domain.Orders">
        <id property="id" column="id"/>
        <result property="orderNum" column="orderNum"/>
        <result property="orderTime" column="orderTime"/>
        <result property="orderStatus" column="orderStatus"/>
        <result property="peopleCount" column="peopleCount"/>
        <result property="payType" column="payType"/>
        <result property="orderDesc" column="orderDesc"/>
        <!--配置Order对象中product映射 ofType是类型，用全限定名或别名-->
        <!--一对一查询-->
        <association property="product" javaType="domain.Product" select="dao.IProductDao.findById" column="productId" fetchType="lazy"/>
        <association property="member" javaType="domain.Member" select="dao.IMemberDao.findMemberById" column="memberId" fetchType="lazy"/>
        <!--多对多查询,需要根据中间表来查询-->
        <collection property="travellers" ofType="domain.Traveller" column="id" select="dao.ITraveller.findTravellerByOrderId" fetchType="lazy"/>
        <!--如果是一对多查询，还是用colleaction标签，只不过不需要通过中间表查询-->
    </resultMap>

    <select id="findAll" resultMap="orderMap">
     select o.id as oid,o.memberid,o.orderDesc,o.orderNum,o.orderStatus,o.orderTime,o.payType,o.peopleCount,o.productId,
     p.id as pid,p.cityName,p.departureTime,p.productDesc,p.productName,p.productNum,p.productPrice,p.productStatus,
     t.id as tid,t.credentialsNum,t.credentialsType,t.`name`,t.phoneNum,t.sex,t.travellerType,
     m.id as mid,m.email,m.`name`,m.nickName,m.phoneNum
     from orders o LEFT OUTER JOIN order_traveller ot on o.id=ot.orderId , product p,traveller t,member m
     WHERE o.productId = p.id and t.id = ot.travellerId and m.id = o.memberid
    </select>

    <select id="findAll1" resultMap="OrderMapLazy">
        select * from orders
    </select>

    <select id="getAll" resultType="domain.Orders">
        select * from orders
    </select>

    <select id="findById" resultMap="OrderMapLazy" parameterType="java.lang.String">
        select * from orders where id = #{id}
    </select>

</mapper>

posted @ 2019-05-30 00:02 小钟233 阅读(3443) 评论(0) 编辑收藏举报

刷新页面返回顶部