Hi there,

I am stucked with a very simple query with just 1 table. If I provide a criteria for the query, I received only the first row with the size of the array as duplicate entries (ie, if a total of 42 rows shall be found, I got the 42 rows but all from the first row.) If I dont provide a criteria for the search (ie, just list all result from the table), I get a correct result set.

Does any body know what's wrong with my code, or is this a bug from hibernate?

Appreciate for you help!!

Helpless Developer!


Hibernate version: 2.1.8

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Wed May 18 11:36:18 GMT+08:00 2005 -->
<hibernate-mapping package="com.company.dao">

<class name="FileMovementView" table="FILE_MOVEMENT_VIEW" lazy="true">
<composite-id name="caseNo" class="CaseNo" unsaved-value="none">
<key-property name="caseCourtSys" column="CASE_COURT_SYS" type="java.lang.String" />
<key-property name="caseType" column="CASE_TYPE" type="java.lang.String" />
<key-property name="caseSerNo" column="CASE_SER_NO" type="java.lang.Integer" />
<key-property name="caseYear" column="CASE_YR" type="java.lang.Integer" />
</composite-id>

<component name="caseNo" class="CaseNo" insert="false" update="false">
<property name="caseCourtSys" column="CASE_COURT_SYS"/>
<property name="caseType" column="CASE_TYPE"/>
<property name="caseSerNo" column="CASE_SER_NO"/>
<property name="caseYear" column="CASE_YR"/>
</component>
<property name="seq" column="SEQ" type="java.lang.Integer" />
<property name="outMoveIntNo" column="OUT_MOVE_INT_NO" type="java.lang.Integer" />
<property name="cmsUserId" column="CMS_USER_ID" type="java.lang.String" not-null="true" />
<property name="isOut" column="IS_OUT" type="java.lang.String" />
<property name="currLoc" column="CURR_LOC" type="java.lang.String" />
<property name="destLoc" column="DEST_LOC" type="java.lang.String" />
<property name="docIntNo" column="DOC_INT_NO" type="java.lang.Integer" />
<property name="mergeCaseFile" column="MERGE_CASE_FILE" type="java.lang.String" />
<property name="remark" column="REMARK" type="java.lang.String" />
<property name="lastUpdateDate" column="LAST_UPDATE_DATE" type="java.sql.Timestamp" />
<property name="objectType" column="OBJECT_TYPE" type="java.lang.String" />
<property name="objectDtl" column="OBJECT_DTL" type="java.lang.String" />
<set name="documentCmsLink" batch-size="3" lazy="true">
<key>
<column name="CASE_COURT_SYS"/>
<column name="CASE_TYPE"/>
<column name="CASE_SER_NO"/>
<column name="CASE_YR"/>
</key>
<one-to-many class="DocumentCmsLink"/>
</set>
</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

**please note this is a DAO object**

public static List getFileMovementViewByCaseNo(CaseNo caseNoIn)
throws HibernateException {
Session session = DaoUtil.getFactory().getSession();
//FileMovementView fileMovementResults = (FileMovementView) session.get(FileMovementView.class,caseNo);

Query q = session.createQuery("from FileMovementView as fmv " +
"where (fmv.caseNo.caseCourtSys = :a " +
"and fmv.caseNo.caseType = :b " +
"and fmv.caseNo.caseSerNo = :c " +
"and fmv.caseNo.caseYear = :d) " +
"order by seq desc" ).setMaxResults(1);
q.setParameter("a", caseNoIn.getCaseCourtSys(), Hibernate.STRING);
q.setParameter("b", caseNoIn.getCaseType(), Hibernate.STRING);
q.setParameter("c", caseNoIn.getCaseSerNo(), Hibernate.INTEGER);
q.setParameter("d", caseNoIn.getCaseYear(), Hibernate.INTEGER);

List result = q.list();
getLog().debug("******* fileMovementViewByCaseNo.size() = " + result.size());

return result;
}


Full stack trace of any exception that occurs: no exception found

Name and version of the database you are using: Oracle 8i

The generated SQL (show_sql=true):

