How to use regular expressions with Hibernate/Oracle

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.

Answers


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)

Need Your Help

How do I add HTML links in C# TextBox?

c# html textbox hyperlink

How can I put a link in a C# TextBox? I have tried to put HTML tags in the box but instead of showing a link it shows the entire HTML tag. Can this be done with a TextBox?

rails 4 users reviews for users how to do this

ruby-on-rails ruby-on-rails-4

i want create reviews for users from users, how better create db and Active Record Associations, i am thinking of create model reviews, reviews table with content string and table users_users with 2

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.