11. Spring Data Spanner

Spring Data is an abstraction for storing and retrieving POJOs in numerous storage technologies. Spring Cloud GCP adds Spring Data support for Google Cloud Spanner.

A Spring Boot starter is provided to auto-configure the various Storage components.

Maven coordinates for the Spring Boot Starter for Spanner, using Spring Cloud GCP BOM:

<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
</dependency>

Gradle coordinates:

dependencies {
    compile group: 'org.springframework.cloud', name: 'spring-cloud-gcp-starter-data-spanner'
}

This setup takes care of bringing in the latest compatible version of Cloud Java Spanner libraries as well.

11.1 Configuration

To setup Spring Data Spanner, you have to configure the following:

  • Setup the connection details to Google Cloud Spanner.
  • Enable Spring Data Repositories (optional).

11.1.1 Spanner settings

You can the use Spring Boot Starter for Spring Data Spanner to autoconfigure Google Cloud Spanner in your Spring application. It contains all the necessary setup that makes it easy to authenticate with your Google Cloud project. The following configuration options are available:

Name

Description

Optional

Default value

spring.cloud.gcp.spanner.instance-id

Spanner instance to use

No

 

spring.cloud.gcp.spanner.database

Spanner database to use

No

 

spring.cloud.gcp.spanner.project-id

GCP project ID where the Google Cloud Spanner API is hosted, if different from the one in the Spring Cloud GCP Core Module

Yes

 

spring.cloud.gcp.spanner.credentials.location

OAuth2 credentials for authenticating with the Google Cloud Spanner API, if different from the ones in the Spring Cloud GCP Core Module

Yes

 

spring.cloud.gcp.spanner.credentials.scopes

OAuth2 scope for Spring Cloud GCP Spanner credentials

Yes

https://www.googleapis.com/auth/spanner.data

11.1.2 Repository settings

Spring Data Repositories can be configured via the @EnableSpannerRepositories annotation on your main @Configuration class. With our Spring Boot Starter for Spring Data Spanner, @EnableSpannerRepositories is automatically added. It is not required to add it to any other class, unless there is a need to override finer grain configuration parameters provided by @EnableSpannerRepositories.

11.1.3 Autoconfiguration

Our Spring Boot autoconfiguration creates the following beans available in the Spring application context:

  • an instance of SpannerTemplate
  • an instance of all user defined repositories extending CrudRepository or PagingAndSortingRepository, when repositories are enabled
  • an instance of DatabaseClient from the Google Cloud Java Client for Spanner, for convenience and lower level API access

11.2 Object Mapping

Spring Data Spanner allows you to map domain POJOs to Spanner tables via annotations:

@Table(name = "traders")
public class Trader {

	@PrimaryKey
	@Column(name = "trader_id")
	String traderId;

	String firstName;

	String lastName;
}

11.2.1 Table

The @Table annotation can provide the name of the Spanner table that stores instances of the annotated class, one per row. This annotation is optional, and if not given, the name of the table is inferred from the class name with the first character uncapitalized.

SpEL expressions for table names

In some cases, you might want the @Table table name to be determined dynamically. To do that, you can use Spring Expression Language.

For example:

@Table(name = "trades_#{tableNameSuffix}")
public class Trade {
	// ...
}

The table name will be resolved only if the tableNameSuffix value/bean in the Spring application context is defined. For example, if tableNameSuffix has the value "123", the table name will resolve to trades_123.

11.2.2 Primary Keys

For a simple table, you may only have a primary key consisting of a single column. Even in that case, the @PrimaryKey annotation is required. @PrimaryKey identifies the one or more ID properties corresponding to the primary key.

Spanner has first class support for composite primary keys of multiple columns. You have to annotate all of your POJO’s fields that the primary key consists of with @PrimaryKey as below:

@Table(name = "trades")
public class Trade {
	@PrimaryKey(keyOrder = 2)
	@Column(name = "trade_id")
	private String tradeId;

	@PrimaryKey(keyOrder = 1)
	@Column(name = "trader_id")
	private String traderId;

	private String action;

	private Double price;

	private Double shares;

	private String symbol;
}

The keyOrder parameter of @PrimaryKey identifies the properties corresponding to the primary key columns in order, starting with 1 and increasing consecutively. Order is important and must reflect the order defined in the Spanner schema. In our example the DDL to create the table and its primary key is as follows:

