At first I am sorry for bad English, it's not my mother tongue. But I will try my best...

Basic things...
Hibernate version: 3.0.5
Using: the Criteria API (very important, because I generate the queries dynamically!)
Database: a Lotus Domino database (connected through a dominoJDBC driver)

Hi! =)

I already worked with Hibernate, but I think I am still new to it anyway. So I searched for the answer of my question in the tutorial and in this forum. But unfortunately I could'nt find any helping topic.

So I ask you.

To avoid misunderstandings: I already have another solution, but it takes too long. That's why I ask my question anyhow.

In short: I'd like to know, how to create and configure a Criteria which represents some kind of INNER JOIN between two tables/views.

In long: There is a database table called "Ticket" (by the way: I work with the database of a Trouble Ticket System; it's not common, it is developed and used in the company I am working for). The data in the table represents trouble tickets. These trouble tickets are historized by adding new datasets to the table.
So if there is a change of a trouble ticket's attributes a new ticket will be inserted into the table instead of updating the dataset. That is done by
a Lotus Notes frontend. So that's not my problem yet.

I "only" want to (have to) read the data with a Java application. That ain't no problem. I only append some dynamically generated Criterions to a Criteria and get my tickets.

BUT: I also get every historized version of a trouble ticket. The big problem is, that I can't differ between the old versions and the latest version of a ticket (that is internally managed by the Lotus Domino database). Even the unique identifier "NoteID" (generated by the database) can't help, because it is not consecutively numbered.

However, I got some kind of solution: There is a detour.
The database offers a view that only determines the latest trouble tickets.

The 2nd BUT: From that view I don't get all the attributes of a trouble ticket I need.

So I did the following in my application: With help of the Hibernate Criteria API I ask the "Ticket" table for all trouble tickets I want to have. Here I get -as I said- the latest and the older versions of the datasets. Afterwards I filter the old tickets out by iterating every ticket (old and new) and comparing the NoteID with the NoteIDs I get from the view (as I said, in that view only the latest versions are fetched). Therefor I introduced a dummy.

In the follwing you can see an extract of my hibernate-mapping-xml's:


Here you can see some Java code:

That works really fine. But it takes too long time!

I would prefer to use something what is similar to the following SQL-code (only an example!):

Or something like the following SQL-code:


I hope you understand my request. Furthermore I hope this is possible to implement with the Criteria API.

Thanks a lot!
Best regards from Hamburg, Germany!

Arvid.

EDIT: Wow, my English really buggy... ;-) Next time I should order my thoughts and words first. But I won't try to correct my whole text now.

Hi,

Here's an example (read the catch info at the end):

criteria = getHibernateSession()
.createCriteria(Parent.class);
criteria = criteria.createAlias("children", "c");
criteria.add(Expression.eq("c.childId", 128));
walker = criteria.list().iterator();

Parent class looks like this:

Parent {
long parentId;
Set<Child> children;//many-to-one mapping: child -> parent
}

Child {
long childId;
}

This query returns one parent and one child (the one with id=128).

The catch is that if parent does not have a collection of children (no join relationship), then this does not work (you will get an error saying that children is not a property of Parent class).

HTH,

Bratek

Actually I don't have a many-to-one-mapping. It's more a kind of one-is-the-same-as-one-mapping....
But nevertheless... It maybe will work, I think.

I'll try tomorrow . Thanks a lot!

Best regards!
Arvid.

PS: I guess "children" (the first parameter of the createAlias method in your example) shall be the name of the second table. Thanks again!

Actually, children is the name of the collection in Parent class. That collection holds elements from child table (which could be called CHILD, for example). Hibernate criteria work with objects and their properties, not table names. Hence the catch.

Bratek

Mhm.... But exactly that is my problem: There is no relation of that kind. No parents and no childs....
Does that mean my problem isn't solved yet? (Or am I too stupid to understand?)

I have no one-to-one-mapping, no many-to-one-mapping and no many-to-many-mapping.


Ok, let me summarize my situation again..... It's a bit complicated (also for me).

There is a table and a view on that table.

The table has about 100.000 different objects. Nearly every object has a couple of duplicates (with another ID). Those are stored in that table too. So the real count of datasets is maybe about half a million... Maybe more or less, that doesn't matter...
However, only one of an object's duplicates is the one I need. The datasets have no fields telling me, that they are the right or wrong dataset copy or variant. And the objects' IDs also don't help me, because the IDs are in no order.

Besides that table there is a view. That view filters out all duplicates I don't need. (Please, don't ask why and how. It is as it stands.) Thus using that view is the only way for me to get the right objects.

So I do the following to get "my" objects and filter out the wrong duplicates:
At first I fetch a few (or more) datasets inclusive their duplicates from the table.
Afterwards I iterate all datasets found in the table. During that iteration I fetch every dataset from the view whose ID is the same as the one from the current dataset (coming out of the table). See my Java code extract above for details.

So what I wanne do is something like:

That SQL statement I want to reproduce with Criteria and Criterion objects.

I repeat: The objects in the table have NO elements of the objects in the view. The only relation is that both objects have the same ID.

I know it's a complicated database design... But I can't change anything.
And actually now I am a bit confused...

Thank you for all your help!
Arvid.

Take a look at DetachedCriteria class (there are examples on the net on how to use those). Also, if all else fails, you can just write HSQL and get the data you need. However, if your Hibernate schema mapping does not include parent-child relationships between the selected tables, you will not get back an object hierarchy, just a set of random data.

Bratek

The DetachedCriteria isn't what I am looking for too...
As you can see in the Java code extract of my first post, there already is a Session I can get a Criteria from. I don't need to get Criteria in the "detached mode".

HSQL is not as flexible and dynamic as Criteria is. That's the reason I chose Hibernate. Otherwise I also could implement the queries with SQL.

Well, I think I should give up.... Actually I do have a solution. It is just too slow, because of the iteration of the found datasets and the repeated database access. But that seems to be better than nothing, I think.....

So, I let it be.

Thanks for time and effort anyhow! =)

Best regards from sunny Hamburg, Germany! (In fact it is not sunny! It's damned raining out here...)

Bye.
Arvid.

Have you thought about creating a view of the data and using that to query from hibernate?

No, I don't want to create a view on that table. That view already exists and I don't have any influence on the whole database. Neither the tables nor the views I am allowed to change...

I don't know how.. The view is able to distinguish between the table's objects I want to have and those I don't want to have. There are no fields I can use to do the same. The view propably uses some fields from the Lotus Domino database I can't access via SQL... ??? I really don't know.
For me it is a miracle.

Anyway... I just do it like I already do it... If anybody has a better idea I would be thankful. Otherwise I won't cry. :-)

Thanks for all!

Greetings from Hamburg.
Arvid.

Hi, it is just an Idea....

You could use the polymorphism and build a table per class model. you use your ticket table as the base entity (Ticket.class) and build a subclass (TicketUptodate) and map all the column to your baseclass.

Than you can create criteria for the TicketUptodate class an hibernate will join the baseclass (Ticket.class) table to get the values. That will be an INNER JOIN.

I didn't try but i guess it could work


simon

mhm... well, that's an idea....
I will try it during this week. Maybe on Thursday I can do it.....

I will also post the results here, if you are interested...

Thank you!

Macht's gut! / Bye.
Arvid.