Hibernate version:
version 3.1.2, Jan 27, 2006
Mapping documents:
Code:
<hibernate-mapping>
<class name="mypackage.SomeTable" table="SOME_TABLE">
<meta attribute="generated-class">mypackage.SomeTableBase</meta>
<composite-id name="id" class="mypackage.SomeTableBaseId">
<meta attribute="generated-class">Id</meta>
<key-property name="id" type="java.lang.Long">
<column name="ID" />
</key-property>
<key-property name="subid" type="java.lang.Character">
<column name="SUBID" length="1" />
</key-property>
</composite-id>
<!-- Lot of other irrelevant properties here -->
</class>
</hibernate-mapping>
Relevant DB2 DDL:Code:
CREATE TABLE SCHEMA.SOME_TABLE (
"ID" BIGINT NOT NULL,
"SUBID" CHAR(1) NOT NULL,
-- Lot of irrelevant fields here.
CONSTRAINT "SOME_TABLE_PK" PRIMARY KEY ("ID", "SUBID")
);
Code between sessionFactory.openSession() and session.close():Too big. The relevant part is:
Code:
List ids = new ArrayList();
ids.add(new SomeTableBaseId(new Long("1"), new Character('A'));
ids.add(new SomeTableBaseId(new Long("1"), new Character('B'));
ids.add(new SomeTableBaseId(new Long("2"), new Character('A'));
criteria.add(Restrictions.in("id", ids));
Full stack trace of any exception that occurs:Code:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1552)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at *snip* (own code here)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;s_.SUBID) in (;<values>
at com.ibm.db2.jcc.a.rf.e(rf.java:1680)
at com.ibm.db2.jcc.a.rf.a(rf.java:1239)
at com.ibm.db2.jcc.b.jb.h(jb.java:139)
at com.ibm.db2.jcc.b.jb.a(jb.java:43)
at com.ibm.db2.jcc.b.w.a(w.java:30)
at com.ibm.db2.jcc.b.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.rf.n(rf.java:1219)
at com.ibm.db2.jcc.a.sf.gb(sf.java:1790)
at com.ibm.db2.jcc.a.sf.d(sf.java:2266)
at com.ibm.db2.jcc.a.sf.X(sf.java:508)
at com.ibm.db2.jcc.a.sf.executeQuery(sf.java:491)
at org.enhydra.jdbc.core.CorePreparedStatement.executeQuery(CorePreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 13 more
Name and version of the database you are using:IBM DB2 8.1.
The generated SQL (show_sql=true):Code:
select
this_.ID as ID0_0_,
this_.SUBID as SUBID0_0_,
(lot of irrelevant columns here)
from
SCHEMA.SOME_TABLE this_
where
(this_.ID, this_.SUBID)
in
((?, ?), (?, ?), (?, ?))
Debug level Hibernate log excerpt:Irrelevant.
Problems with Session and transaction handling?No.
The main cause is that the generated SQL is incompatible with DB2.
Well, I was just asking if this can't be done the other way? How should I create Hibernate criteria to accomplish for example the following DB2-compatible query:
Code:
select
this_.ID as ID0_0_,
this_.SUBID as SUBID0_0_,
(lot of irrelevant columns here)
from
SCHEMA.SOME_TABLE this_
where
(this_.ID = ? AND this_.SUBID = ?) OR (this_.ID = ? AND this_.SUBID = ?) OR (this_.ID = ? AND this_.SUBID = ?)
I can't use Restrictions.or because this is only limited to two Criterions, it does not support a list of Criterions.
Or is there just no way to implement it and should I go ahead with HQL?
Or are you prolly about to add/fix the DB2 compatibility for such queries?
Login
FAQ
Search