CREATE TABLE trades (
    trader_id STRING(MAX),
    trade_id STRING(MAX),
    action STRING(15),
    symbol STRING(10),
    price FLOAT64,
    shares FLOAT64
) PRIMARY KEY (trader_id, trade_id)

Spanner does not have automatic ID generation. For most use-cases, sequential IDs should be used with caution to avoid creating data hotspots in the system. Read Spanner Primary Keys documentation for a better understanding of primary keys and recommended practices.

11.2.3 Columns

All accessible properties on POJOs are automatically recognized as a Spanner column. Column naming is generated by the PropertyNameFieldNamingStrategy by default defined on the SpannerMappingContext bean. The @Column annotation optionally provides a different column name than that of the property.

11.2.4 Relationships

Currently there is no support to map relationships between objects. I.e., currently we do not have ways to establish parent-children relationships directly via annotations. This feature is actively being worked on.

11.2.5 Supported Types

Spring Data Spanner supports the following types for regular fields:

  • com.google.cloud.ByteArray
  • com.google.cloud.Date
  • com.google.cloud.Timestamp
  • java.lang.Boolean, boolean
  • java.lang.Double, double
  • java.lang.Long, long
  • java.lang.Integer, int
  • java.lang.String
  • double[]
  • long[]
  • boolean[]
  • java.util.Date
  • java.util.Instant
  • java.sql.Date

11.2.6 Lists

Spanner supports ARRAY types for columns. ARRAY columns are mapped to List fields in POJOS. When using List, the @ColumnInnerType annotation is required to specify what is the type of the elements.

Example:

@ColumnInnerType(innerType = Double.class)
List<Double> curve;

Spring Data Spanner supports the following inner types:

  • com.google.cloud.ByteArray
  • com.google.cloud.Date
  • com.google.cloud.Timestamp
  • java.lang.Boolean, boolean
  • java.lang.Double, double
  • java.lang.Long, long
  • java.lang.Integer, int
  • java.lang.String
  • java.util.Date
  • java.util.Instant
  • java.sql.Date

11.2.7 Custom types

Custom converters can be used extending the type support for user defined types.

  1. Converters need to implement the org.springframework.core.convert.converter.Converter interface both directions.
  2. The user defined type needs to be mapped to one the basic types supported by Spanner:

    • com.google.cloud.ByteArray
    • com.google.cloud.Date
    • com.google.cloud.Timestamp
    • java.lang.Boolean, boolean
    • java.lang.Double, double
    • java.lang.Long, long
    • java.lang.String
    • double[]
    • long[]
    • boolean[]
  3. An instance of both Converters needs to be passed to a MappingSpannerConverter, which then has to be made available as a @Bean for SpannerConverter.

For example:

We would like to have a field of type Person on our Trade POJO:

@Table(name = "trades")
public class Trade {
  //...
  Person person;
  //...
}

Where Person is a simple class:

public class Person {

  public String firstName;
  public String lastName;

}

We have to define the two converters:

  public class PersonWriteConverter implements Converter<Person, String> {

    @Override
    public String convert(Person person) {
      return person.firstName + " " + person.lastName;
    }
  }

  public class PersonReadConverter implements Converter<String, Person> {

    @Override
    public Person convert(String s) {
      Person person = new Person();
      person.firstName = s.split(" ")[0];
      person.lastName = s.split(" ")[1];
      return person;
    }
  }

That will be configured in our @Configuration file:

@Configuration
public class ConverterConfiguration {

	@Bean
	public SpannerConverter spannerConverter(SpannerMappingContext spannerMappingContext) {
		return new MappingSpannerConverter(spannerMappingContext,
				Arrays.asList(new PersonWriteConverter()),
				Arrays.asList(new PersonReadConverter()));
	}
}

11.3 Spanner Template

SpannerOperations and its implementation, SpannerTemplate, provides the Template pattern familiar to Spring developers. It provides:

  • Resource management
  • One-stop-shop to Spanner operations with the Spring Data POJO mapping and conversion features
  • Exception conversion

Using the autoconfigure provided by our Spring Boot Starter for Spanner, your Spring application context will contain a fully configured SpannerTemplate object that you can easily autowire in your application:

@SpringBootApplication
public class SpannerTemplateExample {

	@Autowired
	SpannerTemplate spannerTemplate;

