Mybatis（19）注解实现多表查询

两个类，User，Account

查询每个账户以及其对应的用户信息，一对一查询，采用及时加载

Account类

package com.itheima.domain;

import java.io.Serializable;

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    //多对一（mybatis中称之为一对一）的映射，一个账户只能属于一个用户
    private User user;

    public User getUser() {
        return user;
    }

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

    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 +
                ", user=" + user +
                '}';
    }
}

User类

public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;
    //加入get和set方法
}

IAccountDao接口

public interface IAccountDao {
    /**查询所有账户，并且获取每个账户所属的用户信息**/
    @Select("select * from account")
    @Results(id="accountMap",value = {
            @Result(id=true,column = "id",property = "id"),
            @Result(column = "uid",property = "uid"),
            @Result(column = "money",property = "money"),
            @Result(column = "uid",property = "user",one =@One(select = "com.itheima.dao.IUserDao.findById",fetchType = FetchType.EAGER))
    })
    List<Account> findAll();
    /**根据用户id查询账户信息**/
    @Select("select * from account where uid=#{uid}")
    List<Account> finAccountByUid(Integer userId);
}

验证：

@Test
    public void testFindAll(){
        List<Account> accounts=accountDao.findAll();
        for(Account account:accounts){
            System.out.println("每个账户信息");
            System.out.println(account);
            System.out.println(account.getUser());
        }
    }

结果：

Account{id=3, uid=46, money=2000.0, user=User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Wed Mar 07 17:37:26 CST 2018, accounts=[Account{id=1, uid=46, money=1000.0, user=null}, Account{id=3, uid=46, money=2000.0, user=null}]}}
User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Wed Mar 07 17:37:26 CST 2018, accounts=[Account{id=1, uid=46, money=1000.0, user=null}, Account{id=3, uid=46, money=2000.0, user=null}]}

实现一对多查询，每个用户以及对应的账户信息

User类

public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;
    //一对多关系映射，一个用户对应多个账户
    private List<Account> accounts;

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

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

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    public String getUserSex() {
        return userSex;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public Date getUserBirthday() {
        return userBirthday;
    }

    public void setUserBirthday(Date userBirthday) {
        this.userBirthday = userBirthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userAddress='" + userAddress + '\'' +
                ", userSex='" + userSex + '\'' +
                ", userBirthday=" + userBirthday +
                ", accounts=" + accounts +
                '}';
    }
}

IUserDao接口：

public interface IUserDao {
    /*查询所有用户*/
    @Select("select * from user")
    @Results(id = "userMap",value = {
            @Result(id=true,column = "id",property = "userId"),
            @Result(column = "username",property = "userName"),
            @Result(column = "address",property = "userAddress"),
            @Result(column = "sex",property = "userSex"),
            @Result(column = "birthday",property = "userBirthday"),
            @Result(column ="id",property = "accounts",many = @Many(select = "com.itheima.dao.IAccountDao.finAccountByUid",
            fetchType = FetchType.LAZY))
    })
    List<User> findAll();
    /*查询一个用户*/
    @Select("select * from user where id=#{id}")
    @ResultMap(value = {"userMap"})
    User findById(Integer userId);
    /*根据用户名称模糊查询*/
    @Select("select * from user where username like #{username}")
    @ResultMap(value = {"userMap"})
    List<User> findUserByName(String username);
}

验证：

@Test
    public void testFindAllUser(){
        List<User> users=userDao.findAll();
        for (User user:users){
            System.out.println("每个用户的账户信息");
            System.out.println(user);
            System.out.println(user.getAccounts());
        }
    }

结果：

User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Wed Mar 07 17:37:26 CST 2018, accounts=[Account{id=1, uid=46, money=1000.0, user=null}, Account{id=3, uid=46, money=2000.0, user=null}]}
[Account{id=1, uid=46, money=1000.0, user=null}, Account{id=3, uid=46, money=2000.0, user=null}]