How to use regular expressions with Hibernate/Oracle

Question

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.

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

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.

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.

anon · Accepted Answer · 2010-12-02 17:23:01Z

7

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

Example code:

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

answered Dec 2, 2010 at 17:23

anon

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Beardsley Over a year ago

Works great if you need to use the :alpha: operator Oracle provides since overriding the : with \\: never seemed to work.

Kisanagaram · Accepted Answer · 2015-03-10 17:56:53Z

0

Similar working example

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

answered Mar 10, 2015 at 17:56

Kisanagaram

2992 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to use regular expressions with Hibernate/Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related