Hibernate inner join across databases

Question

I have 1 table in my database (instance_main) that I need to inner join on in another database (instance_core). Typically I would use something like this.

SELECT
instance_core.cnm.MenuID,
instance_core.cnm.NavigationID,
instance_core.cnm.StackView,
instance_core.cnm.StackClass,
instance_core.cnm.IsAdmin
FROM
instance_core.CoreNavigationMenu cnm
INNER JOIN instance_main.ACLGroupPermissions gp ON instance_core.cnm.MenuID = instance_main.gp.MenuID
WHERE instance_main.gp.GroupID = 1

I do not have a many-to-many relationship from ACLGroupPermissions to CoreNavigationMenu.

So far I have tried this, but it always returns a null result. Any help with this is greatly appreciated.

@Override
public List<CoreNavigationMenu> getPermissions(AclGroup dataObject) {

    List<CoreNavigationMenu> aclList = template.find("FROM CoreNavigationMenu cnm inner join ACLGroupPermissions agp on cnm.MenuID = agp.MenuID WHERE agp.GroupID = ?",dataObject.getGroupId());        
    
    return aclList;
}

My ACLGroupPermissions entity looks like this: ACLGroupPermission Entity

My CoreNavigationMenu entity looks like this: CoreNavigationMenu Entity

You can not do it, http://stackoverflow.com/questions/3552330/doing-a-join-over-2-tables-in-different-databases-using-hibernate — Adisesha, Aug 08 '11 at 15:27

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

Agreed with Adi's comment - this cannot be done, per the reasons mentioned at Doing a join over 2 tables in different databases using Hibernate.

(Primarily posting this answer in an attempt to either get this question some additional attention / competitive answers, or at the least, to simply remove this from the growing list of unanswered questions.)

edited May 23 '17 at 11:59

Community

1
1

answered Dec 28 '11 at 02:16

ziesemer

27,712
8
86
94

Hibernate inner join across databases

1 Answers1