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.
