NonUniqueResultException: Problem with generated SQL on composite-id mapping.
I would like to select one document by the document id.
I get an error with the following code:
Criteria lCriteria = lSession.createCriteria(Document.class);
Document lDocumentExample = new Document();
lDocumentExample.setDocumentID(aDocumentID);
lCriteria.add(Example.create(lDocumentExample));
lDocument = (Document) lCriteria.uniqueResult();
But it works with the following code:
lDocument = (Document) lSession.get(Document.class, aDocumentID);
Why does the first statement generate the following bad SQL? Am I mapping something incorrectly?
select this_.documentID as documentID0_, this_.Title as Title0_0_, this_1_.Text as Text1_0_ from Document this_ inner join DocumentText this_1_ on this_.documentID=this_1_.DocumentID where (1=1)
SQL:
drop table Document cascade constraints
drop table DocumentText cascade constraints
drop table Entity cascade constraints
drop sequence hibernate_sequence
create table Document (
DocumentID varchar2(255) not null,
Title varchar2(255),
primary key (DocumentID)
)
create table DocumentText (
DocumentID varchar2(255) not null,
Text varchar2(255) not null,
primary key (DocumentID)
)
create table Entity (
EntityID varchar2(255) not null,
SurfaceForm varchar2(255) not null,
Offset number(10,0) not null,
Length number(10,0) not null,
Relevance number(10,0),
primary key (EntityID)
)
alter table DocumentText add constraint FKB1FBD908EE105D6C foreign key (DocumentID) references Document
create sequence hibernate_sequence
Hibernate version: 3.0rc1
Mapping documents:
DocumentID Mapping:
<hibernate-mapping package="XXXXXX">
<class name="DocumentID" table="Document">
<id name="documentID" column="DocumentID" type="string">
<generator class="native" />
</id>
</class>
</hibernate-mapping>
Document Mapping:
<hibernate-mapping package="XXXXXX">
<class name="Document" table="Document">
<composite-id name="documentID" class="DocumentID">
<key-property name="DocumentID"/>
</composite-id>
<property name="title" type="string">
<column name="Title" not-null="false"/>
</property>
<join table="DocumentText">
<key column="DocumentID" />
<property name="documentImage" type="string" lazy="true">
<column name="Text" not-null="true"/>
</property>
</join>
</class>
</hibernate-mapping>
EntityID Mapping:
<hibernate-mapping package="XXXXXX">
<class name="EntityID" table="Entity">
<id name="entityID" column="EntityID" type="string">
<generator class="native" />
</id>
</class>
</hibernate-mapping>
Entity Mapping:
<hibernate-mapping package="XXXXXX">
<class name="TaggableEntity" table="Entity">
<composite-id name="entityID" class="EntityID">
<key-property name="EntityID"/>
</composite-id>
<property name="surfaceForm" type="string">
<column name="SurfaceForm" not-null="true"/>
</property>
<property name="byteOffset" type="integer">
<column name="Offset" not-null="true"/>
</property>
<property name="tagByteLength" type="integer">
<column name="Length" not-null="true"/>
</property>
<property name="relevance" type="integer">
<column name="Relevance" not-null="false"/>
</property>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
int docID = 77;
DocumentID lDocumentID = new DocumentID();
lDocumentID.setDocumentID(docID);
Session session = HibernateUtil.currentSession();
Transaction tx = session.beginTransaction();
XXXXXXDocumentDAO lXXXXXXDocumentDAO = new XXXXXXDocumentDAO();
Document lDocument = lXXXXXXDocumentDAO.getDocumentByDocumentId(lDocumentID);
out.println("DocumentID: " + lDocument.getDocumentID() + "<br/>");
out.println("DocumentImage: " + lDocument.getDocumentImage() + "<br/>");
out.println("DocumentText: " + lDocument.getDocumentText() + "<br/>");
public class XXXXXXDocumentDAO implements DocumentDAO {
public XXXXXXDocumentDAO() {
HibernateUtil.currentSession();
HibernateUtil.beginTransaction();
}
public Document getDocumentByDocumentId(DocumentID aDocumentID) throws InfrastructureException {
Session lSession = HibernateUtil.currentSession();
Document lDocument = null;
try {
Criteria lCriteria = lSession.createCriteria(Document.class);
Document lDocumentExample = new Document();
lDocumentExample.setDocumentID(aDocumentID);
lCriteria.add(Example.create(lDocumentExample));
lDocument = (Document) lCriteria.uniqueResult();
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
return lDocument;
}
}
Full stack trace of any exception that occurs:
xxxxxx.exceptions.InfrastructureException: org.hibernate.NonUniqueResultException: query did not return a unique result: 140
at xxxxxxxxxxx.getDocumentByDocumentId(XXXXXXXXXDocumentDAO.java:37)
at xxxxxxxxxxx.doGet(XXXXXServlet.java:80)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:825)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:738)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:526)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.hibernate.NonUniqueResultException: query did not return a unique result: 140
at org.hibernate.impl.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:582)
at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:432)
at XXXXXXXXXDocumentDAO.getDocumentByDocumentId(XXXXXXXXXXXXDocumentDAO.java:35)
... 17 more
Name and version of the database you are using: Oracle 9i
The generated SQL (show_sql=true):
select this_.documentID as documentID0_, this_.Title as Title0_0_, this_1_.Text as Text1_0_ from Document this_ inner join DocumentText this_1_ on this_.documentID=this_1_.DocumentID where (1=1)
Debug level Hibernate log excerpt: Just the facts...
14:46:50,808 WARN HbmBinder:419 - Could not perform validation checks for component as the class xxxxxxxxxxxxx.DocumentID was not found
14:46:50,949 WARN HbmBinder:419 - Could not perform validation checks for component as the class xxxxxxxxxxxxx.EntityID was not found
|