I have a huge problem with a collection (bag) in hibernate in a rather complex data-collector program using a database with more than a million data. So it is important only to load neccesary data through hibernate.

Now to the problem:

I have an energycase with a collection of subcases:

<bag name="subcases" table="EnergyCaseSubcases">
<key column="id"/>
<many-to-many class="EnergySubcase" column="subcase"/>
</bag>

A subcase points to a channel and before I can delete the channel I have to check if it exists in any energycases - if it does I have to give a message to the user. From a channel I can find the subcase, but not the energycase from the subcase.

In SQL it is quite easy:

select * from Energycase where id in (select subcase.id from EnergyCaseSubcases subcase where subcase='a known id'

So my problem is to go backward using a bag, how can I do that?

Note: since there is copyrights on the program I can't write the java-clases and hibernate mappings in more details, sorry!

For the check you can use your SQL
http://www.hibernate.org/hib_docs/v3/re ... -nativesql

Our design is developed so we could change to use another java-database layer and the program must be able to run on several different databases.

Therefore will we not use SQL directly - all our call is made through transaction string in HQL which ensure that the call will work on all databases hibernate support.

So we will only use plain SQL if we can't do the check in any other ways. I am about to make a usable way now, but it will retrieve the same information from the database several times - and lots of it is uneeded.

Any other have a solution?