Deleting Data from the Database

To delete data from the database, you need to create Credentials, a Data Source, and Camel DSL as specified in the “Fetching from the Database” section. The route content must include an SQL query for data deletion.

Simple Record Deletion

For example, the following clearValues route deletes all records from the simple table of the datasource data source, except those where the id attribute is equal to ‘1’ or ‘2’.

- route:
    id: "clearValues"
    from:
      uri: "timer:start?delay=-1&repeatCount=1"
      steps:
        - setBody:
            constant: "delete from simple where id not in ('1','2')"
        - to: "jdbc:datasource"

Fetching with Subsequent Deletion

An example of a context that takes data from the todb data source, processes it through the RecordsDaoEndpoint daoEndpoint, and clears the simple table from which it took the data:

- beans:
    - name: "daoEndpoint"
      type: ru.citeck.ecos.integrations.domain.cameldsl.service.RecordsDaoEndpoint
      properties:
        sourceId: testDao
        pkProp: id
        columnMap:
          name: content
          state: currentState
          type: type
- route:
    id: "getValues"
    from:
      uri: "timer:start?delay=-1&repeatCount=1"
      steps:
        - setBody:
            constant: "select * from simple"
        - to: "jdbc:todb"
        - split:
            simple: "${body}"
            steps:
              - to: "bean:daoEndpoint"
              - to: "direct:clearValues"
- route:
    id: "clearValues"
    from:
      uri: "direct:clearValues"
      steps:
        - setBody:
            constant: "delete from simple"
        - to: "jdbc:todb"

Note

The value of constant is automatically converted to lowercase. For example, the query "select * from simple order by COMPANY_ID" will raise the error ERROR: column "company_id" does not exist, because the column name is converted to lowercase.