使用hibernate标准，是否有一种方法来转义特殊字符？ [英] Using hibernate criteria, is there a way to escape special characters?

问题描述

对于这个问题，我们希望避免写一个特殊的查询，因为查询必须在多个数据库之间不同。

For this question, we want to avoid having to write a special query since the query would have to be different across multiple databases. Using only hibernate criteria, we want to be able to escape special characters.

这种情况是需要能够转义特殊字符的原因：

This situation is the reason for needing the ability to escape special characters:

假设我们在数据库中有表'foo'。表'foo'只包含1个字段，称为'name'。 名称字段可以包含可能在数据库中被视为特殊的字符。这样的名称的两个示例是name_1和name％1。 '_'和'％'都是特殊字符，至少在Oracle中。如果用户在输入数据库之后想要搜索这些示例中的一个，可能会出现问题。

Assume that we have table 'foo' in the database. Table 'foo' contains only 1 field, called 'name'. The 'name' field can contain characters that may be considered special in a database. Two examples of such a name are 'name_1' and 'name%1'. Both the '_' and '%' are special characters, at least in Oracle. If a user wants to search for one of these examples after they are entered in the database, problems may occur.

criterion = Restrictions.ilike("name", searchValue, MatchMode.ANYWHERE);
return findByCriteria(null, criterion);

在此代码中，searchValue是用户赋予应用程序使用的值搜索。如果用户想要搜索％，则用户将返回数据库中每个foo条目。这是因为'％'字符表示字符串匹配的任意数量的字符通配符，而hibernate生成的SQL代码将如下所示：

In this code, 'searchValue' is the value that the user has given the application to use for its search. If the user wants to search for '%', the user is going to be returned with every 'foo' entry in the database. This is because the '%' character represents the "any number of characters" wildcard for string matching and the SQL code that hibernate produces will look like:

select * from foo where name like '%' 

推荐答案

LikeExpression的构造函数都受到保护，因此，这不是一个可行的选择。此外，它还有自己的问题。

LikeExpression's constructors are all protected, so it's not a viable option. Also, it has problems of its own.

一位同事和我创建了一个很好的补丁。补丁的要点是对于使用MatchMode的LikeExpression构造函数，我们转义特殊字符。对于使用一个字符（转义字符）的构造函数，我们假设用户自己转义特殊字符。

A colleague and I created a patch which works pretty well. The gist of the patch is that for the LikeExpression constructor which consumes a MatchMode, we escape the special characters. For the constructor which consumes a Character (the escape character), we assume the user escapes the special characters on their own.

我们还参数化了转义字符，如果它们使用类似\或引号字符的方式，则不能破坏SQL查询。

We also parameterized the escape character to ensure that it can't corrupt the SQL query if they use something like \ or a quote character.

package org.hibernate.criterion;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.TypedValue;

public class LikeExpression implements Criterion {
    private final String propertyName;
    private final String value;
    private final Character escapeChar;

    protected LikeExpression(
            String propertyName,
            Object value) {
        this(propertyName, value.toString(), (Character) null);
    }

    protected LikeExpression(
            String propertyName,
            String value,
            MatchMode matchMode) {
        this( propertyName, matchMode.toMatchString( value
                .toString()
                .replaceAll("!", "!!")
                .replaceAll("%", "!%")
                .replaceAll("_", "!_")), '!' );
    }

    protected LikeExpression(
            String propertyName,
            String value,
            Character escapeChar) {
        this.propertyName = propertyName;
        this.value = value;
        this.escapeChar = escapeChar;
    }

    public String toSqlString(
            Criteria criteria,
            CriteriaQuery criteriaQuery) throws HibernateException {
        Dialect dialect = criteriaQuery.getFactory().getDialect();
        String[] columns = criteriaQuery.getColumnsUsingProjection( criteria, propertyName );
        if ( columns.length != 1 ) {
            throw new HibernateException( "Like may only be used with single-column properties" );
        }
        String lhs = lhs(dialect, columns[0]);
        return lhs + " like ?" + ( escapeChar == null ? "" : " escape ?" );

    }

    public TypedValue[] getTypedValues(
            Criteria criteria,
            CriteriaQuery criteriaQuery) throws HibernateException {
        return new TypedValue[] {
                criteriaQuery.getTypedValue( criteria, propertyName, typedValue(value) ),
                criteriaQuery.getTypedValue( criteria, propertyName, escapeChar.toString() )
        };
    }

    protected String lhs(Dialect dialect, String column) {
        return column;
    }

    protected String typedValue(String value) {
        return value;
    }

}

lhs和typedValue方法，新的IlikeExpression应该回答这些问题。

If you're wondering what the lhs and typedValue methods are for, the new IlikeExpression should answer those questions.

package org.hibernate.criterion;

import org.hibernate.dialect.Dialect;

public class IlikeExpression extends LikeExpression {

    protected IlikeExpression(
            String propertyName,
            Object value) {
        super(propertyName, value);
    }

    protected IlikeExpression(
            String propertyName,
            String value,
            MatchMode matchMode) {
        super(propertyName, value, matchMode);

    }

    protected IlikeExpression(
            String propertyName,
            String value,
            Character escapeChar) {
        super(propertyName, value, escapeChar);
    }

    @Override
    protected String lhs(Dialect dialect, String column) {
        return dialect.getLowercaseFunction() + '(' + column + ')';
    }

    @Override
    protected String typedValue(String value) {
        return super.typedValue(value).toLowerCase();
    }

}

是使限制使用这些新类：

After this, the only thing left is to make Restrictions use these new classes:

public static Criterion like(String propertyName, Object value) {
    return new LikeExpression(propertyName, value);
}

public static Criterion like(String propertyName, String value, MatchMode matchMode) {
    return new LikeExpression(propertyName, value, matchMode);
}

public static Criterion like(String propertyName, String value, Character escapeChar) {
    return new LikeExpression(propertyName, value, escapeChar);
}

public static Criterion ilike(String propertyName, Object value) {
    return new IlikeExpression(propertyName, value);
}

public static Criterion ilike(String propertyName, String value, MatchMode matchMode) {
    return new IlikeExpression(propertyName, value, matchMode);
}

public static Criterion ilike(String propertyName, String value, Character escapeChar) {
    return new IlikeExpression(propertyName, value, escapeChar);
}

编辑：哦。这适用于Oracle。但我们不确定其他数据库。

Oh yeah. This works for Oracle. We're not sure about other databases though.

这篇关于使用hibernate标准，是否有一种方法来转义特殊字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！