-->
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.  [ 5 posts ] 
Author Message
 Post subject: Self Join with condition
PostPosted: Thu Jun 23, 2016 2:49 am 
Newbie

Joined: Thu Jun 23, 2016 2:33 am
Posts: 3
Please consider the below example.

Id Parent Id Value
1 - x
2 1 x
3 1 x
4 2 x
5 3 Y

Hibernate hierarchical query for retrieve the hierarchy those "value = X"

The expected result is

1
|
|__ 2
| |
| |
| |__4
|
|__ 3

How we can achieve the same in hibernate


Top
 Profile  
 
 Post subject: Re: Self Join with condition
PostPosted: Thu Jun 23, 2016 3:50 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
As easy as pie!

Assuming you have the following entity:

Code:
@Entity(name = "Node")
public static class Node  {

    @Id
    @GeneratedValue
    private Long id;

    @ManyToOne
    @JoinColumn(name = "parent_id")
    private Node parent;

    private String val;

    @Transient
    private List<Node> children = new ArrayList<>();

    public Node() {}

    public Node(String value) {
        this.val = value;
    }

    public Node getParent() {
        return parent;
    }

    public List<Node> getChildren() {
        return children;
    }

    public void addChild(Node child) {
        children.add(child);
        child.parent = this;
    }
}


You need a hierarchical query like this one:

Code:
Node root = (Node) doInJPA(entityManager -> {
    return entityManager
        .unwrap(Session.class)
        .createQuery(
            "SELECT n " +
            "FROM Node n " +
            "WHERE n.val = :val")
        .setParameter("val", "x")
        .setResultTransformer(new ResultTransformer() {
            @Override
            public Object transformTuple(Object[] tuple, String[] aliases) {
                Node node = (Node) tuple[0];
                if(node.parent != null) {
                    node.parent.addChild(node);
                }
                return node;
            }

            @Override
            public List transformList(List collection) {
                return Collections.singletonList(collection.get(0));
            }
        })
        .uniqueResult();
});


The beauty of it is the ResultTransformer which allows you to reconstruct the hierarchical structure from the table-oriented ResultSet.


Top
 Profile  
 
 Post subject: Re: Self Join with condition
PostPosted: Thu Jun 23, 2016 4:22 am 
Newbie

Joined: Thu Jun 23, 2016 2:33 am
Posts: 3
Dear mihalcea_vlad,

Thank you for the reply. Is there any other way rather than CONNECT BY PRIOR.
Has any HQL quey to retive the same result. (Sinle query )

Thanks
Shiju


Top
 Profile  
 
 Post subject: Re: Self Join with condition
PostPosted: Thu Jun 23, 2016 4:47 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Sure, I just updated my original answer.


Top
 Profile  
 
 Post subject: Re: Self Join with condition
PostPosted: Thu Jun 23, 2016 7:20 am 
Newbie

Joined: Thu Jun 23, 2016 2:33 am
Posts: 3
Thanks the upadte mihalcea_vlad


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 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.