Regex Queries with Generated Hibernate for PostgreSQL

General Questions/Discussions
1

Hi,

I’d like to know how I can perform a query through my generated hibernate-classes for PostgreSQL.

I try to query something like that:


SELECT * FROM table ~* 'foo';

via a database-tool I get a valid result.

The same query doesn’t work with my hibernate.
I create a string that contains “table ~* ‘foo’”

What am I doing wrong?

Regards,
Eraser

2

Please check the syntax in HQL in following link:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html

3

Does it matter that I use a Tomcat instead of a JBoss due to this documentation?

I found some forum posts that metioned that I have to patch my Hibernate Parser to understand ‘RLIKE’

Or is it possible to replace the mentioned ~* above with any other HQL commands?

4

Hello Eraser,

Actually the rlike is not yet supported. You can try using native SQL for Regex Query, but these will result in losing the greatest benefit from ORM since result from native SQL is not object. I suggest you can just select the ID of the record from query, then using the load by ID method to retrieve the object record.


Session session =PersistentManager.instance().getSession().getHibernateSession();
String sql = "your_select_statement";
Query query = session.createSQLQuery(sql);		

Object[] result = query.list().toArray(); 
for (int i = 0; i < result.length; i++) {
		// get the ID from result,
		// then using the LoadByID method to retrieve the actual object
}

Best regards,
Rain