-->
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: @OrderBy doesn't work in order to sort collection using SQL
PostPosted: Thu Oct 27, 2016 4:43 am 
Newbie

Joined: Thu Oct 27, 2016 4:31 am
Posts: 2
There are 3 tables. There is the variable of "relatedCameraSet" need to order by "camera.name" using SQL, but the field of "camera.name" is not in table of "RelatedCamera", is in the outer joined table of "Camera". The following annotation of @OrderBy doesn't work.

Code:
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.Table;

@Entity
@Table(name = "MICRO_MAP")
public class MicroMap { //main table
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME", length = 32, nullable = false)
    private String name;

    @OneToMany(mappedBy="mapId",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    @OrderBy("camera.name") //OrderBy the field of "name" in Camera table
    private Set<RelatedCamera> relatedCameraSet;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<RelatedCamera> getRelatedCameraSet() {
        return relatedCameraSet;
    }

    public void setRelatedCameraSet(Set<RelatedCamera> relatedCameraSet) {
        this.relatedCameraSet = relatedCameraSet;
    }   
}


Code:
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "RELATED_CAMERA")
public class RelatedCamera {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "MAP_ID")
    private String mapId;

    @ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    @JoinColumn(name = "CAMERA_ID", referencedColumnName="id",nullable = true)
    private Camera camera;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getMapId() {
        return mapId;
    }

    public void setMapId(String mapId) {
        this.mapId = mapId;
    }

    public Camera getCamera() {
        return camera;
    }

    public void setCamera(Camera camera) {
        this.camera = camera;
    }   
}


Code:
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "CAMERA")
public class Camera {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME")
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }   
}


The above annotation of @OrderBy doesn't work. How to write @OrderBy annotation in order to sort by camera name?
Thanks alot!


Last edited by luckysohu on Thu Oct 27, 2016 10:05 pm, edited 4 times in total.

Top
 Profile  
 
 Post subject: Re: How to write @OrderBy to sort collection using SQL?
PostPosted: Thu Oct 27, 2016 3:41 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Like this:

Code:
@OneToMany(mappedBy="mapId",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@SortComparator(CameraNameComparator.class)
private SortedSet<RelatedCamera> relatedCameraSet  = new TreeSet<>();


Where CameraNameComparator is:

Code:
public static class CameraNameComparator implements Comparator<RelatedCamera> {
    @Override
    public int compare(RelatedCamera o1, RelatedCamera o2) {
        return o1.getCamera().getName().compareTo( o2.getCamera().getName() );
    }
}


Top
 Profile  
 
 Post subject: It's a bug or defect in Hibernate
PostPosted: Thu Oct 27, 2016 9:49 pm 
Newbie

Joined: Thu Oct 27, 2016 4:31 am
Posts: 2
Thank vlad for providing the solution to sort collection, but your solution doesn't use SQL to sort.
Is there any possibility to sort the collection just by SQL, not implement Comparable interface with passing a Comparator?

With doing more research, now i think it's a bug or defect in Hibernate, becuase it's OK to use "camera.name" in the HQL of " from RelatedCamera where mapId=1 order by camera.name", and it's not OK to use "camera.name" in @OrderBy("camera.name") annotation.


Top
 Profile  
 
 Post subject: Re: @OrderBy doesn't work in order to sort collection using SQL
PostPosted: Fri Oct 28, 2016 2:13 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It's not a bug. You should read the JPA spec which clearly states that:

Quote:
The dot (".") notation is used to refer to an attribute within an embedded attribute. The value of each
identifier used with the dot notation is the name of the respective embedded field or property.


So, you can only use nested properties if the underlying collection entry is an embeddable, not an entity type.


Top
 Profile  
 
 Post subject: Re: @OrderBy doesn't work in order to sort collection using SQL
PostPosted: Wed Nov 09, 2016 2:55 pm 
Newbie

Joined: Wed Nov 09, 2016 2:45 pm
Posts: 3
Dear luckysohu,

use List insted of Set, with Set @OrderBy doesn't work.


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.