In a spring boot application we use queryDSL for accessing a database. The application shall print all items from a table that match (user-input dependend) search parameters.

Examples:

  • Give me all locations with a name that exactly matches "Berlin"
  • Give me all locations with a name that begins with "Ber".

So we have to dynamically create a where clause.

We use an entity class like

package example;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "location")
public class LocationEntity {

    @Id
    @GeneratedValue
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    public LocationEntity() {
        // -
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

a query class like

package example;

import com.querydsl.core.types.dsl.EntityPathBase;
import com.querydsl.core.types.dsl.NumberPath;
import com.querydsl.core.types.dsl.StringPath;

public class QLocationEntity extends EntityPathBase<LocationEntity> {
    private static final long serialVersionUID = 1L;

    public static final QLocationEntity DEFAULT = new QLocationEntity("loc_1");

    public final NumberPath<Long> id = createNumber("id", Long.class);

    public final StringPath name = createString("name");

    public QLocationEntity(String tableAlias) {
        super(LocationEntity.class, tableAlias);
    }
}

a repository class like

package example;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface LocationRepository extends JpaRepository<LocationEntity, Long>,
        /* needed for query DSL. */
        QuerydslPredicateExecutor<LocationEntity> {
    /*
     * We don't need custom methods.
     */
}

and a litte helper class like

package example;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.BooleanExpression;

@Service
public class LocationRepositoryHelper {
    protected final Log LOG = LogFactory.getLog(getClass());

    @Autowired
    private LocationRepository repository;

    public Sort sort = new Sort(Sort.Direction.ASC, "name");

    public Iterable<LocationEntity> findEntities(String paramLocation, boolean autocomplete)
            throws RequestParameterInvalideException {
        Predicate p = generatePredicate(paramLocation, autocomplete);
        if (p != null) {
            return repository.findAll(p, sort);
        } else {
            return repository.findAll(sort);
        }
    }

    BooleanExpression generatePredicate(String location, boolean autocomplete) {
        if ("".equals(location.trim())) {
            return null;
        } else if (autocomplete) {
            return QLocationEntity.DEFAULT.name.startsWith(location);
        } else {
            return QLocationEntity.DEFAULT.name.eq(location);
        }
    }
}

(This example is far less complex than our real application code, but it should be sufficient to demonstrate our problem.)

Our application asks the helper class for a list and spring boot should do the rest by voodoo magic.

When we use an oracle database as rdbms all is fine. When we use ignite, all is fine as long as we don't try to use 'autocompletion'.

When we try to "autocomplete the search string" we get an IgniteException that says: unsupported query: locationen0_.name like ?1 ESCAPE '!'

We logged the following sql string:

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name like ? escape '!' order by locationen0_.name asc

We assume "?" has to be replaced with "Ber%", so the fully qualified sql statement should be:

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name like 'Ber%' escape '!' order by locationen0_.name asc;

We ran this statement manually in an SQL console on an Oracle DB (12.*) and on Ignite (2.7). On Oracle all was fine, Ignite still announced we would have a syntax error / unsupported query. So we tried some alternatives on Ignite...

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name = 'Berlin' order by locationen0_.name asc;
=> all fine, but doesn't return what we want.

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name like 'Berlin' order by locationen0_.name asc;
=> all fine, but still doesn't return what we want.

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name = 'Ber%' order by locationen0_.name asc;
=> still all fine and would return what we want.

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name = 'Berlin' escape '!' order by locationen0_.name asc;
=> all fine (woot??), but wouldn't return what we want.

select locationen0_.id as id1_0_, locationen0_.name as name2_0_ from my_location locationen0_ where locationen0_.name = 'Ber%' escape '!' order by locationen0_.name asc;
=> (the original statement) unsupported query, but is what queryDSL (supposedly) generates and what should return what we want.

Our first conclusion is:

  1. Ignite knows the ESCAPE key word; using the key word doesn't cause a problem in all cases.
  2. Ignite understands like-statements with "%" inside.
  3. Ignite does not accept like-statements with "%" inside in combination with the escape key word.

After several hours of analysis we now know "what causes the problem", but we don't know, why this is a problem at all. The queryDSL framework (version 4.2.1) attaches the escape key word hardcoded, so we have no clue how to suppress it. Switching to another framework could be an option, though we'd like to avoid the refactoring. To dump the framework and "build the statement by string concatenation" would work but is no option for productive code.

So our questions are: Does anyone use queryDSL and Ignite and does NOT have this problem? If so, do you use queryDSL in a totally other way than we do? (Do we use queryDSL in a way "it is not meant to be used"?) Or do you know a configuration option for Ignite that solves the problem? Does anyone else have a hint?

1 Answers

0
alamar On

I have researched the source and it turns out Ignite prohibits ESCAPE explicitly. We check if ESCAPE is supplied and indicate an error if it is.

I think you can file an issue against Apache Ignite JIRA.