mybatis的连表查询

first：创建环境

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

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

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

public class Role {
    private Integer id;
    private String role;
    
//多对多的映射关系
    private List<User> users;

    @Override
    public String toString() {
        return "Role [id=" + id + ", role=" + role + ", users=" + users + "]";
    }

    public Integer getId() {
        return id;
    }

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

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }
}

public class User implements Serializable{
    private Integer id;
    private String name;
    private List<Integer> idList;
    //一对多的映射关系，主表实体包含从表list
    private List<Account> accounts;
    
    //多对多的映射关系，
    private List<Role> roles;
    
    public List<Role> getRoles() {
        return roles;
    }
    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }
    public List<Account> getAccounts() {
        return accounts;
    }
    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }
    public List<Integer> getIdList() {
        return idList;
    }
    public void setIdList(List<Integer> idList) {
        this.idList = idList;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", roles=" + roles + "]";
    }
    
    
}

 

一，一对一

特别：mybatis中将多对一，看做一对一

<!-- 定义account和user的resultMap -->
        <resultMap type="account" id="accountUserMap">
        <id property="id" column="aid"></id>
        <result property="uid" column="user_id" ></result>
        <result property="money" column="money" ></result>
        <!--    定义封装User里的内容 ,即外键的内容 -->
        <!-- javaType="user" 这里本来应该写全类名，但是主配置中写了别名 -->
        <association property="user" column="user_id" javaType="user">
        <id property="id" column="id"></id>
        <result property="name" column="name" ></result>
        
        </association> 
        </resultMap>

    <!-- 一对一，或者多对一  配置查询所有  -->
        <select id="findAll" resultMap="accountUserMap">
                select u.*,a.id as aid,a.user_id ,a.money from my_account as a,user as u where a.id=u.id ;
        </select>
        

二，一对多

<!-- 一对多 -->
    <resultMap type="user" id="userAccountMap">
        <id property="id" column="id"></id>
        <result property="name" column="name" ></result>
        
        <!--    定义封装accounts里的内容 ,即外键的内容 -->
        <!-- ofType="account" 这里本来应该写全类名，但是主配置中写了别名 -->
        <collection property="accounts" ofType="account">
            <id property="id" column="aid"></id>
            <result property="uid" column="user_id" ></result>
            <result property="money" column="money" ></result>
        </collection>
        
        </resultMap>
        
        
        <select id="findAllDuo" resultMap="userAccountMap">
                select u.*,a.id as aid,a.user_id ,a.money from my_account as a,user as u where a.id=u.id ;
        </select>
        

三，多对多

<!-- 多对多 -->
        
        <resultMap type="user" id="userRoleMap">
        <id property="id" column="id"></id>
        <result property="name" column="name" ></result>
        
        <!--    定义封装Roles里的内容 ,即外键的内容 -->
        <!-- ofType="account" 这里本来应该写全类名，但是主配置中写了别名 -->
        <collection property="roles" ofType="role">
            <id property="id" column="rid"></id>
        
            <result property="role" column="role" ></result>
        </collection>
        
        </resultMap>
        
        
        <select id="findAllManyTOMany" resultMap="userRoleMap">
                select u.*,r.id as rid, r.role from user as u left outer join myusertorole ur on u.id=ur.user_id left outer join 
                  my_role r on r.id=ur.role_id
             
        </select>

 四，补充：JNDI数据源

存储模式：