Mybatis 多表查询一对一、一对多、多对多的xml配置和注解配置

一、一对一

1、配置文件方式

<resultMap id="accountMap" type="Account">
    <id property="id" column="aid"></id>
    <result property="uid" column="uid"></result>
    <result property="money" column="money"></result>
    <association property="user" javaType="User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
    </association>
</resultMap>

<select id="findAll" resultMap="accountMap">
    select u.*,a.id as aid,a.money from account a inner join user u on a.uid = u.id
</select>

2、注解配置

import java.util.List;

public interface IOrdersDao {


    @Select("select * from orders")
    @Results({
            @Result(id = true, 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 = "payTypeStr", column = "payTypeStr"),
            @Result(property = "orderDesc", column = "orderDesc"),
            @Result(property = "product", column = "productId", javaType = Product.class,
                    one = @One(select = "com.itheima.ssm.dao.IProductDao.findById"))
    })
    public List<Orders> findAll() throws Exception;
}

import java.util.List;

public interface IProductDao {

    //根据id查询
    @Select("select * from product where id = #{id}")
    public Product findById(String id) throws Exception;
}

二、一对多

1、配置文件方式

    <resultMap id="userMap" type="User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
        <collection property="accounts" ofType="Account">
            <id property="id" column="aid"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>

    <!-- 查询所有 -->
    <select id="findAll" resultMap="userMap">
       select u.*, a.id as aid, a.uid, a.money
        from user u
        LEFT OUTER JOIN account a
        on a.UID = u.id
    </select>

 2、注解配置方式

public interface IOrdersDao {

    //多表操作
    @Select("select * from orders where id=#{ordersId}")
    @Results({
            @Result(id = true, 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 = "payTypeStr", column = "payTypeStr"),
            @Result(property = "orderDesc", column = "orderDesc"),
            @Result(property = "product", column = "productId", javaType = Product.class,
                    one = @One(select = "com.itheima.ssm.dao.IProductDao.findById")),
            @Result(property = "member", column = "memberId", javaType = Member.class,
                    one = @One(select = "com.itheima.ssm.dao.IMemberDao.findById")),
            @Result(property = "travellers", column = "id", javaType = java.util.List.class,
                    many = @Many(select = "com.itheima.ssm.dao.ITravellersDao.findByOrdersId"))

    })
    public Orders findById(String ordersId) throws Exception;
}

public interface ITravellersDao {

    @Select("select * from traveller where id in(select travellerId from order_traveller where orderId=#{ordersId})")
    public List<Traveller> findByOrdersId(String ordersId) throws Exception;
}

三、多对多

    <resultMap id="roleMap" type="role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <collection property="users" ofType="User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleMap">
        select u.*, r.ID as rid, r.ROLE_NAME, r.ROLE_DESC
        from role r
        LEFT OUTER JOIN user_role ur on r.ID = ur.RID
        LEFT OUTER JOIN user u on u.id = ur.UID
    </select>