Ebean: Join multiple expressions with OR

Question

I use the PLAY Framework 2.2.1 and I want to build a query to search the database for terms I get from an input form.

This is what I have got so far but it doesn't work:

...
List<String> terms;
public static Model.Finder<Integer, XYZ> find = new Model.Finder<Integer, XYZ.class>(Integer.class, XYZ.class);
ExpressionList<XYZ> el = XYZ.find.where();

for(String term : terms){
    el.add(Expr.ilike("name", term + "%"));
}

List<XYZ> results = XYZ.find.where().add(el.disjunction()).findList();

I don't know how to add and connect multiple expressions with OR to my query.

biesior · Accepted Answer · 2013-11-25T17:00:54.070

5

I think that you'll do that fastest with custom query (like showed in select sample)

com.avaje.ebean.Query<Xyz> query = com.avaje.ebean.Ebean.createQuery(Xyz.class);
query.where("lower(first_name) like :first_name OR lower(last_name) like :last_name ")
     .setParameter("first_name", "John".toLowerCase()+"%")
     .setParameter("last_name", "Doe".toLowerCase() + "%");

List<Xyz> xyzList = query.findList();

You can also make more advanced combination with numbered params, ie:

List<String> sqlList = new ArrayList<>();
List<Object> paramsList = new ArrayList<>();


sqlList.add("lower(first_name) like ?");
paramsList.add("John".toLowerCase() + "%");

sqlList.add("lower(last_name) like ?");
paramsList.add("John".toLowerCase() + "%");

if (optionallyCheckTheNick) {
    sqlList.add("lower(nick) like ?");
    paramsList.add("johnie".toLowerCase() + "%");
}


Query<Xyz> query = Ebean.createQuery(Xyz.class);
query.where(StringUtils.join(sqlList, " OR "));

int i = 1; // first param uses index 1 NOT 0!
for (Object param : paramsList) {
    query.setParameter(i, param);
    i++;
}


List<Xyz> xyzList = query.findList()

Just make sure that you always adds to sqlList and paramsList in pairs!

edited Nov 25 '13 at 17:00

answered Nov 25 '13 at 14:51

biesior

55,576
10
125
182

What if I don't want twice "like" but n "like". How can I make a loop? Is there no method for this issue? If I understood right, one can use the method dijunction() for a given list of Expressions (=> ExpressionList). But I don't know how to create a new instance and how to apply it. – Matthias Munz Nov 25 '13 at 15:00
Your code works fine for me except that there are no results if I a have more than one parameter for one attribute. What I want is a query like this: select t0.id c0, t0.name c1 from view_xyz t0 where t0.name like "ab%" or t0.name like "b% or t0.name like "c%" But System.out.println(query.getGeneratedSql()) returns this: select t0.id c0, t0.name c1 from view_xyz t0 where t0.name like ? – Matthias Munz Nov 26 '13 at 08:45
1

I finally managed it. I forgot that I first have to split the values that I get from DynamicForm f = play.data.Form.form().bindFromRequest(); Map data = f.data(); for (Map.Entry entry : data.entrySet()) {...} – Matthias Munz Nov 26 '13 at 09:02

Ebean: Join multiple expressions with OR

1 Answers1