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

Example 6 with SqlTemplate

use of com.alibaba.otter.node.etl.common.db.dialect.SqlTemplate 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 7 with SqlTemplate

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

Example 8 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");
        }
    });
}