HQL: order by property nullable

Question

Assuming two tables, A[a_id, b_id] and B[b_id,c] .

I need to execute an HQL query of the form "From A a ORDER BY abc" , and b in class A

The query, however, returns only instances of A that have the non-null b property. This is because Hibernate generates SQL forms of "SELECT FROM A,B WHERE A.b_id = B.b_id ORDER BY Bc"

How to return all instances of A with those whose null in b appears first / last?

Answer 1

What about:

 from A a left join a.b_fk b order by bc 

The left join will take care of creating the connection, even if the b_fk property in the java object (and not in the table) is null.

Edited . Sorry, I mentioned sorting zeros differently. To sort (without accepting null values), you can specify "desc" to invert the sort order (default = "asc"). For nulls, I believe Hibernate allows you to order a default database ... Try it yourself in your database to see what happens (sorry for the misleading in the first version of the message).

You can find a lot of information in the Hibernate help documentation:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html

The rest usually depends on the database you use ...

Answer 2

I have the same problem and I solve it like this:

 SELECT a FROM a LEFT JOIN ab b LEFT JOIN bc c ORDER BY c.id 

In the HQL syntax!

"LEFT JOIN" allows you to display a string when the child is NULL.

In fact, if you do not specify a connection, hibernate automatically creates an "INNER JOIN" that removes child NULLs.