如何在 Hibernate/Oracle 中使用正则表达式

How to use regular expressions with Hibernate/Oracle

提问by Bret

I'm trying to implement a web service which accepts a list of strings, each of which is a regular expression. These need to be compared against six columns of a database, and any rows which match need to be returned.

我正在尝试实现一个 Web 服务，它接受一个字符串列表，每个字符串都是一个正则表达式。这些需要与数据库的六列进行比较，并且需要返回匹配的任何行。

I believe Oracle has a regexp_like() function which I might be able to use, but I'm looking for the bestway to do this using Hibernate, so I'm not working against the persistence engine.

我相信 Oracle 有一个我可能可以使用的 regexp_like() 函数，但我正在寻找使用 Hibernate 执行此操作的最佳方法，因此我不反对持久性引擎。

I started with something like this, in which the participants collection contains the regular expressions:

我从这样的事情开始，其中参与者集合包含正则表达式：

List<Message> messages = new ArrayList<Message>();
List<Message> m1 = ((Session) entityManager.getDelegate())
    .createCriteria(MessageSSR.class).add(Restrictions.or(
            Restrictions.in("Node2Id", participants),
            Restrictions.in("Node2Id", participants))).list();
List<Message> m2 = ((Session) entityManager.getDelegate())
    .createCriteria(MessageSSR.class).add(Restrictions.or(
            Restrictions.in("Node3Id", participants),
            Restrictions.in("Node4Id", participants))).list();
List<Message> m3 = ((Session) entityManager.getDelegate())
    .createCriteria(MessageSSR.class).add(Restrictions.or(
            Restrictions.in("Node5Id", participants),
            Restrictions.in("Node6Id", participants))).list();
messages.addAll(m1);
messages.addAll(m2);
messages.addAll(m3);

This doesn't work because "in" won't do what I want, and this does not appear to tell Hibernate to use a regular expression match.

这不起作用，因为“in”不会做我想要的，而且这似乎没有告诉 Hibernate 使用正则表达式匹配。

This is the only answer I've come up with, but it looks really ugly:

这是我想出的唯一答案，但它看起来真的很难看：

List<Message> messages = new ArrayList<Message>();
for (String re : participants) {
    List<Message> m1 = ((Session) entityManager.getDelegate())
        .createCriteria(MessageSSR.class)
        .add(Restrictions.or(
                Restrictions.sqlRestriction("regexp_like(NODE_1, " + re + ")"),
                Restrictions.sqlRestriction("regexp_like(NODE_2, " + re + ")")
        )).list();
    List<Message> m2 = ((Session) entityManager.getDelegate())
        .createCriteria(MessageSSR.class)
        .add(Restrictions.or(
                Restrictions.sqlRestriction("regexp_like(NODE_3, " + re + ")"),
                Restrictions.sqlRestriction("regexp_like(NODE_4, " + re + ")")
        )).list();
    List<Message> m3 = ((Session) entityManager.getDelegate())
        .createCriteria(MessageSSR.class)
        .add(Restrictions.or(
                Restrictions.sqlRestriction("regexp_like(NODE_5, " + re + ")"),
                Restrictions.sqlRestriction("regexp_like(NODE_6, " + re + ")")
        )).list();
    messages.addAll(m1);
    messages.addAll(m2);
    messages.addAll(m3);
}

I'm trying to push as much of this over to Oracle as I can. This appraoch seems likely to work, but putting the restrictions in without using parameters means I'm losing a lot of potential efficiency. Can anyone see a better way to do this? For simplicity, I'm trusting the regular expressions that are being passed to me.

我正在尝试将尽可能多的内容推送给 Oracle。这种方法似乎可行，但是在不使用参数的情况下进行限制意味着我失去了很多潜在的效率。谁能看到更好的方法来做到这一点？为简单起见，我相信传递给我的正则表达式。

回答by Adam Hawkes

There's nothing in the hibernate docs for performing regular expression queries (using HQL or Criteria queries). The approach using the sqlRestrictionsshould probably be changed to one of the overloaded methods to avoid a SQL Injection vulnerability.

休眠文档中没有用于执行正则表达式查询（使用 HQL 或 Criteria 查询）的内容。使用 的方法sqlRestrictions可能应该更改为重载方法之一，以避免 SQL 注入漏洞。

Example code:

示例代码：

Restrictions.sqlRestriction("regexp_like({alias}.NODE_1, ?)", re, Hibernate.STRING)

回答by Kisanagaram

Similar working example

类似的工作示例

 Criterion criterion = Restrictions.sqlRestriction("regexp_like (column_name, ?, 'i')", "(^|\s)"+searchValue+"($|\s|.$)", StringType.INSTANCE);