Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Named query returning repeating results
PostPosted: Wed Jan 31, 2007 12:34 pm 
Newbie

Joined: Mon Sep 18, 2006 6:34 am
Posts: 18
Hi,

I'm having a problem with the results of a named query.

When I run the query from the mapping file below in query analyzer with the parameters substituted from the values set in the java code, I get 104 records back, each of them unique. However, when I run the query as a named query in:

Code:
public ReportVO[] getReportWhereClientNameLike(
            final String gcliNm, final Date startDate, final Date endDate) {

        final String namedQuery =
            "us.dc.connect.dao.reportByClientName";

        Query query =
            HibernateExtranetSession.getSessionFactory().getCurrentSession().
            getNamedQuery(namedQuery);
        query.setString("clientName", gcliNm);
        query.setDate("startDate", startDate);
        query.setDate("endDate", endDate);

        List list = query.list();
        return convertToVOArray(query.list());
    }


I get back 104 ReportVO entries in the ReportVO[] array, but all ReportVOs are the same and contain the same data.

I've tried using the getCurrentSession().createSQLQuery(String) method, passing in the query text but that makes no difference.

Has anyone seen this before? If so, is there a fix?

Hibernate version: 3.2

Mapping documents:
Report.hbm.xml:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="us.dc.connect.vo.extranet.ReportVO" mutable="false">
      <composite-id>
            <key-property name="gcliNm" />
            <key-property name="polNum" type="us.dc.connect.dao.hibernateutil.TrimmedString" />
       </composite-id>
       <property name="ccfileRcvdDtm" />
       <property name="ccfileEvntDsc" />
        <property name="ccfileEvntStatDsc" />
        <property name="ccfileMbrCnt" />
        <property name="ccfile10pctChgInd" />
        <property name="ccpflIsActiveInd" />
     </class>
     <sql-query name="us.dc.connectivity.dao.reportByClientName">
        <return alias="Report" class="us.dc.connect.vo.extranet.ReportVO" />
        <![CDATA[
           SELECT client_t.gcli_nm AS gcliNm
             , pol_t.pol_num AS polNum
             , cc_file_t.ccfile_rcvd_dtm AS ccfileRcvdDtm
             , cc_file_event_t.ccfile_evnt_dsc AS ccfileEvntDsc
             , cc_file_event_status_type_t.ccfile_evnt_stat_dsc AS ccfileEvntStatDsc
             , cc_file_t.ccfile_mbr_cnt AS ccfileMbrCnt
             , cc_file_t.ccfile_10pct_chg_ind AS ccfile10pctChgInd
             , cc_profile_t.ccpfl_is_active_ind AS ccpflIsActiveInd
         FROM client_t
             INNER JOIN pol_t ON
              client_t.gcli_id = pol_t.gcli_id
              INNER JOIN cc_profile_t ON
                  pol_t.gcli_id = cc_profile_t.gcli_id
                  AND pol_t.pol_id = cc_profile_t.pol_id
                  INNER JOIN cc_file_t ON
                      cc_profile_t.gcli_id = cc_file_t.gcli_id
                      AND cc_profile_t.pol_id = cc_file_t.pol_id
                      INNER JOIN cc_file_event_history_t ON
                          cc_file_t.gcli_id = cc_file_event_history_t.gcli_id
                          AND cc_file_t.pol_id = cc_file_event_history_t.pol_id
                          AND cc_file_t.ccfile_seq_num = cc_file_event_history_t.ccfile_seq_num
                          INNER JOIN cc_file_event_t ON
                              cc_file_event_history_t.ccfile_evnt_cd = cc_file_event_t.ccfile_evnt_cd
                          INNER JOIN cc_file_event_status_type_t ON
                              cc_file_event_history_t.ccfile_evnt_stat_cd = cc_file_event_status_type_t.ccfile_evnt_stat_cd
         WHERE
             ((cc_file_event_history_t.ccfile_evnt_cd = 'CCLOAD' AND (cc_file_event_history_t.ccfile_evnt_stat_cd = 'SUCCESS' OR cc_file_event_history_t.ccfile_evnt_stat_cd = 'WTHERROR'))
             OR (cc_file_event_history_t.ccfile_evnt_cd = 'LD' AND (cc_file_event_history_t.ccfile_evnt_stat_cd = 'SUCCESS' OR cc_file_event_history_t.ccfile_evnt_stat_cd = 'WTHERROR'))
             OR (cc_file_event_history_t.ccfile_evnt_stat_cd = 'FAILURE')
             OR (cc_file_event_history_t.ccfile_evnt_stat_cd = 'SYSPROC'))
             AND cc_file_event_history_t.ccfile_evnt_strt_dtm = (
                 SELECT MAX(ccfile_evnt_strt_dtm)
                 FROM cc_file_event_history_t
                 WHERE ((cc_file_event_history_t.ccfile_evnt_cd = 'CCLOAD' AND (ccfile_evnt_stat_cd = 'SUCCESS' OR ccfile_evnt_stat_cd = 'WTHERROR'))
                     OR (cc_file_event_history_t.ccfile_evnt_cd = 'LD' AND (ccfile_evnt_stat_cd = 'SUCCESS' OR ccfile_evnt_stat_cd = 'WTHERROR'))
                     OR (ccfile_evnt_stat_cd = 'FAILURE')
                     OR (ccfile_evnt_stat_cd = 'SYSPROC'))
                     AND gcli_id = cc_file_t.gcli_id
                     AND pol_id = cc_file_t.pol_id
                     AND ccfile_seq_num = cc_file_t.ccfile_seq_num)
               AND cc_file_t.ccfile_rcvd_dtm BETWEEN :startDate AND :endDate
             AND client_t.gcli_nm LIKE :clientName
        ]]>
     </sql-query>
</hibernate-mapping>


Name and version of the database you are using: MS SQL Server 2000 SP4
Code:


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.