描述一下问题:
已知有两个表,一个是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严格意义上来说对应的是结果集里面的列名,而不是数据库中的列,比如起别名的话就对应的是别名,不是原来的列,切记切记!!!
欢迎关注:雄雄的小课堂