In this tutorial, we are going to learn how to configure multiple databases in the grails 3.x application. This is necessary when you are dealing with multiple databases from the same application especially when dealing with an existing remote database. We are using different MySql databases for testing.
Let's create three MySql databases called db_default, db_one, db_two. Let's look into the application.yml file to configure the different databases and we will do it for the development environment, in other environments, the procedure will be the same.
environments:
development:
dataSource:
dbCreate: update
url: jdbc:mysql://localhost:3306/db_default
driverClassName: com.mysql.jdbc.Driver
dialect: org.hibernate.dialect.MySQL5InnoDBDialect
username: root
password: root
Here, we are setting the default database as
db_default. So for this database, all the gorm queries will be the same as in a normal application.
Now, let's set up for other two databases.
dataSources:
first:
dialect: org.hibernate.dialect.MySQLInnoDBDialect
driverClassName: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://localhost:3306/db_one
dbCreate: update
second:
dialect: org.hibernate.dialect.MySQLInnoDBDialect
driverClassName: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://localhost:3306/db_two
dbCreate: update
For more than two, we need to define the databases by defining
dataSources and giving the corresponding names. Here the custom name is whatever name you want. We are giving the name
first for
db_one and
second for
db_two.
The overall implementation for application.yml file looks as below:
environments:
development:
dataSource:
dbCreate: update
url: jdbc:mysql://localhost:3306/db_default
driverClassName: com.mysql.jdbc.Driver
dialect: org.hibernate.dialect.MySQL5InnoDBDialect
username: root
password: root
dataSources:
first:
dialect: org.hibernate.dialect.MySQLInnoDBDialect
driverClassName: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://localhost:3306/db_one
dbCreate: update
second:
dialect: org.hibernate.dialect.MySQLInnoDBDialect
driverClassName: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://localhost:3306/db_two
dbCreate: update
Now, how we can use the domain classes for the specific databases. Let's create a simple domain class called
Book.groovy.
class Book {
String title
static mapping = {
datasource 'first'
}
}
In the above example, the Book table is mapped to the
db_one database so all operations regarding this will be in
db_one database. Similarly, we can map the second database as well. We can map the single domain to multiple databases as below:
class Book {
String title
static mapping = {
datasources([ConnectionSource.DEFAULT, 'first', 'second'])
}
}
In the above example, the Book domain will be available in all the databases.
Now, let's look into how we can query for the specific database.
The first DataSource specified is the default when not using an explicit namespace, so in this case, the default one is used. But you can call GORM methods on the 'first' or 'second' DataSource with the DataSource name, for example:
def book = Book.first.get(1) // this will get from db_one
book.first.save() //this will save in db_one
def book = Book.second.get(1) // this will get from db_two
book.second.save() // this will save in db_two
def book = Book.get(1) // this will get from db_default
book.save() // this will save in db_default
We can use groovy native SQL as well. Let's look at the example.
import grails.transaction.Transactional
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Qualifier
import javax.sql.DataSource
@Transactional
class DbOneService {
@Autowired
@Qualifier('dataSource_first')
DataSource dataSource
def test() {
def sql = new Sql(dataSource)
def rows = sql.rows("select * from....")
println "rows: "+rows
}
}
Here, we are creating the DbOneService which is annotated with
Qualifier where the
db_one database name is configured as
dataSource_first. This will provide the
db_one database connection to do DB operation. You can simply execute the native SQL command as shown above. Similarly, we can do the same for the second database called
db_two.
import grails.transaction.Transactional
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Qualifier
import javax.sql.DataSource
@Transactional
class DbTwoService {
@Autowired
@Qualifier('dataSource_second')
DataSource dataSource
def test() {
def sql = new Sql(dataSource)
def rows = sql.rows("select * from....")
println "rows: "+rows
}
}
This way, we can deal with multiple databases.
