Author: Cezar Andrei – Principal Member Technical Staff – NoSQL development
This blog shows how to use the SDK for Spring Data module in your application to manage data in Oracle NoSQL Database Cloud Service.
The sample application will show how to store, retrieve, update, query and delete Customer POJOs (Plain Old Java Objects) in a NoSQL Database store.
Do you want your Spring application to access data with predictable single-digit millisecond response times, at a massive scale, in a highly available elastic scaling store? Do you want to be able to move your application between on-premise and cloud at a moment’s notice? Then let’s embark together in a 15 minute tutorial to untangle these mysteries.
What you need
- About 15 minutes
- JDK 1.8 or later installed on your system
- Maven 3.2 or later installed on your system
- Oracle Cloud Service account – Try Oracle NoSQL Database Cloud Service for free
This tutorial will make use of Oracle NoSQL Database Cloud Service, if you don’t have an account already you can sign-up and try the Oracle NoSQL Database Cloud Service with a 30-day trial or try the always free tier. If you use the always free tier, you will need to create your table ahead of time using the OCI console. Refer to this blog for detailed steps. The CREATE TABLE DDL statement is in the first line of the debug output down below.
Preparation
The Oracle NoSQL SDK for Spring Data is available on Maven Central.
If you already have a Spring application you can skip this step, otherwise use Maven’s quick start archetype to generate a simple maven application:
$ mvn -B archetype:generate -DgroupId=org.example.app -DartifactId=my-app -DarchetypeArtifactId=maven-archetype-quickstart
$ cd my-app
Setup dependencies
Add the following dependencies inside pom.xml file:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.3.4.RELEASE</version>
</dependency>
<dependency>
<groupId>com.oracle.nosql.sdk</groupId>
<artifactId>spring-data-oracle-nosql</artifactId>
<version>1.2.0</version>
</dependency>
Set the following properties to avoid errors related to Java source and target version:
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
Next steps will guide you through the code for the application. You’ll need to add 4 new source code files into the src/main/java directory: define an entity, define a repository, define a database configuration and a simple application. The following diagram provides the components of the Oracle NoSQL Database SDK for Spring Data.
Define the entity
We must define the entity to be saved in the database. In our application Customer class will contain the data for a customer. Create the src/main/java/org/example/app/Customer.java file and add the following code:
package org.example.app;
import java.util.Date;
import com.oracle.nosql.spring.data.core.mapping.NosqlId;
import com.oracle.nosql.spring.data.core.mapping.NosqlTable;
@NosqlTable(storageGB = 1, writeUnits = 10, readUnits = 10)
public class Customer {
@NosqlId(generated = true)
long customerId;
String firstName;
String lastName;
Date createdAt;
@Override
public String toString() {
return "Customer{" +
"customerId=" + customerId +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
", createdAt='" + createdAt + '\'' +
'}';
}
}
For this class, the module automatically creates a table called Customer in the store, if one doesn’t exist already.
The @NosqlTable annotation field contains the parameters required for the table creation. StorageGB specifies the maximum amount of storage, in gigabytes, allowed for the table. ReadUnits and WriteUnits specifies the maximum read and write throughput allowed for the table. These settings can be changed using the OCI console or programmatically.
In this case, Customer class contains a customerId field, the key for a customer, and three other String fields for first and last names. The customerId field is annotated with @NosqlId annotation in order to let the module know to internally generate a key. The annotation is optional and it can be removed if the id field is generated by the application.
In the general case, the entity can contain any number of other fields, like other POJOs, lists or arrays of POJOs. For a complete list of supported data types look at NoSQL for Spring Data Module documentation.
For each entity stored, the SDK will create a row with two columns: a primary key column corresponding to the entity id and a second JSON typed column that contains the rest of the entity fields.
Define a simple repository
The SDK allows storing and retrieving data to/from NoSQL Database, this is done using the CRUD (creare, read, update, delete) methods in NosqlRepository.
The SDK also allows the developer to define special methods that will generate derived Nosql queries.
Create the file src/main/java/org/example/app/CustomerRepository.java and add the following code, which defines the interface for a Customer repository:
package org.example.app;
import java.util.Date;
import com.oracle.nosql.spring.data.repository.NosqlRepository;
public interface CustomerRepository
extends NosqlRepository<Customer, Long>
{
Iterable<Customer> findByLastName(String lastname);
Iterable<Customer> findByCreatedAtBetween(Date start, Date end);
}
Our CustormerRepository extends NosqlRepository and has, as type parameters, the entity type: Customer and the ID type: Long. It also contains two methods that will generate queries based on the method name. The query for findByLastName method will filter customers based on the lastName value, while the query for findByDateBetween method will filter customers based on the date field. For all supported method constructs see the documentation.
How to connect to Oracle NoSQL Database
The application needs to know how to connect to the cloud service. This is done defining a configuration Spring bean that provides a NosqlDbConfig object.
There are a couple of ways to connect to Oracle NoSQL Database Cloud Service. If the application is running in an Oracle Cloud instance it can use instance principal authentication. For this, use the code in the next section. For all other cases use the signature authentication section.
Authenticate using instance principal
When running the application in the Oracle Cloud, the recommended way to connect to the cloud service is to use instance principal authentication. This requires a one-time setup and the following code in src/main/java/org/example/app/AppConfig.java:
package org.example.app;
import oracle.nosql.driver.NoSQLHandleConfig;
import oracle.nosql.driver.Region;
import oracle.nosql.driver.iam.SignatureProvider;
import com.oracle.nosql.spring.data.config.AbstractNosqlConfiguration;
import com.oracle.nosql.spring.data.config.NosqlDbConfig;
import com.oracle.nosql.spring.data.repository.config.EnableNosqlRepositories;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import oracle.nosql.driver.kv.StoreAccessTokenProvider;
@Configuration
@EnableNosqlRepositories
public class AppConfig extends AbstractNosqlConfiguration {
@Bean
public NosqlDbConfig nosqlDbConfig()
throws java.io.IOException {
/* Config for cloud service using instance principal. */
SignatureProvider provider = SignatureProvider.createWithInstancePrincipal();
/* Use the same region your instance VM runs in. */
NoSQLHandleConfig config = new NoSQLHandleConfig(Region.US_PHOENIX_1, provider);
/* Compartment_id is required when using instance principal
Set it to the compartment id used in the setup step. */
config.setDefaultCompartment("compartment_id");
return new NosqlDbConfig(config);
}
}
Note: When using instance principal configuration, a compartment id must also be set.
Authenticate using Signature Provider
This configuration allows the application to connect from anywhere on internet.
The code below requires tenancy id, user id, fingerprint information which can be found on the profile page of the cloud account under User Information tab on View Configuration File. Also add in the passphrase to your private key. For instructions on generating a key pair in PEM format and how to get its fingerprint, see Required Keys and OCIDs. That page also contains information on how to get the tenancy id, user id, and fingerprint information.
The configuration code goes in file src/main/java/org/example/app/AppConfig.java. Create the file and add the code:
package org.example.app;
import java.io.File;
import oracle.nosql.driver.NoSQLHandleConfig;
import oracle.nosql.driver.Region;
import oracle.nosql.driver.iam.SignatureProvider;
import com.oracle.nosql.spring.data.config.AbstractNosqlConfiguration;
import com.oracle.nosql.spring.data.config.NosqlDbConfig;
import com.oracle.nosql.spring.data.repository.config.EnableNosqlRepositories;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import oracle.nosql.driver.kv.StoreAccessTokenProvider;
@Configuration
@EnableNosqlRepositories
public class AppConfig extends AbstractNosqlConfiguration {
@Bean
public NosqlDbConfig nosqlDbConfig()
throws java.io.IOException {
/* Config for cloud service */
return new NosqlDbConfig(new NoSQLHandleConfig(
Region.US_ASHBURN_1.endpoint(),
new SignatureProvider(
"ocid1.tenancy.oc1...", //tenantId
"ocid1.user.oc1...", //userId
"ab:...", // fingerprint of the key
new File("/home/cezar/.oci/oci_api_key.pem"), // path to your private key file
null // passphrase for the (encrypted) private key
)
));
}
}
The NosqlDbConfig object is created by providing the region endpoint and the path to the cloud config file. If you are using the always free tier, then the endpoint needs to be Phoenix. Regions where the NoSQL always free tier is available is contained on this list.
NosqlDbConfig contains helper methods to different setups:
- for cloud: NosqlDbConfig.createCloudConfig(“endpoint”, configFile);
- for cloud simulator: NosqlDbConfig.createCloudSimConfig(“endpoint”);
- for on-prem unsecure store: NosqlDbConfig.createProxyConfig(“endpoint”);
- for on-prem secure store: NosqlDbConfig.createProxyConfig(“endpoint”, user, password);
Running the application
First let’s add the application code into src/main/java/org/example/app/App.java:
package org.example.app;
import java.util.Date;
import java.time.Instant;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
@SpringBootApplication
public class App implements CommandLineRunner
{
@Autowired
private CustomerRepository repo;
public static void main( String[] args )
{
ConfigurableApplicationContext
ctx = SpringApplication.run(App.class, args);
ctx.close();
System.exit(0);
}
@Override
public void run(String... args) throws Exception {
// Remove all previous customers
repo.deleteAll();
// Create and save a couple of customers
Customer s1 = new Customer();
s1.firstName = "John";
s1.lastName = "Doe";
s1.createdAt = Date.from(Instant.parse("2021-01-01T10:01:10Z"));
repo.save(s1);
// Note repo.save(s1) sets up the customerId inside s1.
System.out.println("\nsaved: " + s1);
Customer s2 = new Customer();
s2.firstName = "John";
s2.lastName = "Smith";
s2.createdAt = Date.from(Instant.parse("2021-04-19T20:25:20Z"));
repo.save(s2);
System.out.println("\nsaved: " + s2);
// Add other customers here.
// Retrieve all customers
System.out.println("\nfindAll:");
repo.findAll()
.forEach(c -> System.out.println(" " + c));
// Filter customers by last name
System.out.println("\nfindByLastName: Smith");
repo.findByLastName("Smith")
.forEach(c -> System.out.println(" " + c));
// Filter customers by date
System.out.println("\nfindByCreatedAtBetween: January and February 2021");
repo.findByCreatedAtBetween(
Date.from(Instant.parse("2021-01-01T00:00:00Z")),
Date.from(Instant.parse("2021-02-28T24:00:00Z")))
.forEach(c -> System.out.println(" " + c));
}
}
Our App defines a repo field of type CustomerRepository. This is auto wired by Spring framework to an implementation of our CustomerRepository interface. This allows us to directly save, update, delete or find objects from repository.
Now we can build and run the application:
mvn compile
mvn exec:java -Dexec.mainClass="org.example.app.App"
saved: Customer{customerId=1013, firstName='John', lastName='Doe', createdAt='Fri Jan 01 04:01:10 CST 2021'}
saved: Customer{customerId=1014, firstName='John', lastName='Smith', createdAt='Mon Apr 19 15:25:20 CDT 2021'}
findAll:
Customer{customerId=1013, firstName='John', lastName='Doe', createdAt='Fri Jan 01 04:01:10 CST 2021'}
Customer{customerId=1014, firstName='John', lastName='Smith', createdAt='Mon Apr 19 15:25:20 CDT 2021'}
findByLastName: Smith
Customer{customerId=1014, firstName='John', lastName='Smith', createdAt='Mon Apr 19 15:25:20 CDT 2021'}
findByCreatedAtBetween: January and February 2021
Customer{customerId=1013, firstName='John', lastName='Doe', createdAt='Fri Jan 01 04:01:10 CST 2021'}
As we can see from the output the two Customer objects are saved into the repository. After the save call, the object has the generated id value in place. This value can be used to later find, update or delete this object.
Also repo.findByLastName(“Smith”) returns only the entities that follow the criteria of having the lastName field equal to the given value. This is achieved by internally generating a NoSQL database query that implements the specified criteria. The output of the generated queries is available if DEBUG logging in com.oracle.nosql.spring.data is enabled.
To run the application with debug enabled use:
mvn exec:java -Dexec.mainClass="org.example.app.App" -Dlogging.level.com.oracle.nosql.spring.data=DEBUG
2021-04-20 12:56:07.519 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : DDL: CREATE TABLE IF NOT EXISTS Customer (customerId LONG GENERATED ALWAYS as IDENTITY (NO CYCLE), kv_json_ JSON, PRIMARY KEY( customerId ))
2021-04-20 12:56:10.037 INFO 39450 --- [.app.App.main()] org.example.app.App : Started App in 4.265 seconds (JVM running for 6.242)
2021-04-20 12:56:10.043 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Prepare: DELETE FROM Customer
2021-04-20 12:56:10.470 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Q: DELETE FROM Customer
saved: Customer{customerId=17, firstName='John', lastName='Doe', createdAt='Fri Jan 01 04:01:10 CST 2021'}
saved: Customer{customerId=1015, firstName='John', lastName='Smith', createdAt='Mon Apr 19 15:25:20 CDT 2021'}
findAll:
2021-04-20 12:56:11.941 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Prepare: SELECT * FROM Customer t
2021-04-20 12:56:12.195 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Q: SELECT * FROM Customer t
Customer{customerId=17, firstName='John', lastName='Doe', createdAt='Fri Jan 01 04:01:10 CST 2021'}
Customer{customerId=1015, firstName='John', lastName='Smith', createdAt='Mon Apr 19 15:25:20 CDT 2021'}
findByLastName: Smith
2021-04-20 12:56:12.835 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Prepare: declare $p_lastName String; select * from Customer as t where t.kv_json_.lastName = $p_lastName
2021-04-20 12:56:12.907 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Q: declare $p_lastName String; select * from Customer as t where t.kv_json_.lastName = $p_lastName
Customer{customerId=1015, firstName='John', lastName='Smith', createdAt='Mon Apr 19 15:25:20 CDT 2021'}
findByCreatedAtBetween: January and February 2021
2021-04-20 12:56:13.014 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Prepare: declare $p_date Timestamp; $p_date1 Timestamp; select * from Customer as t where (cast(t.kv_json_.date as Timestamp) >= $p_date AND cast(t.kv_json_.date as Timestamp) <= $p_date1)
2021-04-20 12:56:13.086 DEBUG 39450 --- [.app.App.main()] c.o.n.spring.data.core.NosqlTemplate : Q: declare $p_date Timestamp; $p_date1 Timestamp; select * from Customer as t where (cast(t.kv_json_.date as Timestamp) >= $p_date AND cast(t.kv_json_.date as Timestamp) <= $p_date1)
Customer{customerId=17, firstName='John', lastName='Doe', createdAt='Fri Jan 01 04:01:10 CST 2021'}
Details on all supported constructs are available in SDK for Spring Data documentation. Also when you require queries that these constructs cannot provide, you can add native queries to the repository interfaces. Check the documentation on how to run native queries.
Enjoy and let us know how it went!
If you want to find out more about the Oracle NoSQL Database Cloud Service, please visit this page.