List of usage examples for org.apache.commons.dbutils.handlers MapListHandler MapListHandler
public MapListHandler()
BasicRowProcessor for conversion. From source file:com.gs.obevo.db.impl.core.checksum.SameSchemaDbChecksumDao.java
@Override public ImmutableCollection<ChecksumEntry> getPersistedEntries(final PhysicalSchema physicalSchema) { return sqlExecutor.executeWithinContext(physicalSchema, new ThrowingFunction<Connection, ImmutableCollection<ChecksumEntry>>() { @Override//w w w .jav a 2s . co m public ImmutableCollection<ChecksumEntry> safeValueOf(Connection conn) throws Exception { return ListAdapter .adapt(jdbc.query(conn, "SELECT * FROM " + platform.getSchemaPrefix(physicalSchema) + checksumTableName, new MapListHandler())) .collect(new Function<Map<String, Object>, ChecksumEntry>() { @Override public ChecksumEntry valueOf(Map<String, Object> result) { return ChecksumEntry.createFromPersistence(physicalSchema, (String) result.get(objectTypeColumnName), (String) result.get(objectName1ColumnName), blankToNull((String) result.get(objectName2ColumnName)), (String) result.get(checksumColumnName)); } }).toImmutable(); } }); }
From source file:com.gs.obevo.db.impl.core.jdbc.JdbcHelper.java
public MutableList<Map<String, Object>> queryForList(Connection conn, String sql) { return ListAdapter.adapt(this.query(conn, sql, new MapListHandler())); }
From source file:com.gs.obevo.dbmetadata.impl.dialects.SybaseAseMetadataDialect.java
@Override public ImmutableCollection<ExtraIndexInfo> searchExtraConstraintIndices(DaSchema schema, String tableName, Connection conn) throws SQLException { QueryRunner query = new QueryRunner(); // using queryRunner so that we can reuse the connection // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions String tableClause = tableName == null ? "" : " AND tab.name = '" + tableName + "'"; ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, "select tab.name TABLE_NAME, ind.name INDEX_NAME, status2 & 8 IS_CONSTRAINT, status2 & 512 IS_CLUSTERED " + "from " + schema.getName() + "..sysindexes ind, " + schema.getName() + "..sysobjects tab " + "where ind.id = tab.id " + tableClause, new MapListHandler())).toImmutable(); return maps.collect(new Function<Map<String, Object>, ExtraIndexInfo>() { @Override// w w w . j a v a 2 s . c om public ExtraIndexInfo valueOf(Map<String, Object> map) { return new ExtraIndexInfo((String) map.get("TABLE_NAME"), (String) map.get("INDEX_NAME"), (Integer) map.get("IS_CONSTRAINT") != 0, (Integer) map.get("IS_CLUSTERED") != 0); } }); }
From source file:com.gs.obevo.dbmetadata.impl.dialects.MsSqlMetadataDialect.java
@Override public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException { QueryRunner query = new QueryRunner(); // using queryRunner so that we can reuse the connection // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, "SELECT rul.name as RULE_NAME\n" + "FROM " + schema.getName() + "..sysobjects rul\n" + "WHERE rul.type = 'R'\n" + "and not exists (\n" + "\t-- Ensure that the entry is not attached to a table; otherwise, it is a regular table constraint, and will already be dropped when the table is dropped\n" + "\tselect 1 from " + schema.getName() + "..sysconstraints c\n" + "\twhere c.constid = rul.id\n" + ")\n", new MapListHandler())).toImmutable(); return maps.collect(new Function<Map<String, Object>, DaRule>() { @Override/* w w w .ja v a 2 s . com*/ public DaRule valueOf(Map<String, Object> map) { return new DaRuleImpl((String) map.get("RULE_NAME"), schema); } }); }
From source file:com.gs.obevo.db.impl.core.deploy.CsvStaticDataDeployerTest.java
private void testNormalInsertAndDeleteUseCase(boolean caseSensitiveCsv) { this.jdbc.execute(conn, "CREATE TABLE " + schema + "." + table + " (\n" + "AID INT NOT NULL,\n" + "BID INT NOT NULL,\n" + "STRINGFIELD VARCHAR(30)\tNULL,\n" + "TIMESTAMPFIELD TIMESTAMP\tNULL,\n" + "CID INT NULL,\n" + "UPDATETIMEFIELD TIMESTAMP NOT NULL, \n" + "PRIMARY KEY (AID)\n" + ")\n"); DbEnvironment env = new DbEnvironment(); env.setPlatform(PLATFORM);/* w ww .j av a2 s. c o m*/ env.setNullToken("(null)"); env.setDataDelimiter('^'); Change artifact = mock(Change.class); when(artifact.getPhysicalSchema()).thenReturn(new PhysicalSchema(schema)); when(artifact.getObjectName()).thenReturn(table); when(artifact.getMetadataAttribute(TextMarkupDocumentReader.ATTR_UPDATE_TIME_COLUMN)) .thenReturn("UPDATETIMEFIELD"); String columnHeaders = "aId^bId^stringField^timestampField^cId"; if (!caseSensitiveCsv) { columnHeaders = columnHeaders.toUpperCase(); } System.out.println("First, try an insert"); when(artifact.getConvertedContent()).thenReturn(columnHeaders + "\n" + "1^2^AB,C^2012-01-01 12:12:12^\n" + "2^3^(null)^2013-01-01 11:11:11.65432^9\n" + "3^4^ABC^(null)^9\n" + "4^4^0006^(null)^9\n"); LocalDateTime preDeployTime = new LocalDateTime(); CsvStaticDataDeployer csvStaticDataDeployer = new CsvStaticDataDeployer(env, getSqlExecutor(), this.ds, metadataManager, new H2DbPlatform()); csvStaticDataDeployer.deployArtifact(artifact); List<Map<String, Object>> results = this.jdbc.query(conn, "select * from " + schema + "." + table + " order by AID", new MapListHandler()); assertEquals(4, results.size()); this.verifyRow(results.get(0), 1, 2, "AB,C", new LocalDateTime("2012-01-01T12:12:12"), null, preDeployTime, true); this.verifyRow(results.get(1), 2, 3, null, new LocalDateTime("2013-01-01T11:11:11.65432"), 9, preDeployTime, true); this.verifyRow(results.get(2), 3, 4, "ABC", null, 9, preDeployTime, true); this.verifyRow(results.get(3), 4, 4, "0006", null, 9, preDeployTime, true); System.out.println("Now, trying an update: row 2 is updated, row 3 is deleted, row 5 is added"); when(artifact.getConvertedContent()).thenReturn(columnHeaders + "\n" + "1^2^AB,C^2012-01-01 12:12:12^\n" + "2^3^(null)^2013-02-02 22:22:22.56789^9\n" + "4^4^0006^(null)^9\n" + "5^5^ABCD^(null)^9\n"); preDeployTime = new LocalDateTime(); csvStaticDataDeployer.deployArtifact(artifact); results = this.jdbc.query(conn, "select * from " + schema + "." + table + " order by AID", new MapListHandler()); assertEquals(4, results.size()); this.verifyRow(results.get(0), 1, 2, "AB,C", new LocalDateTime("2012-01-01T12:12:12"), null, preDeployTime, false); this.verifyRow(results.get(1), 2, 3, null, new LocalDateTime("2013-02-02T22:22:22.56789"), 9, preDeployTime, true); this.verifyRow(results.get(2), 4, 4, "0006", null, 9, preDeployTime, false); this.verifyRow(results.get(3), 5, 5, "ABCD", null, 9, preDeployTime, true); }
From source file:edu.pitt.dbmi.ipm.service.storage.PostgreStorage.java
@Override public JSONObject getJSONResult(String SPARQL_URL, String query) throws QueryException { JSONObject jsonObj = null;//from ww w .jav a 2s . c o m List<Map<String, Object>> listOfMaps = null; Connection connection = null; try { connection = getConnection(); QueryRunner queryRunner = new QueryRunner(); listOfMaps = queryRunner.query(connection, query, new MapListHandler()); // convert list of maps to listof maps of maps List<Map<String, Map<String, Object>>> newList = new LinkedList<Map<String, Map<String, Object>>>(); Map<String, Map<String, Object>> outerMap = null; Map<String, Object> innerMap = null; List<String> headerList = new LinkedList<String>(); Object value = ""; String key = null; for (Map<String, Object> oldMap : listOfMaps) { outerMap = new HashMap<String, Map<String, Object>>(); for (Map.Entry<String, Object> entry : oldMap.entrySet()) { key = entry.getKey(); innerMap = new HashMap<String, Object>(); value = entry.getValue(); if (value != null) { innerMap.put("value", value); outerMap.put(key, innerMap); } if (!headerList.contains(key)) headerList.add(key); } newList.add(outerMap); } String newJson = "{\"results\":{\"bindings\":" + new Gson().toJson(newList) + "},\"head\":{\"vars\":" + new Gson().toJson(headerList) + "}}"; listOfMaps.clear(); listOfMaps = null; newList.clear(); newList = null; jsonObj = new JSONObject(newJson); } catch (SQLException se) { throw new QueryException( "PostgreStorage getJSONResult: Couldn't query the database." + se.getMessage()); } catch (JSONException e) { throw new QueryException("PostgreStorage getJSONResult: " + e.getMessage()); } catch (Exception e) { throw new QueryException("PostgreStorage getJSONResult: " + e.getMessage()); } finally { DbUtils.closeQuietly(connection); } return jsonObj; }
From source file:com.gs.obevo.dbmetadata.impl.dialects.SybaseAseMetadataDialect.java
@Override public ImmutableCollection<ExtraRerunnableInfo> searchExtraViewInfo(DaSchema schema, String tableName, Connection conn) throws SQLException { String query = String/* ww w .j a va2 s . c o m*/ .format("select obj.name name, com.number number, colid2 colid2, colid colid, text text\n" + "from %1$s..syscomments com\n" + ", %1$s..sysobjects obj\n" + "where com.id = obj.id\n" + "and com.texttype = 0\n" + "and obj.type in ('V')\n" + "order by com.id, number, colid2, colid\n", schema.getName()); QueryRunner qr = new QueryRunner(); // using queryRunner so that we can reuse the connection ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(qr.query(conn, query, new MapListHandler())) .toImmutable(); ImmutableList<ExtraRerunnableInfo> viewInfos = maps .collect(new Function<Map<String, Object>, ExtraRerunnableInfo>() { @Override public ExtraRerunnableInfo valueOf(Map<String, Object> object) { return new ExtraRerunnableInfo((String) object.get("name"), null, (String) object.get("text"), null, ((Integer) object.get("colid2")).intValue(), ((Integer) object.get("colid")).intValue()); } }); return viewInfos.groupBy(ExtraRerunnableInfo.TO_NAME).multiValuesView() .collect(new Function<RichIterable<ExtraRerunnableInfo>, ExtraRerunnableInfo>() { @Override public ExtraRerunnableInfo valueOf(RichIterable<ExtraRerunnableInfo> objectInfos) { MutableList<ExtraRerunnableInfo> sortedInfos = objectInfos.toSortedList(Comparators .fromFunctions(ExtraRerunnableInfo.TO_ORDER2, ExtraRerunnableInfo.TO_ORDER1)); StringBuilder definitionString = sortedInfos.injectInto(new StringBuilder(), new Function2<StringBuilder, ExtraRerunnableInfo, StringBuilder>() { @Override public StringBuilder value(StringBuilder sb, ExtraRerunnableInfo rerunnableInfo) { return sb.append(rerunnableInfo.getDefinition()); } }); return new ExtraRerunnableInfo(sortedInfos.get(0).getName(), null, definitionString.toString()); } }).toList().toImmutable(); }
From source file:com.gs.obevo.dbmetadata.impl.dialects.MsSqlMetadataDialect.java
@Override public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException { QueryRunner query = new QueryRunner(); ImmutableList<Map<String, Object>> maps = ListAdapter .adapt(query.query(conn,/*from w w w .j a v a2s . c o m*/ "SELECT DOMAIN_NAME as USER_TYPE_NAME " + "FROM INFORMATION_SCHEMA.DOMAINS " + "WHERE DOMAIN_CATALOG = '" + schema.getName() + "'", new MapListHandler())) .toImmutable(); return maps.collect(new Function<Map<String, Object>, DaUserType>() { @Override public DaUserType valueOf(Map<String, Object> map) { return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema); } }); }
From source file:dbutils.DbUtilsTemplate.java
/** * ??Map?Map?List//from www . j a v a 2s . c o m * * @param sql sql? * @param params ? * @return */ public List<Map<String, Object>> find(String sql, Object[] params) { queryRunner = new QueryRunner(); List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Connection conn = null; try { conn = dataSource.getConnection(); if (params == null) { list = queryRunner.query(conn, sql, new MapListHandler()); } else { list = queryRunner.query(conn, sql, new MapListHandler(), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } return list; }
From source file:io.seqware.pipeline.plugins.FileProvenanceQueryTool.java
@Override public ReturnValue do_run() { Path randomTempDirectory = null; Path originalReport = null;/*from ww w. jav a 2 s. c o m*/ Path bulkImportFile = null; try { if (options.has(this.inFileSpec)) { originalReport = FileSystems.getDefault().getPath(options.valueOf(inFileSpec)); } else { originalReport = populateOriginalReportFromWS(); } List<String> headers; List<Boolean> numericDataType; // construct column name and datatypes // convert file provenance report into derby bulk load format try (BufferedReader originalReader = Files.newBufferedReader(originalReport, Charset.defaultCharset())) { // construct column name and datatypes String headerLine = originalReader.readLine(); headers = Lists.newArrayList(); numericDataType = Lists.newArrayList(); for (String column : headerLine.split("\t")) { String editedColumnName = StringUtils.lowerCase(column).replaceAll(" ", "_").replaceAll("-", "_"); headers.add(editedColumnName); // note that Parent Sample SWID is a silly column that has colons in it numericDataType.add( !editedColumnName.contains("parent_sample") && (editedColumnName.contains("swid"))); } bulkImportFile = Files.createTempFile("import", "txt"); try (BufferedWriter derbyImportWriter = Files.newBufferedWriter(bulkImportFile, Charset.defaultCharset())) { Log.debug("Bulk import file written to " + bulkImportFile.toString()); while (originalReader.ready()) { String line = originalReader.readLine(); StringBuilder builder = new StringBuilder(); int i = 0; for (String colValue : line.split("\t")) { if (i != 0) { builder.append("\t"); } if (numericDataType.get(i)) { if (!colValue.trim().isEmpty()) { builder.append(colValue); } } else { // assume that this is a string // need to double quotes to preserve them, see // https://db.apache.org/derby/docs/10.4/tools/ctoolsimportdefaultformat.html builder.append("\"").append(colValue.replaceAll("\"", "\"\"")).append("\""); } i++; } derbyImportWriter.write(builder.toString()); derbyImportWriter.newLine(); } } } randomTempDirectory = Files.createTempDirectory("randomFileProvenanceQueryDir"); // try using in-memory for better performance String protocol = "jdbc:h2:"; if (options.has(useH2InMemorySpec)) { protocol = protocol + "mem:"; } Connection connection = spinUpEmbeddedDB(randomTempDirectory, "org.h2.Driver", protocol); // drop table if it exists already (running in IDE?) Statement dropTableStatement = null; try { dropTableStatement = connection.createStatement(); dropTableStatement.executeUpdate("DROP TABLE " + TABLE_NAME); } catch (SQLException e) { Log.debug("Report table didn't exist (normal)"); } finally { DbUtils.closeQuietly(dropTableStatement); } // create table creation query StringBuilder tableCreateBuilder = new StringBuilder(); // tableCreateBuilder tableCreateBuilder.append("CREATE TABLE " + TABLE_NAME + " ("); for (int i = 0; i < headers.size(); i++) { if (i != 0) { tableCreateBuilder.append(","); } if (numericDataType.get(i)) { tableCreateBuilder.append(headers.get(i)).append(" INT "); } else { tableCreateBuilder.append(headers.get(i)).append(" VARCHAR "); } } tableCreateBuilder.append(")"); bulkImportH2(tableCreateBuilder, connection, bulkImportFile); // query the database and dump the results to try (BufferedWriter outputWriter = Files.newBufferedWriter(Paths.get(options.valueOf(outFileSpec)), Charset.defaultCharset(), StandardOpenOption.CREATE, StandardOpenOption.TRUNCATE_EXISTING)) { // query the database and dump the results to QueryRunner runner = new QueryRunner(); List<Map<String, Object>> mapList = runner.query(connection, options.valueOf(querySpec), new MapListHandler()); // output header if (mapList.isEmpty()) { Log.fatal("Query had no results"); System.exit(-1); } StringBuilder builder = new StringBuilder(); for (String columnName : mapList.get(0).keySet()) { if (builder.length() != 0) { builder.append("\t"); } builder.append(StringUtils.lowerCase(columnName)); } outputWriter.append(builder); outputWriter.newLine(); for (Map<String, Object> rowMap : mapList) { StringBuilder rowBuilder = new StringBuilder(); for (Entry<String, Object> e : rowMap.entrySet()) { if (rowBuilder.length() != 0) { rowBuilder.append("\t"); } rowBuilder.append(e.getValue()); } outputWriter.append(rowBuilder); outputWriter.newLine(); } } DbUtils.closeQuietly(connection); Log.stdoutWithTime("Wrote output to " + options.valueOf(outFileSpec)); return new ReturnValue(); } catch (IOException | SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException ex) { throw new RuntimeException(ex); } finally { if (originalReport != null) { FileUtils.deleteQuietly(originalReport.toFile()); } if (bulkImportFile != null) { FileUtils.deleteQuietly(bulkImportFile.toFile()); } if (randomTempDirectory != null && randomTempDirectory.toFile().exists()) { FileUtils.deleteQuietly(randomTempDirectory.toFile()); } } }