2 Ways To Create Multiple Google Spanner Data Sources in SpringBoot App

4 min readApr 1, 2023

Google Spanner is a globally distributed relational database system developed by Google. It is designed to provide high scalability, availability, and consistency across geographically diverse locations. Spanner uses a combination of advanced techniques, including synchronous replication, distributed transactions, and atomic clocks, to achieve its goals.

Spanner is a fully managed service provided by Google Cloud Platform (GCP), and it can scale to thousands of nodes across multiple data centers. It is used by a variety of industries, including finance, retail, and telecommunications, to store and manage large amounts of data with low latency and high throughput.

Spanner supports standard SQL queries, as well as features such as automatic sharding, online schema changes, and backup and recovery. It is a highly advanced database system that is designed for use cases that require high availability, low latency, and strong consistency guarantees.

In this article we will cover how to configure multiple spanner database sources in a Spring Boot application but before we dive into the database configuration we are assuming that you already have basic code setup for spanner configuration in your spring boot app make sure you have a dependency in pom.xml file, if not you can copy and add it in your pom.xml file from maven repository

https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-gcp-starter-data-spanner

add these properties in your application.properties file

spring.cloud.gcp.spanner.instance-id=[your-instance-id] 
spring.cloud.gcp.spanner.database=[your-database-name]

these properties points your spanner database connection to the spanner instance and database that you already have created on cloud, the above properties are configured for a single database if you want to multiple data sources here is what you have to do

Method # 1 — Using a single Spanner Bean

Create a Bean of DatabaseIdProvider like this

@Configuration
public class DataSourceConfiguration {
    
    private static String database;

    private static Map<String, String> databaseConfig;

    @Bean
    public Map<String, String> dataBaseConfigMap() {
        Map<String, String> dataBaseConfig = new HashMap<>();
        dataBaseConfig.put("db1", "db1-project-Id,db1-instance-id,db1-db-name");  //DataBase 1 Config
        dataBaseConfig.put("db2", "db2-project-Id,db2-instance-id,db2-db-name");  //DataBase 2 Config
        dataBaseConfig.put("db3", "db3-project-Id,db3-instance-id,db3-db-name");  //DataBase 3 Config
        return dataBaseConfig;
    }

    public static void setDatabase(String dataBaseConn) {
        database = dataBaseConn;
    }

    @Bean
    public DatabaseIdProvider databaseIdProvider(SpannerOptions spannerOptions, Map<String, String> dataBaseConfigMap) {
        String[] dbConfig = dataBaseConfig.get(database).split(",");
        String projectId = dbConfig[0];
        String instanceId = dbConfig[1];
        String databaseName = dbConfig[2];
        return () ->
                DatabaseId.of(
                        projectId, instanceId, databaseName);
    }
}

In the above class we are setting multiple database configurations in dataBaseConfigMap and passing it into the databaseIdProvider Bean and trying to get the connection using the database key, this database key we can set in the class where we need that particular database connection like this

@RestController
@RequestMapping("/")
public class DBConnector {

@Autowired
DBRepository dbRepository;

@GetMapping("/getDB1Connection")
public String getDB1Connection() {
    DataSourceConfiguration.setDatabase("db1");
    String data = dbRepository.findById("db1-id");
    return "db1Connected - "+ data;
 }

@GetMapping("/getDB2Connection")
public String getDB2Connection() {
    DataSourceConfiguration.setDatabase("db2");
    String data = dbRepository.findById("db2-id");
    return "db2Connected - " + data;
 }

}

here we have configured two REST endpoints,

/getDB1Connection connects our application to db1
/getDB2Connection connects our application to db2

after acquiring a connection we can easily perform db transactions, make sure you already have the repository setup in the code like this

public interface DBRepository extends SpannerRepository<Table, String[]> {
  
  Table findById(String id);

}

The great advantage of this method is it will not take any extra memory to save multiple connections, only one spanner Bean will be switched among multiple data source configurations, it can help in the scenario where we have numerous data sources and we want to switch among the configurations upon request, but the drawback of this approach is it can not carry multiple connections in parallel, only one database connection will be active at one time.

Method # 2 — Using multiple Spanner Templates

To make parallel database connections we need to create multiple spanner template Beans use these template beans for database operations

