Working with multiple CDS Data Stores in CAP NG Java

Motivation

Starting from Cds4j v.1.24.0 it is possible to create CdsDatastoreConnector via public API. This blog post gives some ideas and explains how to achieve this in Spring Boot Application. It also demonstrates creation of separate data store connectors operating on 2 SQL data sources.

Goal

We will create an application, capable of querying 2 separate SQL data sources using Java CDS Query Language (part of Cds4j) and use the transaction management system provided by Spring Framework. At the end I will provide the link to the Git repository with a complete application.

Configure CdsDataStoreConnector

To create an instance of com.sap.cds.CdsDataStoreConnector we need first to:

• Configure and register primary javax.sql.Datasource. For the sake of simplicity we will use the external configuration properties (primary.datasource.*).

  @Primary
  @Bean
  @ConfigurationProperties(prefix = "primary.datasource")
  public DataSource ds() {
      return DataSourceBuilder.create().build();
  }

• Register primary PlatformTransactionManager bean using Spring DataSourceTransactionManager which supports NESTED propagation out-of-the-box. This allows to execute queries within a nested transaction if a current transaction exists.

  @Primary
  @Bean
  public PlatformTransactionManager transactionManager(DataSource ds) {
      return new DataSourceTransactionManager(ds);
  }

• Register primary CdsDataStoreConnector bean, using a wrapped (managed) connection of a javax.sql.Datasource, thus allowing to avoid physical close of the transactional connecton.

  @Primary
  @Bean
  public CdsDataStoreConnector cdsDataStoreConnector(DataSource ds, CdsTransactionManager transactionManager)
              throws IOException {
      final Supplier managedConnection = () -> wrapConnection(ds, DataSourceUtils.getConnection(ds));
      return CdsDataStoreConnector.createJdbcConnector(getCdsModel(), transactionManager).connection(managedConnection).build();
  }
  

• Add the configuration for com.sap.cds.transaction.TransactionManager, used by Cds4j to check, whether there exists an active transaction or the whole transaction should be rolled back.

  @Configuration
  public class CdsTransactionManager implements TransactionManager {
  
      @Override
      public boolean isActive() {
          return TransactionSynchronizationManager.isActualTransactionActive();
      }
  
      @Override
      public void setRollbackOnly() {
          TransactionInterceptor.currentTransactionStatus().setRollbackOnly();
      }
  }
  

This is all you need to do to create an instance of CdsDataStoreConnector.
The same configuration applies to the secondary data source, except that instead of @Primary the named beans are to be registered and referenced by name in the application.

Business layer

Now, that we are familiar with the configuration, let’s consider the domain model and the business services.

CDS Model

For the demo purposes we will use 2 data sources – primary and secondary. The models are as simple as that:

Primary Data Source

entity Book {
  key id : Integer;
  title : String;
}

Secondary Data Source

entity Author {
  key id : Integer;
  name : String;
}

Business Services

The services are simple Spring Components implementing CRUD operations using CDS QL for Java.

Primary data source

@Component
public class BookService {

    private final CdsDataStore dataStore;
    ...

    public BookService(CdsDataStoreConnector primaryConnector) {
        this.dataStore = primaryConnector.connect();
        ...
    }

    @Transactional
    public void saveBooks(List<Map> books) {
        CqnInsert insert = Insert.into("Book").entries(books);
        dataStore.execute(insert);
    }

    @Transactional
    public Result readAllBooks() {
        CqnSelect select = Select.from("Book");
        return dataStore.execute(select);
    }
    ...
}

Secondary data source

@Component
public class AuthorService {

private final CdsDataStore dataStore;

public AuthorService(@Qualifier("secondary") CdsDataStoreConnector secondaryConnector) {
    dataStore = secondaryConnector.connect();
}

@Transactional(transactionManager = "secondaryTx")
public void saveAuthors(List<Map<String, Object>> authors) {
    CqnInsert insert = Insert.into("Author").entries(authors);
    dataStore.execute(insert);
}
...
}

NOTE the usage of @Transactional annotation, which defines the transactional boundaries.

also

NOTE the usage of @Qualifier("secondary") and @Transactional(transactionManager = "secondaryTx") for the secondary data source. This is the way to distinguish between the data sources currently operated on.

Putting all together

Finally we are ready to consume the services in an application. For that we will create an integration test which uses both services to write and read the data (for both data sources).

@RunWith(SpringRunner.class)
@SpringBootTest
public class IntegrationTest {
    @Autowired
    private BookService bookService;

    @Autowired
    private AuthorService authorService;
    ...

    @Test
    public void testWriteReadDataFrom2DataSources() {
        bookService.saveBooks(booksData);
        authorService.saveAuthors(authorsData);

        Result allBooks = bookService.readAllBooks();
        Result allAuthors = authorService.readAllAuthors();

        assertThat(allBooks.toJson()).isEqualTo(expectedBooksJson);
        assertThat(allAuthors.toJson()).isEqualTo(expectedAuthorsJson);
    }
    ...
}

Tip: running the sample application will generate a bunch of logs. This is done on purpose to demonstrate the transaction boundaries and the generated SQL. However the log level can be reduced and re-configured via corresponding properties in application.properties.

logging.level.ROOT=INFO
logging.level.org.springframework.jdbc=DEBUG
logging.level.com.sap.cds.impl=DEBUG

Conclusion

In this article we have learned how to configure and create an instance of Cds4j data store connector in a Spring Boot application to query an SQL data source. This can be achieved by a pretty simple Spring configuration. In addition to that we have had a look at how several data store connectors can be configured and used in the same project to communicate with different SQL databases.
The link to the complete application and the integration test can be found in GitHub Repo

Please, feel free to provide the feedback. We are always happy and opened to your questions in the SAP CAP community! Join and share your opinion.