JAVA: NamedQuery String problem

Question

Hello guys I am having some problems with exact matches while doing a NamedQuery.

I am currently using something like this:

@NamedQuery(name = MyClass.GET_ENTRY_BY_NAME, query = "select e from Entry e where e.name =:"+ Entry.NAME )

...

Query query = em.createNamedQuery(MyClass.GET_ENTRY_BY_NAME);
        query.setParameter(Entry.NAME, myEntry.getName());

It works for most cases, however I noticed that in case the user pass the file name with an space at the end, the namedQuery ignores that character. For example:

Query query = em.createNamedQuery(MyClass.GET_ENTRY_BY_NAME);
        query.setParameter(Entry.NAME, myEntry.getName()+ " ");

Will return the same result as the query before. Bypassing my 'valid entry' validation. In other words I'd like the query to return no entry at all and treat the error later on.

One workaround I could think of, is to put single quotes surrounding my parameter in the namedQuery, like this:

@NamedQuery(name = MyClass.GET_ENTRY_BY_NAME, query = "select e from entry e where e.name =':"+ Entry.NAME "'")

However it will trash my code in case the String contains single quotes in it...

Any ideas guys?

Answer 1

I guess this happens because your database field is declared as CHAR(...), and therefore stored values are padded with whitespaces which are not taken into account by = operation.

So, you may either declare your database field as VARCHAR(...) or use a built-in trim function:

query = "select e from Entry e where trim(trailing from e.name) =:"+ Entry.NAME

Answer 2

I did some research in JPA and found out that it does some automatic trimming for CHARs, I am not sure if this behaves the same with Strings, but since it is happening to me... I believe so. The only way to bypass it is by setting some attribute within the session DatabaseLogin object (see http://www.eclipse.org/eclipselink/api/1.1/org/eclipse/persistence/sessions/DatabaseLogin.html#setShouldTrimStrings) .

Well I didn't want to be messing up with the session properties so I decided to make some sort of check and throwing the same exception as the NoResultException catch does in my code.

I basically took the result from the database and compared the field with the String I used:

query.setParameter(Entry.NAME, myEntry.getName());

...

if(!StringUtils.equals(result.getName(), myEntry.getName()){
   do a cool throw just like NoResultException Catch
}

I also had to include the Trim function axtavt! This is just to make sure that if the database has a column with trailing spaces and it matches the parameter given by the user, it will be included as a valid answer. For example:

Database entry: Name = "Flavio " - Trimmed with Function = "Flavio".

Parameter passed: Name = "Flavio " - Trimmed by JPA automatic function = "Flavio".

If it isnt trimmed at all it will just Compare "Flavio " with "Flavio", returning NoResult when it was supposed to return that Entry.

Nasty workaround, but as long as there is no other way to stop the auto-trimming we will have to just make use of this sort of things.

Thanks for all the other answers!!