@Configuration
public class DataSourceConfiguration {

 @Bean
 public SpannerMappingContext spannerMappingContext() {
  return new SpannerMappingContext();
 }

 @Bean
 public SpannerEntityProcessor spannerEntityProcessor(SpannerMappingContext mappingContext) {
  return new ConverterAwareMappingSpannerEntityProcessor(mappingContext);
 }

 @Bean
 public SpannerSchemaUtils spannerSchemaUtils(
  SpannerMappingContext spannerMappingContext, SpannerEntityProcessor spannerEntityProcessor) {
  return new SpannerSchemaUtils(spannerMappingContext, spannerEntityProcessor, true);
 }

 @Bean
 public SpannerMutationFactory spannerMutationFactory(
  SpannerEntityProcessor spannerEntityProcessor,
  SpannerMappingContext spannerMappingContext,
  SpannerSchemaUtils spannerSchemaUtils) {
  return new SpannerMutationFactoryImpl(
   spannerEntityProcessor, spannerMappingContext, spannerSchemaUtils);
 }

 //Spanner Template for DB1
 @Bean
 @Qualifier("db1Template")
 public SpannerTemplate db1SpannerTemplate(SpannerMappingContext mappingContext,
   SpannerEntityProcessor spannerEntityProcessor,
   SpannerSchemaUtils spannerSchemaUtils,
   SpannerMutationFactory spannerMutationFactory) {
   SpannerOptions bagSpannerOptions = SpannerOptions.newBuilder()
    .setProjectId("db1-project-id").build())
  .build();

 DatabaseId databaseId = DatabaseId.of("db1-project-id",
  "db1-spanner-instance",
  "db1-database-name");

 DatabaseClient databaseClient = bagSpannerOptions.getService().getDatabaseClient(databaseId);
 return new SpannerTemplate(
  () -> databaseClient,
  mappingContext,
  spannerEntityProcessor,
  spannerMutationFactory,
  spannerSchemaUtils);
}

//Spanner Template for DB2
@Bean
@Qualifier("db2Template")
public SpannerTemplate db2SpannerTemplate(SpannerMappingContext mappingContext,
  SpannerEntityProcessor spannerEntityProcessor,
  SpannerSchemaUtils spannerSchemaUtils,
  SpannerMutationFactory spannerMutationFactory) {
  SpannerOptions bagSpannerOptions = SpannerOptions.newBuilder()
   .setProjectId("db2-project-id").build())
 .build();

DatabaseId databaseId = DatabaseId.of("db2-project-id",
 "db2-spanner-instance",
 "db2-database-name");

DatabaseClient databaseClient = bagSpannerOptions.getService().getDatabaseClient(databaseId);
return new SpannerTemplate(
 () -> databaseClient,
 mappingContext,
 spannerEntityProcessor,
 spannerMutationFactory,
 spannerSchemaUtils);
}

}

here we have completely separated out the spanner templates for each data source, this allows us to retain parallel data source connections in the memory.

Now we can easily perform queries using each spanner template beans

@RestController
@RequestMapping("/")
public class DBConnector {

@Autowired
@Qualifier("db1Template")
SpannerTemplate db1Template;

@Autowired
@Qualifier("db2Template")
SpannerTemplate db2Template;


@GetMapping("/getDB1Connection")
public String getDB1Connection() {
    Statement db1Statement = Statement.newBuilder("Select * from table where id IN UNNEST(@id)")
                              .bind("id").build();
    String data = db1Template.executeDmlStatement(db1Statement);
    return "db1Connected - "+ data;
 }

@GetMapping("/getDB2Connection")
public String getDB2Connection() {
    Statement db2Statement = Statement.newBuilder("Select * from table where id IN UNNEST(@id)")
                              .bind("id").build();
    String data = db2Template.executeDmlStatement(db2Statement);
    return "db2Connected - "+ data;
 }

}

The great advantage of this method is we are able to retain and use multiple database sources in parallel, both of the connections will be separate and transactions will be isolated, only drawback of this method is it will occupy more memory to retain multiple data sources.

2 Ways To Create Multiple Google Spanner Data Sources in SpringBoot App

Method # 1 — Using a single Spanner Bean

Method # 2 — Using multiple Spanner Templates

Written by Waqar Mansoor