Recopie MS-SQL vers PostgreSQL

# Datasource MS-SQL
camel.datasource.mssql.url=jdbc:sqlserver://mssql:1433;databaseName=sourcedb;encrypt=false
camel.datasource.mssql.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
camel.datasource.mssql.username=sa
camel.datasource.mssql.password=Str0ngPass!

# Datasource PostgreSQL
camel.datasource.postgres.url=jdbc:postgresql://postgres:5432/targetdb
camel.datasource.postgres.driver-class-name=org.postgresql.Driver
camel.datasource.postgres.username=camel
camel.datasource.postgres.password=camel

# Taille des lots
sync.batch.size=1000

# Route 1 : point d'entrée REST
- route:
    id: sync-trigger
    description: Déclenche la synchronisation sur POST /sync
    from:
      uri: rest:post:sync
    steps:
      - setProperty:
          name: offset
          expression:
            constant: 0
      - setProperty:
          name: totalRows
          expression:
            constant: 0
      - to:
          uri: direct:sync-loop
      - setBody:
          expression:
            simple: "${exchangeProperty.totalRows} lignes synchronisées"

# Route 2 : boucle de pagination
- route:
    id: sync-loop
    description: Lit les lots MS-SQL jusqu'à épuisement
    from:
      uri: direct:sync-loop
    steps:
      - log:
          message: "Lecture lot offset=${exchangeProperty.offset}"

      # Lecture d'un lot
      - setBody:
          expression:
            simple: >
              SELECT id, nom, prix, actif
              FROM dbo.produits
              ORDER BY id
              OFFSET ${exchangeProperty.offset} ROWS
              FETCH NEXT {{sync.batch.size}} ROWS ONLY
      - to:
          uri: jdbc:mssql

      # Arrêt si lot vide
      - choice:
          when:
            - simple: "${body.size()} == 0"
              steps:
                - log:
                    message: "Synchronisation terminée — ${exchangeProperty.totalRows} lignes"
                - stop: {}
          otherwise:
            steps:
              # Traitement du lot
              - to:
                  uri: direct:sync-batch

              # Incrémenter offset et totalRows
              - setProperty:
                  name: offset
                  expression:
                    groovy: >
                      exchange.getProperty('offset', Integer) + {{sync.batch.size}}
              - setProperty:
                  name: totalRows
                  expression:
                    groovy: >
                      exchange.getProperty('totalRows', Integer) + exchange.getProperty('batchSize', Integer)

              # Itération suivante
              - to:
                  uri: direct:sync-loop

# Route 3 : traitement d'un lot
- route:
    id: sync-batch
    description: Transforme et insère un lot dans PostgreSQL
    from:
      uri: direct:sync-batch
    steps:
      - setProperty:
          name: batchSize
          expression:
            simple: "${body.size()}"
      - split:
          expression:
            simple: "${body}"
          steps:
            # Conversion BIT → BOOLEAN
            - setBody:
                expression:
                  groovy: |
                    def row = request.body
                    row['actif'] = row['actif'] == 1
                    return row

            # Upsert PostgreSQL
            - to:
                uri: jdbc:postgres
                body: >
                  INSERT INTO produits (id, nom, prix, actif)
                  VALUES (:#id, :#nom, :#prix, :#actif)
                  ON CONFLICT (id) DO UPDATE
                  SET nom   = EXCLUDED.nom,
                      prix  = EXCLUDED.prix,
                      actif = EXCLUDED.actif

curl -X POST http://localhost:8080/sync

1500000 lignes synchronisées

docker exec -it camel-postgres psql -U camel -d targetdb \
  -c "SELECT COUNT(*) FROM produits;"