We have problem where we need to use like clause for array of strings e.g name

select * from xyz where name like '%ABC%';

Now here values will come in form of String Arrays

do we tried using

String[] a_Name; has some values

l_Query.setParameterList("Name",a_Name)

we tried using

select
r1
from
xyz r1
where
r1.Name like :Name

&

select
r1
from
xyz r1
where
r1.Name like ':Name'

It didnt work , we vene tried create Expression.like("Name",a_Name) but it gives Class cast exception as its expecting Object.


Any Solution.

how do you want to put the values in the query? give me an example of what the query should look like if this array is passed:

String[] param = new String[]{"foo", "bar"}



Farzad-

What about to use Criteria instead of HSQL? We use Criteria queries in 99% of cases

I don't think you can use LIKE as you would an IN clause.

You probably need to do something like (in SQL terms)

select * from some_table where text_column like '%ABC%' OR text_column like '%DEF%' OR text_column like '%GHI%'

Though if you're doing a bunch of crazy text match queries, you might be better off using Hibernate Search (Lucene)

Try using critera query
with "like" restriction in disjunctions
and you can iterate this for every parameter in the Array.

It will create a SQL like:

select * from.... where name like '% 1stString %'
or name like '% 2stString %'
or name like '% 3stString %'
....

this worked in my case..
hope it helps you also

Basically the query should be something like this

select * from xyz
where
Name like '%ABC%'
and Report_Date = (
select max(Report_Date) from
xyz
where
Name like '%ABC%'
)

where Name will be populated as a String []

String[] name = new String[]{"%ABC%", "%EFG
%", "%IRY%"}

we tried using

l_Query.setParameterList("Name",name) with namedquery as

select * from xyz
where
Name like :name
and Report_Date = (
select max(Report_Date) from
xyz
where
Name like :name
)

Its was giving ORA-00907: missing right parenthesis

Another way we tried using "like" restriction in disjunctions which will create the query as

select * from xyz
where
name like '%GEGW%' or name like '%BC07%' or name like '%BACF%' and Report_Date = (
select max(Report_Date) from
xyz
where
name like '%GEGW%' or name like '%BC07%' or name like '%BACF%'
);

This will not give the right data from sub query as it will give max of report_date among all names.

Based on above cases i might end up using a sp .

Any other solution using crietria queries or something else .

Thanks for all inputs till now