Mybatis多表查询

以用户和账户的模型来分析多表关系。
Account表

Role表

User表

user_role表

1.多对一查询（一对一查询）

一个账户只能供某个用户使用，从查询账户信息出发关联查询用户信息为一对一查询。

1.1定义Account实体类

在Account类中加入User类的对象作为Account类的一个属性

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

1.2定义账户的持久层接口Accountdao

public interface Accountdao {
    //List<AccountUser> findAll();
    //List<AccountUser> findAllaccount();
    List<Account> findAll();
}

1.3编写Accountdao.xml

<resultMap id="accountmap" type="com.pojo.Account">
         <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>

        <association property="user" javaType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>

     <select id="findAll" resultMap="accountmap">
         select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid=u.id
     </select>

association：一个复杂类型的关联，用来处理关联查询。
property：属性名
JavaType：属性类型

1.4测试

 @Test
    public void testfindallaccount() throws Exception {
        //56.使用代理对象查询所有方法
        List<Account> ac = accountdao.findAll();
        for (Account a : ac) {
            System.out.println(a);
            System.out.println(a.getUser());

        }
        des();
    }

1.5结果

2.一对多查询

2.1在User类中加入List

//主表加入从表的实体类
    private List<Account> accounts;

    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

2.2在用户User添加持久层接口

public interface Userdao {
    //查找用户所有信息
    List<User> findAll();
}

2.3编写Userdao.xml映射文件

<resultMap id="usermap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>
        <result column="address" property="address"></result>
       <collection property="accounts" ofType="Account">
           <id column="aid" property="id"></id>
           <result column="uid" property="uid"></result>
           <result column="money" property="money"></result>
       </collection>
   </resultMap>
    <select id="findAll" resultMap="usermap">
        select u.*,a.id as aid,a.uid,a.money from user u left join account a on a.uid=u.id
    </select>

ofType:指定映射到list集合属性中pojo的类型。<>中的类型。集合用ofType,否则用javaType。
2.4测试

 //多对一
    @Test
    public void testfindall() throws Exception {
        List<User> users = userdao.findAll();
        for (User user : users) {
            System.out.println(user);
            System.out.println(user.getAccounts());
        }
        des();
    }

2.5结果

3.多对多查询

Role和User多对多

3.1Role关联User

3.1.1编写Role实体类

public class Role implements Serializable {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

3.1.2编写Role持久层接口Roledao

public interface Roledao {
    List<Role> findAll();
}

3.1.3编写Roledao.xml

<resultMap id="rolemap" type="role">
        <id column="rid" property="roleId"></id>
        <result column="ROLE_NAME" property="roleName"></result>
        <result column="ROLE_DESC" property="roleDesc"></result>
        <collection property="users" ofType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
            <result column="address" property="address"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="rolemap">
        select u.*,r.id as rid,r.role_name,r.role_desc from role r
        left join user_role ur on r.id=ur.rid
        left join user u on u.id=ur.uid
    </select>

3.1.4测试

//多对多
    @Test
    public void test4() throws Exception {
        List<Role> roles=roledao.findAll();
        for (Role role : roles) {
            System.out.println(role);
            System.out.println(role.getUsers());
        }
        des();
    }

3.1.5结果

3.2User关联Role

与Role关联User类似
结果：