List of usage examples for org.apache.commons.dbutils.handlers ColumnListHandler ColumnListHandler
public ColumnListHandler()
From source file:name.marcelomorales.siqisiqi.bonecp.DataSourceProviderTest.java
@Test public void testStatistics() throws Exception { Config config = ConfigFactory.parseString("bonecp.url=\"jdbc:derby:memory:dbstats;create=true\"") .withFallback(ConfigFactory.load()); DataSourceProvider dsp = new DataSourceProvider(config); assertNull(dsp.getStatistics());/*from w w w . jav a 2 s.c om*/ DataSource dataSource = dsp.get(); Connection connection = dataSource.getConnection(); connection.createStatement().execute("CREATE TABLE TABLETEST1 (ACOLUMN VARCHAR(10))"); connection.commit(); QueryRunner queryRunner = new QueryRunner(); queryRunner.update(connection, "INSERT INTO TABLETEST1 VALUES ('AAA')"); queryRunner.update(connection, "INSERT INTO TABLETEST1 VALUES (?)", "BBB"); queryRunner.update(connection, "INSERT INTO TABLETEST1 VALUES (?)", "CCC"); connection.commit(); List<String> values = queryRunner.query(connection, "SELECT * FROM TABLETEST1 ORDER BY ACOLUMN ASC", new ColumnListHandler<String>()); connection.commit(); connection.close(); assertEquals("AAA", values.get(0)); assertEquals("BBB", values.get(1)); assertEquals("CCC", values.get(2)); Statistics statistics = dsp.getStatistics(); assertTrue(statistics.getCacheHits() > 0); assertTrue(statistics.getCacheMiss() > 0); assertEquals(1, statistics.getConnectionsRequested()); assertEquals(4, statistics.getStatementsPrepared()); dsp.close(); }
From source file:com.gs.obevo.db.impl.platforms.db2.Db2DeployerMainIT.java
@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.cleanEnvironment().setupEnvInfra() .deploy(new MainDeployerArgs().reason("try1").deployExecutionAttributes( Sets.immutable.with(new DeployExecutionAttributeImpl("A", "aval"), new DeployExecutionAttributeImpl("B", "bval")))); }/*from w w w . java 2 s . co m*/ 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 = "DEPLOY_TRACKER"; MutableList<DeployExecution> executions = dbDeployerAppContext.getDeployExecutionDao() .getDeployExecutions(schema1).toSortedListBy(DeployExecution.TO_ID); 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("DEPLOY_TRACKER").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); } }
From source file:com.gs.obevo.db.impl.platforms.db2.Db2ReOrgStatementExecutorIT.java
private void performReorgExecution(final boolean autoReorgEnabled, final int errorCode) { this.setupExecutor(autoReorgEnabled); this.executor.executeWithinContext(physicalSchema, new Procedure<Connection>() { @Override/*from ww w . j a v a 2 s. c om*/ 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 { // this next statement will fire a reorg switch (errorCode) { case 668: expectedColumns = Sets.mutable.with("A", "E"); executorJdbc.update(conn, "insert into a (a) values (5)"); break; case 20054: expectedColumns = Sets.mutable.with("A"); 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 = '" + schemaName + "' AND tabname = 'A'"; List<String> columnsInTableA = db2JdbcTemplate.query(conn, columnListSql, new ColumnListHandler<String>()); assertEquals(expectedColumns, Sets.mutable.withAll(columnsInTableA)); } }); }
From source file:com.gs.obevo.db.impl.platforms.db2.Db2ReOrgStatementExecutorIT.java
private void createTable(Connection conn, String tableName, boolean requireReorg) { try {/*from ww w . j ava2 s . c o m*/ 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 = '" + schemaName + "' AND tabname = '" + tableName + "'"; List<String> columnsInTableA = this.db2JdbcTemplate.query(conn, columnListSql, new ColumnListHandler<String>()); assertEquals(expectedColumns, FastList.newList(columnsInTableA)); }
From source file:io.stallion.dataAccess.db.DB.java
/** * Query the database with arbitrary SQL and return a scalar object (a string, number, boolean, etc). * * @param sql//ww w.ja va2 s.co m * @param params * @param <T> * @return */ public <T> List<T> queryColumn(String sql, Object... params) { QueryRunner runner = new QueryRunner(dataSource); try { return runner.query(sql, new ColumnListHandler<T>(), params); } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:net.sourceforge.seqware.pipeline.plugins.checkdb.plugins.AttributePlugin.java
@Override public void check(SelectQueryRunner qRunner, SortedMap<Level, Set<String>> result) throws SQLException { try {/*from www. j a v a2 s . c o m*/ String query = IOUtils .toString(AttributePlugin.class.getResourceAsStream("duplicate_attribute_keys.sql")); List<Integer> executeQuery = qRunner.executeQuery(query, new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "Entities with duplicate attribute keys in non-sample tables: ", executeQuery); query = IOUtils .toString(AttributePlugin.class.getResourceAsStream("duplicate_sample_attribute_keys.sql")); executeQuery = qRunner.executeQuery(query, new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "Samples with duplicate attribute keys: ", executeQuery); } catch (IOException ex) { throw new RuntimeException(ex); } }
From source file:net.sourceforge.seqware.pipeline.plugins.checkdb.plugins.OrphanCheckerPlugin.java
@Override public void check(SelectQueryRunner qRunner, SortedMap<Level, Set<String>> result) throws SQLException { // orphans when nothing references a particular entity List<Integer> executeQuery = qRunner.executeQuery( "SELECT sw_accession FROM experiment WHERE experiment_id NOT IN (SELECT experiment_id FROM sample);", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.TRIVIAL, "Unreferenced Experiments: ", executeQuery); // Tony mentioned that many samples will not be referenced until a sequencer_run is added // executeQuery = qRunner.executeQuery("SELECT sw_accession FROM sample WHERE sample_id NOT IN (select sample_id from ius UNION select sample_id from lane);", new ColumnListHandler<Integer>()); // CheckDB.processOutput(result, Level.TRIVIAL, "Unreferenced samples: " , executeQuery); executeQuery = qRunner.executeQuery( "SELECT sw_accession FROM ius WHERE ius_id NOT IN (select ius_id from processing_ius);", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.TRIVIAL, "Unreferenced IUSes: ", executeQuery); executeQuery = qRunner.executeQuery( "SELECT sw_accession FROM file WHERE file_id NOT IN (select file_id from processing_files);", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.TRIVIAL, "Unreferenced Files: ", executeQuery); executeQuery = qRunner.executeQuery( "SELECT sw_accession FROM workflow WHERE workflow_id NOT IN (select workflow_id from workflow_run);", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.TRIVIAL, "Unreferenced Workflows: ", executeQuery); // orphans when what should really be a not-null foreign key is null executeQuery = qRunner.executeQuery("SELECT sw_accession FROM sample WHERE experiment_id IS NULL;", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "Samples not attached to experiments: ", executeQuery); executeQuery = qRunner.executeQuery("SELECT sw_accession FROM lane WHERE sequencer_run_id IS NULL;", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "Lanes not attached to sequencer runs: ", executeQuery); // processing, which is just weird executeQuery = qRunner.executeQuery( "SELECT sw_accession FROM processing WHERE workflow_run_id IS NULL AND ancestor_workflow_run_id IS NULL;", new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "Processings attached to no workflow runs: ", executeQuery); }
From source file:net.sourceforge.seqware.pipeline.plugins.checkdb.plugins.SampleHierarchyPlugin.java
@Override public void check(SelectQueryRunner qRunner, SortedMap<Level, Set<String>> result) throws SQLException { String query = "WITH sample_parent_count AS (\n" + " select sample_id, count(parent_id) AS parent_count from sample_hierarchy GROUP BY sample_id\n" + ")\n" + "SELECT * from sample s \n" + "JOIN sample_parent_count spc ON s.sample_id=spc.sample_id \n" + "WHERE parent_count > 1;"; List<Integer> executeQuery = qRunner.executeQuery(query, new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "Samples with more than one parent: ", executeQuery); }
From source file:net.sourceforge.seqware.pipeline.plugins.checkdb.plugins.WorkflowRunConventionsPlugin.java
@Override public void check(SelectQueryRunner qRunner, SortedMap<Level, Set<String>> result) throws SQLException { try {/*from ww w .j a va2 s .c o m*/ /** * May not be true for downsteam workflow runs * List<Integer> executeQuery = qRunner.executeQuery("select sw_accession from workflow_run WHERE workflow_run_id NOT IN (select workflow_run_id FROM ius_workflow_runs);", new ColumnListHandler<Integer>()); * CheckDB.processOutput(result, Level.TRIVIAL, "Workflow runs not connected to an IUS via ius_workflow_runs: " , executeQuery); **/ // workflow runs not connected to a study String query = IOUtils.toString( AttributePlugin.class.getResourceAsStream("workflow_runs_not_connected_to_study.sql")); List<Object[]> workflow_run_study_pairs = qRunner.executeQuery(query, new ArrayListHandler()); List<Integer> unreachableByStudy = new ArrayList<>(); // number studies -> workflow runs SortedMap<Integer, SortedSet<Integer>> reachableByMultipleStudies = new TreeMap<>(); for (Object[] pair : workflow_run_study_pairs) { int studyCount = Integer.valueOf(pair[1].toString()); if (pair[0] == null) { continue; } int sw_accession = Integer.valueOf(pair[0].toString()); if (studyCount == 0) { unreachableByStudy.add(sw_accession); } else if (studyCount > 1) { if (!reachableByMultipleStudies.containsKey(studyCount)) { reachableByMultipleStudies.put(studyCount, new TreeSet<Integer>()); } reachableByMultipleStudies.get(studyCount).add(sw_accession); } } CheckDB.processOutput(result, Level.SEVERE, "'Completed' Workflow runs not reachable by studies: ", unreachableByStudy); // workflow runs connected to more than one study if (reachableByMultipleStudies.size() > 0) { for (Entry<Integer, SortedSet<Integer>> e : reachableByMultipleStudies.entrySet()) { CheckDB.processOutput(result, Level.WARNING, "'Completed' Workflow runs reachable by " + e.getKey() + " studies: ", new ArrayList<>(e.getValue())); } } query = IOUtils.toString( AttributePlugin.class.getResourceAsStream("workflow_runs_not_connected_in_hierarchy.sql")); List<Integer> executeQuery = qRunner.executeQuery(query, new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.SEVERE, "'Completed' Workflow runs reachable by ius_workflow_runs but not via the processing_hierarchy: ", executeQuery); query = IOUtils.toString(AttributePlugin.class.getResourceAsStream("new_input_files_versus_old.sql")); executeQuery = qRunner.executeQuery(query, new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.TRIVIAL, "Workflow runs with input files via workflow_run_input_files but not via the processing hierarchy: ", executeQuery); query = IOUtils.toString(AttributePlugin.class.getResourceAsStream("old_input_files_versus_new.sql")); executeQuery = qRunner.executeQuery(query, new ColumnListHandler<Integer>()); CheckDB.processOutput(result, Level.TRIVIAL, "Workflow runs with input files via the processing hierarchy but not via workflow_run_input_files: ", executeQuery); } catch (IOException ex) { throw new RuntimeException(ex); } }
From source file:org.openmrs.contrib.databaseexporter.ExportContext.java
public Set<Integer> executeIdQuery(String sql) { return new HashSet<Integer>(executeQuery(sql, new ColumnListHandler<Integer>())); }