Creating Tables with Composite Keys

Learn to create a table with composite primary key fields using Oracle NoSQL Database SDK for Spring Data.

You use the @NosqlKey annotation to identify the annotated field as a component of the composite primary key.

Example 2-4 Creating a table with composite primary key fields

The following examples shows how to model Student as an entity and use universityId, academicYear, and studentId fields as composite keys.

A Composite key is helpful when you want to use more than one primary key field conjointly to identify a unique row. Within the composite key, you can identify the primary key fields that can be a part of the Shard Key and also specify the ordering of the fields.

The following example shows how to create a composite key with the key fields from student data. You define a class named StudentKey to represent the composite key class and then use that in the Student entity as described in the following example:

Create a composite key class with the @NosqlKey annotation to identify the composite keys. Set the shardKey value to true if the field is a part of the shard key. Set the order value for all the fields in the order of primary key field generation in the table. For more details on the shardKey and order elements, see Table 1-4.

In the following code sample, the universityId, academicYear, and studentId fields represent the key fields for identifying a student's data and are declared as the primary key fields using the @NosqlKey annotation. For an illustration of ordering within the primary key fields, consider two of the primary key fields as shard keys and the third as a non-shard key.

Set the shardKey value of the universityId and academicYear fields to true, and the studentId field to false. Set the order value for the universityId field to 0 to create the universityId field as the first primary key field and academicYear to 1 to create as second primary key field. As the studentId field is a non-shard key, its order value must be higher than the shard keys. Set the order value of the studentId field to 2.

import com.oracle.nosql.spring.data.core.mapping.NosqlKey;
import java.io.Serializable;
import java.util.Objects;
 
/* Define a composite Key class */

public class StudentKey implements Serializable {
    
    @NosqlKey(shardKey = true, order = 0)
    long universityId;

    @NosqlKey(shardKey = true, order = 1)
    int academicYear;

    @NosqlKey(shardKey = false, order = 2)
    long studentId;

    /* public or package protected constructor required when retrieving from database */
    public StudentKey() {
    }

    public StudentKey(long universityId, int academicYear, long studentId) {
        this.universityId = universityId;
        this.academicYear = academicYear;
        this.studentId = studentId;
    }

    public long getUniversityId() {
        return universityId;
    }

    public void setUniversityId(long universityId) {
        this.universityId = universityId;
    }

    public int getAcademicYear() {
        return academicYear;
    }

    public void setAcademicYear(int academicYear) {
        this.academicYear = academicYear;
    }

    public long getStudentId() {
        return studentId;
    }

    public void setStudentId(long studentId) {
        this.studentId = studentId;
    }

    /* Define equals method */
    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof StudentKey)) {
            return false;
        }
        StudentKey studentKey = (StudentKey) o;
        return Objects.equals(universityId, studentKey.universityId) &&
                Objects.equals(academicYear, studentKey.academicYear) &&
                Objects.equals(studentId, studentKey.studentId);
    }

    /* Define hashcode method */
    @Override
    public int hashCode() {
        return Objects.hash(universityId, academicYear, studentId);
    }
}

Create the Student entity class with StudentKey as a composite primary key. The StudentKey is annotated with @NosqlId in the entity class to indicate the primary key.

You can declare any non-key fields in the entity class. The non-key fields will be included as JSON data in the kv_json_ column.

import com.oracle.nosql.spring.data.core.mapping.NosqlId;
import com.oracle.nosql.spring.data.core.mapping.NosqlTable;
import com.oracle.nosql.spring.data.core.mapping.NosqlKey;

import java.io.Serializable;
import java.util.Objects;
 
/*The @NosqlTable annotation specifies that
  this class will be mapped to an Oracle NoSQL Database table.*/

@NosqlTable

public class Student {
    @NosqlId 
    StudentKey studentKey;
    String firstName;
    String lastName;
    String resident;
    
    /* public or package protected constructor required when retrieving from database */
    public Student() {
        studentKey = new StudentKey();
    }
    
    
    /*This method overrides the toString() method, and then concatenates id and name, and then returns a String*/
    @Override
    public String toString() {
        return "Student{" +
                "universityId=" + studentKey.universityId + ", " +
                "academicYear=" + studentKey.academicYear + ", " +
                "studentId=" + studentKey.studentId + ", " +
                "firstName=" + firstName + ", " +
                "lastName=" + lastName + ", " +
                "resident=" + resident+
                '}';
    }
}

Note:

You must set up the AppConfig class that provides a NosqlDbConfig Spring bean. The NosqlDbConfig Spring bean describes how to connect to the Oracle NoSQL Database. You must also create an interface that extends the NosqlRepository interface to retrieve the data from the Oracle NoSQL Database. For details, see the section Accessing Oracle NoSQL Database Using Spring Data Framework.

The Spring Data Framework creates the Student table with the following DDL:

/* Student table DDL */

CREATE TABLE IF NOT EXISTS Student (
   universityId LONG,
   academicYear INTEGER,
   studentId LONG,
   kv_json_ JSON,
   PRIMARY KEY(SHARD(universityId, academicYear), studentId)
)

The primary key fields universityId and academicYear are also shard keys and studentId is a non-shard primary key field.