MyBatis多表查询

SELECT * FROM mybatis.user;select * from mybatis.role;select * from mybatis.account;select * from mybatis.user_role;

import com.jinke.domain.Account;
import java.util.List;
public interface AccountDao {
    List<Account> findAll();}

import com.jinke.domain.Role;
import java.util.List;
public interface RoleDao {
    List<Role> findAll();}

import com.jinke.domain.User;
import java.util.List;
public interface UserDao {    List<User> findAll();}

import java.io.Serializable;
public class Account implements Serializable {
    private Integer id;    private Integer uid;    private Double money;
    private 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;    }
    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 +                '}';    }}

import java.io.Serializable;import java.util.List;
public class Role implements Serializable {    private Integer id;    private String rolename;    private String roledesc;
    private List<User> users;
    public Integer getId() {        return id;    }
    public void setId(Integer id) {        this.id = id;    }
    public String getRolename() {        return rolename;    }
    public void setRolename(String rolename) {        this.rolename = rolename;    }
    public String getRoledesc() {        return roledesc;    }
    public void setRoledesc(String roledesc) {        this.roledesc = roledesc;    }
    public List<User> getUsers() {        return users;    }
    public void setUsers(List<User> users) {        this.users = users;    }
    @Override    public String toString() {        return "Role{" +                "id=" + id +                ", rolename='" + rolename + '\'' +                ", roledesc='" + roledesc + '\'' +                '}';    }}

import java.io.Serializable;import java.util.Date;import java.util.List;
public class User implements Serializable {    private Integer id;    private String username;    private String address;    private String sex;    private Date birthday;
    private List<Role> roles;
    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 String getAddress() {        return address;    }
    public void setAddress(String address) {        this.address = address;    }
    public String getSex() {        return sex;    }
    public void setSex(String sex) {        this.sex = sex;    }
    public Date getBirthday() {        return birthday;    }
    public void setBirthday(Date birthday) {        this.birthday = birthday;    }
    public List<Role> getRoles() {        return roles;    }
    public void setRoles(List<Role> roles) {        this.roles = roles;    }
    @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.jinke.dao.AccountDao">
    <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" javaType="user">            <id property="id" column="id"/>            <result column="username" property="username"/>            <result column="address" property="address"/>            <result column="sex" property="sex"/>            <result column="birthday" property="birthday"/>        </association>    </resultMap>
    <select id="findAll" resultMap="accountUserMap">        select * from user a,account b where a.id = b.uid    </select></mapper>

<?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.jinke.dao.RoleDao">
    <resultMap id="roleMap" type="role">        <id property="id" column="rid"/>        <result property="rolename" column="rolename"/>        <result property="roledesc" column="roledesc"/>        <collection property="users" ofType="user">            <id property="id" column="id"/>            <result property="username" column="username"/>            <result property="sex" column="sex"/>            <result property="address" column="address"/>            <result property="birthday" column="birthday"/>        </collection>    </resultMap>
    <select id="findAll" resultMap="roleMap">        select u.*,r.id as rid,r.rolename,r.roledesc from mybatis.role as r        left outer join mybatis.user_role as ur on r.id = ur.rid        left outer join mybatis.user as u on u.id = ur.uid    </select></mapper>

<?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.jinke.dao.UserDao">
    <resultMap id="userAccountMap" type="user">        <id property="id" column="id"/>        <result property="username" column="username"/>        <result property="address" column="address"/>        <result property="sex" column="sex"/>        <result property="birthday" column="birthday"/>        <collection property="roles" ofType="role">            <id property="id" column="rid"/>            <result property="rolename" column="rolename"/>            <result property="roledesc" column="roledesc"/>        </collection>    </resultMap>
    <select id="findAll" resultMap="userAccountMap">        select u.*,r.id as rid,r.rolename,r.roledesc from mybatis.user as u        left outer join mybatis.user_role as ur on u.id = ur.uid        left outer join mybatis.role as r on r.id = ur.rid    </select>
    <select id="findById" parameterType="Integer" resultType="user">        select * from user where id = #{uid};    </select></mapper>

import com.jinke.dao.AccountDao;import com.jinke.dao.RoleDao;import com.jinke.dao.UserDao;import com.jinke.domain.Account;import com.jinke.domain.Role;import com.jinke.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.InputStream;import java.util.List;
public class MyBatisTest {
    private InputStream in;    private SqlSession sqlSession;    private UserDao userDao;    private AccountDao accountDao;    private RoleDao roleDao;
    @Before    public void init() throws Exception {        in = Resources.getResourceAsStream("SqlMapConfig.xml");        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();        SqlSessionFactory factory = builder.build(in);        sqlSession = factory.openSession();        userDao = sqlSession.getMapper(UserDao.class);        accountDao = sqlSession.getMapper(AccountDao.class);        roleDao = sqlSession.getMapper(RoleDao.class);    }
    @After    public void destroy() throws Exception {        sqlSession.commit();        sqlSession.close();        in.close();    }
    @Test    public void testFindAccount() {        List<Account> accounts = accountDao.findAll();        for (Account account : accounts) {            System.out.println(account + " " + account.getUser());        }    }
    @Test    public void testFindUser() {        List<User> users = userDao.findAll();        for (User user : users) {            System.out.println(user + " " + user.getRoles());        }    }
    @Test    public void testFindRole() {        List<Role> roles = roleDao.findAll();        for (Role role : roles) {            System.out.println(role + " " + role.getUsers());        }    }}