LIKE语句导致Ignite2.7上使用QueryDSL进行不支持的查询

Question

在Spring Boot应用程序中，我们使用queryDSL访问数据库。应用程序将打印表中匹配（用户输入依赖）搜索参数的所有项。

null

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;
    }
}

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);
    }
}

类似于

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.
     */
}

和一个类似于

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);
        }
    }
}

（这个示例远没有我们实际的应用程序代码复杂，但它应该足以证明我们的问题。）

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

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;

我们在Oracle DB(12.*)和Ignite(2.7)上的SQL控制台中手动运行该语句。在Oracle上一切正常，Ignite仍然宣布我们将出现语法错误/不支持的查询。所以我们在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.

我们的第一个结论是：

1. 点燃知道逃生关键词；使用关键字并不是在所有情况下都会导致问题。
2. Ignite理解内部带有“%”的like-statements。
3. Ignite不接受包含“%”和escape关键字的LIKE语句。

Answer 1

我研究了源代码，结果发现Ignite明确禁止逃跑。我们检查是否提供了ESCAPE，如果提供了则指示错误。

我认为您可以针对Apache Ignite Jira提出问题。