criteria query is not getting data when adding or condition

Question

I have a Java file that query data and I'm trying to add a filtering function. By using criteria builder and criteria query, I manage to get data that I want to filter.

As starter, this is the column to display my data:

Name              Host
Fikrie            ubuntu
Fikrie2           unix
Fikrie3           ulalala
Fikrie4           ugagaga

There are 3 variable used here. The Name column, is displaying data from name. For the Host column, it is a bit tricky. It will display the hostname, but if there is a logAsHost display, this data will overwrite the hostname.

So this is how my data really look like:

Name              Host
Fikrie            ubuntu      <-- hostname = 1, logAsHost = ubuntu
Fikrie2           unix        <-- hostname = 123, logAsHost = unix
Fikrie3           ulala       <-- hostname = ulala, logAsHost = no value
Fikrie4           ugaga       <-- hostname = ugaga, logAsHost = no value

When I try to filter just 1 variable, I manage to do so. (Eg. filter by Name). When I try to filter out 2 variable, then a problem happen. I didnt manage to get any data.

This is the code that I use:

public List<Connection> retrieveAll(String nameFilter, String hostFilter,
        int start, int length) {
    ServiceUtil.requireAdmin();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
    Root<Connection> c = q.from(Connection.class);
    q.select(c);

    logger.info("nameFilter = [" + nameFilter + "]");
    logger.info("hostFilter = [" + hostFilter + "]");

    //This is the line that I use to query data.
    //when I replace Connection_.hostname with Connection_.logAsHost, or Connection_.name
    //It works just fine. So I use either one of these line to query.
    //q.where(cb.like(c.get(Connection_.hostname), "%" + hostFilter + "%"));
    //q.where(cb.like(c.get(Connection_.logAsHost), "%" + hostFilter + "%"));
    //q.where(cb.like(c.get(Connection_.name), "%" + nameFilter + "%"));

    //This is the problem part.
    //When I add cb.or, it cannot get any data.
    //From the documentation, it should just be q.where(cb.or(A, B))
    //Where A is the first expression and B is the second.
    // I have confirm both the expression are working by calling it separately
     q.where(cb.or(cb.like(c.get(Connection_.hostname), "%" + hostFilter + "%")), cb.like(c.get(Connection_.logAsHost), "%" + hostFilter + "%"));

    List<Connection> results = em.createQuery(q).setFirstResult(start)
            .setMaxResults(length).getResultList();

    for (Connection conn : results) {
        logger.info("Name=" + conn.getName() + ", hostname=["
                + conn.getHostname() + "]" + ", logAsHost =["
                + conn.getLogAsHost() + "]");
    }

    return results;
}

This the log to show that the data is available or not:

I used c.get(Connection_.hostname), and passed u to hostFilter,

INFO nameFilter = []
INFO hostFilter = [u]
INFO Name=fikrie3, hostname=[ulala], logAsHost =[]
INFO Name=testt, hostname=[ugaga], logAsHost =[]

I used c.get(Connection_.logAsHost), and passed u to hostFilter,

INFO nameFilter = []
INFO hostFilter = [u]
INFO Name=fikrie, hostname=[192.168.56.90], logAsHost =[ubuntu]
INFO Name=fikrie2, hostname=[192.168.56.90], logAsHost =[unix]

I combine both and passed u to hostFilter,

 INFO nameFilter = []
 INFO hostFilter = [u]

I'm assuming the cb.or() is causing this error. If so, how do I use OR condition properly in criteriaquery? I'm following this part or(Expression<java.lang.Boolean> x, Expression<java.lang.Boolean> y) from the documentation.

score 0 · Answer 1 · answered Oct 01 '15 at 11:42

For me it looks like only a problem with your parenthesis. Your predicates are not parameter to the or method

you have

q.where(
    cb.or(
        cb.like(c.get(Connection_.hostname), "%" + hostFilter + "%")
    ),
    cb.like(c.get(Connection_.logAsHost), "%" + hostFilter + "%")
);

Which lead to an AND of both predicates.

It should rather be

q.where(
    cb.or(
        cb.like(c.get(Connection_.hostname), "%" + hostFilter + "%"),
        cb.like(c.get(Connection_.logAsHost), "%" + hostFilter + "%")
    )
);

criteria query is not getting data when adding or condition

1 Answers1