项目场景:
执行两个表之间的一对多查询出现的错误和解决方案
问题描述:
使用嵌套结果的查询方法查询时,查询出来的集合有对象,对象中的某一个或多个字段值为null <!-- 根据编号查询用户及对应的订单 -->
<!-- 一对多:嵌套结果 -->
<select id="getUsersById2" parameterType="Integer" resultMap="UserWithCustomerMap2">
select u.*,c.cusID as customer_id,c.address
from sys_user u,customer c
where u.userID=c.userID
and u.userID=#{userID}
</select>
<resultMap type="user" id="UserWithCustomerMap2">
<id property="userID" column="userID"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="userPass" column="userPass"/>
<result property="userTel" column="userTel"/>
<result property="userAddress" column="userAddress"/>
<result property="userImg" column="userImg"/>
<result property="userState" column="userState"/>
<collection property="customerList" ofType="customer">
<id property="cusID" column="cusID"/>
<result property="cusName" column="cusName"/>
<result property="createID" column="createID"/>
<result property="source" column="source"/>
<result property="industry" column="industry"/>
<result property="cusLevel" column="cusLevel"/>
<result property="linkman" column="linkman"/>
<result property="phone" column="phone"/>
<result property="mobile" column="mobile"/>
<result property="zipCode" column="zipCode"/>
<result property="address" column="address"/>
<result property="createTime" column="createTime"/>
</collection>
</resultMap>
原因分析:
打印结果为空应该考虑的问题:- 可能是collection标签或者里的column属性值不对
- sql语句的错,复制语句到数据库中调试
解决方案:
修改sql语句为:select u.*,c.* from sys_user u,customer c where u.userID=c.userID and u.userID=#{userID}若select关键词后只指定个别字段那么查询出来的对象只有指定的字段有值