How-to: Grails 3.0.2 + Oracle Database 12c?

Question

I am making my first steps with Grails and try to create a hello world application with an Oracle Database 12c.

Unfortunately the tutorial documentation on the database specific part at www.grails.org seems not that comprehensive (in contrast for example to that of Ruby on Rails, which I used now several years) - especially when it comes to non-H2 databases.

Is there some recent tutorial on how to get a Grails 3.0.2 app up and running with an Oracle Database 12c?

Mainly I am interested on a) where to put the corresponding JDBC driver and b) how the database configuration should look like.

For a)

grails.org talks about a "lib" directory but does not explain, where it exists or should be created. Other sources say, that the "lib" directory is outdated since several Grails versions and that JDBC drivers should be loaded via dependencies from some repositories - which is obviously not possible with the closed-source Oracle JDBC driver.

For b)

Currently, I have created basic database configuration at <my-app>/grails-app/conf/DataSource.groovy for Oracle 12c:

dataSource {
        url = "jdbc:oracle:thin:@my-server:my-port:my-sid"
        driverClassName = "oracle.jdbc.OracleDriver"
        dialect = "Oracle10gDialect"
        username = "my-user"
        password = "my-pass"
}

Should that be enough for a simple hello world app?

Everything I have found so far at the web is outdated either on the Grails side (covering versions 2.x oder 1.x) and/or the Oracle side (covering versions 10* oder 11*).

Thanks for any hints!

---

Update (just for the record and to bring together the comments below):

1)

Edit <myapp>/grails-app/conf/application.yml.

hibernate:
    jdbc:
        use_get_generated_keys: true
(...)
dataSource:
    pooled: true
    jmxExport: true
    driverClassName: oracle.jdbc.OracleDriver
    username: <myuser>
    password: <mypassword>
(...)
environments:
    development:
        dataSource:
            dbCreate: create
            url: jdbc:oracle:thin:@<myserver>:<myport>:<mysid>
(...)

2)

Get the corresponding Oracle JDBC driver that fits your JDK and Oracle DB release. In my case (openJDK 7 + Oracle 12c) this was ojdbc7.jar release 12.1.0.2.0 from the Oracle Website: http://www.oracle.com/technetwork/database/features/jdbc/index.html

3)

Place this JDBC driver in <myapp>/lib (create this directory, if it does not yet exists).

4)

Add details of this JDBC driver to the dependencies section of <myapp>/build.gradle (modify if necessary to fit your environment/releases):

(...)
dependencies {
    (...)
    runtime "com.oracle.ojdbc7:12.1.0.2.0" 
    (...)
}
(...)

5)

Create a Maven artifact (modify if necessary to fit your environment/releases):

mvn install:install-file -Dfile=lib/ojdbc7.jar -DgroupId=com.oracle -DartifactId=ojdbc7 -Dversion=12.1.0.2.0 -Dpackaging=jar -DgeneratePom=true

6)

Add the path to the JDBC driver to your CLASSPATH: export CLASSPATH=/<somewhere>/<myapp>/lib/ojdbc7.jar

7)

Enjoy ...

Answer 1

Hibernate as of 2.0.1 (Grails 2.0.1) supported a property "dialect". As shown below. Presume there's a similar property still for newer versions of Hibernate.

File .../grails-app/conf/DataSource.groovy

// Global data source settings
// Note that these global properties values are composited with
// the environment-specific properties under "environments"
dataSource {
  pooled = true
  driverClassName = "oracle.jdbc.OracleDriver"
  ...
  // Required for function with Oracle 12c and above.
  // Not required for Oracle 11.2 and below. For the present works with both.
  // Specifying Oracle 10 dialect is sufficient.
  // Oracle 12c specific dialect support is not available for Hibernate 2.0.1.
  // Works with either ojdbc7 or ojdbc8 jars.
  dialect = org.hibernate.dialect.Oracle10gDialect
}

Applying the "dialect" property was sufficient to run older code against both Oracle 11g, 12c. It bought time for code migration to newer versions and dependencies.

Answer 2

1. download ojdbc7.jar from http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html

2. create a <myapp>/lib folder in the main folder of your application (lib folder was removed from Grails 3)

3. copy ojdbc7.jar to lib folder

4. add this line to the <myapp>/build.gradle file

  dependencies{
       (...)
       runtime fileTree(dir: 'lib', include: '*.jar')
       (...)
  }

5. create lines in the <myapp>/grails-app/conf/application.yml

 hibernate:
      (...)
      jdbc:
         use_get_generated_keys: true

 dataSource:
    pooled: true
    jmxExport: true
    driverClassName: oracle.jdbc.OracleDriver
    username: DBUSERNAME
    password: dbpassword

 environments:
     development:
         dataSource:
             dbCreate: create
             url: jdbc:oracle:thin:@localhost.net:1521:dbname
     test:
         dataSource:
             dbCreate: update
             url: jdbc:oracle:thin:@localhost.net:1521:dbname
     production:
         dataSource:
             dbCreate: update
             url: jdbc:oracle:thin:@locaLhost.net:1521:dbname

Answer 3

You can get the JDBC driver through this maven command.

mvn install:install-file \ -Dfile=/lib/ojdbc7.jar \ -DgroupId=com.oracle \ -DartifactId=ojdbc7 \ -Dversion=12.1.0.1 \ -Dpackaging=jar \ -DgeneratePom=true

Answer 4

yes this will be OK for meanwhile. But there are details missing. I'll post detailed instructions as soon as i get to the office. Actually there is a sample for mySql somewhere on the grails site, and from it it's pretty clear how to make same for an oracle DB.

I just have created a new app and compared it with myne for oracle 11g. Here it is:

1. ./build.gradle, add following line to "dependencies" section:

   runtime "com.oracle:ojdbc14:10.2.0.3.0"

2. ./grails-app/conf/application.yml, add following to "hibernate" section:

jdbc:
    use_get_generated_keys: true

3. ./grails-app/conf/application.yml, dataSource: section should be like:

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: oracle.jdbc.OracleDriver
    username: YOURUSERNAME
    password: yoursecret

4. ./grails-app/conf/application.yml, environments: configuration, all three environments should be like:

environments:
development:
    dataSource:
        dbCreate: create
        url: jdbc:oracle:thin:@somehost.net:1521:YOURORAINSTANCE
test:
    dataSource:
        dbCreate: update
        url: jdbc:oracle:thin:@somehost.net:1521:YOURORAINSTANCE
production:
    dataSource:
        dbCreate: update
        url: jdbc:oracle:thin:@somehost.net:1521:YOURORAINSTANCE

And the Hibernate dialect for Oracle you have is pretty old one, I think you'd better remove it, without specifying it explicitly all works fine.