How to querying across databases with grails ?

Question

Is there a way to querying across 2 databases in grails ?

Example (I made a select on two databases - works and test) :

        select
            c.crf_name,
            c.crf_id,
            ig.group_id,
            ig.group_name,
        from
            works.crfs c,
            test.item_groups ig;

1) I would like to query against two databases, and attach results to a domain.

Or :

2) Is it possible to mixing one query part with data from database and other part with domain class ?

Edit : I need to do a single query mixing tables from 2 databases (one db is PostgreSQL and other db is Mysql). So, in grails is it possible to mix to dataSources beans in one query ?

Edit 2 : Here a better example :

select
    igm.item_id,
    igm.item_group_id as group_id,
    igm.crf_version_id,
    ig.name as group_name
from
    works.item_group_metadata igm,
    test.item_group ig
where
    igm.item_group_id=ig.item_group_id
;

Answer 1

You should use a "UNION SELECT" which is supported by most databases. Make sure that both select statements have the same number of columns.

    select
        crf_name,
        crf_id
    from
        ig
    UNION SELECT
        group_id,
        group_name
    from
        id

A union select concatenates the results for 2 queries, for example:

select 1 union select 2

Answer 2

Have you looked at the Datasources plugin? It sounds like it will do what you require, but I think you'd need to use HQL or finders on the domain objects directly, rather than SQL, for it to work.

I haven't used the plugin myself but I'd be interested to hear how it goes it you try it.

HTH

Answer 3

If you are planning to do your own sql (like it seems to be the case) over 2 datasources, I suggest that you define your 2 datasources as Spring beans in grails-app/conf/spring.

e.g. (drop your db drivers in /lib and replace the values to match your RDBMS drivers and connection string) :

import org.apache.commons.dbcp.BasicDataSource
import oracle.jdbc.driver.OracleDriver

beans = { 
    worksDataSource(BasicDataSource) {
        driverClassName = "oracle.jdbc.driver.OracleDriver"
        url = "jdbc:oracle:thin:@someserver:someport:works"
        username = "works"
        password = "workspassword"
     }

     testDataSource(BasicDataSource) {
        driverClassName = "oracle.jdbc.driver.OracleDriver"
        url = "jdbc:oracle:thin:@someserver:someport:test"
        username = "test"
        password = "testpassword"
     }
}

Then create a service to handle your queries, like :

import groovy.sql.Sql

class SomeService {
    def worksDataSource
    def testDataSource

    def query1 = """
          SELECT crf_name, crf_id
            FROM works.crfs
    """

    def query2 = """
          SELECT group_id, group_name
            FROM test.item_groups
    """

    def sqlWorks = Sql.newInstance(query1)
    def sqlTest = Sql.newInstance(query2)

    // Then do whatever you like with the results of the 2 queries
    // e.g. 

    sqlWorks.eachRow{ row -> 
       def someDomainObject = new SomeDomainObject(prop1 : row.crf_name, prop2 : crf_id)
       someDomainObject.otherProp = whateverYouLike()
       someDomainObject.save()
    }    
}

Your query doesn't have a where clause so I don't know how you want to relate the data coming from your 2 tables...

If you want to do a single query mixing tables from 2 databases, (ask your DBA to) establish a DBLink between databases test and works and perform the query on the datasource containing the DBLink.

I hope this helps.