Examples with DbMapping com.alibaba.otter.canal.client.adapter.rdb.config.MappingConfig.DbMapping used on opensource projects

Example 6 with DbMapping

use of com.alibaba.otter.canal.client.adapter.rdb.config.MappingConfig.DbMapping in project canal by alibaba.

the class RdbSyncService method update.

/**
 * 更新操作
 *
 * @param config 配置项
 * @param dml DML数据
 */
private void update(BatchExecutor batchExecutor, MappingConfig config, SingleDml dml) throws SQLException {
    Map<String, Object> data = dml.getData();
    if (data == null || data.isEmpty()) {
        return;
    }
    Map<String, Object> old = dml.getOld();
    if (old == null || old.isEmpty()) {
        return;
    }
    DbMapping dbMapping = config.getDbMapping();
    Map<String, String> columnsMap = SyncUtil.getColumnsMap(dbMapping, data);
    Map<String, Integer> ctype = getTargetColumnType(batchExecutor.getConn(), config);
    StringBuilder updateSql = new StringBuilder();
    updateSql.append("UPDATE ").append(SyncUtil.getDbTableName(dbMapping)).append(" SET ");
    List<Map<String, ?>> values = new ArrayList<>();
    boolean hasMatched = false;
    for (String srcColumnName : old.keySet()) {
        List<String> targetColumnNames = new ArrayList<>();
        columnsMap.forEach((targetColumn, srcColumn) -> {
            if (srcColumnName.equalsIgnoreCase(srcColumn)) {
                targetColumnNames.add(targetColumn);
            }
        });
        if (!targetColumnNames.isEmpty()) {
            hasMatched = true;
            for (String targetColumnName : targetColumnNames) {
                updateSql.append("`").append(targetColumnName).append("`").append("=?, ");
                Integer type = ctype.get(Util.cleanColumn(targetColumnName).toLowerCase());
                if (type == null) {
                    throw new RuntimeException("Target column: " + targetColumnName + " not matched");
                }
                BatchExecutor.setValue(values, type, data.get(srcColumnName));
            }
        }
    }
    if (!hasMatched) {
        logger.warn("Did not matched any columns to update ");
        return;
    }
    int len = updateSql.length();
    updateSql.delete(len - 2, len).append(" WHERE ");
    // 拼接主键
    appendCondition(dbMapping, updateSql, ctype, values, data, old);
    batchExecutor.execute(updateSql.toString(), values);
    if (logger.isTraceEnabled()) {
        logger.trace("Update target table, sql: {}", updateSql);
    }
}

Example 7 with DbMapping

use of com.alibaba.otter.canal.client.adapter.rdb.config.MappingConfig.DbMapping in project canal by alibaba.

the class RdbEtlService method executeSqlImport.

/**
 * 执行导入
 */
protected boolean executeSqlImport(DataSource srcDS, String sql, List<Object> values, AdapterConfig.AdapterMapping mapping, AtomicLong impCount, List<String> errMsg) {
    try {
        DbMapping dbMapping = (DbMapping) mapping;
        Map<String, String> columnsMap = new LinkedHashMap<>();
        Map<String, Integer> columnType = new LinkedHashMap<>();
        Util.sqlRS(targetDS, "SELECT * FROM " + SyncUtil.getDbTableName(dbMapping) + " LIMIT 1 ", rs -> {
            try {
                ResultSetMetaData rsd = rs.getMetaData();
                int columnCount = rsd.getColumnCount();
                List<String> columns = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    columnType.put(rsd.getColumnName(i).toLowerCase(), rsd.getColumnType(i));
                    columns.add(rsd.getColumnName(i));
                }
                columnsMap.putAll(SyncUtil.getColumnsMap(dbMapping, columns));
                return true;
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                return false;
            }
        });
        Util.sqlRS(srcDS, sql, values, rs -> {
            int idx = 1;
            try {
                boolean completed = false;
                StringBuilder insertSql = new StringBuilder();
                insertSql.append("INSERT INTO ").append(SyncUtil.getDbTableName(dbMapping)).append(" (");
                columnsMap.forEach((targetColumnName, srcColumnName) -> insertSql.append(targetColumnName).append(","));
                int len = insertSql.length();
                insertSql.delete(len - 1, len).append(") VALUES (");
                int mapLen = columnsMap.size();
                for (int i = 0; i < mapLen; i++) {
                    insertSql.append("?,");
                }
                len = insertSql.length();
                insertSql.delete(len - 1, len).append(")");
                try (Connection connTarget = targetDS.getConnection();
                    PreparedStatement pstmt = connTarget.prepareStatement(insertSql.toString())) {
                    connTarget.setAutoCommit(false);
                    while (rs.next()) {
                        completed = false;
                        pstmt.clearParameters();
                        // 删除数据
                        Map<String, Object> pkVal = new LinkedHashMap<>();
                        StringBuilder deleteSql = new StringBuilder("DELETE FROM " + SyncUtil.getDbTableName(dbMapping) + " WHERE ");
                        appendCondition(dbMapping, deleteSql, pkVal, rs);
                        try (PreparedStatement pstmt2 = connTarget.prepareStatement(deleteSql.toString())) {
                            int k = 1;
                            for (Object val : pkVal.values()) {
                                pstmt2.setObject(k++, val);
                            }
                            pstmt2.execute();
                        }
                        int i = 1;
                        for (Map.Entry<String, String> entry : columnsMap.entrySet()) {
                            String targetClolumnName = entry.getKey();
                            String srcColumnName = entry.getValue();
                            if (srcColumnName == null) {
                                srcColumnName = targetClolumnName;
                            }
                            Integer type = columnType.get(targetClolumnName.toLowerCase());
                            Object value = rs.getObject(srcColumnName);
                            if (value != null) {
                                SyncUtil.setPStmt(type, pstmt, value, i);
                            } else {
                                pstmt.setNull(i, type);
                            }
                            i++;
                        }
                        pstmt.execute();
                        if (logger.isTraceEnabled()) {
                            logger.trace("Insert into target table, sql: {}", insertSql);
                        }
                        if (idx % dbMapping.getCommitBatch() == 0) {
                            connTarget.commit();
                            completed = true;
                        }
                        idx++;
                        impCount.incrementAndGet();
                        if (logger.isDebugEnabled()) {
                            logger.debug("successful import count:" + impCount.get());
                        }
                    }
                    if (!completed) {
                        connTarget.commit();
                    }
                }
            } catch (Exception e) {
                logger.error(dbMapping.getTable() + " etl failed! ==>" + e.getMessage(), e);
                errMsg.add(dbMapping.getTable() + " etl failed! ==>" + e.getMessage());
            }
            return idx;
        });
        return true;
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return false;
    }
}