mybatis一对一查询（多对一）

mybatis一对一查询（多对一）

需求：查询所有账户信息，关联查询用户信息。

注意：因为一个账户信息只能供某个用户使用，所以从查询账户信息出发关联查询用户信息为一对一查询。如果从用户信息出发查询用户下的账户信息则为一对多查询，因为一个用户可以有多个账户。

1、方式一

1.1、账户信息实体类

package com.yl.bean;

import java.io.Serializable;

/**
 * 账户实体类
 */
public class Account implements Serializable {
    private Integer id;//账户id
    private Integer uid;//用户id
    private Double money;//余额

    public Account() {
    }

    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、用户实体类

package com.yl.bean;

import java.io.Serializable;
import java.sql.Date;

/**
 * 用户实体类
 */
public class User implements Serializable {
    private Integer id;//id
    private String username;//用户名
    private Date birthday;//生日
    private String sex;//性别
    private String address;//地址

    public User() {
    }

    public Integer getId() {
        return id;
    }

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

    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 "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

1.3、编写sql语句

//实现查询账户信息时，也要查询账户所对应的用户信息。
SELECT 
 account.*,
 user.username,
 user.address
FROM
 account,
 user
WHERE account.uid = user.id

1.4、定义AccountUser类

为了能够封装上面 SQL 语句的查询结果，定义 AccountCustomer 类中要包含账户信息同时还要包含用户信息，所以我们要在定义 AccountUser 类时可以继承 User 类。

package com.yl.bean;

public class AccountUser extends User{
    private String username;//用户名
    private String address;//地址

    @Override
    public String getUsername() {
        return username;
    }

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

    @Override
    public String getAddress() {
        return address;
    }

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

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

1.5、定义账户持久层接口

package com.yl.dao;

import com.yl.bean.Account;
import com.yl.bean.User;

import java.util.List;

/**
 * 账户持久层接口
 */
public interface IAccountDao {

    /**
     * 查询所有账户
     */
    List<AccountUser> queryAll();
}

1.6、配置文件

<?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.itheima.dao.IAccountDao">
	<!-- 配置查询所有操作，resultType使用了别名-->
    <select id="findAll" resultType="accountuser">
		select a.*,u.username,u.address from account a,user u where a.uid =u.id;
	</select>
</mapper>

注意：因为上面查询的结果中包含了账户信息同时还包含了用户信息，所以我们的返回值类型 returnType的值设置为 AccountUser 类型，这样就可以接收账户信息和用户信息了。

1.7、测试

@Test
public void testFindAll() {
    List<AccountUser> accountusers = accountDao.findAll();
    for(AccountUser au : accountusers) {
        System.out.println(au);
	} 
}

1.8、小结

定义专门的 po 类作为输出类型，其中定义了 sql 查询结果集所有的字段。此方法较为简单，企业中使用普遍。

2、方式二

使用 resultMap，定义专门的 resultMap 用于映射一对一查询结果。通过面向对象的(has a)关系可以得知，我们可以在 Account 类中加入一个 User 类的对象来代表这个账户是哪个用户的。

2.1、修改账户信息实体类

package com.yl.bean;

import java.io.Serializable;

/**
 * 账户实体类
 */
public class Account implements Serializable {
    private Integer id;//账户id
    private Integer uid;//用户id
    private Double money;//余额
    private User user;//账户所属用户

    public Account() {
    }

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

    public User getUser() {
        return user;
    }

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

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

2.2、用户实体类

package com.yl.bean;

import java.io.Serializable;
import java.sql.Date;

/**
 * 用户实体类
 */
public class User implements Serializable {
    private Integer id;//id
    private String username;//用户名
    private Date birthday;//生日
    private String sex;//性别
    private String address;//地址

    public User() {
    }

    public Integer getId() {
        return id;
    }

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

    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 "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

2.3、账户持久层接口

package com.yl.dao;

import com.yl.bean.Account;
import com.yl.bean.User;

import java.util.List;

/**
 * 账户持久层接口
 */
public interface IAccountDao {

    /**
     * 查询所有账户
     */
    List<Account> queryAll();
}

注意：第二种方式，将返回值改 为了 Account 类型。因为 Account 类中包含了一个 User 类的对象，它可以封装账户所对应的用户信息。

2.4、配置文件

<?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.yl.dao.IAccountDao">
    <!--配置Account属性对应数据库信息-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--一对一关系映射，javaType使用了别名-->
        <association property="user" column="uid" javaType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>
    
    <!--查询所有账户-->
    <select id="queryAll" resultMap="accountUserMap">
        SELECT * FROM USER u,account a WHERE u.`id`=a.`UID`
    </select>
</mapper>

2.5、测试

package com.yl;

import com.yl.bean.Account;
import com.yl.bean.User;
import com.yl.dao.IAccountDao;
import com.yl.dao.IUserDao;
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.sql.Date;
import java.util.List;


public class AccountTest {
    private InputStream inputStream;
    private IAccountDao accountDao;
    private SqlSession sqlSession;

    @Before
    public void init() throws IOException {
        //读取配置文件
        inputStream= Resources.getResourceAsStream("SqlMapperConfig.xml");
        //创建工厂
        SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory= factoryBuilder.build(inputStream);
        //创建SqlSession对象
        sqlSession=sessionFactory.openSession();
        //创建IUserDao接口代理对象
        accountDao=sqlSession.getMapper(IAccountDao.class);
    }

    @After
    public void after() throws IOException {
        //提交事务
        sqlSession.commit();;
        //关闭资源
        sqlSession.close();
        inputStream.close();
    }

    /**
     * 查询所有
     */
    @Test
    public void test01() throws IOException {
        //查询所有账户
        List<Account> accountList=accountDao.queryAll();
        //遍历输出
        for (Account account:accountList){
            System.out.println(account);
            System.out.println(account.getUser().toString());
        }
    }

}