JAVA: problem with NamedQuery String

Question

Hi guys, I'm having problems with exact matches when doing 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()); 

This works in most cases, however, I noticed that if the user passes a file name with a space at the end, namedQuery ignores this 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 request before. Bypassing my valid record validation. In other words, I would like the request to not return any record and handle the error later.

A workaround I could think of is single quotes associated with my parameter in namedQuery, for example:

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

However, it will be garbage of my code if String contains single quotes in it ...

Any ideas guys?

Answer 1

I did some research at JPA and found out that it does some automatic cropping for CHARs, I'm not sure if this behaves the same with strings, but since this happens to me ... I think so. The only way around this is to set the attribute in the DatabaseLogin object of the session (see http://www.eclipse.org/eclipselink/api/1.1/org/eclipse/persistence/sessions/DatabaseLogin.html#setShouldTrimStrings ).

Well, I didn’t want to interfere with the session properties, so I decided to do some kind of check and throw the same exception as catch NoResultException 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 enable the Trim axtavt feature! This is just to make sure that if the database has a column with trailing spaces and it matches the parameter specified by the user, it will be included as a valid answer. For example:

Writing to the database: Name = "Flavio" - is truncated using the function = "Flavio".

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

If it is not cropped at all, it will simply compare Flavio with Flavio, returning NoResult when it should have returned this record.

An unpleasant workaround, but so far there is no other way to stop automatic cropping, we just have to use such things.

Thanks for all the other answers !!

Answer 2

I suppose this is because the database field is declared as CHAR(...) , and therefore the stored values ​​are padded with spaces that are not taken into account in the = operation.

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

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