Hibernate Community • View topic

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Custom HQL Function

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

wavyx

Post subject: Custom HQL Function

Posted: Wed Apr 03, 2013 4:07 am

Newbie

Joined: Wed Apr 03, 2013 4:05 am
Posts: 2

Hi,

I'm trying to support custom functions via HQL queries:
- ilike (because I would like to avoid the ugly lower() like lower()
- Postgresql full-text search ts_vector() @@ ts_query()

So I extended the PostgresqlDialect as explained in:
- https://forum.hibernate.org/viewtopic.php?p=2447426
- https://groups.google.com/forum/?fromgroups=#!topic/play-framework/UdmuM77yC24
(- http://stackoverflow.com/questions/11793159/how-to-use-oracles-regexp-like-in-hibernate-hql)

Code:

    CustomPostgresDialect() {
        super()
        registerFunction( "custom_ilike", new VarArgsSQLFunction(StandardBasicTypes.BOOLEAN, "", " ilike ", ""));

    }

Since I use hibernate with Grails, I added to Datasource.groovy:

Code:

dataSource {
    dialect = "CustomPostgresDialect"
}

but when I run:

Code:

Item.executeQuery("SELECT DISTINCT i FROM Item i WHERE custom_ilike(i.status, '%')")

I got the following error:

Code:

org.springframework.orm.hibernate3.HibernateQueryException: unexpected AST node: ( near line 1, column 83 [SELECT DISTINCT i FROM com.auctelia.platform.model.sales.Item i WHERE custom_ilike(i.status, '%')]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 83 [SELECT DISTINCT i FROM com.auctelia.platform.model.sales.Item i WHERE custom_ilike(i.status, '%')]

Did I miss something?
Any other preferred solutions? The only option I see would be raw SQL with new Sql(Datasource).

Thanks for your help!

Last edited by wavyx on Wed Apr 03, 2013 5:11 am, edited 1 time in total.

Top

wavyx

Post subject: Re: Custom HQL Function

Posted: Wed Apr 03, 2013 5:11 am

Newbie

Joined: Wed Apr 03, 2013 4:05 am
Posts: 2

Finally found the solution, looking at examples on stackoverflow.

Here are my custom functions:

Code:

    TableNameSequencePostgresDialect() {
        super()
        registerFunction( "custom_ilike", new SQLFunctionTemplate (StandardBasicTypes.BOOLEAN, "?1 ilike ?2", false));
        registerFunction( "custom_textsearch", new VarArgsSQLFunction(StandardBasicTypes.BOOLEAN, "to_tsvector(", ") @@ to_tsquery(", ")"));
    }

And you absolutely need to specify "= true" when using them in HQL query:

Code:

Item.executeQuery("SELECT DISTINCT i FROM Item i WHERE custom_ilike(i.status, 'status-%') = true )")
Item.executeQuery("SELECT DISTINCT i FROM Item i WHERE custom_textsearch(i.name, 'hello | world') = true )")

I hope this will help someone else ;)
Eric

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum