Hopefully, I'm not wasting anybody's time, but I tried to do my homework first.

I'm new to Hibernate, but have:

looked through Hibernate in Action
looked through Hibernate Quickly
googled
searched through these Forums

My problem is that I need to get a result set for which my gut-response says to use a Union.

When I started down the path, I had the option to use a SQL query, or use an HQL query. The trade-offs were: use SQL and write it the way you want and understand, but it will be tied to the implementation of SQL; use HQL and use the object models that the code is based on, be independent of SQL implementation, but be tied to Hibernate. I opted for the Hibernate path, to go with the object model, and be SQL-independent, and to force myself out of my comfort zone.

Now, I have gone down the path, to find (from all the resources above) that Hibernate doesn't support Union - at least not directly.

So, I'm looking for options. I'll list the ones that I see, and then give a little more detail on my query. Please provide me with guidance on the pros and cons of each option, and a suggestion for which you would do under two circumstances - 1) if time is not a consideration; 2) if the iteration has already ended, and you need to catch up fast, without sacrificing (too much) design.

My options:

go back to the beginning and just go down the SQL query path;
figure out how to resolve this using a View (as I have seen suggested);
figure out how to resolve this using the union-sbuclass (as I have seen mentioned briefly here and there);
just query the second part, and do the union within my java code;
find some other solution (as may be found through the insight of this forum).

Description of problem:
A given person may go to many different hospitals within a hospital system. At each individual hospital, they have a unique medical record number and demographic information. They are linked across the system, though, by an enterprise patient number. Therefore, I (as a patient) might have 2 rows in the patient table, one for each hospital that I visited, but each would have the same "epn". If I (as a hospital staff) queried the database, I might get back a result set that only had one of my patient instances in it (e.g., maybe due to misspelling of name in one entry). I need to take my result set, and then query back to the database to make sure that I got all instances of the patients that I am looking for (via their "epn" number).

So, I ended up with the basic query of:


