Mybatis--多表查询

一、一对一查询

1.实体类

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String address;
    private String sex;
}
public class Account  {
    private int id;
    private int uid;
    private double money;
    private User user;
}

2.映射类

public interface IAccountDao {
    /**
     * 查询所有账户
     */
    public List<Account> findAll();
}

3.表结构

4.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.unclezs.dao.IAccountDao">
    <resultMap id="AccountUser" type="account">
        <id property="id" column="aid"/>
        <result property="money" column="money"/>
        <result property="uid" column="uid"/>
        <association property="user" column="uid">
            <id property="id" column="uid"/>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
            <result property="birthday" column="birthday"/>
            <result property="sex" column="sex"/>
        </association>
    </resultMap>
    <select id="findAll" resultMap="AccountUser">
        SELECT u.*,a.id aid,a.MONEY,a.UID from user u,account a where u.id=a.uid;
    </select>
</mapper>

二、一对多查询

1.实体类

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String address;
    private String sex;
	private List<Account> accounts;
}
public class Account  {
    private int id;
    private int uid;
    private double money;
}

2.XML配置

 <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <collection property="accounts" ofType="account">
            <id property="id" column="aid"/>
            <result property="money" column="money"/>
            <result property="uid" column="uid"/>
        </collection>
    </resultMap>
    <!--查询全部-->
    <select id="findAll" resultMap="userAccountMap">
        SELECT u.*,a.id aid,a.money,a.uid from user u LEFT OUTER JOIN account a on u.id=a.UID
    </select>

三、多对多查询

1.表结构

2.实体类

public class Role {
    private int id;
    private String role_name;
    private String role_desc;
    private List<User> users;

映射配置文件

<?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.unclezs.dao.IRoleDao">

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

    <select id="findAll" resultMap="roleMap">
        SELECT u.*,r.id rid,r.ROLE_DESC,r.ROLE_NAME 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>
</mapper>

4.映射类

public interface IRoleDao {
    /**
     * 查找所有角色
     */
    public List<Role> findAll();
}

四、延迟加载

一对多

<collection property="accounts" ofType="account" select="com.unclezs.dao.IAccountDao.findById" column="id"/>

一对一

<association property="user" column="uid"  javaType="user" select="com.unclezs.dao.IUserDao.findUserById"/>