[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4030) Implement support for native recursive query functionality of popular DBMSes

[Christian Beikov (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-4030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46532#comment-46532 ] 

Christian Beikov commented on HHH-4030:
---------------------------------------

I would also like to see such a feature! The JPA specification should include it!
A query syntax like, 'FROM Entity e JOIN FETCH RECURSIVE e.parent' would be nice!
For DBMS that do not support a recursive quering approach the implementation should fall back to querying every level automatically in the JVM, like:

EntityManager em = ...
Entity e = em.createQuery("...", Entity.class);
List<Entity> entities = new ArrayList<Entity>();

while(e != null){
  entities.add(e);
  e = e.getParent();
}

> Implement support for native recursive query functionality of popular DBMSes
> ----------------------------------------------------------------------------
>
>                 Key: HHH-4030
>                 URL: https://hibernate.onjira.com/browse/HHH-4030
>             Project: Hibernate ORM
>          Issue Type: New Feature
>          Components: core, query-hql, query-sql
>         Environment: Hibernate 3.3.2, MSSQL
>            Reporter: David Cracauer
>
> We have a couple of areas in our system where need to load tree structures from our database.  These trees can be very deep (often 20+ levels).  SQL Server 2005 introduced Common Table Expressions, a kind of in-line view that can be used recursively.  
> These allow us to quickly get a result set like this:
> id, label, parentId
> 1, foo, null
> 2, foo2, 1
> 3, foo3, 2
> 4, foo4, 2
> 5, foo5, 4
> 6, foo6, 1
> From a tree like this:
>    [1, foo]
>      |
>      |  -[2, foo2]
>      |        | - [3, foo3]
>      |        | - [4, foo4]
>      |              | - [5, foo5]
>      |-  [6, foo6]
> using a query like this (not tested): 
> with MyCTE(
>    id, 
>    label,
>    parentId)
> as 
>   ( select n.id, n.label, n.parentid
>       from Node n
> UNION ALL
>   select c.id, c.label, c.parentId
> from MyCTE c
>     inner join Node n on n.id=c.parentId)
> select * from MyCTE
> It happens many times more quickly than we've been able to load the graphs with Hibernate, even using batching etc.
> We've tried using hibernate's built in native sql support without success.  The alias injection breaks the Common Table Expression definition ( with MyCTE ), as it is a view definition and doesn't allow for the 'id as id_276' syntax, rather requiring just a column name.
> I know that there are several other major DBMSes that support recursive querying now.  Is there a way to have support for this functionality in hibernate core?

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira