JPA @ManyToMany ordering failure using @OrderBy

Question

When JPA tries to select AdmUser entity I have sql error:

ERROR: column locations1_.name does not exist.

Is there anything wrong with my entities? My AdmUser entity:

@Entity
@Table(name = "ADM_USERS")
@SequenceGenerator(name = "ADM_USER_SEQ", sequenceName = "ADM_USER_SEQ", allocationSize = 1)
public class AdmUser implements EntityInt, Serializable {

    private static final long serialVersionUID = 786L;

    @Id
    @Column(nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ADM_USER_SEQ")
    private Long id;
(...)
    @ManyToMany(cascade = CascadeType.MERGE, fetch = FetchType.EAGER)
    @JoinTable(name = "loc_locations_adm_users", joinColumns = @JoinColumn(name = "id_user", referencedColumnName="id"), 
                inverseJoinColumns = @JoinColumn(name = "id_location"))
    @OrderBy("name")
    private Set<LocLocation>        locations;
(...)
}

My LocLocation Entity:

@Entity
@Table(name = "loc_locations", schema = "public")
@SequenceGenerator(name = "LOC_LOCATIONS_SEQ", sequenceName = "LOC_LOCATIONS_SEQ", allocationSize = 1)
public class LocLocation implements EntityInt, java.io.Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "id", unique = true, nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "LOC_LOCATIONS_SEQ")
    private Long id;

    @Column(nullable = false, unique = true, length = 200)
    private String name;
(...)

    @ManyToMany(cascade = CascadeType.REFRESH, fetch = FetchType.LAZY, mappedBy="locations")
    private List<AdmUser>       users;
}

And now - when JPA tries to select AdmUser entity I have sql error. The query generated by JPA looks that:

 select
  admuser0_.id as id1_2_0_,
  admuser0_.actived as actived2_2_0_,
  admuser0_.admin as admin3_2_0_,
  admuser0_.allow_ip as allow_ip4_2_0_,
  admuser0_.created as created5_2_0_,
  admuser0_.deleted as deleted6_2_0_,
  admuser0_.id_domain as id_doma16_2_0_,
  admuser0_.email as email7_2_0_,
  admuser0_.language as language8_2_0_,
  admuser0_.login as login9_2_0_,
  admuser0_.name as name10_2_0_,
  admuser0_.passwd as passwd11_2_0_,
  admuser0_.phone as phone12_2_0_,
  admuser0_.picture as picture13_2_0_,
  admuser0_.surname as surname14_2_0_,
  admuser0_.theme as theme15_2_0_,
  locations1_.id_user as id_user1_2_1_,
  loclocatio2_.id as id_locat2_6_1_,
  loclocatio2_.id as id1_17_2_,
  loclocatio2_.description as descript2_17_2_,
  loclocatio2_.name as name3_17_2_
  from
        public.ADM_USERS admuser0_
   left outer join
        public.loc_locations_adm_users locations1_
            on admuser0_.id=locations1_.id_user
   left outer join
        public.loc_locations loclocatio2_
            on locations1_.id_location=loclocatio2_.id
   where
        admuser0_.id=1
   order by
        locations1_.name

The order by points to locations1_.name, but should be loclocatio2_.name. Have I anything wrong with my entities?

score 0 · Answer 1 · 2014-10-08T10:30:51.940

0

You have a Set on one side for that field. Consequently there is no "ordering" (other than what hashCode() gves). Use a List if you want ordering (this is Java, nothing to do with JPA really).

You also seem to be missing a "mappedBy" on the non-owner side of that M-N.

edited Oct 08 '14 at 10:30

answered Oct 08 '14 at 09:09

Not really, because in other place I have Set and ordering works properly. The problem is generated by JPA query which even I try to execute in SQL editor - I have the same error: ERROR: column locations1_.name does not exist – robson Oct 08 '14 at 09:54
Yes, I know that simple HashSet has no ordering. So, I'm wondering why in other cases it is properly sorted. Anyway, even changing to List - error is the same. – robson Oct 08 '14 at 10:22
note my update to the answer ... mappedBy is missing? Also you only need to define the join table from one side when it is a M-N – Oct 08 '14 at 10:31
1

This is incorrect Hibernate will use a SortedSet implementation when required http://stackoverflow.com/questions/2560590/hibernate-ordering-a-set – Alan Hay Oct 08 '14 at 11:10
have an upvote, a mappedBy is required on a bidirectional M-N relation – Neil Stockton Oct 08 '14 at 12:30
@AlanHay good point (+1), this explains why my Set is sorted, thanks – robson Oct 09 '14 at 07:10
I've added mappedBy. I've tried with mappedBy on both sides. OrderBy still tries to sort AdmUserLocLocation table :-( – robson Oct 09 '14 at 07:38

score 0 · Accepted Answer · answered Oct 09 '14 at 08:08

0

The @OrderBy works fine with ManyToMany. Also with the structure I provided in my question. The problem was my query and JPA didn't managed with it. Sorry.

answered Oct 09 '14 at 08:08

robson

1,623
8
28
43

no idea what that means "the problem was my query". You didn't provide a query in the question. – Oct 09 '14 at 09:08
I didn't provide query, because query doesn't point to AdmUser entity. It was loaded dynamicaly by JPA while selecting connected data. I noticed later that during selecting AdmUser directly - OrderBy works properly. Sorry. – robson Oct 09 '14 at 12:16

JPA @ManyToMany ordering failure using @OrderBy

2 Answers2