Java 使用休眠标准，有没有办法转义特殊字符？

Using hibernate criteria, is there a way to escape special characters?

提问by Kevin Crowell

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:

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

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.

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

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

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:

在此代码中，“searchValue”是用户为应用程序提供的用于其搜索的值。如果用户想要搜索 '%'，用户将与数据库中的每个 'foo' 条目一起返回。这是因为 '%' 字符表示字符串匹配的“任意数量的字符”通配符，并且休眠生成的 SQL 代码如下所示：

select * from foo where name like '%' 

Is there a way to tell hibernate to escape certain characters, or to create a workaround that is not database type specific?

有没有办法告诉 hibernate 转义某些字符，或者创建一个不特定于数据库类型的解决方法？

采纳答案by Daniel Reeves

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

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.

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

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.

我们还参数化了转义字符，以确保它不会破坏 SQL 查询，如果它们使用诸如 \ 或引号字符之类的东西。

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

}

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

如果您想知道 lhs 和 typedValue 方法的用途，新的 IlikeExpression 应该可以回答这些问题。

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:

在此之后，唯一剩下的就是让 Restrictions 使用这些新类：

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

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

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

回答by james

if you use LikeExpression directly, it enables you to specify the escape character. i assume that should be all you need.

如果直接使用 LikeExpression，则可以指定转义字符。我想这应该就是你所需要的。

回答by Kalem

It's not a very clean way to do it but a sqlRestrinction should be easier:

这不是一种非常干净的方法，但 sqlResrinction 应该更容易：

criterions.add(Restrictions.sqlRestriction(columnName+ " ilike '!%' escape '!'"));

You can even do a start with search using the same principle:

您甚至可以使用相同的原则从搜索开始：

criterions.add(Restrictions.sqlRestriction(columnName+ " ilike '!%%' escape '!'"));

回答by Yahor

If you use Hibernate 3.2+, you can subclass LikeExpression, and then create factory like/ilikemethods:

如果您使用 Hibernate 3.2+，您可以LikeExpression创建子类，然后创建工厂like/ilike方法：

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.LikeExpression;
import org.hibernate.criterion.MatchMode;

public class EscapedLikeRestrictions {
    private EscapedLikeRestrictions() {}

    public static Criterion likeEscaped(String propertyName, String value, MatchMode matchMode) {
        return likeEscaped(propertyName, value, matchMode, false);
    }

    public static Criterion ilikeEscaped(String propertyName, String value, MatchMode matchMode) {
        return likeEscaped(propertyName, value, matchMode, true);
    }

    private static Criterion likeEscaped(String propertyName, String value, MatchMode matchMode, boolean ignoreCase) {
        return new LikeExpression(propertyName, escape(value), matchMode, '!', ignoreCase) {/*a trick to call protected constructor*/};
    }

    private static String escape(String value) {
        return value
                .replace("!", "!!")
                .replace("%", "!%")
                .replace("_", "!_");
    }
}

回答by Alexis Pérez

If you use sqlRectrictions, the correct way to do it is the following:

如果您使用 sqlRectictions，正确的做法如下：

criterions.add(Restrictions.sqlRestriction(columnName+" LIKE '!%' ESCAPE '!'"));

criterions.add(Restrictions.sqlRestriction(columnName+" LIKE '!%' ESCAPE '!'"));

Is like a sql query, doesn't work ilike => use LIKE using Oracle 12i.

就像一个 sql 查询，不起作用 ilike => 使用 LIKE 使用 Oracle 12i。