15:29:03,431 INFO [STDOUT] Hibernate: select * from ( select this.CASE_COURT_SYS as CASE_COU1_0_, this.CASE_TYPE as CASE_TYPE0_, this.CASE_SER_NO as CASE_SER3_0_, this.CASE_YR as CASE_YR0_, this.SEQ as SEQ0_, this.OUT_MOVE_INT_NO as OUT_MOVE6_0_, this.CMS_USER_ID as CMS_USER7_0_, this.IS_OUT as IS_OUT0_, this.CURR_LOC as CURR_LOC0_, this.DEST_LOC as DEST_LOC0_, this.DOC_INT_NO as DOC_INT_NO0_, this.MERGE_CASE_FILE as MERGE_C12_0_, this.REMARK as REMARK0_, this.LAST_UPDATE_DATE as LAST_UP14_0_, this.OBJECT_TYPE as OBJECT_15_0_, this.OBJECT_DTL as OBJECT_DTL0_ from FILE_MOVEMENT_VIEW this where (this.CASE_COURT_SYS=? and this.CASE_TYPE=? and this.CASE_SER_NO=? and this.CASE_YR=?) ) where rownum <= ?

Debug level Hibernate log excerpt: no exception found

[quote="r8r"]The generated SQL (show_sql=true):

15:29:03,431 INFO [STDOUT] Hibernate: select * from ( select this.CASE_COURT_SYS as CASE_COU1_0_, this.CASE_TYPE as CASE_TYPE0_, this.CASE_SER_NO as CASE_SER3_0_, this.CASE_YR as CASE_YR0_, this.SEQ as SEQ0_, this.OUT_MOVE_INT_NO as OUT_MOVE6_0_, this.CMS_USER_ID as CMS_USER7_0_, this.IS_OUT as IS_OUT0_, this.CURR_LOC as CURR_LOC0_, this.DEST_LOC as DEST_LOC0_, this.DOC_INT_NO as DOC_INT_NO0_, this.MERGE_CASE_FILE as MERGE_C12_0_, this.REMARK as REMARK0_, this.LAST_UPDATE_DATE as LAST_UP14_0_, this.OBJECT_TYPE as OBJECT_15_0_, this.OBJECT_DTL as OBJECT_DTL0_ from FILE_MOVEMENT_VIEW this where (this.CASE_COURT_SYS=? and this.CASE_TYPE=? and this.CASE_SER_NO=? and this.CASE_YR=?) ) where rownum <= ?

Not sure where the rownum <= ? comes from, could this be the trick?

** please note the above class is incorrect, shall be the one below **


public static List fetchFileMovementViewByCaseNo(final CaseNo caseNoIn)
throws HibernateException {
final Session session = DaoUtil.getFactory().getSession();
session.get(FileMovementView.class,caseNo);
getLog().debug("******* 1() = " + caseNoIn.getCaseCourtSys());
getLog().debug("******* 2() = " + caseNoIn.getCaseType());
getLog().debug("******* 3() = " + caseNoIn.getCaseSerNo());
getLog().debug("******* 4() = " + caseNoIn.getCaseYear());

List result2 = session.createCriteria(FileMovementView.class).add(Expression.eq(FileMovementView.CASE_NO,(caseNoIn))).setMaxResults(50).list();

getLog().debug("******* yumyumyum = " + result2.size());
getLog().debug("******* fileMovementViewByCaseNo.size() = " + result2.size());

return result2;
}



16:17:59,212 DEBUG [com.company.dao.FileMovementView] ******* 1() = HH
16:17:59,212 DEBUG [com.company.dao.FileMovementView] ******* 2() = A
16:17:59,212 DEBUG [com.company.dao.FileMovementView] ******* 3() = 1248
16:17:59,212 DEBUG [com.company.dao.FileMovementView] ******* 4() = 2001
16:17:59,212 INFO [STDOUT] Hibernate: select * from ( select this.CASE_COURT_SYS as CASE_COU1_0_, this.CASE_TYPE as CASE_TYPE0_, this.CASE_SER_NO as CASE_SER3_0_, this.CASE_YR as CASE_YR0_, this.SEQ as SEQ0_, this.OUT_MOVE_INT_NO as OUT_MOVE6_0_, this.CMS_USER_ID as CMS_USER7_0_, this.IS_OUT as IS_OUT0_, this.CURR_LOC as CURR_LOC0_, this.DEST_LOC as DEST_LOC0_, this.DOC_INT_NO as DOC_INT_NO0_, this.MERGE_CASE_FILE as MERGE_C12_0_, this.REMARK as REMARK0_, this.LAST_UPDATE_DATE as LAST_UP14_0_, this.OBJECT_TYPE as OBJECT_15_0_, this.OBJECT_DTL as OBJECT_DTL0_ from FILE_MOVEMENT_VIEW this where (this.CASE_COURT_SYS=? and this.CASE_TYPE=? and this.CASE_SER_NO=? and this.CASE_YR=?) ) where rownum <= ?
16:17:59,228 DEBUG [com.company.dao.FileMovementView] ******* yumyumyum = 42
16:17:59,228 DEBUG [com.company.dao.FileMovementView] ******* fileMovementViewByCaseNo.size() = 42