Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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.)
Query problem with IS NULL [message #976921] Thu, 08 November 2012 23:39 Go to next message
a juan is currently offline a juanFriend
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
Re: Query problem with IS NULL [message #978172 is a reply to message #976921] Fri, 09 November 2012 22:19 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
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
icon10.gif  Re: Query problem with IS NULL [message #981980 is a reply to message #976921] Mon, 12 November 2012 21:20 Go to previous message
a juan is currently offline a juanFriend
Messages: 2
Registered: November 2012
Junior Member
Very Happy perfect, thank you very much, I had spent many days trying querys

only one note

user LIKE :name, is changed to user.name LIKE :name
Previous Topic:JPQL Bug? BigInteger 22 digits (precision=22, scale=0)
Next Topic:Cache Coordination with WebSphere (WAS) v.8
Goto Forum:
  


Current Time: Fri Dec 19 14:25:06 GMT 2014

Powered by FUDForum. Page generated in 0.03067 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software