org.apache.commons.dbutils.handlers.ColumnListHandler Java Examples

private void createTable(Connection conn, String tableName, boolean requireReorg) {
    try {
        executorJdbc.update(conn, "DROP TABLE " + tableName);
    } catch (Exception ignore) {
        // Ignoring the exception, as no clear "DROP TABLE IF EXISTS" is
        // available in DB2
    }

    executorJdbc.update(conn, "create table " + tableName + " (a integer, b integer, c integer, d integer, e integer) ");
    executorJdbc.update(conn, "insert into " + tableName + " (a) values (3)");
    MutableList<String> expectedColumns;
    if (requireReorg) {
        executorJdbc.update(conn, "alter table " + tableName + " drop column b");
        executorJdbc.update(conn, "alter table " + tableName + " drop column c");
        executorJdbc.update(conn, "alter table " + tableName + " drop column d");
        expectedColumns = Lists.mutable.with("A", "E");
    } else {
        expectedColumns = Lists.mutable.with("A", "B", "C", "D", "E");
    }
    // Assert the columns which are available in table A
    String columnListSql = "select colname from syscat.COLUMNS where tabschema = '" + physicalSchema.getPhysicalName() + "' AND tabname = '"
            + tableName + "'";
    List<String> columnsInTableA = this.db2JdbcTemplate.query(conn, columnListSql,
            new ColumnListHandler<String>());
    assertEquals(expectedColumns, FastList.newList(columnsInTableA));
}
 

/**
 * Checks whether the {@code empty.db} script creates an empty database.
 */
@Test
public void testCleanDB() throws Exception {
    try (Connection conn = db.getConnection()) {
        QueryRunner qr = new QueryRunner();

        List<String> results = qr.query(conn, "SELECT * FROM classes;", new ColumnListHandler<>());
        assertEquals(0, results.size());

        final String query2 = String.join("\n",
                "SELECT *",
                "FROM games",
                "WHERE ID > 0"
        );
        results = qr.query(conn, query2, new ColumnListHandler<>());
        assertEquals(0, results.size());

        results = qr.query(conn, "SELECT * FROM mutants;", new ColumnListHandler<>());
        assertEquals(0, results.size());

        results = qr.query(conn, "SELECT * FROM tests;", new ColumnListHandler<>());
        assertEquals(0, results.size());
    }
}
 

@Override
public ImmutableSet<String> getGroupNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return Sets.immutable
            .withAll(jdbc.query(conn, "select ROLENAME from sysibm.SYSROLES", new ColumnListHandler<String>()))
            .newWithAll(jdbc.query(conn, "select GRANTEE from sysibm.SYSDBAUTH", new ColumnListHandler<String>()))
            .collect(StringFunctions.trim());  // db2 sometimes has whitespace in its return results that needs trimming
}
 

@Override
public List<String> getAllProjectValidIds() {
    return process(new Handler<List<String>>() {
        @Override
        public List<String> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection,"select id from project where status='VALID' and permission='PUBLIC' order by createTime desc ",new ColumnListHandler<String>("id"));
        }
    });
}
 

@Override
public void initUserThirdlyBinds(final User user) {
    process(new Handler<Object>() {
        @Override
        public Object handle(Connection connection, QueryRunner qr) throws SQLException {
            List<String> columns = qr.query(connection,"select type from user_third where userId = ?",new ColumnListHandler<String>("type"),user.getId());
            if(columns!=null && columns.size()>0){
                for(String type:columns){
                    user.getBindingMap().put(type,true);
                }
            }
            return null;
        }
    });
}
 

