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

Example 11 with DbDialect

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

the class DbDialectIntegration method test_mysql.

@Test(expectedExceptions = RuntimeException.class)
public void test_mysql() {
    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("test", "ljh_demo");
    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.VARCHAR, Types.INTEGER, Types.DECIMAL, Types.BIGINT };
    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);
            throw new RuntimeException("rollback");
        }
    });
}

Example 12 with DbDialect

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

the class DbDialectTableTest method testMysqlTable.

@Test
public void testMysqlTable() {
    DbDataMedia mysqlMedia = getMysqlMedia();
    DbDialect dbDialect = dbDialectFactory.getDbDialect(1L, mysqlMedia.getSource());
    Table table = dbDialect.findTable(mysqlMedia.getNamespace(), mysqlMedia.getName());
    want.object(table).notNull();
    System.out.println("tableName = " + table.getName());
    Column[] columns = table.getColumns();
    for (Column column : columns) {
        System.out.println("columnName = " + column.getName() + ",columnType = " + column.getTypeCode() + ",isPrimary = " + column.isPrimaryKey() + ",nullable = " + column.isRequired());
    }
}

Example 13 with DbDialect

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

the class DbDialectTest method test_oracle.

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

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(ORACLE_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(ORACLE_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);
            // 执行deleate
            sql = sqlTemplate.getDeleteSql(ORACLE_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(ORACLE_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
            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 14 with DbDialect

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

the class DbPerfIntergration method test_stack.

@Test
public void test_stack() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(1L);
    dbMediaSource.setDriver("com.mysql.jdbc.Driver");
    dbMediaSource.setUsername("otter");
    dbMediaSource.setPassword("otter");
    dbMediaSource.setUrl("jdbc:mysql://127.0.0.1:3306/retl");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.MYSQL);
    DbDataMedia dataMedia = new DbDataMedia();
    dataMedia.setSource(dbMediaSource);
    dataMedia.setId(1L);
    dataMedia.setName("ljhtable1");
    dataMedia.setNamespace("otter");
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dataMedia.getSource());
    want.object(dbDialect).clazIs(MysqlDialect.class);
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    // 插入数据准备
    int minute = 5;
    int nextId = 1;
    final int thread = 10;
    final int batch = 50;
    final String sql = "insert into otter.ljhtable1 values(? , ? , ? , ?)";
    final CountDownLatch latch = new CountDownLatch(thread);
    ExecutorService executor = new ThreadPoolExecutor(thread, thread, 60, TimeUnit.SECONDS, new ArrayBlockingQueue(thread * 2), new NamedThreadFactory("load"), new ThreadPoolExecutor.CallerRunsPolicy());
    for (int sec = 0; sec < minute * 60; sec++) {
        // 执行秒循环
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < thread; i++) {
            final int start = nextId + i * batch;
            executor.submit(new Runnable() {

                public void run() {
                    try {
                        transactionTemplate.execute(new TransactionCallback() {

                            public Object doInTransaction(TransactionStatus status) {
                                JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
                                return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

                                    public void setValues(PreparedStatement ps, int idx) throws SQLException {
                                        int id = start + idx;
                                        StatementCreatorUtils.setParameterValue(ps, 1, Types.INTEGER, null, id);
                                        StatementCreatorUtils.setParameterValue(ps, 2, Types.VARCHAR, null, RandomStringUtils.randomAlphabetic(1000));
                                        // RandomStringUtils.randomAlphabetic()
                                        long time = new Date().getTime();
                                        StatementCreatorUtils.setParameterValue(ps, 3, Types.TIMESTAMP, new Timestamp(time));
                                        StatementCreatorUtils.setParameterValue(ps, 4, Types.TIMESTAMP, new Timestamp(time));
                                    }

                                    public int getBatchSize() {
                                        return batch;
                                    }
                                });
                            }
                        });
                    } finally {
                        latch.countDown();
                    }
                }
            });
        }
        long endTime = System.currentTimeMillis();
        try {
            latch.await(1000 * 60L - (endTime - startTime), TimeUnit.MILLISECONDS);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        if (latch.getCount() != 0) {
            System.out.println("perf is not enough!");
            System.exit(-1);
        }
        endTime = System.currentTimeMillis();
        System.out.println("Time cost : " + (System.currentTimeMillis() - startTime));
        try {
            TimeUnit.MILLISECONDS.sleep(1000L - (endTime - startTime));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        nextId = nextId + thread * batch;
    }
    executor.shutdown();
}

Example 15 with DbDialect

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

the class TimeTableIntegration method test_mysql.

@Test
public void test_mysql() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(10L);
    dbMediaSource.setDriver("oracle.jdbc.OracleDriver");
    dbMediaSource.setUsername("otter1");
    dbMediaSource.setPassword("jonathan");
    dbMediaSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:ointest");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.ORACLE);
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dbMediaSource);
    // want.object(dbDialect).clazIs(MysqlDialect.class);
    Table table = dbDialect.findTable("otter2", "test_time");
    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.TIMESTAMP, Types.TIMESTAMP, Types.DATE, Types.TIME, Types.INTEGER, Types.INTEGER };
    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");
        }
    });
}