Grails GORM domain association across two datasources

Question

Is it possible to have an association between two domain classes (i.e. belongsTo) if the other domain class uses a different datasource? The two datasources are different database drivers too.

I suspect this may not be possible, but I wanted to reach out to the community here to see if it was possible. Right now I'm trying to do it and I am getting the usual suspected Hibernate error:

Invocation of init method failed; nested exception is org.hibernate.MappingException: An association from the table domain_class_A refers to an unmapped class: DomainClassB

Sample:

class DomainClassA {
    static belongsTo = [dcB: DomainClassB]

    static mapping = {
        datasource "ds1"
        table name: "domain_class_A", schema: "schema_A"
    }
}

class DomainClassB {
    static hasMany = [dcA: DomainClassA]

    static mapping = {
        datasource "ds2"
        table name: "domain_class_B", schema: "schema_B"
    }
}

You could get a way out following as mentioned [here](http://grails.1312388.n4.nabble.com/Grals-2-0-0-Multiple-Datasources-error-td4584075.html) and [here](http://grails.1312388.n4.nabble.com/Associations-across-databases-td4641257.html) for non-associated domains, but I am highly skeptical you can do the same using `hasMany` and `belongsTo`. — dmahapatro, Sep 12 '13 at 16:03

score 8 · Accepted Answer · answered Sep 12 '13 at 16:58

As @dmahapatro points out in his comment, this is similar to the 1-element case, and creating your own methods to manage the relationship is the way to go. This is also related to a talk I did a while back about performance issues with mapped collections, so you can kill two birds with one stone: http://www.infoq.com/presentations/GORM-Performance

If you don't need the collection, i.e. if you only use it to add new instances of the child object, then this will work because the get call for the DomainClassB instance will use its datasource:

class DomainClassA {
   Long domainClassBId
   private DomainClassB dcB

   DomainClassB getDomainClassB() {
      if (!dcB && domainClassBId) {
         dcB = DomainClassB.get(domainClassBId)
      }
      dcB
   }

   void setDomainClassB(DomainClassB dc) {
      domainClassBId = dc.id
   }

   static transients = ['domainClassB']

   static mapping = {
      datasource "ds1"
      table name: "domain_class_A", schema: "schema_A"
   }
}

class DomainClassB {

    static mapping = {
        datasource "ds2"
        table name: "domain_class_B", schema: "schema_B"
    }
}

Creating a new DomainClassA instance is a bit different from the traditional addTo... approach, but it's not too bad:

DomainClassB dcb = ...
def dca = new DomainClassA(domainClassBId: dcb.id)
dca.save()

If you do want access to all of the DomainClassA instances for a DomainClassB, you can add a method for that:

Set getDomainClassAs() {
   DomainClassA.findAllByDomainClassBId(id)
}

But since you're doing the query yourself, you don't have to load all of the instances if you only need some, so you can do whatever queries you want.

This seems to be the route that I was about to take but I just wanted to make sure there wasn't any grails/GORM magic that could do it for me that I was missing. I really only need DomainClassA to pull a reference to a DomainClassB instance and its related items so this solution should help me out a bit. It still beats having to do the linkage logic outside of the domain class which was the other alternative. — Thomas Farvour, Sep 12 '13 at 17:26

score 0 · Answer 2 · answered Jun 01 '14 at 17:35

I came up with another solution if anyone wants something a bit more "automated" if that applies, maybe it has repercusions in performance but makes it easier to work with specially if you, like me are using MongoDB for Grails plugin, which sometimes makes it neccessary to use the native java API to get documents.

What I did is that in DomainA, I use beforeValidate() to assign the value of domainClassBId and onLoad() to assign the value of dcB. This way, the flow will be more natural to what everyone is used to with Hibernate. When saving the DomainA, the will assing a DomainB to the DomainA and the onvalidate code will persist only the id to the corresponding datasource. When you load objects, for example with the DomainA.find() method, the onLoad code will make sure that the returned object has a property of type DomainB instead of an Long which you then use for quering the databse. It is basically the same approach denoted before by Burt Beckwith but in a more simple way in terms of saving and loading the DomainA. Again I'm not sure if it has performance issues, that's something I would like someone with more experience could help us with.

I got an error: "An association from the table domain_class_a refers to an unmapped class: com.example.DomainClassB" — Michal_Szulc, Dec 01 '15 at 12:47

score 0 · Answer 3 · answered Jan 25 '19 at 12:53

Configure your Database to create a DB-Link between two databases then you can do something like the following: Say you DB Link in database 1 pointing to database 2 is database2link then you query will look like.

select * from domain_class_A tb inner join domain_class_B@database2link tb2 on tb.domain_class_B_Id=tb2.id

And you can map DomainClassB by providing @database2link in the table name in the mapping i.e.

static mapping = {
    datasource "ds1"
    table name: "domain_class_B@database2link", schema: "schema_B"
}

Note that we are using datasource as ds1 only.

Grails GORM domain association across two datasources

3 Answers3

Linked