Eclipse Community Forums: EclipseLink » Query problem with IS NULL

Home » Eclipse Projects » EclipseLink » Query problem with IS NULL(when asked whether an item is null and also asked his name, the query returns only the values that have the name, null values are omitted, but I also want null values.)

Show: Today's Messages :: Show Polls :: Message Navigator

Query problem with IS NULL [message #976921]

Thu, 08 November 2012 23:39

a juan

Messages: 2
Registered: November 2012

Junior Member

I have a problem JPQL querying.

when asked whether an item is null and also asked his name, the query returns only the values that have the name, null values are omitted, but I also want null values.

my entities are similar to these.

public class Activity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String info;

@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date dateOfEvent;

@OneToOne
private User user;

... getters and setters...

public class User implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String username;
private String name;

...getters and setters...

My query is...

SELECT a FROM Activity a WHERE a.user IS NULL OR a.user LIKE :name

For example. If i have

|Activity|
|id,info,user,...|
|1,act1,user1,...|
|2,act2,user1,...|
|3,act3,NULL,...|
|4,act4,user1,...|
|5,act5,NULL,...|

only 1,2 and 4 are return, but i want 3,4 also

Report message to a moderator

Re: Query problem with IS NULL [message #978172 is a reply to message #976921]

Fri, 09 November 2012 22:19

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

Using a.user in the query forces an inner join to be used. What you will need to do is use an alias in the query, and define the alias with an outer join.
"SELECT a FROM Activity a left join a.user user WHERE user IS NULL OR user LIKE :name"

Best Regards,
Chris

Report message to a moderator