-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 4:31 am 
Newbie

Joined: Tue Jan 12, 2016 3:59 am
Posts: 10
I have a class annotated like this:
Code:
@Entity
@Table(name = "ncc_failure")
public class NccFailure
{
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_STORE")
    private long id;


    @CollectionOfElements( fetch = FetchType.EAGER)
    @JoinTable(
            name = "ncc_failure_subnet_link",
            joinColumns = @JoinColumn(name = "ncc_failure__id")
    )
    @Column( name="ip_subnet_id", nullable = false)
    private final Set<String> subnetIds = new HashSet<>();

    @AccessType("property")
    @Lob
    @Column(name = "summary")
    private String summary;


    @AccessType("property")
    @Lob
    @Column(name = "file_lines_data")
    private String  fileLinesData;
}


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:
Code:
update ncc_failure set summary='new summary' where id in
(select distinct id from ncc_failure as failure inner join ncc_failure_subnet_link as link on failure.id = link.ncc_failure__id
where link.ip_subnet__id in ('id1', 'id2', 'id3', 'id4'));


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:
Code:
update NccFailure set summary='new summary' where id in (select id from NccFailure as failure where any elements(failure.subnetIds) in ('id1', 'id2', 'id3', 'id4'));

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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 5:40 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
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:

Code:
int count = session.createQuery(
    "update NccFailure f set f.summary=:summary " +
    "where f.id in (" +
    "   select nf.id " +
    "   from NccFailure nf " +
    "where 'id1' in elements(nf.subnetIds) ) ")
    .setParameter("summary", "summary")
    .executeUpdate();
assertEquals(1, count);


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


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 2:38 pm 
Newbie

Joined: Tue Jan 12, 2016 3:59 am
Posts: 10
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:
Code:
update ncc_failure set summary='new summary' where id in
(select distinct id from ncc_failure as failure inner join ncc_failure_subnet_link as link on failure.id = link.ncc_failure__id
where link.ip_subnet__id in (select id from ip_subnet where name like 'San Jose%'));


where IPSubnet is defined simply like this:
Code:
@Entity
@Table(name = "ip_subnet")
public class IPSubnet {
    @Column(name="id", length = 32)
    private String id;

    @Column(name="name", length = 255)
    private String name;
}


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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 2:49 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 4:23 pm 
Newbie

Joined: Tue Jan 12, 2016 3:59 am
Posts: 10
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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 4:52 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 7:21 pm 
Newbie

Joined: Tue Jan 12, 2016 3:59 am
Posts: 10
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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 9:42 pm 
Newbie

Joined: Tue Jan 12, 2016 3:59 am
Posts: 10
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.


Top
 Profile  
 
 Post subject: Re: How to do HQL join with a collection of primitive elements?
PostPosted: Tue Jan 12, 2016 11:37 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.