Examples with OracleSqlTemplate com.alibaba.otter.node.etl.common.db.dialect.oracle.OracleSqlTemplate used on opensource projects

Example 1 with OracleSqlTemplate

use of com.alibaba.otter.node.etl.common.db.dialect.oracle.OracleSqlTemplate in project otter by alibaba.

the class SqlTemplateTest method test_oracle.

@Test
public void test_oracle() {
    SqlTemplate sqlTemplate = new OracleSqlTemplate();
    // 执行insert
    String sql1 = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
    String sql2 = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
    want.bool(sql1 == sql2);
    // 执行update
    sql1 = sqlTemplate.getUpdateSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
    sql2 = sqlTemplate.getUpdateSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
    want.bool(sql1 == sql2);
    // 执行deleate
    sql1 = sqlTemplate.getDeleteSql(SCHEMA_NAME, TABLE_NAME, pkColumns);
    sql2 = sqlTemplate.getDeleteSql(SCHEMA_NAME, TABLE_NAME, pkColumns);
    want.bool(sql1 == sql2);
    // 执行merge
    sql1 = sqlTemplate.getMergeSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
    sql2 = sqlTemplate.getMergeSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
    want.bool(sql1 == sql2);
}

Example 2 with OracleSqlTemplate

use of com.alibaba.otter.node.etl.common.db.dialect.oracle.OracleSqlTemplate in project otter by alibaba.

the class SqlBuilderLoadInterceptor method before.

public boolean before(DbLoadContext context, EventData currentData) {
    // 初步构建sql
    DbDialect dbDialect = dbDialectFactory.getDbDialect(context.getIdentity().getPipelineId(), (DbMediaSource) context.getDataMediaSource());
    SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    EventType type = currentData.getEventType();
    String sql = null;
    String schemaName = (currentData.isWithoutSchema() ? null : currentData.getSchemaName());
    // 注意insert/update语句对应的字段数序都是将主键排在后面
    if (type.isInsert()) {
        if (CollectionUtils.isEmpty(currentData.getColumns()) && sqlTemplate instanceof OracleSqlTemplate) {
            // 如果表为全主键，直接进行insert
            // sql
            sql = sqlTemplate.getInsertSql(schemaName, currentData.getTableName(), buildColumnNames(currentData.getKeys()), buildColumnNames(currentData.getColumns()));
        } else {
            sql = sqlTemplate.getMergeSql(schemaName, currentData.getTableName(), buildColumnNames(currentData.getKeys()), buildColumnNames(currentData.getColumns()), new String[] {}, !dbDialect.isDRDS());
        }
    } else if (type.isUpdate()) {
        // String[] keyColumns = buildColumnNames(currentData.getKeys());
        // String[] otherColumns =
        // buildColumnNames(currentData.getUpdatedColumns());
        // boolean existOldKeys = false;
        // for (String key : keyColumns) {
        // // 找一下otherColumns是否有主键，存在就代表有主键变更
        // if (ArrayUtils.contains(otherColumns, key)) {
        // existOldKeys = true;
        // break;
        // }
        // }
        boolean existOldKeys = !CollectionUtils.isEmpty(currentData.getOldKeys());
        boolean rowMode = context.getPipeline().getParameters().getSyncMode().isRow();
        String[] keyColumns = null;
        String[] otherColumns = null;
        if (existOldKeys) {
            // 需要考虑主键变更的场景
            // 构造sql如下：update table xxx set pk = newPK where pk = oldPk
            keyColumns = buildColumnNames(currentData.getOldKeys());
            otherColumns = buildColumnNames(currentData.getUpdatedColumns(), currentData.getKeys());
        } else {
            keyColumns = buildColumnNames(currentData.getKeys());
            otherColumns = buildColumnNames(currentData.getUpdatedColumns());
        }
        if (rowMode && !existOldKeys) {
            // 如果是行记录,并且不存在主键变更，考虑merge sql
            sql = sqlTemplate.getMergeSql(schemaName, currentData.getTableName(), keyColumns, otherColumns, new String[] {}, !dbDialect.isDRDS());
        } else {
            // 否则进行update sql
            sql = sqlTemplate.getUpdateSql(schemaName, currentData.getTableName(), keyColumns, otherColumns);
        }
    } else if (type.isDelete()) {
        sql = sqlTemplate.getDeleteSql(schemaName, currentData.getTableName(), buildColumnNames(currentData.getKeys()));
    }
    // 处理下hint sql
    if (currentData.getHint() != null) {
        currentData.setSql(currentData.getHint() + sql);
    } else {
        currentData.setSql(sql);
    }
    return false;
}