mybatis中,collection配置后查询只显示一条记录
【摘要】 描述一下问题:已知有两个表,一个是user表,一个是address,一(user)对多(address)的关系,在user的实体类里面写属性:private List<Address> addressList = new ArrayList<Address>(); public List<Address> getAddressList() { return addressList; } ...
描述一下问题:
已知有两个表,一个是user表,一个是address,一(user)对多(address)的关系,在user的实体类里面写属性:
private List<Address> addressList = new ArrayList<Address>();
public List<Address> getAddressList() {
return addressList;
}
public void setAddressList(List<Address> addressList) {
this.addressList = addressList;
}
然后写了一个测试方法,根据用户的编号查询地址信息,一个用户对应多个地址。
配置如下:
<resultMap type="User" id="userAddMap">
<id property="id" column="id"/>
<result property="userName" column="userName"/>
<result property="userCode" column="userCode"/>
<result property="userPassword" column="userPassword"/>
<result property="gender" column="gender"/>
<result property="birthday" column="birthday"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<result property="userRole" column="userRole"/>
<result property="createdBy" column="createdBy"/>
<result property="creationDate" column="creationDate"/>
<result property="modifyBy" column="modifyBy"/>
<result property="modifyDate" column="modifyDate"/>
<result property="roleName" column="roleName"/>
<collection property="addressList" ofType="Address">
<id property="aid" column="id"/>
<result property="contact" column="contact"/>
<result property="addressDesc" column="addressDesc"/>
<result property="postCode" column="postCode"/>
<result property="tel" column="tel"/>
<result property="createdBy" column="createdBy"/>
<result property="creationDate" column="creationDate"/>
<result property="modifyBy" column="modifyBy"/>
<result property="modifyDate" column="modifyDate"/>
<result property="userId" column="userId"/>
</collection>
</resultMap>
sql映射如下:
<select id="findUserByidAndAddress" parameterType="Integer" resultMap="userAddMap">
SELECT u.*,a.id,a.contact,a.addressdesc,a.postCode
FROM smbms_user u,smbms_address a
WHERE u.id = a.userid AND u.id = #{id}
</select>
看起来没有任何问题吧,写了个测试方法:
@Test
public void findUserByidAndAddress(){
SqlSession sqlSession = utils.getSqlSession();
User user = sqlSession.getMapper(IUserDao.class).findUserByidAndAddress(1);
List<Address> addressList = user.getAddressList();
for (Address address : addressList) {
System.out.println(address.getAddressDesc());
}
}
运行结果如图所示:
可是数据库中却有三条记录:
经过自己琢磨,不行。
然后上网查了下,原来是数据库中两个表中的主键都是id,如果配置collection一对多关联的话需要改别名,我就试着改了下,发现,可以了。
改过之后的映射文件如下:
<resultMap type="User" id="userAddMap">
<id property="id" column="id"/>
<result property="userName" column="userName"/>
<result property="userCode" column="userCode"/>
<result property="userPassword" column="userPassword"/>
<result property="gender" column="gender"/>
<result property="birthday" column="birthday"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<result property="userRole" column="userRole"/>
<result property="createdBy" column="createdBy"/>
<result property="creationDate" column="creationDate"/>
<result property="modifyBy" column="modifyBy"/>
<result property="modifyDate" column="modifyDate"/>
<result property="roleName" column="roleName"/>
<collection property="addressList" ofType="Address">
<id property="aid" column="aid"/>
<result property="contact" column="contact"/>
<result property="addressDesc" column="addressDesc"/>
<result property="postCode" column="postCode"/>
<result property="tel" column="tel"/>
<result property="createdBy" column="createdBy"/>
<result property="creationDate" column="creationDate"/>
<result property="modifyBy" column="modifyBy"/>
<result property="modifyDate" column="modifyDate"/>
<result property="userId" column="userId"/>
</collection>
</resultMap>
sql语句如下:
<select id="findUserByidAndAddress" parameterType="Integer" resultMap="userAddMap">
SELECT u.*,a.id as aid,a.contact,a.addressdesc,a.postCode
FROM smbms_user u,smbms_address a
WHERE u.id = a.userid AND u.id = #{id}
</select>
注意:resultMap中的property对应的是实体类里面的属性,而column严格意义上来说对应的是结果集里面的列名,而不是数据库中的列,比如起别名的话就对应的是别名,不是原来的列,切记切记!!!
欢迎关注:雄雄的小课堂
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)