Configuring SpringBoot with Oracle Autonomous Database

Peter Song | Developer Advocate for JDBC

This blog is intended to help Java developers who are new to Autonomous Database and want to connect to it from their SpringBoot application. Link to full repository is here, but I suggest reading all the way through to understand.

I will go over the following 3 methods:

• Using application.properties in conjunction with Universal Connection Pooling (UCP)
• Using application.yaml with Oracle DataSource
• Using a Kubernetes Secret

Bonus: For one way TLS, check out this link

Connecting via Application.properties

First make sure to have the following dependency in your pom.xml. To see the full list of dependencies I have you can check the pom.xml file in the repo.

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11-production</artifactId>
    <version>21.5.0.0</version>
    <type>pom</type>
</dependency>

This is by far the easiest way to connect to the Oracle Database.

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@petertestdb_medium?TNS_ADMIN=/Users/psong/Downloads/Wallet_petertestDB
spring.datasource.username=<DB_USER_HERE>
spring.datasource.password=<password_here>
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect

In reality the only lines you really need are the first four “spring.datasource” lines, but if you want to use spring-data-jpa, you also need to include the last line. If you are familiar with JDBC syntax, then you know how to fill in the url, but for those that aren’t, after the @ we populate a tnsnames.ora alias and after the “?” we reference the location of the wallet file.

Now for local development this is fine, but for production we obviously do not want our password in plain text. That will be covered in section 3, using a Kubernetes Secret. If you want to take advantage of connection pooling, then the Universal Connection Pool (UCP) is a great option which also provides:

• Extensive knobs for fine tuning the pool’s behavior
• Runtime connection load balancing over clustered databases (a.k.a. RAC)
• Transparent and fast connection failover (Transparent Application Continuity)
• Support for Sharded Databases
• Support for Multi-tenant pluggable Databases

I’d suggest adding additional lines in your application.properties file (see hereafter). If you want to take advantage of UCP, then just add the following additional lines.

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@petertestdb_medium?TNS_ADMIN=/Users/psong/Downloads/Wallet_petertestDB
spring.datasource.username=<DB_USER_HERE>
spring.datasource.password=<password_here>
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
##Properties of UCP
oracle.jdbc.fanEnabled=false
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
spring.datasource.oracleucp.sql-for-validate-connection=select * from dual
spring.datasource.oracleucp.connection-pool-name=connectionPoolName1
spring.datasource.oracleucp.initial-pool-size=15
spring.datasource.oracleucp.min-pool-size=10
spring.datasource.oracleucp.max-pool-size=30
##Logging properties for UCP
logging.level.root=trace
logging.file.name=logs.log
logging.level.oracle.ucp=trace

This will tell Spring to use UCP, and set the necessary defaults, as well as enable some useful logging information for UCP.

Connecting via Application.yaml with OracleDataSource

This method is for those who want to use yaml files and also for older versions of Oracle like 11.2 or 10g/9i. These versions cannot take advantage of Oracle’s UCP, so we will go another route. In the application.yaml, structure it as follows.

spring:
  datasource:
    driver_class_name: oracle.jdbc.OracleDriver
    password: <password_here>
    url: jdbc:oracle:thin:@petertestdb_medium?TNS_ADMIN=/Users/psong/Downloads/Wallet_petertestDB
    username: <username_here>
  jpa:
    database: ORACLE
    database-platform: org.hibernate.dialect.Oracle12cDialect
    hibernate:
      ddl-auto: none
    show-sql: true

When you start the application, SpringBoot will automatically look for the application.yaml file and be able to read the hibernate dialect etc. However, to configure database connection via Oracle DataSource, you still need to create a separate class.

You should have a separate DbSettings.java file with the appropriate class. Here’s what the class looks like:

package com.springboot.MyTodoList.config;


import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

// This is only used for local testing.
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class DbSettings {
    private String url;
    private String username;
    private String password;
    private String driver_class_name;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDriver_class_name() {
        return driver_class_name;
    }

    public void setDriver_class_name(String driver_class_name) {
        this.driver_class_name = driver_class_name;
    }
}

@ConfigurationProperties(prefix = “spring.datasource) tells SpringBoot that spring.datasource is where we will find the appropriate information for our database connection. We will then map the yaml attributes to this DbSettings class, with the same names, so

private String url

maps to the url attribute in the yaml file.

Finally, with the getters and setters in place, we will create a separate Configuration class called OracleConfiguration.java like so

package com.springboot.MyTodoList.config;


import oracle.jdbc.pool.OracleDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;


import javax.sql.DataSource;
import java.sql.SQLException;
@Configuration
public class OracleConfiguration {
    Logger logger = LoggerFactory.getLogger(DbSettings.class);
    @Autowired
    private DbSettings dbSettings;
    @Bean
    public DataSource dataSource() throws SQLException{
        OracleDataSource ds = new OracleDataSource();
//      For local testing
        ds.setDriverType(dbSettings.getDriver_class_name());
        logger.info("Using Driver " + dbSettings.getDriver_class_name());
        ds.setURL(dbSettings.getUrl());
        logger.info("Using URL: " + dbSettings.getUrl());
        ds.setUser(dbSettings.getUsername());
        logger.info("Using Username: " + dbSettings.getUsername());
        ds.setPassword(dbSettings.getPassword());
        return ds;
    }
}

