Guide of Spring Data integration with ScalarDB

The usage of Spring Data integration with ScalarDB basically follows Spring Data JDBC - Reference Documentation. This guide describes several important topics to use Spring Data integration with ScalarDB and its limitations.

Configurations

Spring Data integration with ScalarDB is supposed to be used as a part of Spring application. The following properties are needed at least.

spring.datasource.driver-class-name

This needs to be set to fixed value com.scalar.db.sql.jdbc.SqlJdbcDriver.

spring.datasource.driver-class-name=com.scalar.db.sql.jdbc.SqlJdbcDriver

spring.datasource.url

This value follows ScalarDB JDBC connection URL configuration. See also ScalarDB JDBC Guide.

spring.datasource.url=jdbc:scalardb:?scalar.db.contact_points=jdbc:mysql://localhost:3306/my_app_ns&scalar.db.username=root&scalar.db.password=mysql&scalar.db.storage=jdbc&scalar.db.consensus_commit.isolation_level=SERIALIZABLE&scalar.db.consensus_commit.async_commit.enabled=false

scalardb.namespace

This is an optional. But if you use a single ScalarDB namespace in an application, it's recommended to set this property so that you don't need to specify @Table(schema = schema_name) annotation parameter on all Spring Data model classes.

scalardb.namespace=my_app_ns

Annotations

@EnableScalarDbRepositories annotation is needed on the JVM application to use Spring Data integration with ScalarDB as follows.

@SpringBootApplication
@EnableScalarDbRepositories
public class MyApplication {
  //  These repositories are described in the next section in details
  @Autowired GroupRepository groupRepository;
  @Autowired UserRepository userRepository;

  :

Persistent entity model

The users of Spring Data JDBC for ScalarDB needs to write classes for object mapping to ScalarDB tables. How to write those classes are written in Persisting Entities, so this section describes some limitations on the ScalarDB integration.

These are example model classes:

domain/model/User

// This model class corresponds to the following table schema:
//
//   create table my_app_ns.user (id bigint, group_id bigint, name text, primary key (id));
//
//   -- UserRepository can use `name` column as a condition in SELECT statement
//   -- as the column is a ScalarDB secondary index.
//   create index on my_app_ns.user (name);

// Set `schema` parameter in @Table annotation if you don't use `scalardb.namespace` property.
//
// Spring Data automatically decides the target table name based on a model class name.
// You can also specify a table name by setting `value` parameter.
//
// @Table(schema = "my_app_ns", value = "user")
@Table
public class User {
  @Id
  public final Long id;

  public final Long groupId;

  // Spring Data automatically decides the target column name based on an instance variable name.
  // You can also specify a column name by setting `value` parameter in @Column annotation.
  // @Column("name")
  public final String name;

  public User(Long id, Long groupId, String name) {
    this.id = id;
    this.groupId = groupId;
    this.name = name;
  }
}

domain/model/Group

// This model class corresponds to the following table schema:
//
//   create table my_app_ns.group (account_id int, group_type int, balance int, primary key (account_id, group_type));

@Table
public class Group {
  // This column `account_id` is a part of PRIMARY KEY in ScalarDB SQL
  //
  // Spring Data JDBC always requires a single @Id annotation while it doesn't allow multiple @Id annotations.
  // The corresponding ScalarDB SQL table `group` has a primary key consisting of multiple columns.
  // So, Spring Data @Id annotation can't be used in this case, but @Id annotation must be put on any instance variable
  // (@Id annotation can be put on `balance` as well.)
  @Id
  public final Integer accountId;

  // This column `group_type` is also a part of PRIMARY KEY in ScalarDB SQL
  public final Integer groupType;

  public final Integer balance;

