It's a common pattern to have a self-referencing table, in order to model an hierarchy tree. Now, sorting results by hierarchy, is an entirely different subject. Some databases, like Oracle, has the start with / connect by clauses. But to bring that to the JPA world is another different story. And imagine that with the uber ugly JPA 2 criteria queries. I was already using Querydsl with JPA (using EclipseLink), and it allowed a very clean solution.First, I brought database functions to the rescue, and wrote a function which receives an entity id, a table name, the name column and the parent id column. So, the function can be reused on any type of entity. As the DB is Postgres, here comes the code:create or replace function name_hierarchy (p_id bigint, p_table varchar, p_name_col varchar, p_parent_id_col varchar) returns varchar as $$ declare sql text; current_id bigint; v_name varchar; v_parent_id bigint; path varchar[]; begin current_id := p_id; while current_id is not null loop sql := 'select id, ' || p_name_col || ', ' || p_parent_id_col || ' from ' || p_table || ' where id = ' || current_id; execute sql into current_id, v_name, v_parent_id; path := array_prepend(v_name, path); current_id := v_parent_id; end loop; return array_to_string(path, ' > '); end; $$ language plpgsql stable;Then, I needed to create a Querydsl operator to represent the function. It contains a name and the argument types.public class CustomOperators { public static final Operator<String> NAME_HIERARCHY = new OperatorImpl<String>( "name_hierarchy", Long.class, String.class, String.class, String.class);}There was also a custom Querydsl templates class to actually convert that operator into JPQL code (note that EclipseLink uses FUNC('name', args...) to invoke native database functions): public static class CustomTemplates extends EclipseLinkTemplates { private static final CustomTemplates INSTANCE = new CustomTemplates(); public static CustomTemplates getInstance() { return INSTANCE; } private CustomTemplates() { add(CustomOperators.NAME_HIERARCHY, "FUNC('name_hierarchy', {0}, {1}, {2}, {3})"); } }As the example entity is Configuration, the final plumbing needed is a method annotated with @QueryDelegate(Configuration.class), so the extension method can be created in any class: @QueryDelegate(Configuration.class) public static StringExpression nameHierarchy(EntityPath<Configuration> configuration) { NumberPath<Long> id = (NumberPath<Long>) FieldUtils.readField(configuration, "id"); return StringOperation.create( CustomOperators.NAME_HIERARCHY, id, StringTemplate.create("'configurations'"), StringTemplate.create("'name'"), StringTemplate.create("'parent_id'")); }Finally, I can use the nameHierarchy method anywhere on queries, like:QConfiguration c = QConfiguration.configuration;List<Configuration> configs = new JPAQuery() .from(c) .orderBy(c.nameHierarchy.asc()) .list(c);Looks like that nameHierarchy method was always there, doesn't it? And the same idea can be reused on any other function, seamlessly blending them on the query metamodel. Now try to make something similar with JPA's criteria api! I normally say that either the project I work on (http://www.cyclos.org) is too special or we're just unlucky with the default operation in most libraries we use.As we need streaming BLOBs (we don't want to load entire images into memory), and EclipseLink by default doesn't handle streaming.So I had to do a subclass of org.eclipse.persistence.platform.database.PostgreSQLPlatform. The following methods were implemented: @Override public Object getObjectFromResultSet(ResultSet resultSet, int columnNumber, int type, AbstractSession session) throws SQLException { String name; if (type == Types.BIGINT) { // May be a number or an OID name = resultSet.getMetaData().getColumnTypeName(columnNumber); if ("OID".equalsIgnoreCase(name)) { return resultSet.getBlob(columnNumber); } } return super.getObjectFromResultSet(resultSet, columnNumber,