I am trying to use the query by example approach provided for within Hibernate. And it works ... sort of ... No matter what I put in the example object, the query returns all records from the database (as objects, of course). Clearly, I am doing something wrong.

Here is my code, including the documentation that tells you what it is supposed to do:

/** Find all service providers that are "like" a specified
ServiceProvider object. This method inplements a "query
by example" of the database, searching for all
ServiceProviders which are "like" the specified ServiceProvider.
The ServiceProvider example potentially has some of
the "key field" attributes empty or only partially
completed. The search algorithm implemented in this method
will return zero or more <code>ServiceProvider</code>
objects, each encapsulating a "matched" record.
@param sp A <code>ServiceProvider</code> encapsulating the
"key field" data to be used as the basis for the query..
@return An array of ServiceProviders, each
encapsulating a "matched" record. If no matches were
found, a zero length array is returned. */
public ServiceProvider[] findAllLikeThis(ServiceProvider sp)
throws HibernateException {
List results;
//
Session session = HibernateUtil.getSession();
try {
Transaction tx = session.beginTransaction();
results = session.createCriteria(ServiceProvider.class)
.add (Example.create(sp)
.enableLike(MatchMode.START)
.excludeProperty("specialities")
.excludeProperty("size")
.excludeProperty("rate")
.excludeProperty("owner"))
.list();
tx.commit();
} catch (HibernateException he) {
he.printStackTrace();
System.out.println(he.getCause());
throw he;
} finally {
HibernateUtil.closeSession();
}
return (ServiceProvider[])results.toArray(new ServiceProvider[0]);
}

There is a composite-key on this object, the two attributes which are NOT excluded from the Example, name and location. None of the attributes are null ... ever. The ServiceProvider objects are constructed so that every attribute is initialized as an empty srting, if no other value is supplied. I want to be able to specify a name like "Fred" and have the query by example find all "Fred%" records ("Fred", "Fredrick", Fredwena", etc). If I specify no values, I want the query to find all records; effectively find all like "%" (MatchMode. START). The query as presently built finds all records no matter what I specify.

What is wrong? Thanks.

What will be found depends on the property values of ServiceProvider.

If I got you right, the only properties not excluded in ServiceProvider are primary key properties. If yes, this will not work.

The reference manual states in chapter 16.6


HTH
Ernst

Oh, Ernst! You are absolutley right! I have read that sentence in the manual at 16.6 several times, but the significance - in this context - never hit me.

So, ... query by example is of limited use ... certainly limited use to me. I guess I will try using an HQL statement, then I will externalize that once I get it to work.

Thank goodness for the forums ... and for good folks like you who are willing to help others.

Thank you. I am grateful.

Here is the solution I have come to:

Transaction tx = session.beginTransaction();
results = session.createQuery
("From ServiceProvider "
+ "where name like :sp_name "
+ "and location like :sp_location")
.setString("sp_name", sp.getName()+"%")
.setString("sp_location", sp.getLocation()+"%")
.list();
tx.commit();

It works pretty well (I have one ity-bitty issue, but I will ask about that in a separate posting). However, I do not expect to require this query anywhere else in the code, so there really is no reason to externalize it.