'like' selection from collection

flint · **Joined:** Thu Oct 16, 2008 10:46 am **Posts:** 2

Hi,

I have an object which contains a List of Strings as a member. (A customer has several maildomains, see mapping below).

I would now like to be able to search for a customer who has a certain maildomain.
I was able to do this, with the following code:

Query query = session.createQuery("from Customer c where :mailDomain in elements(c.mailDomains)");
query.setString("mailDomain", maildomain);

Unfortunately with this I am only able to search for a customer with the exact maildomain.
If a customer contains for example the following domains: "domain.com, domain.co.uk, domain.fr" I would like to be able to find this customer with a searchstring for example like "omai".

Is there any solution to this?
My only idea so far is selecting the customer_id from the table maildomains2customer with Restrictions.like("maildomain", "%" + searchString + "%") and after that, fetching the customers.

But I wonder, if there is a better solution!
Thanks for your thoughts on this!!

Hibernate version:
3.2.5-ga

Mapping documents:
<class name="Customer">
<id name="id" column="CustomerId">
<generator class="native" />
</id>

<set name="mailDomains" table="maildomains2customer">
<key column="customer_id" />
<element column="maildomain" type="string" unique="true"/>
</set>
</class>

Name and version of the database you are using:
MSSql 2005

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Code:

Query query = session.createQuery("select c from Customer c 
join c.mailDomains mails
where mails like '%' + searchString + '%'");

flint · **Joined:** Thu Oct 16, 2008 10:46 am **Posts:** 2

worked like a charm.
Thanks a lot!

SIau_Tie · **Posted:** Fri Oct 17, 2008 3:58 am

Another way is using Criteria

Code:

Criteria crit = session.createCriteria(Customer.class).createAlias("mailDomains", "mails");
crit.add(Restrictions.like("mails", input, MatchMode.ANYWHERE);
List list = crit.list();

SIau_Tie · **Posted:** Fri Oct 17, 2008 4:08 am

Another way is using Criteria

Code:

Criteria crit = session.createCriteria(Customer.class).createAlias("mailDomains", "mails");
crit.add(Restrictions.like("mails", input, MatchMode.ANYWHERE);
List list = crit.list();