@Override
public <T> List<T> queryColumnList(String sql, Object... params) {
    List<T> list;
    try {
        list = queryRunner.query(sql, new ColumnListHandler<T>(), params);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return list;
}
 

@Override
public <T> List<T> queryColumnList(String sql, Object... params) {
    List<T> list;
    try {
        list = queryRunner.query(sql, new ColumnListHandler<T>(), params);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return list;
}
 

@Override
public ImmutableSet<String> getGroupNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return Sets.immutable
            .withAll(jdbc.query(conn, "select ROLE from DBA_ROLES", new ColumnListHandler<String>()))
            .collect(StringFunctions.trim());
}
 

@Override
public ImmutableSet<String> getGroupNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return Sets.immutable.<String >withAll(jdbc.query(conn, "select ROLE_NAME from INFORMATION_SCHEMA.APPLICABLE_ROLES", new ColumnListHandler<String>()));
}
 

@Override
public ImmutableSet<String> getUserNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return Sets.immutable.<String>withAll(jdbc.query(conn, "select USER_NAME from INFORMATION_SCHEMA.SYSTEM_USERS", new ColumnListHandler<String>()));
}
 

@Override
public ImmutableSet<String> getGroupNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return Sets.immutable
            .withAll(jdbc.query(conn, "select rolname from pg_roles", new ColumnListHandler<String>()))
            .collect(StringFunctions.trim());
}
 

@Override
public ImmutableSet<String> getGroupNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return ListAdapter.adapt(jdbc.query(conn, physicalSchema.getPhysicalName() + "..sp_helpgroup", new ColumnListHandler<String>("Group_name"))).toSet().toImmutable();
}
 

@Override
public ImmutableSet<String> getUserNamesOptional(Connection conn, PhysicalSchema physicalSchema) throws SQLException {
    return ListAdapter.adapt(jdbc.query(conn, physicalSchema.getPhysicalName() + "..sp_helpuser", new ColumnListHandler<String>("Users_name"))).toSet().toImmutable();
}
 

@Test
public void testDeploy() throws Exception {
    int stepsToRun = 3;  // this toggle is here to help w/ local testing

    DbDeployerAppContext dbDeployerAppContext = null;

    if (stepsToRun >= 0) {
        System.out.println("Running step 0");
        dbDeployerAppContext = getAppContext.valueOf(1);
        dbDeployerAppContext
                .setupEnvInfra()
                .cleanEnvironment()
                .deploy(new MainDeployerArgs().reason("try1").deployExecutionAttributes(Sets.immutable.with(
                        new DeployExecutionAttributeImpl("A", "aval"),
                        new DeployExecutionAttributeImpl("B", "bval")
                )))
        ;
    }

    if (stepsToRun >= 1) {
        System.out.println("Running step 1");
        dbDeployerAppContext = getAppContext.valueOf(1);
        dbDeployerAppContext
                .deploy(new MainDeployerArgs().reason("try1a-noop").deployExecutionAttributes(Sets.immutable.with(
                        new DeployExecutionAttributeImpl("A", "aval"),
                        new DeployExecutionAttributeImpl("B", "bval"),
                        new DeployExecutionAttributeImpl("C", "cval")
                )))
        ;
    }

    if (stepsToRun >= 2) {
        System.out.println("Running step 2");
        dbDeployerAppContext = getAppContext.valueOf(2);
        dbDeployerAppContext
                .setupEnvInfra()
                .deploy(new MainDeployerArgs().reason("try2").deployExecutionAttributes(Sets.immutable.with(
                        new DeployExecutionAttributeImpl("C", "cval2"),
                        new DeployExecutionAttributeImpl("E", "eval")
                )))
        ;
    }

    if (stepsToRun >= 3) {
        System.out.println("Running step 3");
        dbDeployerAppContext = getAppContext.valueOf(3);
        dbDeployerAppContext
                .setupEnvInfra()
                .deploy(new MainDeployerArgs().reason("try3").deployExecutionAttributes(Sets.immutable.with(
                        new DeployExecutionAttributeImpl("F", "fval")
                )))
        ;
    }

    String schema1 = "DBDEPLOY01";
    MutableList<DeployExecution> executions = dbDeployerAppContext.getDeployExecutionDao().getDeployExecutions(schema1).toSortedListBy(new Function<DeployExecution, Long>() {
        @Override
        public Long valueOf(DeployExecution deployExecution) {
            return deployExecution.getId();
        }
    });
    assertThat(executions, hasSize(4));
    DeployExecution execution4 = dbDeployerAppContext.getDeployExecutionDao().getLatestDeployExecution(schema1);
    verifyExecution1(executions.get(0));
    verifyExecution1a(executions.get(1));
    verifyExecution2(executions.get(2));
    verifyExecution3(executions.get(3));
    verifyExecution3(execution4);

    JdbcHelper db2JdbcTemplate = new JdbcHelper();

    // Assert the columns which are available in table TEST_TABLE
    String schema = dbDeployerAppContext.getEnvironment().getPhysicalSchema("DBDEPLOY01").getPhysicalName();
    String columnListSql = "select colname from syscat.COLUMNS where tabschema = '" + schema + "' AND tabname = 'TABLE_D'";
    Connection conn = ds.getConnection();
    try {
        List<String> columnsInTestTable = db2JdbcTemplate.query(conn, columnListSql, new ColumnListHandler<String>());
        Assert.assertEquals(Lists.mutable.with("D_ID"), FastList.newList(columnsInTestTable));
    } finally {
        DbUtils.closeQuietly(conn);
    }
}
 

