how to call postgres function with hibernate criteria

Question

I am getting sorted results using hibernate criteria which generates the below sql:

select * from mytable order by name asc;

This is how i do it with hiberante criteria:

criteria.addOrder(Order.asc("name"));

Now, i have created a function in postgres DB as: customSort(text)

and want to use with hibernate criteria such that following sql is generated:

select * from mytable order by customSort(name) asc;

How can i call this function in hibernate to make my custom sorting and generate equivalent sql?

Any code sample will help alot

Thanks, Akhi

Suresh Atta · Accepted Answer · 2013-05-09 10:56:41Z

1

Use Hibernate Native SqlQuery to acheive this.

You cannot access a Database specific native functions in criterial queries.

sess.createSQLQuery("select * from mytable order by
                                        customSort(name) asc").list();

answered May 9, 2013 at 10:56

Suresh Atta

122k38 gold badges206 silver badges315 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

akhi Over a year ago

Thanks baadshah, but i just want to avoid the hardcoded sql. is there any different way?

Suresh Atta Over a year ago

AFAIK, No.Sorry,I do not know if is there any other way.

Craig Ringer Over a year ago

Pretty sure you can map the procedure and then invoke it from Criteria, but I'm blessedly free of having to work with JPA anymore.

akhi · Accepted Answer · 2013-06-27 02:25:30Z

I have spent some time on this and found a solution by extendeding a class CustomOrder.java from Order.java and override the toString() method as below:

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
            throws HibernateException
    {
        String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
        Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);
        StringBuffer fragment = new StringBuffer();
        for (int i = 0; i < columns.length; i++)
        {
            SessionFactoryImplementor factory = criteriaQuery.getFactory();
            fragment.append("customOrder");
            fragment.append("(");
            boolean lower = ignoreCase && type.sqlTypes(factory)[i] == Types.VARCHAR;
            if (lower)
            {
                fragment.append(factory.getDialect().getLowercaseFunction())
                        .append('(');
            }
            fragment.append(columns[i]);
            if (lower)
                fragment.append(')');
            fragment.append(")");
            fragment.append(ascending ? " asc" : " desc");
            if (i < columns.length - 1)
                fragment.append(", ");
        }
        return fragment.toString();
    }

then we need to call :

criteria.addOrder(CustomOrder.asc("name"));

instead of calling criteria.addOrder(Order.asc("name"))

this worked and i was able to place DB method call in where clause as

select * from mytable order by customSort(name) asc;

I am just wondering is there a similar way such that i can place this function call in select part also using criteria?? for example:

select customSort(col1), col2, col3 from mytable order by customSort(name) asc;

pelase post your suggestions.

Seems like you are going to a lot of effort and creating unneeded hard to follow code to just run a stored procedure. I feel sorry for the guy who has to maintain your code in a couple years.

Collectives™ on Stack Overflow

how to call postgres function with hibernate criteria

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related