is there a way to use the criteria api to create a join between different unrelated pojo?

for example:

Select p from State s, Person p where p.language = state.language and state.code=?

in this case there is not an association path between State and Person, so createAlias and createCriteria won't work.

There isn't any way of associating tables with the Criteria API unless there's a declared relationship between them in the mapping files. The best way to do this would be to use HQL.

Though is there a specific reason you can't create the association?

-B

well, there is really no reason to make an association (foreign key) between states languages and peoples languages...

imagine a call center data model:
each client insert his code, and the call is redirected to the first free support person matching the client language. should I do a multi-multi relation between person and clients based on joining the language with two foreign key on a bridge table?

a data model built in such way will become unmanageable. also because now, to set a person language, i need to traverse the relation and setting to each person at least one of the support with the desired language....

this could be resolved using the criteria sub queries, but it's an unnecessary complication.

using criteria, the given query could be rewritten as:

select p from Person p where p.language in (
select s.language from State s where s.code = ? )

and this could be built using a criteria, a projection and then a sub query criteria. as soon as the complexity rises (two columns per join, three table, other conditions) complexity becomes unmanageable.

Based on what you described, my solution would be to have




This will allow you to find what you're looking for. Though based on the example, I'm not sure why you would need the state table. If the concern is that you want to find a person who speaks a particular language; then the lookup would be done on the language table. To which there is a valid reason to have the association at this point.

If criteria searching is what you're wanting to do, then this will work. However IMHO don't base your data model on the solution. If your tables are Person and State and they don't have a relationship to each other, then the best bet is to use an HQL query to get what you're looking for. The same search that can be done using Criteria objects, can be done in HQL.

-B

the thing that baffles me is that the contrary is not true: not any hql search could be mapped on the criteria api, it seems.

also, a simple scenario obviously doesn't represent the complex reality of the full problem, so insisting on restructuring the data modle or the query won't work. The question is simple: is or is not possible to build a join using the criteria api?

Also, in your scenario I will come up with a Language class, having a getPersons and getStates getters and setters, wich in the way insert/updates/delete are treated, is just asking for trouble

Without a defined assocation, no, it's not possible. One or both must map an association to the other. If only one maps the association, then only that class may be used to create the originating Criteria so the association can be used.

eg: If Person maps an association to State, then a Criteria created on State can not be used to associate in a Person instance. A Criteria based on Person however could associate a State.

This limitation probably exists because the Criteria API uses the mapping definitions create the SQL query. If no association exists, then there is no way to make a determination that the correct property comparisons are accomplished.

-B