hibernate string search question

[Adrian Pang]

Hi,

I'm a Hibernate newbie and I've a simple dumb question: I'm trying to construct a HQL statement for searching a database string field. What I want is kind of like:

select n.id from ContactName as n where n.name like attern%

so if the pattern is 'a', I get all the contacts whose name start with an A, for example. However, I can't seem to get this to work -- what's the correct syntax for this? I also tried to add % in the pattern string directly, but that doesn't work neither.

Thanks a lot!!!
Adrian

[David Madouros]

If you want the actual ContactName objects back, try using any of the follwing:

Query q = Session.createQuery( "from ContactName where name like :name" );
q.setParameter( "name", arg + "%");
 
Query q = Session.createQuery( "from ContactName where name like :name||'%'" );
q.setParameter( "name", arg);
 
Query q = Session.createQuery( "from ContactName where name like '" + arg + "%'" );

Personally, I prefer the first example.

Also, using 'select n.id' will return a List of Long/Integer (depending on how you defined id) if that's what you were trying to do.
[ March 08, 2007: Message edited by: David Madouros ]

[Mark Spritzler]

I agree with David. Go with the first example.

Do not ever go with the third example, because it leaves you open for SQL based hacker attacks.

Mark

[Adrian Pang]

Thank you!