Eclipse Community Forums: EclipseLink » Eclipselink JPA named query not ignoring white spaces.

Home » Eclipse Projects » EclipseLink » Eclipselink JPA named query not ignoring white spaces.(Eclipselink JPA named query not ignoring white spaces.)

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

Eclipselink JPA named query not ignoring white spaces. [message #894866]

Tue, 10 July 2012 20:24

Chandra Narasimha

Messages: 5
Registered: July 2012

Junior Member

Hi,

I am using JPA2.0 Eclipselink 2.3.2. I have database field CHAR(12) mapped in entity as a String. I have named query to retrieve entity by passing primary key input parameter.

In database value stored only with two characters and remaining are spaces because it is CHAR(12).

Example Table X column id (CHAR12) value "IS "

Named query looks like this

@NamedQueries({
@NamedQuery(name = "getById",
query = "SELECT sec " +
" FROM TABLE ref " +
" WHERE ref.id = :idValue "
)
})

list = em.createNamedQuery("getById ")
.setParameter("idValue ", "IS")
.getResultList();

I am passing parameter string value as a "IS" to the named query but my named query is not returning any list. If I pass with white spaces "IS " it is retrieving.

I don't want to pad with spaces, I would like to pass only "IS". Even I don't want to use Oracle trim function in my named query. trim (training from t.id) -- I don't want to use this (I know it works with this option)

If I execute sql query in database it works fine. Select * from Table where id = 'IS'; - it shows the rows

Why named query not retrieving the results, how to indicate namedquery to ignore char spaces while executing query?

How to get the result from naedquery without using these two above options. Could someone please help me with this issue?

Thanks

Report message to a moderator

Re: Eclipselink JPA named query not ignoring white spaces. [message #894868 is a reply to message #894866]

Tue, 10 July 2012 20:28

Chad Curtis

Messages: 8
Registered: July 2012
Location: Minnesota

Junior Member

Assuming ref.id is a string, you could do
WHERE ref.id.trim() = :idValue

or

use a wildcard on your parameter
.setParameter("idValue ", "IS_")

Hope this helps.

[Updated on: Tue, 10 July 2012 20:30]

Report message to a moderator

Re: Eclipselink JPA named query not ignoring white spaces. [message #894871 is a reply to message #894868]

Tue, 10 July 2012 20:39

Chandra Narasimha

Messages: 5
Registered: July 2012

Junior Member

Hi
Regarding - WHERE ref.id.trim() = :idValue
we can not use ref.id.trim() in named query isntead of that we can use trim(trailing from ref.id) --- My point is I don't want to use Oracle trim function, we don't want to use oracle built in fucntion. If I use trim(trailing from ref.id) in SQL query executed like this TRIM(RPAD(column name)). Same thing I mentioned in my question.

setParameter("idValue ", "IS_")
I don't want to pad any spaces, I want to pass just the value "IS" with out any padding.

Do let me know if there is any option. Why named query considering char spaces while executing sql query? How to resolve this issue?

Report message to a moderator

Re: Eclipselink JPA named query not ignoring white spaces. [message #899626 is a reply to message #894871]

Wed, 01 August 2012 15:48

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

This seems to be a database/JDBC issue with binding. You can disable parameter binding for the query. (query hint, "eclipselink.jdbc.bind-parameters"="false").

In general you should use a VARCHAR field type, not a CHAR type.

Report message to a moderator

Previous Topic:	Use Spring's MarshallingHttpMessageConverter and MOXy without @XmlRootElement
Next Topic:	DistributedSession cause NPE in InstanceVariableAttributeAccessor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 28 01:41:38 GMT 2016

.:: Contact :: Home ::.