1

I wanted to get records of Patient(POJO class) who's contact number is not null. So, I referred this post.
In the answer two ways are specified 

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;   

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

From above I wrote below hql which runs successfully 

from Patient p where p.contactNo is not null

But, for 2nd type of hql 

from Patient p where not (p.contactNo <=> null)

throws Exception

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: >

How can I use mysql null safe equality operator <=> in HQL?

Community
  • 1
  • 1
Aniket Kulkarni
  • 12,825
  • 9
  • 67
  • 90
  • 1
    Why you want to use **from Patient p where not (p.contactNo <=> null)** when you can easily achieve desired result with **from Patient p where p.contactNo is not null** – Ankit Sharma Oct 08 '13 at 10:43
  • @AnkitSharma: I was just checking both the ways. I want to know why did result in exception? or we can not use in HQL. – Aniket Kulkarni Oct 08 '13 at 10:45

1 Answers1

2

HQL is a different language than MySQL. MySQL operators are not necessarily available in HQL.

This being said, you can given Hibernate MySQL queries (provided your database is MySQL):

Query query = entityMangager.createNativeQuery("Some MySQL code");
List results = query.getResultList();

EntityManager is an interface from the Java Persistence API. Hibernate has a tutorial about using the JPA, but here are the main points:

In order to have an entity manager, you need META-INF/persistence.xml file in your classpath. Then, inside a Java EE container, you get an instance of this interface with the @PersistenceContext annotation:

@PersistenceContext(unitName = "persistenceUnit")
private EntityManager em;

Outside a Java EE container, you can get one with the Persistence class:

EntityManagerFactory factory = Persistence.createEntityManagerFactory("persistenceUnit");
EntityManager em = factory.createEntityManager();

In both case, "persistenceUnit" must be the name of a persistence unit defined in your persistence.xml file.

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
Étienne Miret
  • 6,448
  • 5
  • 24
  • 36
  • You are write I can execute query with native sql. But in different fashion `session.createSQLQuery(hql);`. Could you tell me about `entityManager` object you have used. Some documentation or how to create object `entityManager`? – Aniket Kulkarni Oct 08 '13 at 11:56
  • @Aniket I updated my answer with informations about the JPA and entity managers. – Étienne Miret Oct 10 '13 at 11:28