private void performReorgExecution(final boolean autoReorgEnabled, final int errorCode, final boolean testBatchUpdate) {
    this.setupExecutor(autoReorgEnabled);
    this.executor.executeWithinContext(physicalSchema, new Procedure<Connection>() {
        @Override
        public void value(Connection conn) {
            try {
                executorJdbc.update(conn, "DROP TABLE a");
            } catch (Exception ignore) {
                // Ignoring the exception, as no clear "DROP TABLE IF EXISTS" is
                // available in DB2
            }

            executorJdbc.update(conn, "create table a (a integer, b integer, c integer, d integer, e integer) ");
            executorJdbc.update(conn, "insert into a (a) values (3)");
            executorJdbc.update(conn, "alter table a drop column b");
            executorJdbc.update(conn, "alter table a drop column c");
            executorJdbc.update(conn, "alter table a drop column d");

            MutableSet<String> expectedColumns = null;
            try {
                Object[][] batchArgs = new Object[][] { new Object[0] };  // need single row of empty args for the batchUpdate calls below
                // this next statement will fire a reorg
                switch (errorCode) {
                case 668:
                    expectedColumns = Sets.mutable.with("A", "E");
                    if (testBatchUpdate) {
                        executorJdbc.batchUpdate(conn, "insert into a (a) values (5)", batchArgs);
                    } else {
                        executorJdbc.update(conn, "insert into a (a) values (5)");
                    }
                    break;
                case 20054:
                    expectedColumns = Sets.mutable.with("A");
                    if (testBatchUpdate) {
                        executorJdbc.batchUpdate(conn, "alter table a drop column e", batchArgs);
                    } else {
                        executorJdbc.update(conn, "alter table a drop column e");
                    }
                    break;
                default:
                    throw new IllegalArgumentException("Unsupported error code for this test: " + errorCode);
                }

                if (!autoReorgEnabled) {
                    fail("Expected an exception here if we do not have autoReorgEnabled");
                }
            } catch (RuntimeException e) {
                if (autoReorgEnabled) {
                    fail("If reorg is enabled, then we should not have thrown an exception here: " + e.getMessage());
                } else {
                    return;
                }
            }

            // Assert the columns which are available in table A
            String columnListSql = "select colname from syscat.COLUMNS where tabschema = '" + physicalSchema.getPhysicalName() + "' AND tabname = 'A'";
            List<String> columnsInTableA = db2JdbcTemplate.query(conn, columnListSql,
                    new ColumnListHandler<String>());
            assertEquals(expectedColumns, Sets.mutable.withAll(columnsInTableA));
        }
    });
}