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.)

Query problem with IS NULL [message #976921]

Thu, 08 November 2012 18:39

Eclipse User

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 17:19

Eclipse User

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

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

Mon, 12 November 2012 16:20

Eclipse User

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:

-=] Back to Top [=-

Current Time: Tue Jul 08 03:31:11 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter