I have a class annotated like this:


After Hibernate generates the schema, I have one table (ncc_failure) with 3 fields: id, summary and property. And then there's a link table (ncc_failure_subnet_link) with 2 fields: ncc_failure__id and ip_subnet__id. The idea is that one NccFailure object can contains many subnetId's.

What I want to do is to update the summary of all the NccFailure objects whose subnetIds contains at least one from a fixed list. In plain SQL I'll write it as:


However I don't know how to write it properly with HQL. All my attempts have failed so far. I would imagine it's something like this:

But this fails with "syntax error at or near "any"".
I've also tried some other variations of "any" and "elements" and the alias but to no avail. There's always an error. What did I miss?

(The reason I put the title as "HQL join" is because I know if subnetIds is a collection of non-primitive objects, I can write a join with the class of subnetIds and the HQL query will be good. But in this case it's a primitive type collection and I don't know how to do a join, or if one is even necessary. I assume it's implicit, but I can't seem to make it work so far. If it's not implicit, then I don't know how to write that join, hence the question).
Thanks.

First of all, HQL bulk UPDATE and DELETE cannot take joins, so a sub-select is needed instead.

I replicated this mapping in the following test:

I also changed the @CollectionOfElements (which is deprecated) to @ElementCollection.

The update HQL statement looks as follows:



I think you were interested in the elements() query expression.

Thank you for your reply, however I think the query you provided does not do the same thing.

First I must clarify what I meant by 'fixed list'. It doesn't mean the list is known at compile time. It just means we can use the "IN" operator against it. In actuality I'll get the list from a sub-select query into another table. So typically in plain SQL I'll write something like this:


where IPSubnet is defined simply like this:


For my query, I wanted to update all the NccFailure objects whose subnetIds has any of the IDs in the (sub-select) list. The query you wrote only works when the list is known at compile time, and has only 1 element. What if it has more than one element? Then you'll have to add a lot of OR's, as in:
'id1' in elements (nf.subnetIds) OR 'id2' in elements (nf.subnetIds) OR 'id3' in elements (nf.subnetIds) etc, right?

It certainly doesn't look as nice as an IN clause (if an IN clause is possible). More importantly, it doesn't work when the list is a sub-select clause.
Is there a way we can concatenate any and elements() together? I thought it'll look right if I can write any elements(nf.subnetIds) in (SELECT...) but that got rejected.

In this case, an SQL bulk update is much more flexible for this mapping.
Or change the basic type collection into a bidirectional one-to-many and you'll be able to execute more complex HQL queries that way.

Just out of curiosity, how do you "change the basic type collection into a bidirectional one-to-many" for that given mapping? I'm certainly open to changing the annotation, as long as the basic database table structure remains more or less the same. (We don't want the NccFailure to contain a collection of objects, we just want to it to contain a collection of IDs). Thanks.

You need to let go the Embeddable and then make the child-side an entity of its own with a @ManyToOne association based on the FK, and the collection becomes a mappedBy @OneToMany.
The DB structure could remain almost the same because you have a separate table to store the basic types and those have an FK already. That table would also need a PK, but that's not difficult to add either.

I know the data structure (in the DB) will be the same, but the key issue for us is that we don't want to keep a collection of objects inside NccFailure.
In fact, we used to have it like that which you described, but the cost of carrying all those objects becomes too much for us, and we find out Hibernate was running hundreds of SQL queries just to populate the the collection. So we thought instead of carrying the full objects, why don't we just carry the IDs, and only retrieve the full objects when necessary. The current mapping (with CollectionOfElements) is the current attempt to switch the mapping while still retaining most of the legacy database structure (which has already been widely deployed).

I still wonder how the any keyword is used in Hibernate though. Even if it doesn't fit my current purpose, just for my education can you or someone post a query with that keyword used with the field subnetIds? I just want to know how it's supposed to be used.

I just found something odd. Apparently the syntax I've been using is right, but it only works when it's on the right side of a comparison. So for example if I write:
...where 'id1' = any elements(nf.subnetIds)
...where 'id1' != some elements(nf.subnetIds)
then there's no problem, and the query executes fine.

But if I swap the operands to:
...where any elements(nf.subnetIds) = 'id1'
...where some elements(nf.subnetIds) != 'id1'
then I got syntax error at the point where any and some are.

This tells me that the support is there, it's just a matter of the parser not knowing how to parse correctly when dealing with any/some. For comparison it doesn't really matter which side, but for the IN operator only one way makes sense, and unfortunately it requires that any/some be on the left side of the operator. Bummer.

That's how the ANY, ALL, SOME operators work. They must be on the right-side: http://www.w3resource.com/sql/special-operators/sql_any.php