How to (re) connect to a second database

We have a Grails app, which has various domain objects stored in the Grails db. The app connects to a second DB, runs some raw SQL, and displays the results in a table. It's basically a reporting server.

We are connecting to the second db by specifying a second datasource in DataSource.groovy e.g.:

dataSource_target {
dialect = org.hibernate.dialect.MySQLInnoDBDialect
pooled = true
jmxExport = true
driverClassName = "com.mysql.jdbc.Driver"
username = "bla"
password = "bla"
}

Then in the controller, we have

static mapping = {
    datasource 'ALL'
 }

Then in the service, we have:

         con = new Sql(dataSource_target)
         rows = con.rows(sql)

It works but with two big problem:

If the reporting app cant connect to the second "target" datasource when you do grails run-app, it won't start.
Once running, if the app loses connection to the target DB, it won't try to reconnect, you have to stop and start the entire Grails app again.

We need something more robust - the target DB is only called when the user decides to run a report - its not run when the app starts, and should try and connect (or reconnect) when we call con = new Sql(datasource).

How might we achieve this? Is there a way to connect to an arbitrary DB at run time, pull back some rows, and if the connection to the target db is lost, then reestablished, next time you run a report it should still work? Also, if the app is started when the target db is not there, it should still start, as it has no dependency on it at start up.

Solution

You might consider adding some additional properties to allow it to try and reconnect. For example:

dataSource_target {
  dialect = org.hibernate.dialect.MySQLInnoDBDialect
  pooled = true
  jmxExport = true
  driverClassName = "com.mysql.jdbc.Driver"
  username = "bla"
  password = "bla"
  properties {
    maxActive = -1
    minEvictableIdleTimeMillis=1800000
    timeBetweenEvictionRunsMillis=1800000
    numTestsPerEvictionRun=3
    testOnBorrow=true
    testWhileIdle=true
    testOnReturn=true
    validationQuery="SELECT 1"
  }
}

I suggest you alter the properties to suit your needs. This however, will not address the issue of the database not being available when the application is started.

To avoid that, you have to get away from using Grails data sources. In that case you would need to define your datasource in your service that is making the calls to the database.

def db = [
  url:'jdbc:hsqldb:mem:testDB', 
  user:'sa', 
  password:'', 
  driver:'org.hsqldb.jdbc.JDBCDriver'
]
def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)

Of course, modify this to suit your needs. You may even pull these settings from Config.groovy instead of hard coding them (highly recommended).