如何用休眠条件调用postgres函数 [英] how to call postgres function with hibernate criteria

问题描述

  select * from mytable order by name asc; 
使用hibernate标准获得排序结果，该标准生成以下sql： 
  

这就是我如何使用hiberante标准做到的：

  criteria.addOrder（Order.asc（name））; 
  

现在，我在postgres DB中创建了一个函数：customSort（text）

，并希望与hibernate标准一起使用，以便生成以下sql：

  select *从customSort（name）asc的mytable命令; 
  

如何在hibernate中调用此函数来进行自定义排序并生成等效的sql？

任何代码示例都可以帮助您获得更多帮助。

谢谢，
Akhi

  public String toSqlString（Criteria criteria，CriteriaQuery criteriaQuery）
 throws HibernateException 
 {
 String []列= 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）
 $ b fragment.append（factory.getDialect（）。getLowercaseFunction（））
 .append（'（'）; 
} 
 fragment.append（columns [i] ）; 
 if（lower）
 fragment.append（'）'）; 
 fragment.append（））; 
 fragment.append（升序？asc：desc）; 
 if（i< columns.length  -  1）
 fragment.append（，）; 
} 
 return fragment.toString（）; 
} 
  

然后我们需要打电话：

  criteria.addOrder（CustomOrder.asc（name））; 
  

而不是调用criteria.addOrder（Order.asc（name））

这个工作，我能够将DB方法调用放置在where子句中

  select *通过customSort（name）asc从mytable订购; 
  

我只是想知道是否有类似的方法可以将此函数调用放在select部分中使用标准？
例如：

 从customtable（col1），col2， 
  

发布您的建议。

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

解决方案

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.

这篇关于如何用休眠条件调用postgres函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！