  public Group(Integer accountId, Integer groupType, Integer balance) {
    this.accountId = accountId;
    this.groupType = groupType;
    this.balance = balance;
  }
}

domain/repository/UserRepository

@Transactional
@Repository
public interface UserRepository extends
	PagingAndSortingRepository<User, Long>, ScalarDbHelperRepository {

  // `insert()` and `update()` are automatically enabled with `ScalarDbHelperRepository`.

  // Many APIs of `CrudRepository` and `PagingAndSortingRepository` are automatically enabled.
  // https://docs.spring.io/spring-data/commons/docs/3.0.x/api/org/springframework/data/repository/CrudRepository.html
  // https://docs.spring.io/spring-data/commons/docs/3.0.x/api/org/springframework/data/repository/PagingAndSortingRepository.html

  // Also, you can prepare complicated APIs with the combination of the method naming conventions.
  // https://docs.spring.io/spring-data/jdbc/docs/3.0.x/reference/html/#repositories.definition-tuning

  // These APIs use the ScalarDB secondary index
  List<User> findByName(String name);
  List<User> findTop2ByName(String name);
  // Current ScalarDB SQL doesn't support range scan or order using secondary indexes
  // List<User> findByNameBetween(String name);
  // List<User> findByGroupIdOrderByName(long groupId);

  default void reverseName(long id) {
    Optional<User> model = findById(id);
    if (model.isPresent()) {
      User existing = model.get();
      User updated =
          new User(
              existing.id,
              existing.groupId,
              existing.name.reverse());
      update(updated);
    }
  }

  default void deleteAfterSelect(long id) {
    Optional<User> existing = findById(id);
    existing.ifPresent(this::delete);
  }
}

domain/repository/GroupRepository

@Transactional
@Repository
public interface GroupRepository
    extends PagingAndSortingRepository<Group, Long>, ScalarDbHelperRepository {

  // @Id annotation is put only on Group.accountId, but ScalarDB SQL expects the combination of
  // `account_id` and `group_type` columns as the table uses them as a primary key. So `findById()` can't be used.
  Optional<Group> findFirstByAccountIdAndGroupType(int accountId, int groupType);

  List<Group> findByAccountIdAndGroupTypeBetweenOrderByGroupTypeDesc(
      int accountId, int groupTypeFrom, int groupTypeTo);

  List<Group> findTop2ByAccountIdAndGroupTypeBetween(
      int accountId, int groupTypeFrom, int groupTypeTo);

  // `update()` method also depends on @Id annotation as well as `findById()`,
  // so users need to write ScalarDB SQL in @Query annotation.
  @Modifying
  @Query(
      "UPDATE \"my_app_ns\".\"group\" SET \"balance\" = :balance \n"
          + " WHERE \"my_app_ns\".\"group\".\"account_id\" = :accountId \n"
          + " AND \"my_app_ns\".\"group\".\"group_type\" = :groupType \n")
  int updateWithAttributes(
      @Param("accountId") int accountId,
      @Param("groupType") int groupType,
      @Param("balance") int balance);

  default void incrementBalance(int accountId, int groupType, int value) {
    Optional<Group> model = findFirstByAccountIdAndGroupType(accountId, groupType);
    model.ifPresent(
        found ->
            updateWithAttributes(
                found.accountId, found.groupType, found.balance + value));
  }

  default void transfer(
      int accountIdFrom, int groupTypeFrom, int accountIdTo, int groupTypeTo, int value) {
    incrementBalance(accountIdFrom, groupTypeFrom, -value);
    incrementBalance(accountIdTo, groupTypeTo, value);
  }

  // This method name and signature results in issuing an unexpected SELECT statement and
  // results in query failure. It looks a bug of Spring Data...
  //
  // void deleteByAccountIdAndGroupType(int accountId, int groupType);

  @Modifying
  @Query(
      "DELETE FROM \"my_app_ns\".\"group\" \n"
          + " WHERE \"my_app_ns\".\"group\".\"account_id\" = :accountId \n"
          + " AND \"my_app_ns\".\"group\".\"group_type\" = :groupType \n")
  int deleteByAccountIdAndGroupType(
      @Param("accountId") int accountId, @Param("groupType") int groupType);

  default void deleteByAccountIdAndGroupTypeAfterSelect(int accountId, int groupType) {
    Optional<Group> entity = findFirstByAccountIdAndGroupType(accountId, groupType);
    entity.ifPresent(found -> deleteByAccountIdAndGroupType(accountId, groupType));
  }
}

Limitations

Multiple column PRIMARY KEY

As you see in the above example, Spring Data JDBC's @Id annotation doesn't support multiple columns. So, if a table has a primary key consisting of multiple columns, users can't use the following APIs and may need to write Scalar SQL DB query in @Query annotation

• findById()
• existsById()
• update(T entity)
• delete(T entity)
• deleteById(ID id)
• deleteAllById(Iterable<? extends ID> ids)

Advanced features

Multi-storage transaction

ScalarDB supports Multi-storage Transaction, and users can use the feature via this Spring Data integration. The following needs to be configured to use the feature.

spring.datasource.url

Here is a sample datasource URL assuming there are two namespaces "north" and "south" that manage data with MySQL and PostgreSQL respectively.

spring.datasource.url=jdbc:scalardb:?scalar.db.storage=multi-storage&scalar.db.multi_storage.storages=mysql,postgresql&scalar.db.multi_storage.namespace_mapping=north:mysql,south:postgresql&scalar.db.multi_storage.default_storage=postgresql&scalar.db.multi_storage.storages.mysql.storage=jdbc&...

@Table annotation on model classes

• schema parameter: multi-storage mapping key name (scalar.db.multi_storage.namespace_mapping)
• value parameter: actual table name

