2

Given the following table, where wildcard is a String:

ID   wildcard
1     foo
2     fo
3     f

I'd like to be able to build a Hibernate Criterion for matching against an input field that matches any of the wildcard's fields.

The following example should clarify what I mean by "match":

querying with "foobar" should match all 3 of these rows since:

foo%, fo%, and f% all include foobar

In other words, I'd like the query to return where the input value begins with a row's wildcard column.

One way for me to do this would to be set an or criterion term where I check if the wildcard equals each of the pieces of foobar.

Example:

select * from table where wildcard = 'f' or wildcard = 'fo' or 
      wildcard = 'foo' or wildcard = 'foob' or 
          wildcard = 'fooba' or wildcard = 'foobar'

But I'm sure that I could do better?

How can I do this with a Hibernate Criterion?

Kevin Meredith
  • 41,036
  • 63
  • 209
  • 384

1 Answers1

2

In Oracle/PostgresQL you can use:

select * from table where 'foobar' like wildcard || '%'

I'm not sure if the || string concatenation is Ansi SQL standard (I think not) so in other databases you need to replace it with the concatenation function or operator of that database.

Erwin Bolwidt
  • 30,799
  • 15
  • 56
  • 79
  • thanks, Erwin, that query worked for me. I +1'd, but I'm still trying to figure out how to do this in Hibernate. – Kevin Meredith Jul 23 '14 at 04:06
  • I'll put this as a comment since I'm not able to test it, but I think this should work: `Restrictions.sqlRestriction("'foobar' like {alias}.wildcard || '%'");` (granted there should be nicer ways, but I'm not sure that the Criterion API can handle complex expressions like concatenation) – Erwin Bolwidt Jul 23 '14 at 04:17