-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
rudi_s
 Post subject: Criteria-Search with Composite-Id
PostPosted: Mon Mar 16, 2009 11:25 am 
Newbie

Joined: Tue Jan 27, 2009 6:42 am
Posts: 7
Hello,

I have the following problem with a Hibernate Criteria-Search and Restrictsions.in() on a Table with a composite-id.

I want to retrieve entities by their composite-key-class as follows:

Code:
//QcpProcessId represents the composite-id on three columns
QcpProcessId process1 = new QcpProcessId("A 75171", new BigDecimal(0), "0201");
QcpProcessId process2 = new QcpProcessId("A 75171", new BigDecimal(5), "test");

QcpProcessId[] ids = new QcpProcessId[] { process1, process2 };

Criteria criteria = session.createCriteria(QcpProcess.class);
criteria.add(Restrictions.in(QcpProcess.PROP_ID, ids));
List list = criteria.list();


The result is this stacktrace

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)

Caused by: java.sql.SQLException: ORA-01722: invalid number

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1120)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1242)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3459)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)

The generated SQL-Query is this:

Code:
select
        this_.PLAN_ID as PLAN1_67_0_,
        this_.REVISION as REVISION67_0_,
        this_.PROCESS_ID as PROCESS3_67_0_,
        this_.REVNOTE as REVNOTE67_0_,
        this_.NOTICE as NOTICE67_0_,
        this_.ORDERNO as ORDERNO67_0_,
    from
        QCP_PROCESS this_
    where
        (
            this_.PLAN_ID, this_.REVISION, this_.PROCESS_ID
        ) in (
            (
                ?, ?, ?
            ), (
                ?, ?, ?
            )
        )


When i execute this query directly on the DB, there is no error.

If I just put one id in the in-clause, regardless of which, the query works fine:

Code:
//QcpProcessId represents the composite-id on three columns
QcpProcessId process1 = new QcpProcessId("A 75171", new BigDecimal(0), "0201");

QcpProcessId[] ids = new QcpProcessId[] { process1 };

Criteria criteria = session.createCriteria(QcpProcess.class);
criteria.add(Restrictions.in(QcpProcess.PROP_ID, ids));
List list = criteria.list();


So i guess, that the mapping is ok:

Code:
<hibernate-mapping>
    <class name="com.xy.db.QcpProcess" table="QCP_PROCESS">
        <composite-id name="id" class="com.xy.db.QcpProcessId">
           
            <key-property name="planId" type="java.lang.String">
               <column name="PLAN_ID" length="16" />
           </key-property>
           
            <key-property name="revision" type="java.math.BigDecimal">
                <column name="REVISION" />
            </key-property>
           
            <key-property name="processId" type="java.lang.String">
               <column name="PROCESS_ID" length="16" />
           </key-property>
                       
        </composite-id>
        <property name="revnote" type="java.lang.String">
            <column name="REVNOTE" length="128" />
        </property>
        <property name="notice" type="java.lang.String">
            <column name="NOTICE" length="2048" />
        </property>
        <property name="orderno" type="java.math.BigDecimal">
            <column name="ORDERNO" />
        </property>
    </class>
</hibernate-mapping>



Hibernate version is 3.3.1, DB is Oracle 10.

Could anyone please give me a hint?

Thank you in advance,
Rudi
Top
 Profile  
 
latha1119
 Post subject:
PostPosted: Mon Mar 16, 2009 2:44 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Hi,
it seems based in the error code, it has something to do with the values you have provided.
Did you try to execute this in the database with both the IN values?
Or atleast with the values as in this case :-
QcpProcessId process2 = new QcpProcessId("A 75171", new BigDecimal(5), "test");

What are the datatypes in the Database defined for the columns PLAN_ID,REVISION and PROCESS_ID? I'm suspecting in the database the datatype for the column PROCESS_ID is defined as Numeric or Number equivalent but in the hibernate you are reading it and saving it as a String. How does your DAO's behave when you try to insert a Record with a String value of "test" for the PROCESS_ID column?
Top
 Profile  
 
rudi_s
 Post subject:
PostPosted: Tue Mar 17, 2009 1:15 pm 
Newbie

Joined: Tue Jan 27, 2009 6:42 am
Posts: 7
Hi latha1119,

the datatypes in the Database defined for the columns PLAN_ID,REVISION and PROCESS_ID are VARCHAR2(16), NUMBER(22) and VARCHAR2(16).

I don't think that the mapping is wrong because I can make the mentioned query, as long as i put just one object in the in-clause.

Example:

Code:
QcpProcessId process1 = new QcpProcessId("A 75171", new BigDecimal(0), "0201");

