Hi all,

First of all this is my first post on this forum ;)

I'm working on genereating a criteria that will return a list of data from two joined tables.
The tables involved are:
Domain (pk id, fk ppo_id, name)
Workmate (pk id, fk domain_id, fk ppo_id, name, ...)

So I need a statement like this:



this statement will vary depending on input filters
(domain name, search string)

I'm using criteria to handle this.




It quits when I create second criteria adding workmate to the equasion. I think this might be happening because the pk name and fk name from respective tables are not equal. Is this the case?
If so, how can I add the createria saying it should look at the id as a key to join. Or am I missing something else?
Or should I just use Query to generate the sql?

Any help would be appreciated in this matter.
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.8

Mapping documents:

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I'm not sure if Criteria supports joining between two unrelated tables. I know it can be set up to join between two related classes that have a uni-directional or bi-directional relationship or proxies already set up in the hbm files. If so, that can be set up via the criteria.setFetchMode(args) method or the chaining of createCriteria methods ontop of each other such as



I'm not entirely positive if the above would work between two unrelated classes or how you would set a Expression.eq(args) method to work between the two.

I'd recomment using hql queries and named queries for simplicity's sake. The criteria seem to be more useful for queries with a large number of options that can be configured by UI interaction.

Thanks for replying. I looked into this uni- bi-directional matter. I appears if I want to use 2 tables that refer to one an other I need to define a bidirectional relationship between the tables.
Normally in SQL we just define a fk that references to other tables pk. But in hibernate the referenced table has to know it has many "children" in the table that refers to it.

Before I had just a <many-to-one> mapping in workmate referring to pk of domain.
Now I added a <set one-to-many> mapping in the domain referring to children in workmate (workmates).

Now the criteria expression sees the second table. That is one problem fixed. ;)

is there a way to print out the generated sql statement before I do
wm.list(); (so before it is executed) ?

in your hibernate config file put



and the sql will show up in your log file.