0

I have the following two different HQL statements.

My Data Structure looks like this:

User

@Entity (name = "User")
public class User
{

    @Id
    @GeneratedValue
    @Column (name = "id")
    private int id;
    @Column (name = "user_name")
    private String username;
    @Column (name = "password")
    private String password;
    @Column (name = "enabled")
    private boolean enabled;
    @ManyToMany (targetEntity = Role.class, cascade =
    {
        CascadeType.ALL
    })
    @JoinTable (name = "user_role", joinColumns =
    {
        @JoinColumn (name = "user_id")
    }, inverseJoinColumns =
    {
        @JoinColumn (name = "role_id")
    })
    private Set<Role> roles;

    /* getters and setters)
}

To cut it short the only difference between the two queries is that one is ASC the other is DESC

@NamedQuery (name = "user.getUsersOrderByRoleAsc",
    query = "FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name ASC"),
@NamedQuery (name = "user.getUsersOrderByRoleDesc",
    query = "FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name DESC"),

The query for ASC returns: A list of Users -> As I would expect.

The query of DESC returns: An List of Object[], and in each object the [0] is the User, while the [1] is just another null object.

That does not make any sense to me. How can simply changing ASC to DESC change the structure of the result set ?

I am using Hibernate 4.3.6.Final.

seba.wagner
  • 3,800
  • 4
  • 28
  • 52

2 Answers2

0

The fastest way to determin, what went wrong is to set the show_sql flag to true in you hibernate configuration file. This will log every rendered query.

See Hibernate show real SQL

Community
  • 1
  • 1
Hannes
  • 2,018
  • 25
  • 32
  • I already did that, the SQL output does look EXACTLY the same (except for the ordering part of course) no matter if I try the ASC or DESC query. – seba.wagner Sep 18 '14 at 21:50
  • Taking a look at the hibernate test classes (`ASTParserLoadingOrderByTest`) I see that order by is allways used with aliases. Line 558 smells like a work around... – Hannes Sep 19 '14 at 12:23
0

Probably there is some Hibernate bug you bumped into, but because you are join fetching a one to many children collections, it's safer to use distinct as well:

@NamedQuery (name = "user.getUsersOrderByRoleAsc",
query = "select distinct u FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name ASC"),
@NamedQuery (name = "user.getUsersOrderByRoleDesc",
query = "select distinct u FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name DESC")
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911