Importing SQL Data

To create an instance of the SQLDataSource class, you need to provide an instance of SQLMapping. This class contains a list of SQL SELECT statements for each entity of the data model, and each statement is associated with a list of attributes from JDL entities mapped to the selected SQL columns. The SQLMapping class follows the Java Bean pattern, and it is up to you on how to provide this instance. One possibility is to build an instance of the configuration directly in plain Java, but you can also use a deserialization framework such as Jackson to read it directly from a JSON or YAML file.

As an example, let's consider the case where we want to implement a service in our application backend extension to import data from an SQL server database, reading the SQL mapping from a YAML file.

You will first need to add the following dependencies to your . /extensions/backend-service-extension/build.gradle file:

dependencies {

    // [...] all other dependencies

    // Dependencies to implement SQL data integration
    implementation "com.decisionbrain.gene:data-integration-base:${versions.decisionbrain.dbgene}"
    implementation "com.decisionbrain.gene:data-integration-sql:${versions.decisionbrain.dbgene}"

    // Dependencies to read YAML files with Jackson
    implementation "com.fasterxml.jackson.core:jackson-databind"
    implementation "com.fasterxml.jackson.dataformat:jackson-dataformat-yaml"

    implementation "org.springframework.boot:spring-boot-starter-jdbc"

    // Dependency to get the JDBC driver class
    implementation "com.microsoft.sqlserver:mssql-jdbc:11.2.1.jre17"
    // for postgresql driver use:
    // implementation "org.postgresql:postgresql"
    }

The connection to the database could be directly configured in your backend extension configuration file located at ./extensions/backend-service-extension/src/main/resources/application.yml:

spring:
  datasource:
    url: jdbc:sqlserver://localhost;databaseName=Staging
    username: sa
    password: secret
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

Then we can add our YAML mapping file as a resource at ./extensions/backend-service-extension/src/main/resources/sql-import-mapping.yml:

entities:
  - name: Activity
    statement: 'SELECT ID, START_DATE, END_DATE, PLANT_ID, PLANT_COUNTRY_CODE FROM ACTIVITY'
    fields:
      - id
      - startDate
      - endDate
      - plant.id
      - plant.countryCode
  - name: PLANT
    statement: 'SELECT ID, COUNTRY_CODE FROM PLANT'
    fields:
      - id
      - countryCode

Then we can write a Spring service that will load the SQL mapping and use the SQLDataSource to import data in a scenario:

@Service
class SQLImportService {

    @Value("classpath:sql-import-mapping.yml")
    Resource mappingResource;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    DataIntegrationService dataIntegrationService;

    private ObjectMapper objectMapper = new ObjectMapper(new YAMLFactory());

    void importData(String scenarioId) throws DataIntegrationException, IOException {
        SQLMapping mapping = objectMapper.readValue(mappingResource.getInputStream(), SQLMapping.class);

        dataIntegrationService.writeData(scenarioId, new SQLDataSource(jdbcTemplate, mapping));
    }
}