	public void doSomething() {
		this.spannerTemplate.delete(Trade.class, KeySet.all());
		//...
		Trade t = new Trade();
		//...
		this.spannerTemplate.insert(t);
		//...
		List<Trade> tradesByAction = spannerTemplate.findAll(Trade.class);
		//...
	}
}

The Template API provides convenience methods for:

  • Reads, and by providing SpannerReadOptions and SpannerQueryOptions

    • Stale read
    • Read with secondary indices
    • Read with limits and offsets
    • Read with sorting
  • Queries
  • DML operations (delete, insert, update, upsert)
  • Partial reads

    • You can define a set of columns to be read into your entity
  • Partial writes

    • Persist only a few properties from your entity
  • Read-only transactions
  • Locking read-write transactions

11.3.1 SQL Query

Spanner has SQL support for running read-only queries. All the query related methods start with query on SpannerTemplate. Using SpannerTemplate you can execute SQL queries that map to POJOs:

List<Trade> trades = this.spannerTemplate.query(Trade.class, Statement.of("SELECT * FROM trades"));

11.3.2 Read

Spanner exposes a Read API for reading single row or multiple rows in a table or in a secondary index.

Using SpannerTemplate you can execute reads, for example:

List<Trade> trades = this.spannerTemplate.readAll(Trade.class);

Main benefit of reads over queries is reading multiple rows of a certain pattern of keys is much easier using the features of the KeySet class.

11.3.3 Advanced reads

Stale read

All reads and queries are strong reads by default. A strong read is a read at a current timestamp and is guaranteed to see all data that has been committed up until the start of this read. A stale read on the other hand is read at a timestamp in the past. Cloud Spanner allows you to determine how current the data should be when you read data. With SpannerTemplate you can specify the Timestamp by setting it on SpannerQueryOptions or SpannerReadOptions to the appropriate read or query methods:

Reads:

// a read with options:
SpannerReadOptions spannerReadOptions = new SpannerReadOptions().setTimestamp(Timestamp.now());
List<Trade> trades = this.spannerTemplate.readAll(Trade.class, spannerReadOptions);

Queries:

// a query with options:
SpannerQueryOptions spannerQueryOptions = new SpannerQueryOptions().setTimestamp(Timestamp.now());
List<Trade> trades = this.spannerTemplate.query(Trade.class, Statement.of("SELECT * FROM trades"), spannerQueryOptions);

Read from a secondary index

Using a secondary index is available for Reads via the Template API and it is also implicitly available via SQL for Queries.

The following shows how to read rows from a table using a secondary index simply by setting index on SpannerReadOptions:

SpannerReadOptions spannerReadOptions = new SpannerReadOptions().setIndex("TradesByTrader");
List<Trade> trades = this.spannerTemplate.readAll(Trade.class, spannerReadOptions);

Read with offsets and limits

Limits and offsets are only supported by Queries. The following will get only the first two rows of the query:

SpannerQueryOptions spannerQueryOptions = new SpannerQueryOptions().setLimit(2).setOffset(3);
List<Trade> trades = this.spannerTemplate.query(Trade.class, Statement.of("SELECT * FROM trades"), spannerQueryOptions);

Note that the above is equivalent of executing SELECT * FROM trades LIMIT 2 OFFSET 3.

Sorting

Reads don’t support sorting. Queries on the Template API support sorting through standard SQL and also via Spring Data Sort API:

List<Trade> trades = this.spannerTemplate.queryAll(Trade.class, Sort.by("action"));

Partial read

Partial read is only possible when using Queries. In case the rows returned by query have fewer columns than the entity that it will be mapped to, Spring Data will map the returned columns and leave the rest as they of the columns are.

Summary of options for Query vs Read

Feature

Query supports it

Read supports it

SQL

yes

no

Partial read

yes

no

Limits

yes

no

Offsets

yes

no

Secondary index

yes

yes

Read using index range

no

yes

Sorting

yes

no

11.3.4 Write / Update

The write methods of SpannerOperations accept a POJO and writes all of its properties to Spanner. The corresponding Spanner table and entity metadata is obtained from the given object’s actual type.

If a POJO was retrieved from Spanner and its primary key properties values were changed and then written or updated, the operation will occur as if against a row with the new primary key values. The row with the original primary key values will not be affected.

Insert

The insert method of SpannerOperations accepts a POJO and writes all of its properties to Spanner, which means the operation will fail if a row with the POJO’s primary key already exists in the table.

