MyBatis中一对多的查询

1.数据库准备：

user表：

CREATE TABLE USER(
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(32),
birthday DATETIME,
sex CHAR(1),
address VARCHAR(256),
PRIMARY KEY(id)
);
INSERT INTO USER(id,username,birthday,sex,address) VALUES(41,"老王",'2018-2-27','男',"北京市顺义区");
INSERT INTO USER(id,username,birthday,sex,address) VALUES(42,"小二王",'2018-3-2','女',"北京市金燕龙");
INSERT INTO USER(id,username,birthday,sex,address) VALUES(43,"小二王",'2018-3-4','女',"北京市金燕龙");
INSERT INTO USER(id,username,birthday,sex,address) VALUES(44,"老王",'2018-3-7','男',"北京");

INSERT INTO USER(id,username,birthday,sex,address) VALUES(45,"小马宝莉",'2018-3-8','女',"北京修正");

account表：

DROP TABLE IF EXISTS account;
CREATE TABLE account(
id INT NOT NULL,
uid INT DEFAULT NULL,
money DOUBLE DEFAULT NULL,
PRIMARY KEY(id),
FOREIGN KEY(uid) REFERENCES USER(id)
)ENGINE =INNODB DEFAULT CHARSET=utf8;

INSERT INTO account(id,uid,money) VALUES(1,41,1000);
INSERT INTO account(id,uid,money) VALUES(2,42,1000);
INSERT INTO account(id,uid,money) VALUES(3,42,2000);

)

2.编写实体类

User.java：

package com.ghh.domain;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data
public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;

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

Account.java：

package com.ghh.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Account {
    private Integer id;
    private Integer uid;
    private double money;
    //从表实体应该包含主表实体的一个对象引用
    private User user;
}

3.编写实体类接口

UserDao:

package com.ghh.dao;
import com.ghh.domain.User;
import java.util.List;

public interface UserDao {
    //查询所有用户，同时获取到用户下所有账户信息
    List<User> findAll();
}

4.编写映射配置文件：

UserDao.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ghh.dao.UserDao">

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

    <resultMap id="userAccountMap" type="user">
        <id property="userId" column="id"/>
        <result property="userName" column="username"/>
        <result property="userAddress" column="address"/>
        <result property="userSex" column="sex"/>
        <result property="userBirthday" column="birthday"/>
        <collection property="accounts" ofType="account">
            <id property="id" column="id"/>
            <result property="uid" column="uid"/>
            <result property="money" column="money"/>
        </collection>
    </resultMap>
</mapper>

5.编写测试类：

package com.ghh.test;


import com.ghh.dao.UserDao;
import com.ghh.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest {
    private InputStream inputStream;
    private SqlSession sqlSession;
    private UserDao mapper;

    @Before
    public void init() throws IOException {
        //1.读取配置文件
        inputStream =
                Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactoryBuilder对象
        SqlSessionFactoryBuilder builder =
                new SqlSessionFactoryBuilder();
        //3.通过SqlSessionFactoryBuilder对象创建sqlSessionFqctory对象
        SqlSessionFactory sqlSessionFactory =
                builder.build(inputStream);
        //通过SqlSessionFactory对象创建SqlSession对象
        sqlSession = sqlSessionFactory.openSession();
        //获取dao的代理对象
        mapper = sqlSession.getMapper(UserDao.class);
    }

    @After
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        inputStream.close();
    }
    @Test
    public void testFindAll(){
        List<User> users = mapper.findAll();
        for(User user:users){
            System.out.println(user);
        }
    }
}