Hibernate: How can I select null objects using HQL?

Question

I have the below select statement

"SELECT new SystemUser(u.id, u.username, u.fullname, u.status, u.group.id, u.group.name, u.organization.id, u.organization.name) FROM SystemUser"

Pojo

public class SystemUser implements Serializable {

private long id;

private String username;

private String password;

private String fullname;

private SimpleStatus status;

private UserGroup group;

private Organization organization;}

and I want to select all data from SystemUser table, but rows where "SystemUser" has no "group" or "organization" are not selected.

How can I select all data using HQL?

Mathias Begert · Accepted Answer · 2015-07-10 08:25:23Z

1

What you use is a constructor query select new SystemUser(...) from SystemUser. This will construct new objects based on the columns you select. I think this is not necessary, you can simply write select u from SystemUser u.

Or even simpler (but not recommended, because this is not JPQL compliant): from SystemUser

answered Jul 10, 2015 at 8:25

Mathias Begert

2,4991 gold badge20 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2015-07-10 07:48:14Z

0

FROM SystemUser WHERE group IS NULL OR organization IS NULL

Should work fine.

answered Jul 10, 2015 at 7:48

David

20.2k31 gold badges110 silver badges128 bronze badges

Comments

Guillermo · Accepted Answer · 2015-07-10 12:55:25Z

0

I never try it but following the JPQL-HQL documentation I think you could do something like this (not sure if the case statement can return null values or just empty string):

SELECT new SystemUser(
    u.id, u.username, u.fullname, u.status, 
    CASE 
        WHEN u.group IS NOT NULL THEN u.group.id
        ELSE NULL, 
    CASE 
        WHEN u.group IS NOT NULL THEN u.group.name
        ELSE NULL, 
    CASE 
        WHEN u.organization IS NOT NULL THEN u.organization.id
        ELSE NULL, 
    CASE 
        WHEN u.organization IS NOT NULL THEN u.organization.name
        ELSE NULL,
) 
FROM User u

answered Jul 10, 2015 at 12:55

Guillermo

1,5339 silver badges19 bronze badges

Collectives™ on Stack Overflow

Hibernate: How can I select null objects using HQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related