How to config applicatiuon.yml when using Micronaut + GORM with multiple data sources

Question

Environmant: Java 11 + Micronaut 2.1 + GORM.

I have two databases to connect. PostgreSQL and MSSQL. I have tried some ways looked up in Internet but all failed. Below is my detail configuration, code, and output log. Please instruct me how to correctly config application.yml. Thanks

Reference sites:

1. Grails Multi-datasource
2. GORM for Hibernate

My application.yml is as below:

micronaut:
  application:
    name: demo
---
hibernate:
  hbm2ddl:
    auto: none
  cache:
    queries: false
    use_second_level_cache: false
    use_query_cache: false
---

dataSource:
  dbCreate: none
  url: jdbc:postgresql://localhost/test_db1
  dialect: org.hibernate.dialect.PostgreSQLDialect
  pooled: true
  jmxExport: true
  driverClassName: org.postgresql.Driver
  username: 'user1'
  password: 'user1'
dataSources:
  emp:
    pooled: true
    jmxExport: true
    dialect: org.hibernate.dialect.SQLServer2012Dialect
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    dbCreate: none
    url: jdbc:sqlserver://localhost:1433;databaseName=test_db2
    username: 'user2'
    password: 'user2'

My entity classes:

import grails.gorm.annotation.Entity
import org.springframework.context.i18n.LocaleContextHolder

@Entity
class Country {
    String enDisplayName;

    static mapping = {
        version false
    }
    static constraints = {
        enDisplayName   nullable: false
    }
}

@Entity
class Employee {
    String employeeId
    String employeeName
    
    static mapping = {
        dataSource 'emp'
        version false
        id name:'employeeId', generator:'assigned'
    }

}

My Main code:

package com.example

import com.example.domain.Country
import com.example.domain.Employee
import io.micronaut.configuration.picocli.PicocliRunner
import io.micronaut.context.ApplicationContext

import picocli.CommandLine
import picocli.CommandLine.Command
import picocli.CommandLine.Option
import picocli.CommandLine.Parameters

@Command(name = 'demo', description = '...',
        mixinStandardHelpOptions = true)
class DemoCommand implements Runnable {

    @Option(names = ['-v', '--verbose'], description = '...')
    boolean verbose

    static void main(String[] args) throws Exception {
        PicocliRunner.run(DemoCommand.class, args)
    }

    void run() {
        // business logic here
        if (verbose) {
            println "Hi!"
        }
        Country.withNewSession {
            List<Country> list = Country.list()
            println("Country count:${list.size()}")

        }
        Employee.withNewSession {
            Employee employee = Employee.findByEmployeeNo('BU4191')
            println("employee name:${employee.employeeName}")
        }

    }
}

log:

16:25:26.624 [main] INFO  i.m.context.env.DefaultEnvironment - Established active environments: [cli]
16:25:26.928 [main] INFO  i.m.c.h.g.HibernateDatastoreFactory - Starting GORM for Hibernate
16:25:27.558 [main] INFO  org.hibernate.Version - HHH000412: Hibernate ORM core version 5.4.21.Final
16:25:27.682 [main] INFO  o.h.validator.internal.util.Version - HV000001: Hibernate Validator 6.1.5.Final
16:25:27.819 [main] INFO  o.h.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
16:25:28.646 [main] INFO  org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
16:25:29.609 [main] INFO  org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect
Country count:4
16:25:30.253 [main] WARN  org.hibernate.orm.deprecation - HHH90000022: Hibernate's legacy org.hibernate.Criteria API is deprecated; use the JPA javax.persistence.criteria.CriteriaQuery instead
16:25:30.283 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42703
16:25:30.283 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: column this_.employee_id does not exist
  Position: 8
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not extract ResultSet; bad SQL grammar [n/a]; nested exception is org.postgresql.util.PSQLException: ERROR: column this_.employee_id does not exist
  Position: 8
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.grails.orm.hibernate.GrailsHibernateTemplate.convertJdbcAccessException(GrailsHibernateTemplate.java:725)
    at org.grails.orm.hibernate.GrailsHibernateTemplate.convertHibernateAccessException(GrailsHibernateTemplate.java:712)
    at org.grails.orm.hibernate.GrailsHibernateTemplate.doExecute(GrailsHibernateTemplate.java:301)
    at org.grails.orm.hibernate.GrailsHibernateTemplate.execute(GrailsHibernateTemplate.java:241)
    at org.grails.orm.hibernate.GrailsHibernateTemplate.executeWithNewSession(GrailsHibernateTemplate.java:153)
    at org.grails.orm.hibernate.AbstractHibernateDatastore.withNewSession(AbstractHibernateDatastore.java:360)
    at org.grails.orm.hibernate.AbstractHibernateGormStaticApi.withNewSession(AbstractHibernateGormStaticApi.groovy:78)
    at org.grails.datastore.gorm.GormEntity$Trait$Helper.withNewSession(GormEntity.groovy:1027)
    at org.grails.datastore.gorm.GormEntity$Trait$Helper$withNewSession.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:148)
    at com.example.domain.Employee.withNewSession(Employee.groovy)
    at com.example.domain.Employee$withNewSession.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:139)
    at com.example.DemoCommand.run(DemoCommand.groovy:34)
    at picocli.CommandLine.executeUserObject(CommandLine.java:1919)
    at picocli.CommandLine.access$1100(CommandLine.java:145)
    at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2332)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2326)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2291)
    at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2159)
    at picocli.CommandLine.execute(CommandLine.java:2058)
    at io.micronaut.configuration.picocli.PicocliRunner.run(PicocliRunner.java:137)
    at io.micronaut.configuration.picocli.PicocliRunner.run(PicocliRunner.java:114)
    at io.micronaut.configuration.picocli.PicocliRunner$run.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:148)
    at com.example.DemoCommand.main(DemoCommand.groovy:21)
Caused by: org.postgresql.util.PSQLException: ERROR: column this_.employee_id does not exist
  Position: 8

Answer 1

Eventually, I have tried the successful result as below:

micronaut:
  application:
    name: demo

dataSource:
  url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
  driverClassName: org.h2.Driver
  username: sa
  password: ''
  pooled: true
  jmxExport: true
hibernate:
  hbm2ddl:
    auto: update
  cache:
    queries: false
    use_second_level_cache: false
    use_query_cache: false

dataSources:
  seconddb:
    url: jdbc:h2:mem:devDb2;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    driverClassName: org.h2.Driver
    username: sa
    password: ''
    readOnly: true
    pooled: true
    jmxExport: true
    hibernate:
      hbm2ddl:
        auto: update
      cache:
        queries: false
        use_second_level_cache: false
        use_query_cache: false