So, the first select really just gives me back the patients that I already had. The second select will give me all the linked patients as long as their epn is not null (which is why I added the first select, so I don't lose those with null epn values).

insert issue here:
So, I wrote my query as above, and I get the exception below. The query goes into my PreprocessorParser correctly, but the sqlString that comes out in my QueryTranslator is missing parts (particularly right after the union keyword). I'm assuming that it is not worth anyone else's time for me proceed with info about trying to do the union, since it is documented to not work.

As noted in my options, I think that I could just do this union/merge in my java code, but I thought it would be cleaner to just bring the whole list back from the database.

Maybe I'm wrong,...maybe I'm just too idealistic,...maybe I'm just too novice.

Regardless, I'm looking for advice. Any takers?

Hibernate version: Hibernate-Version: 3.0.2

Mapping documents:
<?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 package="com.XXX.models"
default-cascade="none" auto-import="true" default-lazy="false">

<typedef name="DateTime" class="com.XXX.common.persistence.hibernate.types.DateTimeType"/>
<typedef name="SmallDateTime" class="com.XXX.common.persistence.hibernate.types.SmallDateTimeType"/>
<typedef name="SmallintToBoolean" class="com.XXX.common.persistence.hibernate.types.SmallintToBooleanType"/>
<typedef name="StringId" class="com.XXX.common.persistence.hibernate.types.StringIdentifierType"/>
<typedef name="StringIdentifier" class="com.XXX.common.persistence.hibernate.types.StringIdentifierMappedType"/>
<typedef name="string" class="com.XXX.common.persistence.hibernate.types.TrimmedStringType"/>
<typedef name="optstring" class="com.XXX.common.persistence.hibernate.types.TrimmedStringType">
<param name="OptionallyExistsInDatabase">true</param>
</typedef>

<!-- ***************************************************************** -->

<class name="FacilityPatient" table="patients"
dynamic-update="true" optimistic-lock="all" batch-size="128"
schema="dbo" catalog="his">

<composite-id name="compositeIdentity">

<key-property name="mrn" type="StringId" access="map">
<column name="mrn" sql-type="char(20)" not-null="true"/>
</key-property>

<key-property name="facilityCode" type="StringId" access="map">
<column name="facility" sql-type="char(10)" not-null="true"/>
</key-property>

</composite-id>

<many-to-one name="facility" cascade="none" insert="false" update="false">
<column name="facility" sql-type="char(10)" not-null="true"/>
</many-to-one>

<bag name="encounters" inverse="true" cascade="none" lazy="true" batch-size="128">
<key>
<column name="mrn"/>
<column name="facility"/>
</key>
<one-to-many class="Encounter"/>
</bag>

<bag name="globalDocuments"
inverse="true" cascade="none" lazy="true" batch-size="128"
persister="com.XXX.common.persistence.hibernate.persister.OneToManyPersister">
<key>
<column name="mrn"/>
<column name="facility"/>
</key>
<one-to-many class="MedicalDocument"/>
<loader query-ref="globalDocumentsForPatient"/>
</bag>

<property name="fullName" type="string">
<column name="name" sql-type="char(40)" not-null="true"/>
</property>

<property name="gpi" type="string">
<column name="gpi" sql-type="char(20)"/>
</property>

<property name="dateOfBirth">
<column name="dob" sql-type="datetime"/>
</property>

<property name="sex">
<column name="sex" sql-type="char(1)"/>
</property>

<property name="ssn" type="string">
<column name="ssn" sql-type="char(27)"/>
</property>

<property name="birthPlace" type="string">
<column name="birth_place" sql-type="char(15)"/>
</property>

<property name="address" type="string">
<column name="address" sql-type="char(50)"/>
</property>

<property name="address2" type="string">
<column name="address2" sql-type="char(50)"/>
</property>

<property name="address3" type="string">
<column name="address3" sql-type="char(50)"/>
</property>

<property name="city" type="string">
<column name="city" sql-type="char(50)"/>
</property>

<property name="state" type="string">
<column name="state" sql-type="char(50)"/>
</property>

<property name="zip" type="string">
<column name="zip" sql-type="char(20)"/>
</property>

<property name="contact" type="string">
<column name="emergency_contact" sql-type="char(40)"/>
</property>

<property name="homePhone" type="string">
<column name="hm_phone" sql-type="char(20)"/>
</property>

<property name="workPhone" type="string">
<column name="wk_phone" sql-type="char(20)"/>
</property>

<property name="church" type="string">
<column name="church" sql-type="char(25)"/>
</property>

<property name="religion" type="string">
<column name="religion" sql-type="char(5)"/>
</property>

<property name="smoker" type="string">
<column name="smoker" sql-type="char(5)"/>
</property>

<property name="oldMrn" type="string">
<column name="old_mrn" sql-type="char(20)"/>
</property>

<property name="lockedAsCharacter">
<column name="lockout" sql-type="char(1)"/>
</property>

<property name="epn" type="optstring">
<column name="epn" sql-type="char(50)"/>
</property>

</class>

.
.
.
</hibernate-mapping>


Truncated stack trace of the exception that occurs:

Listening for transport dt_socket at address: 8000
Sep 28, 2005 2:42:00 PM org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on port 8080
Starting service Tomcat-Standalone
Apache Tomcat/4.1.30-LE-jdk14

.
.
.

28 Sep 2005 14:42:26,261 | main | | | SettingsFactory | RDBMS: Microsoft SQL Server, version: 8.0.818
28 Sep 2005 14:42:26,261 | main | | | SettingsFactory | JDBC driver: i-net OPTA 2000, version: 1.03(4.14)

.
.
.

28 Sep 2005 14:43:05,119 | http8080-Processor5 | 127.0.0.1 | Test Physician 26899 | SQL | select facilitypa0_.mrn as mrn, facilitypa0_.facility as fac
ility, facilitypa0_.facility as facility32_, facilitypa0_.name as name32_, facilitypa0_.gpi as gpi32_, facilitypa0_.dob as dob32_, facilitypa0_.sex as
sex32_, facilitypa0_.ssn as ssn32_, facilitypa0_.birth_place as birth8_32_, facilitypa0_.address as address32_, facilitypa0_.address2 as address10_32
_, facilitypa0_.address3 as address11_32_, facilitypa0_.city as city32_, facilitypa0_.state as state32_, facilitypa0_.zip as zip32_, facilitypa0_.emer
gency_contact as emergency15_32_, facilitypa0_.hm_phone as hm16_32_, facilitypa0_.wk_phone as wk17_32_, facilitypa0_.church as church32_, facilitypa0_
.religion as religion32_, facilitypa0_.smoker as smoker32_, facilitypa0_.old_mrn as old21_32_, facilitypa0_.lockout as lockout32_, facilitypa0_.epn as
epn32_ from his.dbo.patients facilitypa0_ where (((facilitypa0_.mrn='26899' ))and((facilitypa0_.facility='A' )))or(((facilitypa0_.mrn='26899' ))and((
facilitypa0_.facility='B' ))union(select facilitypa1_.mrn, facilitypa1_.facility from his.dbo.patients facilitypa1_ where (facilitypa1_.epn in(select
facilitypa0_.epn from his.dbo.patients facilitypa0_ where (((facilitypa0_.mrn='26899' ))and((facilitypa0_.facility='A' )))or(((facilitypa0_.mrn='26899
' ))and((facilitypa0_.facility='B' )))))))
28 Sep 2005 14:43:05,160 | http8080-Processor5 | 127.0.0.1 | Test Physician 26899 | JDBCExceptionReporter | SQL Error: 156, SQLState: 01000
28 Sep 2005 14:43:05,160 | http8080-Processor5 | 127.0.0.1 | Test Physician 26899 | JDBCExceptionReporter | [EIGDEV62]Incorrect syntax near the keywor
d 'union'.
28 Sep 2005 14:43:05,160 | http8080-Processor5 | 127.0.0.1 | Test Physician 26899 | JDBCExceptionReporter | SQL Error: 170, SQLState: 01000
28 Sep 2005 14:43:05,170 | http8080-Processor5 | 127.0.0.1 | Test Physician 26899 | JDBCExceptionReporter | [EIGDEV62]Line 1: Incorrect syntax near ')
'.
28 Sep 2005 14:43:05,190 | http8080-Processor5 | 127.0.0.1 | Test Physician 26899 | PersistenceException |
com.XXX.common.persistence.PersistenceException: could not execute query
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1560)
at org.hibernate.loader.Loader.list(Loader.java:1540)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:791)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)


Name and version of the database you are using: see stack trace

The generated SQL (show_sql=true): see stack trace