Ia m using hibernate 3.1 and was wondering how to use a like statement in a sql-query when loading a collection. I have the key set up but I don't want it to have to match exactly. Please let me know if I am not clear. Thanks.


<set name="laboratories" inverse="true" lazy="false">
<key property-ref="indicOrganism">
<column name="indic_organisms" not-null="true" />
</key>
<one-to-many class="com.yum.fsraqa.model.Laboratory" />
<loader query-ref="labs" />
</set>

<sql-query name="labs">
<load-collection alias="lab" role="com.yum.fsraqa.model.IndicOrganism.laboratories" lock-mode="read" />
SELECT {lab.*} FROM labratory lab WHERE lab.indic_organisms like :indicOrganism
</sql-query>

I am not sure where to add the %% so that the select will return all results like %indicOrganism%

Thanks again

Yes you have to add the %'s

Hi,

You have to append the % before and after the :indicOrganism variable.

that means if your search is like

indicOrganism = test

then you should pass the indicOrganism like %test%

it will work

I understand that I need the % in the query but I am trying to use this in an automatic loading collection. I am not running any of this in "code" it is all being done on initialization of the class by hibernate.

If I didn't need the like part of the statement, everything would work fine, but since I am not simply using an =, the collection does not get populated correctly.

Does what I want to do make sense?

Hi,

I understood that you have Collection and you want to apply wildcard for all the object in the collection,

You can iterate the collection and append the % before and after. now the collection contains with wild card. you can use it directly like below


List l = new ArrayList();
String s = "%arsh %";
l.add(s);
s = "%avid% ";
l.add(s);
Query q = session.createQuery(" from DefectDetails d where d.Assignedto = :assign");
q.setParameterList("assign", l);

It will work

I know its been a wheil since i posted a resolution, but none of these answers work.

Let me explain a little more about what I am trying to do. I want to have hibernate automatically load the set:

<set name="laboratories" inverse="true" lazy="false">
<key property-ref="indicOrganism">
<column name="indic_organisms" not-null="true" />
</key>
<one-to-many class="com.yum.fsraqa.model.Laboratory" />
<loader query-ref="labs" />
</set>

and it is a many to many relatinship, the Laborator class has a field indicOrganisms that is a list of names deliminated by "-", ex. "One-Two-Three"

I want the IdicOrganism to load any Lab that has its name in this field, i.e. IndicOrganism "One" should load all Labs with "One", "One-Two", etc.

I am trying to use this sql-query
<sql-query name="labs">
<load-collection alias="lab" role="com.yum.fsraqa.model.IndicOrganism.laboratories" lock-mode="read" />
SELECT {lab.*} FROM laboratory lab WHERE lab.indic_organisms like :indicOrganism
</sql-query>

when I add the "%" around the :indicOrganism I get the error,
Line 1: Incorrect syntax near '@P0'