Hi,

My requirement is 'we should join two tables (where they don't have any associations) and retrieve the data by projecting only specific columns'.

I did the Column filtering using the Criteria.Projections functionality. And here, I want to introduce a join condition with one generic table which actually don't have any specific association with my target table.

Let me give my table structures and actual requirement,

Table1: Account (accoun_id, name, firm_id, firm_rep_id, etc..)
Table2: Firm (firm_id, name, etc...)
Table3: FirmRep (firm_rep_id, name, etc...)

Table4: List (list_id, name, member_type, etc...)
Table5: ListMember (list_member_id, list_id, entity_value, etc...)

Assume that, the first 3 tables are the master tables and the last 2 tables are generic list tables, to maintain the list of master data.

I would mean here that List & ListMember tables can contain Account list, Firm list and FirmRep list. And the type of the list can be identified by its member_type, whereas the ListMember table will contain the list of account_Ids / firm_ids / firm_rep_ids in the entity_value column.

So here, my requirement is
1. I need to retrieve the list of accounts by just supplying the list_id using join. (By projecting only specific fields)
2. I need to retrieve the list of firms by just supplying the list_id. (By projecting only specific fields)
3. I need to retrieve the list of firm reps by just supplying the list_id. (By projecting only specific fields)

4. All the above will be implemented in different methods. So retrieving need not to be very generic as like all the three will be implemented in only one method.

So here, you could observe that we can't provide an association mapping between the ListMember(entity_value) to any tables.

In these scenario, how can I write or create my Hibernate Criteria object to retrieve the list of accounts / firms / firmreps.

The question is how to add a join condition in a Criteria where the two tables don't have any association mappings.

Any help, would be highly appreciated.

You can use subselect ...

DetachedCriteria avgWeight = DetachedCriteria.forClass(Cat.class)
.setProjection( Property.forName("weight").avg() );
session.createCriteria(Cat.class)
.add( Property.forName("weight).gt(avgWeight) )
.list();


Don"t forget to rate ;-)

Hi,

I am not clear on the given example, but I will try to understand by implementing this approach.

However I have a doubt in the given example.

Whether the hibernate will generate a join condition or it will use any subquery model to execute this subselect criteria. Since I should take care about the performance and have to use the indexes. I hope that the indexes will be considered only when we use the join. Mostly aggregation and subqueries will access the full tables.

Otherwise I have to see the generated queries by enabling the hibernate logging in debug mode, to exactly find what is the query it generates.

Is there any other approach to handle this?

A subquery generate a subselect ... You can see that in the Hibernate Log with this declaration in hibernate.cfg.xml :
<property name="show_sql">true</property>

Hi,

Thanks a lot for your help.

I did achieve the thing using the subselect option suggested by you.

Given below is the code snippet which I have implemented, and here the input is the 'listId'.