QcpProcessId[] ids = new QcpProcessId[] { process1};

Criteria criteria = session.createCriteria(QcpProcess.class);
criteria.add(Restrictions.in(QcpProcess.PROP_ID, ids));
List list = criteria.list();


No Problem!

Code:
QcpProcessId process2 = new QcpProcessId("A 75171", new BigDecimal(5), "test");

QcpProcessId[] ids = new QcpProcessId[] { process2 };

Criteria criteria = session.createCriteria(QcpProcess.class);
criteria.add(Restrictions.in(QcpProcess.PROP_ID, ids));
List list = criteria.list();


No Problem!

Code:
QcpProcessId process1 = new QcpProcessId("A 75171", new BigDecimal(0), "0201");
QcpProcessId process2 = new QcpProcessId("A 75171", new BigDecimal(5), "test");

QcpProcessId[] ids = new QcpProcessId[] { process1, process2 };

Criteria criteria = session.createCriteria(QcpProcess.class);
criteria.add(Restrictions.in(QcpProcess.PROP_ID, ids));
List list = criteria.list();


The Exception from by previous post is thrown.

Have you further ideas?


Thank you very much,
Rudi
Top
 Profile  
 
latha1119
 Post subject:
PostPosted: Tue Mar 17, 2009 2:25 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Found this article It used to be a bug I guess. Dynamic data binding order is not preserved and is causing issues as in your case.
See this:-
http://opensource.atlassian.com/project ... e/HHH-1743
But it has been resolved in 3.2.0 cr1 and you are in a higher version than that.. So wondering why you still stuck with this..

If you wish as an alternative you could do this:-
you can build Hibernate Criteria Object to do the following query:-

select * from task_function
where (task_id=3 and display_name='test1') or
(task_id=4 and display_name='test2')

Sorry could not be of much help!
Latha.
Top
 Profile  
 
rudi_s
 Post subject:
PostPosted: Wed Mar 18, 2009 12:19 pm 
Newbie

Joined: Tue Jan 27, 2009 6:42 am
Posts: 7
Hi latha,

thank you for the link. Indeed, is is the mentioned bug which is causing this problem. Finally I accomplish to configure log4j to show me the parameters for the sql-statements by setting the logLevel of org.hibernate.type to TRACE. And it tells me the following:

Code:
Hibernate:
    select
        this_.PLAN_ID as PLAN1_67_0_,
        this_.REVISION as REVISION67_0_,
        this_.PROCESS_ID as PROCESS3_67_0_,
        this_.REVNOTE as REVNOTE67_0_,
        this_.NOTICE as NOTICE67_0_,
        this_.ORDERNO as ORDERNO67_0_,
        this_.ISBLOCKED as ISBLOCKED67_0_,
        this_.ISFROZEN as ISFROZEN67_0_,
        this_.DRAW_ID as DRAW9_67_0_,
        this_.U_ID as U10_67_0_,
        this_.CRDT as CRDT67_0_,
        this_.CRBY as CRBY67_0_,
        this_.UPDT as UPDT67_0_,
        this_.UPBY as UPBY67_0_,
        this_.DBVERSION as DBVERSION67_0_
    from
        QCP_PROCESS this_
    where
        (
            this_.PLAN_ID, this_.REVISION, this_.PROCESS_ID
        ) in (
            (
                ?, ?, ?
            ), (
                ?, ?, ?
            )
        )
17:02:36,330 TRACE [main] org.hibernate.type.StringType - binding 'A 75171' to parameter: 1
17:02:36,330 TRACE [main] org.hibernate.type.StringType - binding 'A 75171' to parameter: 2
17:02:36,330 TRACE [main] org.hibernate.type.BigDecimalType - binding '0' to parameter: 3
17:02:36,330 TRACE [main] org.hibernate.type.BigDecimalType - binding '5' to parameter: 4
17:02:36,330 TRACE [main] org.hibernate.type.StringType - binding '0201' to parameter: 5
17:02:36,330 TRACE [main] org.hibernate.type.StringType - binding 'test' to parameter: 6


That means that the parameters are disordered.

Are you sure that this bug has been fixed? I'm using Hibernate 3.3.1 and have still this problem. Should I report this bug again?


Thank you,
Rudi
Top
 Profile  
 
latha1119
 Post subject:
PostPosted: Wed Mar 18, 2009 12:29 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
I would double check the hibernate version you are using. Sometimes the Hibernate JAR the Junit test points to is a different ones than the one that is bundled up in the Webapp. If you still believe yours is Hibernate 3.3.1 then submit as a Bug!

-Latha.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.