  @Table(schema = "north", value = "account")
  public class NorthAccount {
    :

Retry with Spring Retry

Spring Data integration with ScalarDB could throw exceptions when concurrent transactions conflict. Users need to take care of those exceptions by retrying the operations. Spring Retry provides some functionalities for retry. Also in Spring Data integration with ScalarDB, Spring Retry would be helpful to make retry handling simpler and easier. This section introduces how to use Spring Retry.

Dependencies

The following dependencies need to be added to your project.

dependencies {
    implementation "org.springframework.boot:spring-boot-starter:${springBootVersion}"
    implementation "org.springframework.boot:spring-boot-starter-aop:${springBootVersion}"
    implementation "org.springframework.retry:spring-retry:${springRetryVersion}"
}

Annotation

@EnableRetry annotation needs to be added in the JVM application.

@SpringBootApplication
@EnableScalarDbRepositories
@EnableRetry
public class MyApp {
  :

@Retryable annotation makes Spring Data repository class or method automatically retry a failed operation. Spring Data integration with ScalarDB throws an exception that contains java.sql.SQLTransactionRollbackException as a cause when a retryable failure occurs, so it's highly recommended to specify this exception in the annotation. Also, backoff and max attempts can be configured in the annotation like this:

  @Transactional
  @Retryable(
      include = SQLTransactionRollbackException.class,
      maxAttempts = 4,
      backoff = @Backoff(delay = 500, maxDelay = 2000, multiplier = 2))
  default void insertWithRetry(Player player) {
    insert(player);
  }

With @Recover annotation, retry-exhausted failure will be automatically recovered by a specified method.

  @Transactional
  @Retryable(include = SQLTransactionRollbackException.class,
          recover = "recoverInsert")
  default void insertWithRetryAndRecover(Player player) {
    insert(player);
  }

  @Transactional
  @Recover
  default void recoverInsert(Throwable throwable, Player player) throws Throwable {
    Optional<Player> existing = findById(player.id);
    if (!existing.isPresent()) {
      throw throwable;
    }
    logger.info(
        "Found an existing record {}. Updating it with a new record {}", existing.get(), player);

    update(player);
  }

Example application

Example application is a JVM application that uses Spring Data integration with ScalarDB. It only serves as a reference and does not necessarily meet production code standards.

How it works

In order to use Spring Data integration with ScalarDB, the following features are implemented in this integration

• Map jdbc:scalardb protocol in JDBC Connection URL to a Spring Data JDBC dialect class for ScalarDB SQL
  • This feature is handled by ScalarDbDialect and ScalarDbDialectProvider
• Prevent users from using some APIs of Spring Data Repository classes (CrudRepository and PagingAndSortingRepository) unsupported in ScalarDB SQL
  • This feature is handled by ScalarDbJdbcAggregateTemplate which is a bit lower layer Spring Data JDBC component used by Repository classes
• Make Spring Data Repository classes implicitly use the custom JdbcAggregateTemplate (ScalarDbJdbcAggregateTemplate)
  • This feature is handled by ScalarDbJdbcRepositoryFactory and ScalarDbJdbcRepositoryFactoryBean
• Add explicit insert() and update() APIs to Spring Data Repository classes instead of bundled save() which depends on autoincrement ID feature in underlying databases while ScalarDB SQL doesn't support it
  • This feature is handled by ScalarDbHelperRepository and ScalarDbRepositoryImpl
• Support default ScalarDB namespace to avoid writing @Table(schema = schema_name) on all Spring Data model classes
  • This feature is handled by ScalarDbJdbcConfiguration
• Enable all the above features in Spring framework manner
  • This configuration is handled by
    • some Java classes in com.scalar.db.sql.springdata
    • @EnableScalarDbRepositories annotation
    • resources/META-INF/spring/org.springframework.boot.autoconfigure.AutoConfiguration.imports
    • resources/META-INF/spring.factories

References

• Spring Data JDBC - Reference Documentation
• ScalarDB JDBC Guide