Mybatis中的多表操作

数据库中的两表内容如下

user表

account表

一对多操作：查询一个用户的所有信息

实现方式1（不常用）：

创建Account类，成员变量对应数据库字段名

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

    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 +
                '}';
    }
}

创建AccountUser类继承Account，成员变量名为user表中需要查询出来的字段名

public class AccountUser extends Account {
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return super.toString() +
                "AccountUser{" +
                "username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

IAccountDao中的方法

	/**
     * 查询用户所有的信息
     * @return
     */
    List<AccountUser> findAllAccount();

配置文件中的sql语句

	<!--查询一个用户的所有信息-->
    <select id="findAllAccount" resultType="AccountUser">
        SELECT u.*,a.ID,a.MONEY from user u,account a where u.id = a.UID;
    </select>

测试查询

	@Test
    public void testFindAllAccount(){
        List<AccountUser> accounts = iAccountDao.findAllAccount();
        for (Account account : accounts) {
            System.out.println(account);
        }
    }

查询结果

实现方式2（常用）：

一对多关系映射，主表实体类应包含从表实体的集合引用。

User类：除了原有的成员变量，再添加一个accounts

	 //一对多关系映射，主表实体类应包含从表实体的集合引用
    private List<Account> accounts;

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

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

配置文件

	<resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <result property="birthday" column="birthday"></result>
        <collection property="accounts" ofType="account">
            <id property="id" column="id"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>


	<!--查询所有-->
    <select id="findAll" resultMap="userAccountMap">
        select * from user u left outer join account a on u.id = a.uid
    </select>

测试类

	@Test
    public void testFindAll(){
        List<User> users = iUserDao.findAll();
        for (User user : users) {
            System.out.println("------------用户信息------------");
            System.out.println(user);
            System.out.println(user.getAccounts());
        }
    }

查询结果

多对多操作

数据库表内容如下：

user表同上

role表（角色表）

由多对多的关系产生的中间表

创建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 + '\'' +
                '}';
    }
}

IRoleDao中的方法

    /**
     * 查询所有角色
     * @return
     */
    List<Role> findAll();

配置文件

<mapper namespace="com.sx.dao.IRoleDao">

    <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="sex" column="sex"></result>
            <result property="address" column="address"></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>
</mapper>

测试类

/**
     * 查询所有
     */
    @Test
    public void testFindAll(){
        List<Role> roles = iRoleDao.findAll();
        for (Role role : roles) {
            System.out.println("------------用户角色信息------------");
            System.out.println(role);
            System.out.println(role.getUsers());
        }
    }

查询结果