This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Add wildcards in an "in" clause in HQL?

 
Akash Mohapatra
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose I have a list of countries in a database column.

The user enters a list of characters (eg. 'I','J','U',...) to fetch the names of those countries which start with the given character.

I would normally do it in SQL as


I think, the same can be achieved in HQL by creating a query dynamically and adding OR conditions to it.
This is because I cannot use "in" and "like" with each other
i.e I cannot have "...where country_name in(like 'I%', like 'J%')"

Is there an efficient way of doing it in hibernate or do I have to create the query dynamically?
 
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use the Hibernate Criteria API, and leverage the MatchMode type.

Here's some code that searches for emails with .com at the end:



You could add onto it by adding more email addresses, or using the Restrictions class. You can use the MatchMode in various ways:

static MatchMode ANYWHERE
Match the pattern anywhere in the string
static MatchMode END
Match the end of the string to the pattern
static MatchMode EXACT
Match the entire string to the pattern
static MatchMode START
Match the start of the string to the pattern


Check out this little tutorial on the Hibernate3 Criteria API. I think you'll find a few answers to your problem.

How to use the Hibernate Criteria API

-Cameron McKenzie
 
Akash Mohapatra
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the quick reply Cameron
The Criteria API looks great indeed
 
Akash Mohapatra
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cameron,

I tried to set restrictions on the nested properties of class User
i.e I tried

Criteria criteria = session.createCriteria(User.class);
Criterion c1 = Restrictions.eq("address.city", "xyz");
criteria.add(c1);

It causes an exception - Could not find property "address.city" in class User.

So the question here is that can I create restrictions on nested fields of a class? If yes, How?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic