Hibernate Criteria with custom database function

Question

I'm using Hibernate 4 with PostgreSQL 9. I want to filter rows with a custom postgres function which looks as follows:

SELECT * FROM customer WHERE name % ?

(I am using the trigram search, a postgres extension).

To use this function with session.CreateQuery(), I implemented my own SQLFunction and PostgreSQLDialect following this tutorial.

The function works and looks as follows:

session.createQuery("FROM Customer WHERE trgm_match(name, :name) = true");

Now to the difficult part:

I need to query the table using several search criteria and want to use Hibernate's Criteria. How I can I use this custom PostgreSQL function in criteria.add(Restrictions. ?? );

I think that add( Restrictions.sqlRestriction("name % ?", filter, ??) ); might be sufficient, but what should I pass as the third argument? (The column has type VARCHAR)

I often saw the usage of Hibernate.STRING which doesn't seem to exist in Hibernate 4.

Any help would be appreciated.

misko321 · Accepted Answer · 2015-05-23 10:21:37Z

Maybe you could try creating some restrictions first in a form of a Predicate list. Here's an example from my code:

List<Predicate> predicates = new ArrayList<Predicate>();

//add predicates based on your business logic
if (model.getCreateDateFrom() != null)            
predicates.add(cb.greaterThanOrEqualTo(event.get(Event_.createDate), 
                    model.getCreateDateFrom()));

...
//add predicate utilizing your DB function
//0.00001111111 ~= 1 meter
if (model.getLatitude() != null)
    predicates.add(cb.equal(cb.function("is_point_inside_circle", Boolean.class, 
    cb.literal(model.getLatitude()), cb.literal(model.getLongitude()),
    event.get(Event_.latitude), event.get(Event_.longitude), 
    cb.literal(model.getRadius() * 0.00001111111)), true));

//set 'where' clause by converting list to array
cq.where(predicates.toArray(new Predicate[] {}));

//perform query
//...

Read also here: JPA Criteria api with CONTAINS function

Community · Accepted Answer · 2017-05-23 12:30:01Z

0

I found out that Hibernate.STRING is deprecated: Hibernate 4.1Final alternative for Hibernate.STRING

The following statement works, but it would be nicer to get a solution with a custom Restriction class:

add(Restrictions.sqlRestriction("name % ?", filter, StringType.INSTANCE));

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered May 23, 2015 at 10:17

maja

18.2k18 gold badges85 silver badges127 bronze badges

Collectives™ on Stack Overflow

Hibernate Criteria with custom database function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related