Experiment 2: Connecting a Spring Boot application to an Oracle Cloud Autonomous Database

 

Here I describe my experiment of connecting a simple Spring Boot application to an Oracle Cloud Database and then retrieving some data using a REST API call written in Java.

My recipe:

1. Windows 10
2. Spring Tools Suite 4 (the IDE from VMWare, not the Eclipse plugin)
3. A free OCI account with an ATP/ADW instance running, as you can see in experiment 1. If you do not have an OCI account, go here or google for “Oracle Cloud Free Tier”
4. A locally downloaded wallet zip with the necessary credential files to connect to your ADB. For how to obtain and store the wallet files for your ADB refer to the instructions in experiment 1.
5. Postman to test the API calls.

Credits: this experiment is based on what I learned in this video from Dev Workspace #27 (in Italian)

Disclaimer: I’m not a professional Spring developer, and even if I worked on some J2EE applications for several years and made some native Android apps myself, I'm rather out of shape when it comes to Java.

In this example, I'll use Gradle as the automation build tool, instead of the default Maven, because Gradle is the choice for developing Android apps in AndroidStudio for both Java and Flutter projects, and I feel more confident with it.

Setting up the Spring Boot project

The project is simple, but not trivial, so I'll apply some basic good practices even if they might seem overkill for such a simple demo, as they should be a mindset in projects of any size. 

I'll go a bit into the details of the Java part to make sense of what I do and why.

In Spring Tools Suite 4 let's start creating a new Spring Boot project by selecting from the menu: new/project/Spring Starter project

in the next window, select a name for the project and Gradle as the type of build tool. Any version of Java from 8 onwards is good. In my case, I selected OCIWorkSpaceDemo as the project name and left com.example.demo as the package name. All other parameters can be left as they are.

In the next window, for the dependencies choose "Spring web" and "Spring Data JPA". If the latter does not show at the top, you can find "Spring Data JPA" under the SQL dependencies group list.

Now you can click Finish.

In the project panel, your project should look like this:

The one and only class that has been created by the wizard is the startup class OciWorkSpaceDemoApplication.java. We don't need to change anything in it, but it's worth opening it and having a look at what it looks like. You'll find the classic static main() method which calls a Spring method to start and run the whole application.

If you now right-click on the project and select to run it as a Spring Boot application, 

you'll end up with a bunch of errors because we configured a JPA dependence but we have not configured any data source or something similar, but Spring is trying to connect to something anyway.

Connecting to the OCI ADB instance: Data source configuration

Now it's time to set up the information we need to connect to our ADB instance in our OCI tenancy and then run a query against the Star Schema Benchmark (SSB) sample schema.

Open the build.gradle file and add these dependencies to the (few) others:
implementation 'com.oracle.database.jdbc:ojdbc8:19.6.0.0'
implementation 'com.oracle.database.jdbc:ucp:19.6.0.0'

now open the application.properties file under src/main/resources. This file is now totally empty, here we define the connection information for our app via some JPA properties and Hibernate properties. In fact, Hibernate is the persistence provider for JPA.

First thing first, we define the Hibernate dialect to use for our database, :
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

then we define 2 useful properties to format and show up in the Spring console the sql code executed, for debugging and knowledge pourposes

spring.jpa.properties.hibernate.format_sql=true

spring.jpa.show-sql=true

then we define the core connection properties to talk to our ADB instance, and, surprise surprise, you can grab the same information used in experiment 1:

spring.datasource.username=ADMIN

spring.datasource.password=your ADMIN password

spring.jpa.properties.hibernate.default_schema=SSB

spring.jpa.properties.hibernate.ddl-auto=validate

spring.datasource.url= here you can use the same connection string defined in experiment 1, 

but let's recap here how it works:

the connection string is in the format

protocol://host:port/service_name?wallet_location=the_wallet_folder

you can find all the parameters you need by choosing one of the connections defined in the tnsnames.ora file contained in the wallet:

protocol: well, it's always tcps

host: the one defined in the chosen connection, e.g. adb.ap-sidney-1.oraclecloud.com

port: the one defined in the chosen connection, usually 1522

service: again, then one defined in the chosen connection, e.g.  a1b2c3d4e5f6g7h_db202201040000_low.adb.oraclecloud.com

wallet_location: the place where you locally unzip the wallet file, e.g. C:/Oracle/instantclient_19_14/wallet

So you'll end up with a jdbc connection property like this:

spring.datasource.url=jdbc:oracle:thin:@tcps://adb.ap-sidney-1.oraclecloud.com:1522/a1b2c3d4e5f6g7h_db202201040000_low.adb.oraclecloud.com?wallet_location=C:/Oracle/instantclient_19_14/wallet

Now save the application.properties.

At this point, we have all the pieces we need to run the application and let Spring connect to the ADB instance. 

NOTE: to save you from a possible hassle and a headache (like me), let me give this quick hint:

in the project tree, check the presence of the Oracle JDBC driver you manually added before to the dependencies, look under "Project and External Dependencies":

if you cannot find it, right-click on the project and run "Gradle / Refresh Gradle project" and then rebuild the project.

now if you run the application the log should not show any error (it may take a while to connect to the ADB) and the Spring Boot application should run smoothly. 

Note: You may get an error like 

ERROR 14848 --- [main] oracle.simplefan.FanManager  : attempt to configure ONS in FanManager failed

I still haven't gotten rid of this but it's not affecting the scope of this example.

Writing the API service

Till now we didn't write a single line of java code and it's time to do it. Under src/main/java/ create the following 4 packages:

• com.example.demo.presentation
• com.example.demo.service
• com.example.demo.model
• com.example.demo.persistence

