JPA JPQL query returning null

Question

I have the following query, trying to fetch Employee entity objects using part of the @Embeddable id class:

public List<EmployeeTask> getEmployeeById(){
                    return em.createQuery("SELECT a FROM EmployeeTask a where a.id.empcode = '5091'" ).getResultList();

The code above returns null on my jsf page. But when i change this to :

public List<EmployeeTask> getEmployeeById(){
                        return em.createQuery("SELECT a FROM EmployeeTask a where a.id.empcode like '5091'" ).getResultList();

again it returns null. But when i try:

public List<EmployeeTask> getEmployeeById(){
                        return em.createQuery("SELECT a FROM EmployeeTask a where a.id.empcode like '5091%'" ).getResultList();

 }

it returns the contents of the Entity object as expected... Anyone knows why this happens?

More details on what i need to do: the EmployeeTask table contains all tasks performed by the employee. EmployeeTaskPK is string empcode, string taskcode, date since. I want to fetch all records for a given empcode in my Entity instance and list them on a jsf datatable.

Thank you,

check your db: empcode column is a char of size 4? perhaps there are whitespaces — Zeromus
empcode is NCHAR(8). Thank you so much. I guess if i ll define it in my Entity's attribute @Column it will work — Bonzay
Nope columnDefinition="NCHAR(8)" does not work. Any idea how to work with nchar data type in jpa? — Bonzay
Don't hard code the value. Pass it as a parameter, JPA will then use proper type conversion. — M. Deinum

Zeromus Zeromus · Accepted Answer · 2017-08-28T12:30:14

The problem is not in jpa but in database column definition, if you expect your column to be a fixed size CHAR, or in this case NCHAR, is what you go for.

The problem is when you have a string of lower length like "5091" that is only of size 4: what you will get in db is "5091 " with four blanks at the end.

The query will not return your row because "5091 " with blanks is different from "5091" without and the only one working correctly is the like '5091%'

You can solve by either using NVARCHAR which will trim lower length string or adding the whitespaces where needed in queries (which for me seems like a bad practice)

public List<EmployeeTask> getEmployeeById(){
                    return em.createQuery("SELECT a FROM EmployeeTask a where a.id.empcode = '5091    '" ).getResultList();

JPA JPQL query returning null

1 Answers