X

The Oracle NoSQL Database Blog covers all things Oracle NoSQL Database. On-Prem, Cloud and more.

  • May 11, 2021

Getting Started - Accessing Oracle NoSQL Database from Spring Data Applications

Michael Brey
Director of NoSQL Development

 

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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.