Hi,

I have a class PnPrscp300 that has two columns for the primary key. A mpiKey and a version number. The version column is a timestamp that is used in order to keep a history of the data.

I am trying to do a lookup for a given mpiKey and the latest version number. ie. WHERE VERSION IN (SELECT MAX(VERSION) FROM TABLE. How, can I add a where clause to the composite-id so I don't have to perform two calls to the database? One to get the max version number and the other to load the PnPrscp300 object.

Here is my current code and configuration:

Note: the version date field has been hard coded for now.

Hibernate version: 2.1.4

Mapping documents:

<hibernate-mapping>
<class name="com.domain.PnPrscp300" table="PN_PRSCP_300">
<composite-id name="comp_id" class="com.domain.PnPrscp300PK">
<key-property
name="mpiKey"
column="MPI_KEY"
type="java.lang.String"
length="16"
/>
<key-property
name="version"
column="VERSION"
type="java.sql.Timestamp"
length="26"
/>
</composite-id>

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

public PnPrscp300 find(String prescriptionKey) throws PersistenceException {

PnPrscp300PK pk = new PnPrscp300PK();

pk.setMpiKey(prescriptionKey);

pk.setVersion(new Date("1970-01-01 00:00:00.001000"));
PnPrscp300 prescription = null;

Session session = HibernateUtil.getSession();

try {
prescription = (PnPrscp300)session.load(PnPrscp300.class,pk);
} catch (HibernateException ex) {
throw new PersistenceException(ex);
}
return prescription;
}

Thanks in advance!

why don't you use timestamp or version tag in mapping file?

Hi anthony,

Thanks for the response. I think you pointed me in the right direction. I have the Hibernate in Action book and I found a reference on chapter 4 and it looks like this is what I'm looking for. Do you know if this will always give me the latest version?

Where would I go about finding more info on configuring the mapping files? I looked at the DTD but some documentation would really help.

I tried the following and it fails with a composite-id validation exception.

<composite-id name="comp_id" class="org.albertawellnet.pin.domain.PnPrscp300PK"
>
<key-property
name="mpiKey"
column="MPI_KEY"
type="java.lang.String"
length="16"
/>
<timestamp
name="version"
column="VERSION"

/>
</composite-id>

Any ideas on what I am doing wrong? Thanks again.

look at this, i think it is what you need

http://blog.hibernate.org/cgi-bin/blosx ... rytriggers

The article describes my problem exactly. It is a legacy database that uses a version timestamp for history.

However, christian talks about using triggers for the insert. He didn't really mention how to access the data.

I am trying to find a way to do a load with a specified key and the latest version. These two fields make up my primary key but I'm wondering if maybe I could do something like this.

<id
name="mpiKey"
type="java.lang.String"
column="MPI_KEY"
length="16"
>
<generator class="identity" />
</id>

<timestamp name="version" column="VERSION" />

instead of defining the two fields as a composite id.

If you _always_ need the most up-to date version, you could do this with class-level where attributes in the mapping. I can't elaborate much more probably later - maybe have a look yourself and experiment how this can be used.

Thanks for the tip. I've tried a few different combinations with the where= in the class name but it doesn't seem to get added to the query.

Here is my current mapping:

<class
name="org.pin.domain.PnPrscp300"
table="PN_PRSCP_300"
where="version=(select max(version)"
>

<id
name="mpiKey"
type="java.lang.String"
column="MPI_KEY"
length="16"
>
<generator class="identity" />
</id>

<timestamp name="version" column="VERSION" />

Is there anything special I have to do to get my lookup to use the where clause? I've also tried it with "WHERE VERSION IN (SELECT MAX(VERSION)"

Hm, does the where attribute not get added when you do for example a "from Class" query?

If not, this might be a bug ...

I'm relatively new to Hibernate so I'm sure it's probably something I'm doing wrong.

Here is how I'm executing the load:

public PnPrscp300 find(String prescriptionKey) throws PersistenceException {
PnPrscp300 prescription = null;

Session session = HibernateUtil.getSession();

try {
prescription = (PnPrscp300)session.load(PnPrscp300.class,prescriptionKey);
} catch (HibernateException ex) {
throw new PersistenceException(ex);
}
return prescription;
}

It looks okay to me and it works for records that have only one version. When a record has a history, i get a Hibernate exception saying that more than one row has been returned. Looking at the SQL I do not see the where clause added.

Thanks for all your help!

Try doing it with a find "from PnPrsc300 p where p.id = key" ...

Thanks so much guys.

I am getting very close now. I was able to get back all of the objects with the following code based on your suggestion.

Query query = session.createQuery("from PnPrscp300 p where p.mpiKey = :prescriptionKey");
query.setString("prescriptionKey", prescriptionKey);
List results = query.list();

I can then iterate through the results and pick the one with the latest version. I would like to be able to add a select max version to the where clause with this call but I'm still playing with it. Does anybody know how to do this with HQL?

BTW, i would not be able to add the where clause at the class level mapping attribute as we sometimes need to retrieve the history and adding the where clause at that level will always add the where clause. So it will have to be done within the code.

Just so everyone knows, the class mapping where clause was NOT added on the load but was added on the find(). I'm not sure if this is operating as designed or if it is a bug. Does anybody on the Hibernate Team know if the load() was intended to be dynamic? ie. does the primary key have to be established before the load is executed? and is the where class level attribute NOT supposed to be added on the load?

Also, I just wanted to say how great I think the Hibernate framework is and how much I appreciate all of the assistance from the Hibernate Team. It is one of the primary reasons we chose to use the Hibernate framework.

Hey guys,

I just got the query to work. I found a little help from another post. : )

http://forum.hibernate.org/viewtopic.ph ... t=load+max

Here is what the code looked like in the end for anyone else that runs into this problem:

Query query = session.createQuery("from PnPrscp300 p where p.mpiKey = :prescriptionKey and p.version = (select max(q.version) from PnPrscp300 q where q.mpiKey = p.mpiKey)");

query.setString("prescriptionKey", prescriptionKey);

List results = query.list();

and the .hbm.xml looks like this:

<id
name="mpiKey"
type="java.lang.String"
column="MPI_KEY"
length="16"
>
<generator class="identity" />
</id>

<property name="version"
column="VERSION"
type="java.sql.Timestamp"
length="26"
/>

I still would like to have someone answer the other questions from my previous post though just for personal interest. ie. why does the class level where clause not get added on a load()? and is this the only way to to solve the problem or can the primary key on a load be dynamic?

Thanks.

Okay, load() takes a primary key value, which is of course the single identifier for your class. So adding a where-clause here would make no sense.

In fact, if your version column is part of the primary key, it gets really difficult to automate this any way with hibernate - you have to do it manually like you are doing now, in querys.