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

Example 16 with DbDialect

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

the class RowDataTransformer method translateColumn.

private EventColumn translateColumn(EventData data, EventColumn scolumn, TableInfoHolder tableHolder, DataMediaPair dataMediaPair, Multimap<String, String> translateColumnNames) {
    EventType type = data.getEventType();
    EventColumn tcolumn = new EventColumn();
    tcolumn.setNull(scolumn.getColumnValue() == null);
    // 左右两边的主键值必须保持一样，可以不为物理主键
    tcolumn.setKey(scolumn.isKey());
    tcolumn.setIndex(scolumn.getIndex());
    tcolumn.setUpdate(scolumn.isUpdate());
    String columnName = translateColumnName(scolumn.getColumnName(), dataMediaPair, translateColumnNames);
    if (StringUtils.isBlank(columnName)) {
        throw new TransformException("can't translate column name:" + scolumn.getColumnName() + "in pair:" + dataMediaPair.toString());
    }
    // 特殊处理
    // columnName = StringUtils.remove(columnName, "`"); //
    // 处理下特殊字符，eromanga给了错误的字段名
    tcolumn.setColumnName(columnName);
    // 不反查，直接使用源库的类型
    tcolumn.setColumnType(scolumn.getColumnType());
    if (tableHolder != null) {
        // modify by ljh at 2013-01-23
        // 双向同步新增字段，在一边加了字段后，虽然新的字段没有产生业务变化，但会因为某些原因导致传递了新的字段到T模块
        // 此时在目标库并不存在这个字段，导致一直挂起。ps. mysql新增字段时间不是一般的长
        // 所以，做了一个容错处理，针对目标库不存在的字段，如果变更记录在源库不存在变更，并且是null值的，允许丢弃该字段(其实最好还是要判断源库的column的defaultValue和当前值是否一致)
        boolean canColumnsNotExist = tableHolder.isEnableCompatibleMissColumn();
        if (type == EventType.UPDATE) {
            // 非变更字段，且当前值为null
            canColumnsNotExist &= !scolumn.isUpdate() && scolumn.isNull();
        } else if (type == EventType.INSERT) {
            // 当前值为null
            canColumnsNotExist &= scolumn.isNull();
        } else if (type == EventType.DELETE) {
            // 主键不允许不存在
            canColumnsNotExist &= !scolumn.isKey();
        }
        Column matchDbColumn = getMatchColumn(tableHolder.getTable().getColumns(), tcolumn.getColumnName());
        // 匹配字段为空，可能源库发生过DDL操作，目标库重新载入一下meta信息
        if (matchDbColumn == null) {
            // 尝试reload一下table meta
            // 获取目标库的表信息
            DbMediaSource dbMediaSource = (DbMediaSource) dataMediaPair.getTarget().getSource();
            DbDialect dbDialect = dbDialectFactory.getDbDialect(dataMediaPair.getPipelineId(), dbMediaSource);
            String schemaName = tableHolder.getTable().getSchema();
            if (StringUtils.isEmpty(schemaName)) {
                schemaName = tableHolder.getTable().getCatalog();
            }
            // 强制反查一次，并放入cache
            Table table = dbDialect.findTable(schemaName, tableHolder.getTable().getName(), false);
            tableHolder.setTable(table);
            matchDbColumn = getMatchColumn(tableHolder.getTable().getColumns(), tcolumn.getColumnName());
            if (matchDbColumn == null) {
                if (canColumnsNotExist) {
                    return null;
                } else {
                    throw new TransformException(scolumn.getColumnName() + " is not found in " + table.toString() + " and source : " + dataMediaPair.getTarget().getNamespace() + "." + dataMediaPair.getTarget().getName());
                }
            }
        }
        if (tableHolder.isUseTableTransform()) {
            int sqlType = matchDbColumn.getTypeCode();
            tcolumn.setColumnType(sqlType);
        }
    }
    // if (dataMediaPair.getTarget().getSource().getType().isOracle()) {
    // // 特殊处理下oracle编码
    // String encodeValue = SqlUtils.encoding(scolumn.getColumnValue(),
    // scolumn.getColumnType(),
    // dataMediaPair.getSource().getSource().getEncode(),
    // dataMediaPair.getTarget().getSource().getEncode());
    // tcolumn.setColumnValue(encodeValue);
    // } else {
    // mysql编码转化已经在驱动层面上完成
    tcolumn.setColumnValue(scolumn.getColumnValue());
    // }
    // 删除映射关系，避免下次重复转换
    translateColumnNames.remove(scolumn.getColumnName(), columnName);
    return tcolumn;
}

Example 17 with DbDialect

use of com.alibaba.otter.node.etl.common.db.dialect.DbDialect 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");
        }
    });
}