iBATIS 查询多个实例的配置

张丽梅 发布于 2013/06/18 18:04
阅读 1K+
收藏 0

ibits查询传入参数和传出参数不是同一个实体的解决方法。

代码如下:

    <typeAlias alias="cmsStatistic" type="com.geong.report.data.CmsStatistic" />
    <typeAlias alias="statistic" type="com.geong.report.data.StatisticObj" />

<!-- 结果集定义 -->
    <resultMap id="ResultMap" class="cmsStatistic">
        <result property="cId" column="c_id"/>
        <result property="cProcessDate" column="c_process_date"/>
        <result property="cProcessType" column="c_process_type"/>
        <result property="cUserid" column="c_userid"/>
        <result property="cItemType" column="c_item_type"/>
        <result property="cItemId" column="c_item_id"/>
        <result property="cIdPath" column="c_id_path"/>
        <result property="cItemTitle" column="c_item_title"/>
     </resultMap>

<!-- 统计结果集定义  人员工作量统计 -->
    <resultMap id="statisticResultMap" class="statistic">
        <result property="cUser" column="cUser"/>
        <result property="addChannels" column="addChannels"/>
        <result property="addInfos" column="addInfos"/>
        <result property="addPeriodicals" column="addPeriodicals"/>
     </resultMap>

<sql id="conditions">
      <dynamic prepend="where">
        <isNotEmpty prepend="and" property = "cProcessDate">
             cmsStatistic.c_process_date &gt; to_date('#cProcessDate#','yyyy-MM-dd')
        </isNotEmpty>
        <isNotEmpty prepend="and" property = "cEndDate">
             cmsStatistic.c_process_date &lt; to_date('#cEndDate#','yyyy-MM-dd')
        </isNotEmpty>
      </dynamic>
    </sql>

  <!-- 人员工作量统计分页查询 -->
    <select id="statisticsForPage" resultMap="statisticResultMap" parameterClass="statistic">
    select u1.c_realname as cUser, count1 as addChannels, count2 as addInfos , count3 as addPeriodicals
  from (select userid, sum(channelcount) as count1, sum(infocount) as count2 , sum(templatecount) as count3
          from (select C_USERID as userid,
                       count(*) as channelcount,
                       0 as infocount,
                       0 as templatecount
                       
                  from T_CMS_STATISTIC
                 where C_PROCESS_TYPE = 'Add'
                   and (C_ITEM_TYPE = 'Channel' or C_ITEM_TYPE = 'Topic' or
                       C_ITEM_TYPE = 'Periodical')
                <include refid="conditions"/>
                 group by C_USERID
                union
                select C_USERID as userid,
                       0 as channelcount,
                       count(*) as infocount,
                       0 as templatecount
                       
                  from T_CMS_STATISTIC
                 where C_PROCESS_TYPE = 'Add'
                   and (C_ITEM_TYPE = 'Info' or C_ITEM_TYPE = 'Advert' or
                       C_ITEM_TYPE = 'TopicInfo' or
                       C_ITEM_TYPE = 'PeriodicalInfo' or
                       C_ITEM_TYPE = 'Survey')
                <include refid="conditions"/>
                 group by C_USERID
                 union
                 select C_USERID as userid,
                       0 as channelcount,
                       0 as infocount,
                       count(*) as templatecount
                  from T_CMS_STATISTIC
                 where C_PROCESS_TYPE = 'Add'
                   and C_ITEM_TYPE = 'Template'
                 group by C_USERID
                 ) f
         group by userid) tmp,
       t_cms_user u1
 where tmp.userid = u1.c_userid
    
    </select>

这个查询出现异常:

 Check the result mapping for the 'cProcessDate' property.  
--- Cause: java.sql.SQLException: 列名无效]; SQL was [] for task [SqlMapClient operation]
DEBUG 2013-06-18 17:38:52,921 org.springframework.jdbc.datasource.DataSourceUtils: Returning JDBC Connection to DataSource
ERROR 2013-06-18 17:38:52,953 com.geong.pai.web.processor.Operation: /user/statistics/QueryForListPage的step1执行失败, 详细错误信息::org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in com/geong/report/data/mapper/CmsStatistic.xml.  
--- The error occurred while applying a result map.  
--- Check the cmsStatistic.statisticResultMap.  
--- Check the result mapping for the 'cProcessDate' property.  
--- Cause: java.sql.SQLException: 列名无效

以下是问题补充:

@张丽梅:请教各位!!!我的联系邮箱:zhangliquankelly@163.com (2013/06/18 18:05)
加载中
0
红薯
红薯

虽然不会iBatis,但这么多内容最关键的是下面这一句

Check the result mapping for the 'cProcessDate' property. 
Cause: java.sql.SQLException: 列名无效

返回顶部
顶部