to classify consistently the classes we are going to create.

Our REST API is going to retrieve some data from the SSB.CUSTOMER table which has the following structure

the model

the first thing is to create a class in com.example.demo.model to map this entity:

import javax.persistence.Entity;

import javax.persistence.Id;

@Entity

public class Customer {

@Id

private Long cCustkey;

private String cName;

private String cAddress;

private String cCity;

private String cNation;

private String cRegion;

private String cPhone;

private String cMktsegment;

}

@entity tells Spring this class maps an entity in our DB

@Id tells Spring that cCustkey is the primary key of the table.

Notice the names I choose for the properties. Hibernate has its rules to map them to the table columns, in this case, it will map cCustKey to C_CUSTKEY, cName to C_NAME and so on and so forth, using the so-called PhysicalNamingStrategy. 

Since we have not defined any properties about this thing, nor in the application.properties neither in the class itself,  Hibernate defaults to the CamelCaseToUnderscoresNamingStrategy where every '_' is replaced by a camel-case.

You can always override this behaviour with @Column, i.e.:

@Column(name = "C_CUSTKEY")

private Long whatEverNameYouLikeForThisProperty;

Now right-click on the code and generate all the getters and setters for the properties of the class:

That's enough for our entity object.

The persistence

Now that we have an entity, head to the package com.example.demo.persistence and this time create an interface, say RepoCustomer. 

Simply defining this interface will enable the application to perform all the operations on the database without the need of writing any code to do that. Spring Data takes care of creating an object which implements this interface with all the methods to access the database.

import org.springframework.data.jpa.repository.JpaRepository;

import com.example.demo.model.Customer;

public interface RepoCustomer extends JpaRepository<Customer, Long>{

}

as you can see the interface extends the specific repository interface JpaRepository, to leverage the standard CRUD operations. It needs 2 parameters, the entity class and the type of the primary key, in this case <Customer, Long>. that's all.

the service

Now we'll define the service layer, in other words, a class that binds the repository we defined to the presentation layer. In this service class, we define the public methods and logic to read/write the database. Let's call the class ServiceCustomer.

For our purposes, we define a single method which returns a single record from SSB.CUSTOMER, wrapped in our Customer entity:

import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;

import com.example.demo.model.Customer;

import com.example.demo.persistence.RepoCustomer;

public class ServiceCustomer {

@Autowired

private RepoCustomer repo;

    public Customer getById(Long id) {

Optional<Customer> cust = repo.findById(id);

if (cust.isPresent())

  return  cust.get();

else

  return null;   

    }

}

note that we defined a variable repo of type RepoCustomer and use @Autowired to leverage the Spring dependency injection to create a concrete object implementing the JpaRepository interface and call findById()which returns a Customer object. Actually, an Optional object wraps the Customer object.

the presentation

Now it's time for the presentation layer to come into play. It defines the API endpoints we want to expose to the outside.

In the package com.example.demo.presentation we create a controller class, which is responsible to handle all the HTTP commands like GET, POST, PUT, DELETE that may come in from an HTTP request. let's call it ControllerCustomer.

Since this is the class that will handle the API requests, we add to the class the following annotations:

the @RestController annotation 

the @RequestMapping annotation: we'll map all kinds of requests on the root "/api" path in this way:

@RequestMapping("/api")

then we add a method to handle a GET HTTP command that fetches a single record leveraging the service class we defined before:

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.example.demo.model.Customer;

import com.example.demo.service.ServiceCustomer;

@RestController

@RequestMapping("/api")

public class ControllerCustomer {

@Autowired

private ServiceCustomer service;

@GetMapping("/customer/{id}")

public Customer getById(@PathVariable Long id) {

  return service.getById(id);

}

}

again we use @Autowired to leverage the Spring dependency injection to create a concrete object of the type ServiceCustomer.

we define a method to find and return a Customer object to the request in this way:

@GetMapping("/customer/{id}"): the method responds to a GET command triggered on the path /api/customer/ 

remember the definition of @RequestMapping("/api") on top of the class: this maps any kind of request on /api to this class, letting the single class methods handle the specific HTTP commands on specific paths and parameters

{id} tells Spring that the URL contains a parameter with the name "id". The method getById retrieves the URL parameter using the annotation @PathVariable Long id

Then we call the ServiceCustomer method getById(id) to get back to the request command the customer entity.

note: we don't run a simple plain getAll() because the tables in this schema count about 20-30.000.000 rows, so much better to fetch a single record and make a more detailed sample.

Now we have defined:

• a class for the entity layer to represent the rows in the table
• an interface for the persistence layer for the CRUD operations
• a class for the service layer to bind the persistence to the presentation
• a class for the presentation layer as our API endpoint

Test with Postman

now, to test how it works we'll call the API url passing a known customer id, and get back the customer data. To do this we'll use a well famous tool to test APIs: Postman.

Now start the Spring Boot project, in the log you'll find out that the app runs Tomcat on port 8080.

Choose a C_CUSTKEY value from the ADB table SSB.CUSTOMER, i.e. 14047947; the resulting URL to test the REST API endpoint will be http://localhost:8080/api/customer/14047947

Open Postman and copy the URL in a new GET request in the right pane, you don't need to set any other parameter:

Click the "Send" button, and you'll get the customer data wrapped in a "Pretty" JSON object in the panel below. 

Notice that even in the other views available the data are always in JSON format since this is the default format returned by Spring Boot in the response.

now, if you have a look at the application log shown in the Spring Boot IDE, you can see the SQL query managed by Hibernate and run against the ADB:

That's all, and as always, have fun!