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

Example 1 with SqlTemplate

use of com.alibaba.otter.node.etl.common.db.dialect.SqlTemplate 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());
    /**
     * 针对DRDS数据库
     */
    String shardColumns = null;
    if (dbDialect.isDRDS()) {
        // 获取拆分键
        shardColumns = dbDialect.getShardColumns(schemaName, currentData.getTableName());
    }
    // 注意insert/update语句对应的字段数序都是将主键排在后面
    if (type.isInsert()) {
        if (CollectionUtils.isEmpty(currentData.getColumns()) && (dbDialect.isDRDS() || 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(), shardColumns);
        }
    } 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());
            // 这里需要精确获取变更的主键,因为目标为DRDS时主键会包含拆分键,正常的原主键变更只更新对应的单主键列即可
            if (dbDialect.isDRDS()) {
                otherColumns = buildColumnNames(currentData.getUpdatedColumns(), currentData.getUpdatedKeys());
            } else {
                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(), shardColumns);
        } else {
            // 否则进行update sql
            sql = sqlTemplate.getUpdateSql(schemaName, currentData.getTableName(), keyColumns, otherColumns, !dbDialect.isDRDS(), shardColumns);
        }
    } 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;
}

Example 2 with SqlTemplate

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

the class DbDialectTest method test_mysql.

@Test(expectedExceptions = RuntimeException.class)
public void test_mysql() {
    DbDataMedia media = getMysqlMedia();
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, media.getSource());
    want.object(dbDialect).clazIs(MysqlDialect.class);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER, Types.VARCHAR };
    final int[] columnTypes = { Types.CHAR, Types.DECIMAL, Types.BLOB, Types.CLOB, Types.DATE, Types.TIMESTAMP, Types.TIMESTAMP };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行update
            sql = sqlTemplate.getUpdateSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, true, null);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行deleate
            sql = sqlTemplate.getDeleteSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(pkColumnTypes), toValues(pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行merge
            sql = sqlTemplate.getMergeSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true, null);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            throw new RuntimeException("rollback");
        }
    });
}

Example 3 with SqlTemplate

use of com.alibaba.otter.node.etl.common.db.dialect.SqlTemplate 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, true, null);
    sql2 = sqlTemplate.getUpdateSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns, true, null);
    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, null);
    sql2 = sqlTemplate.getMergeSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true, null);
    want.bool(sql1 == sql2);
}

Example 4 with SqlTemplate

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

the class SqlTemplateTest method test_mysql.

@Test
public void test_mysql() {
    SqlTemplate sqlTemplate = new MysqlSqlTemplate();
    // 执行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, true, null);
    sql2 = sqlTemplate.getUpdateSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns, true, null);
    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, null);
    sql2 = sqlTemplate.getMergeSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true, null);
    want.bool(sql1 == sql2);
}

Example 5 with SqlTemplate

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

the class BitTableIntegration method test_mysql.

@Test
public void test_mysql() throws UnsupportedEncodingException {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(10L);
    dbMediaSource.setDriver("com.mysql.jdbc.Driver");
    dbMediaSource.setUsername("xxxxx");
    dbMediaSource.setPassword("xxxxx");
    dbMediaSource.setUrl("jdbc:mysql://127.0.0.1:3306");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.MYSQL);
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dbMediaSource);
    want.object(dbDialect).clazIs(MysqlDialect.class);
    Table table = dbDialect.findTable(SCHEMA_NAME, TABLE_NAME);
    System.out.println(table);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER };
    final int[] columnTypes = { Types.BIT, Types.BIT };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            return null;
        // throw new RuntimeException("rollback");
        }
    });
}