Geek Answers Handbook

How to use regular expressions with Hibernate / Oracle

I am trying to implement a web service that accepts a list of strings, each of which is a regular expression. They need to be compared with six database columns, and any rows returned should be returned.

I believe that Oracle has a regexp_like () function that I could use, but I am looking for a better way to do this with Hibernate, so I am not working against the persistence mechanism.

I started with something like this, in which the collection of participants contains 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 does not work, because "in" will not do what I want, and this does not seem to tell Hibernate to use the regex.

This is the only answer I came up with, but it looks very 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 am trying to do as much for Oracle as I can. This assessment seems to work, but entering restrictions without using parameters means that I am losing a lot of potential efficiency. Can anyone see a better way to do this? For simplicity, I trust the regular expressions that are passed to me.

+4
source share
2 answers

In hibernate docs, nothing exists to execute regular expression queries (using HQL or Criteria queries). The approach using sqlRestrictions should probably be changed to one of the overloaded methods to avoid SQL Injection vulnerability.

Code example:

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

Similar working example

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

More articles:


All Articles