aspose file tools*
The moose likes Object Relational Mapping and the fly likes Like % question Criteria Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Like % question Criteria " Watch "Like % question Criteria " New topic
Author

Like % question Criteria

camilo lopes
Ranch Hand

Joined: Aug 08, 2007
Posts: 202
Hi Guys,

I am confused with LIKE in Criteria I have passed this value to my string: "lop%es"

and in the SQL Server i have saved : lopes (column last name )

the result is true. why? I expected that my criteria list would be empty.

criteria.add(Restrictions.like("lastName", "lop%es"));


any idea guys?

thanks.



Brazil - Sun Certified Java Programmer - SCJP 5
http://www.camilolopes.com/ About Java - Update every Week.
Guide SCJP - tips that you need know http://blog.camilolopes.com.br/livrosrevistaspalestras/
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

What happens if you execute a SELECT statement against the table with ...lastname LIKE 'lop%es' ? Wouldn't it return any result?


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
camilo lopes
Ranch Hand

Joined: Aug 08, 2007
Posts: 202
Hi Kamara,

I executed in DB and the result was empty. so, in DB worked as expected. I did not understand, because in hibernate is different. do you have any idea?
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

"%" act as a wild card character so you should not get an empty result. What query did you run?
camilo lopes
Ranch Hand

Joined: Aug 08, 2007
Posts: 202
I executed
select * from user where lastname LIKE 'Lop%es"
in my db saved we have:
name: camilo
lastname: Lopes


Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

Why you use single and double quotes in the query? Use only single quote. And can you show a screen of this if it still returns no results?
camilo lopes
Ranch Hand

Joined: Aug 08, 2007
Posts: 202
oh sorry I wrote wrong. this is correct:



the result is empty.
Gamini Sirisena
Ranch Hand

Joined: Aug 05, 2008
Posts: 347
If do
select * from user where lastname='Lopes'
what happens?
camilo lopes
Ranch Hand

Joined: Aug 08, 2007
Posts: 202
hm of this way it works.


I do not why of this. I executed againt hsqldb via Hibernate using criteria Like and the string 'LoP%es' the result was as expected. any result. But with sql server, I have data returned with 'Lop%es' what should be ignored once that I do not have this data in DB.

it is strange, is not it?



Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

What happens for any other combination, LIKE 'Lopes%' or LIKE '%Lopes%' etc...?
camilo lopes
Ranch Hand

Joined: Aug 08, 2007
Posts: 202
of this way, it works as expected. db return the record. I executed:



and

LIKE '%Lo%pes%' : the problem here, the DB returned data. I expected empty.

do you get to see any problem?
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

They all seems fine then. Have you used any proprietary type for the column?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Like % question Criteria