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：

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

import java.io.Serializable;
import java.util.Date;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;
}

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.编写实体类接口

AccountDao:

package com.ghh.dao;
import com.ghh.domain.Account;
import java.util.List;
public interface AccountDao {
    List<Account> findAll();
    //查询所有账户，并且带有用户信息和地址
    List<Account> findAllAccount();
}

4.编写映射配置文件：

<?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.AccountDao">

    <!--查询所有用户-->
    <select id="findAllAccount" resultMap="accountUserMap">
        select u.*,a.id as aid,a.money from account a,user u where u.id=a.uid;
    </select>
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="aid"/>
        <result property="uid" column="uid"/>
        <result property="money" column="money"/>
        <!--一对一的关系映射，配置封装User的内容-->
        <association property="user" column="uid">
            <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"/>
        </association>
    </resultMap>
</mapper>

5.编写测试类：

package com.ghh.test;

import com.ghh.dao.AccountDao;
import com.ghh.domain.Account;
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 AccountDao 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(AccountDao.class);
    }

    @After
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        inputStream.close();
    }
    @Test
    public void testFindAllAccount(){
        List<Account> allAccount = mapper.findAllAccount();
        for(Account accountUser:allAccount){
            System.out.println(accountUser);
        }
    }
}