Exporting SQL Data

The SQL data integration library also provides the SQLDataSourceConsumer class. It is a configurable implementation of a datasource consumer that can read any datasource and save its data to an SQL database. This class also takes as a parameter an instance of an SQLMapping class, but this time the SQL statements are expected to be batch UPDATE or INSERT statements, which placeholders are associated to the lists of JDL entity attributes.

Let's consider again an example of a service in our application backend extension that exports data from a scenario to an SQL database, with an SQL mapping deserialized from a YAML file.

We will use the same dependencies as in the previous section for our gradle.build file, the same database configuration, and will add a new SQL mapping file at ./extensions/backend-service-extension/src/main/resource/sql-export-mapping.yml:

entities:
  - name: Activity
    statement: 'INSERT INTO ACTIVITY (ID, START_DATE, END_DATE, PLANT_ID, PLANT_COUNTRY_CODE) values (?, ?, ?, ?, ?)'
    fields:
      - id
      - startDate
      - endDate
      - plant.id
      - plant.countryCode
  - name: Plant
    statement: 'INSERT INTO PLANT (ID, COUNTRY_CODE) values (?, ?)'
    fields:
      - id
      - countryCode

And write another Spring service to load the configuration from the YAML file and use the data integration service with the SQLDataSourceConsumer to export data to a scenario:

@Service
class SQLExportService {

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

    @Autowired
    DataIntegrationService dataIntegrationService;

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

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

        // 10000 is the default batch size
        dataIntegrationService.readData(
            scenarioId,
            new SQLDataSourceConsumer(jdbcTemplate, mapping, 10000),
            List.of("Activity", "Plant")
        );
    }
}