We can use the autowired annotation to tell SpringBoot to use the DbSettings class we created earlier, then grab those values from the yaml file using the setters that we defined in the DbSettings class. Now, when you run the application, you should see the Logger outputting the appropriate username, url, driver type, and password.

Using a Kubernetes Secret

This is by far the most secure method out of the three but also the most difficult to configure. When dealing with user credentials that need to be accessed by Kubernetes, it is best to create a secret. Assuming we have access to the database wallet, within the sqlnet.ora file we need to say where our wallet file is located within the container.

cat - >sqlnet.ora <<!
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/mtdrworkshop/creds")))
SSL_SERVER_DN_MATCH=yes
!

Once we do that, we can create the secret using

kubectl create -f — -n mtdrworkshop <<!
apiVersion: v1
data:
  README: $(base64 -w0 README)
  cwallet.sso: $(base64 -w0 cwallet.sso)
  ewallet.p12: $(base64 -w0 ewallet.p12)
  keystore.jks: $(base64 -w0 keystore.jks)
  ojdbc.properties: $(base64 -w0 ojdbc.properties)
  sqlnet.ora: $(base64 -w0 sqlnet.ora)
  tnsnames.ora: $(base64 -w0 tnsnames.ora)
  truststore.jks: $(base64 -w0 truststore.jks)
kind: Secret
metadata:
name: db-wallet-secret
!

Similarly we create the database password secret with the same syntax

kubectl create -n mtdrworkshop -f - <<!
{
  "apiVersion": "v1",
  "kind": "Secret",
  "metadata": {
     "name": "dbuser"
  },
  "data": {
     "dbpassword": "${BASE64_DB_PASSWORD}"
  }
}
!

Note: ${BASE64_DB_PASSWORD} is a variable that refers to the password of the database, that has been encrypted.

Now that we have both secrets, we need to specify them in the application.yaml file. This file is called “todolistapp-springboot.yaml” in the repository and within the “spec:”, I specify in the url where to grab the wallet from within the container like so.

spec:
  containers:
    - name: todolistapp-springboot
      image: %DOCKER_REGISTRY%/todolistapp-springboot:0.1
      imagePullPolicy: Always
      env:
        - name: db_user
          value: "TODOUSER"
        - name: db_url
          value: "jdbc:oracle:thin:@%TODO_PDB_NAME%_tp?TNS_ADMIN=/mtdrworkshop/creds"
        - name: todo.table.name
          value: "todoitem"
        - name: driver_class_name
          value: "oracle.jdbc.OracleDriver"
        - name: OCI_REGION
          value: "%OCI_REGION%"
        - name: dbpassword
          valueFrom:
            secretKeyRef:
              name: dbuser
              key: dbpassword
              optional: true
      volumeMounts:
        - name: creds
          mountPath: /mtdrworkshop/creds
      ports:
        - containerPort: 8080
  restartPolicy: Always
  volumes:
    - name: creds
      secret:
        secretName: db-wallet-secret

Note: %TODO_PDB_NAME% references the Db alias.

%DOCKER_REGISTRY% refers to the container registry in OCI, which holds your Docker images, available in different tagged versions.

%OCI_REGION% refers to you cloud region in OCI, which is only necessary if you’re running this in the cloud

We are specifying a volume we want to mount by the name of creds and specifying the mount path where we can access the wallet. The %OCI_REGION% is being grabbed as an environment variable. We don’t have to worry about that for our purposes. As you can see dbpassword is not exposed in plain text, we just reference the secret by the key.

Lastly, we need to access this information from within our java code. To do this we use the Environment class to grab environment variables from within the container.

@Configuration
public class OracleConfiguration {
    Logger logger = LoggerFactory.getLogger(DbSettings.class);
    @Autowired
    private DbSettings dbSettings;
    @Autowired
    private Environment env;
    @Bean
    public DataSource dataSource() throws SQLException{
        OracleDataSource ds = new OracleDataSource();
        ds.setDriverType(env.getProperty("driver_class_name"));
        logger.info("Using Driver " +      env.getProperty("driver_class_name"));
        ds.setURL(env.getProperty("db_url"));
        logger.info("Using URL: " + env.getProperty("db_url"));
        ds.setUser(env.getProperty("db_user"));
        logger.info("Using Username " + env.getProperty("db_user"));
        ds.setPassword(env.getProperty("dbpassword"));
        return ds;
    }
}

The full code is available in the repository, and I only included what was important here, but once the pod is running in your kubernetes cluster, there will be environment variables that are set up in your pod. These include the driver_class_name, URL, username, and password. We just simply grab these using the Environment class and when we start up the springboot application from within the pod, it can connect to the database.

For any questions, feel free to shoot me an email at peter.song@oracle.com

If you’ve made it this far, thanks for the read. Hope you found it useful :)