Trade t = new Trade();
this.spannerOperations.insert(t);

Update

The update method of SpannerOperations accepts a POJO and writes all of its properties to Spanner, which means the operation will fail if the POJO’s primary key does not already exist in the table.

// t was retrieved from a previous operation
this.spannerOperations.update(t);

Upsert

The upsert method of SpannerOperations accepts a POJO and writes all of its properties to Spanner using update-or-insert.

// t was retrieved from a previous operation or it's new
this.spannerOperations.upsert(t);

Partial Update

The update methods of SpannerOperations operate by default on all properties within the given object, but also accept String[] and Optional<Set<String>> of column names. If the Optional of set of column names is empty, then all columns are written to Spanner. However, if the Optional is occupied by an empty set, then no columns will be written.

// t was retrieved from a previous operation or it's new
this.spannerOperations.update(t, "symbol", "action");

11.3.5 Transactions

SpannerOperations provides methods to run java.util.Function objects within a single transaction while making available the read and write methods from SpannerOperations.

Read/Write Transaction

Read and write transactions are provided by SpannerOperations via the performReadWriteTransaction method:

@Autowired
SpannerOperations mySpannerOperations;

public String doWorkInsideTransaction() {
  return mySpannerOperations.performReadWriteTransaction(
    transActionSpannerOperations -> {
      // work with transActionSpannerOperations here. It is also a SpannerOperations object.

      return "transaction completed";
    }
  );
}

The performReadWriteTransaction method accepts a Function that is provided an instance of a SpannerOperations object. The final returned value and type of the function is determined by the user. You can use this object just as you would a regular SpannerOperations with a few exceptions:

  • Its read functionality cannot perform stale reads, because all reads and writes happen at the single point in time of the transaction.
  • It cannot perform sub-transactions via performReadWriteTransaction or performReadOnlyTransaction.

As these read-write transactions are locking, it is recommended that you use the performReadOnlyTransaction if your function does not perform any writes.

Read-only Transaction

The performReadOnlyTransaction method is used to perform read-only transactions using a SpannerOperations:

@Autowired
SpannerOperations mySpannerOperations;

public String doWorkInsideTransaction() {
  return mySpannerOperations.performReadOnlyTransaction(
    transActionSpannerOperations -> {
      // work with transActionSpannerOperations here. It is also a SpannerOperations object.

      return "transaction completed";
    }
  );
}

The performReadOnlyTransaction method accepts a Function that is provided an instance of a SpannerOperations object. This method also accepts a ReadOptions object, but the only attribute used is the timestamp used to determine the snapshot in time to perform the reads in the transaction. If the timestamp is not set in the read options the transaction is run against the current state of the database. The final returned value and type of the function is determined by the user. You can use this object just as you would a regular SpannerOperations with a few exceptions:

  • Its read functionality cannot perform stale reads, because all reads happen at the single point in time of the transaction.
  • It cannot perform sub-transactions via performReadWriteTransaction or performReadOnlyTransaction
  • It cannot perform any write operations.

Because read-only transactions are non-locking and can be performed on points in time in the past, these are recommended for functions that do not perform write operations.

11.4 Repositories

Spring Data Repositories are a powerful abstraction that can save you a lot of boilerplate code.

For example:

public interface TraderRepository extends CrudRepository<Trader, String> {
}

Spring Data generates a working implementation of the specified interface, which can be conveniently autowired into an application.

The Trader type parameter to CrudRepository refers to the underlying domain type. The second type parameter, String in this case, refers to the type of the key of the domain type.

For POJOs with a composite primary key, this ID type parameter can be any descendant of Object[] compatible with all primary key properties, any descendant of Iterable, or com.google.cloud.spanner.Key. If the domain POJO type only has a single primary key column, then the primary key property type can be used or the Key type.

For example in case of Trades, that belong to a Trader, TradeRepository would look like this:

public interface TradeRepository extends CrudRepository<Trade, String[]> {

}
public class MyApplication {

	@Autowired
	SpannerOperations spannerOperations;

	@Autowired
	StudentRepository studentRepository;

