Hi, I'm sorry if this is a basic question but I've looked all over for the answer to this.

I'm trying to select lodgings (hotel, bed and breakfast, etc) based on features that a customer requests. I've modelled this as a Lodging class and a LodgingFeature class, both as entities in Hibernate with a many-to-many relationship between them.

This works fine until it comes time to selecting lodgings that match the customer's criteria. I've written the following query:



I then set the features parameter as a java.util.List of LodgingFeature. This seems to work, the generated SQL looks something like this:



This SQL looks fine to me, my problem is that I'm using MySQL, which does not support subselects. Does anyone have any idea how I can avoid the subselect in this situation? Or will I have to use raw JDBC:



To query the junction table for the matching lodging IDs then use Hibernate to retrieve the objects (yuk).

Thanks in advance for any advice.

How about something like


PS: Get a real Database :)

This looks good, it produces:



But then MySQL complains with:

[code]You have an error in your SQL syntax near ''

Be sure to use query.setParameterList() or something like that . Show your calling code if you can't get it to work.


I really think mySql holds up the positive developement of internet applications, everybody uses this thing just because it is most popular and nobody is able to utilize the great features of other databases ...

Fantastic, it works! Thanks very much for your help. For reference, the code ended up looking like:





I have to agree. MySQL is only really useful for simple applications like blogging. For complex apps it doesn't support enough basic constructs to be useful. Oh well, perhaps I'll try and convince my client...