MyBaties里如何多表查询

---

MyBaties里如何多表查询

---

在数据库中有表A，B，C三张表 其中表C为中间表

这里是表A

create table A(
	  id identity,
	  username varchar(20) unique not null,
	  password varchar(20) not null
);

这是表B

create table B(
 		id identity,
		rolename varchar(20) not null
);

这是表 C(中间表)

create table C(
		id identity,
		adminid bigint,
		roleid bigint,
		foreign key(adminid) REFERENCES A(id),
		foreign key(roleid) REFERENCES B(id)
);

这里是方法A

public class A{
  		private Long id;
	    @NotNull
	    //限制用户名最小及最大数值
	    @Size(min = 3 , max = 16,message = "{username.size}")
	    private String username;
	    @NotNull
	    //限制密码最小及最大数值
	    @Size(min = 8,max = 16,message = "{password.size}")
	    private String password;
    
		此处省略getAndset方法
}

这里是方法B

public class B {
	    private Long id;
	    @NotNull
	    private String rolename;
	    
		此处省略getAndset方法
	}

表C为中间表故此处没有实体类

这里是Controller

 public String getRoleid(@PathVariable long id, Model model){
       Role role= roleService.getBIdByAId(id);
        System.out.println("GetRoleid--"+role);  //这里的role的值是 null
        return "nextPage";
       
    }

这里是Service

@Override
public Role getRoleIdByAdminerId(Long aId) {
    System.out.println("RoleService--"+aId);// aid = 3
    Role role=roleRepository.findRoleIdByAdminerId(adminerid);  //这里的 role 的值为 null
    return role;
}

这里是 Repository 接口

public interface BRepository {
    Role findBIdByAId(@Param("id") long id); // id 值为 3
}

这里是 Mapper.xml

<?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="XXX.repository.BRepository">

    <resultMap type="XXX.domain.B" id="BMap">
        <id property="id" column="id" javaType="long" jdbcType="BIGINT"/>
        <result property="rolename" column="rolename"/>
    </resultMap>

	<select id="findBIdByAId" parameterType="long" resultMap="BMap">
        select
            r.id as "id",
            r.rolename as "rolename"
        from
           Role r,Adminer_Role ar,Adminer a
        where
           r.id = ar.roleid
        and
           a.id = ar.adminerid
        and
           ar.adminerid = #{id} // 这里的id 值应为 3
    </select>

</mapper>

//这个SQL 在数据库里有查询结果为

		Id | roleName
   		1  |  Admin

而Mybaties的返回结果 role 为 NULL