	public void demo() {

		this.tradeRepository.deleteAll(); //defined on CrudRepository
		String traderId = "demo_trader";
		Trade t = new Trade();
		t.symbol = stock;
		t.action = action;
		t.traderId = traderId;
		t.price = 100.0;
		t.shares = 12345.6;
		this.spannerOperations.insert(t); //defined on CrudRepository

		Iterable<Trade> allTrades = this.tradeRepository.findAll(); //defined on CrudRepository

		int count = this.tradeRepository.countByAction("BUY");

	}
}

11.4.1 CRUD Repository

CrudRepository methods work as expected, with one thing Spanner specific: the save and saveAll methods work as update-or-insert.

11.4.2 Paging and Sorting Repository

You can also use PagingAndSortingRepository with Spanner Spring Data. The sorting and pageable findAll methods available from this interface operate on the current state of the Spanner database. As a result, beware that the state of the database (and the results) might change when moving page to page.

11.4.3 Query methods by convention

public interface TradeRepository extends CrudRepository<Trade, String[]> {
    List<Trade> findByAction(String action);

	int countByAction(String action);

	// Named methods are powerful, but can get unwieldy
	List<Trade> findTop3DistinctByActionAndSymbolOrTraderIdOrderBySymbolDesc(
  			String action, String symbol, String traderId);
}

In the example above, the query methods in TradeRepository are generated based on the name of the methods, using the Spring Data Query creation naming convention.

List<Trade> findByAction(String action) would translate to a SELECT * FROM trades WHERE action = ?.

The function List<Trade> findTop3DistinctByActionAndSymbolOrTraderIdOrderBySymbolDesc(String action, String symbol, String traderId); will be translated as the equivalent of this SQL query:

SELECT DISTINCT * FROM trades
WHERE ACTION = ? AND SYMBOL = ? AND TRADER_ID = ?
ORDER BY SYMBOL DESC
LIMIT 3

11.4.4 Custom SQL query methods

The example above for List<Trade> fetchByActionNamedQuery(String action) does not match the Spring Data Query creation naming convention, so we have to map a parametrized Spanner SQL query to it.

The SQL query for the method can be mapped to repository methods in one of two ways:

  • namedQueries properties file
  • using the @Query annotation

Query methods with named queries properties

By default, the namedQueriesLocation attribute on @EnableSpannerRepositories points to the META-INF/spanner-named-queries.properties file. You can specify the query for a method in the properties file by providing the SQL as the value for the "interface.method" property:

Trade.fetchByActionNamedQuery=SELECT * FROM trades WHERE trades.action = @tag0
public interface TradeRepository extends CrudRepository<Trade, String[]> {
	// This method uses the query from the properties file instead of one generated based on name.
	List<Trade> fetchByActionNamedQuery(String action);
}

Query methods with annotation

Using the @Query annotation:

public interface TradeRepository extends CrudRepository<Trade, String[]> {
    @Query("SELECT * FROM trades WHERE trades.action = @tag0")
    List<Trade> fetchByActionNamedQuery(String action);
}

Table names can be used directly. For example, "trades" in the above example. Alternatively, table names can be resolved from the @Table annotation on domain classes as well. In this case, the query should refer to table names with fully qualified class names between : characters: :fully.qualified.ClassName:. A full example would look like:

@Query("SELECT * FROM :com.example.Trade: WHERE trades.action = @tag0")
List<Trade> fetchByActionNamedQuery(String action);

This allows table names evaluated with SpEL to be used in custom queries.

11.4.5 REST Repositories

When running with Spring Boot, repositories can act as REST services by simply annotating them:

@RepositoryRestResource(collectionResourceRel = "trades", path = "trades")
public interface TradeRepository extends CrudRepository<Trade, String[]> {
}

The @RepositoryRestResource annotation makes this repository available via REST. For example, you can retrieve all Trade objects in the repository by using curl http://<server>:<port>/trades, or any specific trade via curl http://<server>:<port>/trades/<trader_id>,<trade_id>.

The separator between your primary key components, id and trader_id in this case, is a comma by default, but can be configured to any string not found in your key values by extending the SpannerKeyIdConverter class:

@Component
class MySpecialIdConverter extends SpannerKeyIdConverter {

    @Override
    protected String getUrlIdSeparator() {
        return ":";
    }
}

You can also write trades using curl -XPOST -H"Content-Type: application/json" -d@test.json http://<server>:<port>/trades/ where the file test.json holds the JSON representation of a Trade object.

Include this dependency in your pom.xml to enable Spring Data REST Repositories:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>