五、多(一)对一和一对多查询

1、查询所有账户，同时查询出账户所属的用户，包含用户的用户名和地址信息

实体类

public class User implements Serializable {

    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

public class AccountUser extends Account {

    private String username;
    private String address;

    public String getUsername() {
        return username;
    }

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

    public String getAddress() {
        return address;
    }

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

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

public interface IAccountDao {
    /**
     * 查询所有账户，并且带有用户名称和地址信息
     */
    List<AccountUser> findAllAccount();
}

<?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.liaoxiang.dao.IAccountDao">
    <!--查询所有账户同时包含用户名和地址信息-->
    <select id="findAllAccount" resultType="accountUser">
        select a.*,u.username,u.address from account a , user u where u.id = a.uid;
    </select>
</mapper>

public class AccountTest {

    private InputStream in;
    private SqlSession sqlSession;
    private IAccountDao accountDao;

    @Before//用于在测试方法执行之前执行
    public void init()throws Exception{
        //1.读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.获取SqlSession对象
        sqlSession = factory.openSession(true);
        //4.获取dao的代理对象
        accountDao = sqlSession.getMapper(IAccountDao.class);
    }
    @After//用于在测试方法执行之后执行
    public void destroy()throws Exception{
        //提交事务
        // sqlSession.commit();
        //6.释放资源
        sqlSession.close();
        in.close();
    }
    /**
     * 测试查询所有account，同时包含用户名称和地址
     */
    @Test
    public void testFindAllAccountUser(){
        List<AccountUser> aus = accountDao.findAllAccount();
        for(AccountUser au : aus){
            System.out.println(au);
        }
    }
}
Account{id=1, uid=46, money=1000.0}, AccountUser{username='老王', address='北京'}
Account{id=2, uid=45, money=1000.0}, AccountUser{username='李四', address='广州'}
Account{id=3, uid=46, money=2000.0}, AccountUser{username='老王', address='北京'}

2、多对一查询更常用的方式

多对一（一对一）时实体类

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    // 从表实体应该包含一个主表实体的对象引用,一个Account对应一个User
    private User user;

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

<?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.liaoxiang.dao.IAccountDao">

    <!-- 定义封装account和user的resultMap -->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!-- 多对一的关系映射：配置封装user的内容-->
        <association property="user" column="uid" javaType="user">
            <id property="id" column="id"></id>
            <result column="username" property="username"></result>
            <result column="address" property="address"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>

    <!-- 查询所有 -->
    <select id="findAll" resultMap="accountUserMap">
        select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid;
    </select>
</mapper>

测试类

@Test
public void testFindAllAccount(){
    List<Account> accounts = accountDao.findAll();
    for(Account account : accounts){
        System.out.println("--------------------");
        System.out.println("账户信息：" + account);
        System.out.println("用户信息：" + account.getUser());
    }
}
账户信息：Account{id=1, uid=46, money=1000.0}
用户信息：User{id=46, username='老王', address='北京', sex='男', birthday=Wed Mar 07 17:37:26 CST 2018}
--------------------
账户信息：Account{id=2, uid=45, money=1000.0}
用户信息：User{id=45, username='李四', address='广州', sex='男', birthday=Sun Mar 04 12:04:06 CST 2018}
--------------------
账户信息：Account{id=3, uid=46, money=2000.0}
用户信息：User{id=46, username='老王', address='北京', sex='男', birthday=Wed Mar 07 17:37:26 CST 2018}

3、一对多查询

一对多时实体类，查询所有用户同时获取每个用户下的所有账户信息

public class User implements Serializable {

    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
    //一对多关系映射：主表实体应该包含从表实体的集合引用，一个User对应多个account
    private List<Account> accounts;
   
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

<?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.liaoxiang.dao.IUserDao">

    <!-- 定义User的resultMap-->
    <resultMap id="userAccountMap" 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>
        <!-- 配置user对象中accounts集合的映射 -->
        <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="userAccountMap">
        SELECT u.*,a.id aid,a.uid,a.money FROM USER u LEFT OUTER JOIN account a ON u.id = a.uid
    </select>
</mapper>

测试类：

@Test
public void testFindAll(){
    List<User> users = userDao.findAll();
    for(User user : users){
        System.out.println("用户信息："+user);
        System.out.println("账户信息："+user.getAccounts());
        System.out.println("-----------------------");
    }
}
用户信息：User{id=41, username='张三', address='绵阳', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018}
账户信息：[]
-----------------------
用户信息：User{id=45, username='李四', address='广州', sex='男', birthday=Sun Mar 04 12:04:06 CST 2018}
账户信息：[Account{id=2, uid=45, money=1000.0}]
-----------------------
用户信息：User{id=46, username='老王', address='北京', sex='男', birthday=Wed Mar 07 17:37:26 CST 2018}
账户信息：[Account{id=1, uid=46, money=1000.0}, Account{id=3, uid=46, money=2000.0}]
-----------------------
用户信息：User{id=49, username='王五', address='西藏', sex='女', birthday=Mon Jun 03 16:40:00 CST 2019}
账户信息：[]