List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet getString
String getString(String columnLabel) throws InvalidResultSetAccessException;
From source file:org.mifosplatform.infrastructure.dataqueries.service.ReadWriteNonCoreDataServiceImpl.java
private List<ResultsetRowData> fillDatatableResultSetDataRows(final String sql) { final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql); final List<ResultsetRowData> resultsetDataRows = new ArrayList<ResultsetRowData>(); final SqlRowSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { final List<String> columnValues = new ArrayList<String>(); for (int i = 0; i < rsmd.getColumnCount(); i++) { final String columnName = rsmd.getColumnName(i + 1); final String columnValue = rs.getString(columnName); columnValues.add(columnValue); }// ww w. jav a2 s .com final ResultsetRowData resultsetDataRow = ResultsetRowData.create(columnValues); resultsetDataRows.add(resultsetDataRow); } return resultsetDataRows; }
From source file:org.ohdsi.webapi.service.IRAnalysisService.java
/** * Exports the analysis definition and results * * @param id - the IR Analysis ID to export * @return Response containing binary stream of zipped data *///w w w. j a v a 2s .c o m @GET @Produces(MediaType.APPLICATION_JSON) @Path("/{id}/export") @Transactional public Response export(@PathParam("id") final int id) { Response response = null; HashMap<String, String> fileList = new HashMap<>(); HashMap<Integer, String> distTypeLookup = new HashMap<>(); distTypeLookup.put(1, "TAR"); distTypeLookup.put(2, "TTO"); try { IncidenceRateAnalysis analysis = this.irAnalysisRepository.findOne(id); Set<ExecutionInfo> executions = analysis.getExecutionInfoList(); fileList.put("analysisDefinition.json", analysis.getDetails().getExpression()); // squentially return reults of IR calculation. In Spring 1.4.2, we can utlilize @Async operations to do this in parallel. // store results in single CSV file ArrayList<String[]> summaryLines = new ArrayList<>(); ArrayList<String[]> strataLines = new ArrayList<>(); ArrayList<String[]> distLines = new ArrayList<>(); for (ExecutionInfo execution : executions) { Source source = execution.getSource(); String resultsTableQualifier = source.getTableQualifier(SourceDaimon.DaimonType.Results); // perform this query to CDM in an isolated transaction to avoid expensive JDBC transaction synchronization DefaultTransactionDefinition requresNewTx = new DefaultTransactionDefinition(); requresNewTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); TransactionStatus initStatus = this.getTransactionTemplateRequiresNew().getTransactionManager() .getTransaction(requresNewTx); // get the summary data List<AnalysisReport.Summary> summaryList = getAnalysisSummaryList(id, source); if (summaryLines.isEmpty()) { summaryLines.add("db_id#targetId#outcomeId#total#timeAtRisk#cases".split("#")); } for (AnalysisReport.Summary summary : summaryList) { summaryLines.add(new String[] { source.getSourceKey(), String.valueOf(summary.targetId), String.valueOf(summary.outcomeId), String.valueOf(summary.totalPersons), String.valueOf(summary.timeAtRisk), String.valueOf(summary.cases) }); } // get the strata results List<AnalysisReport.StrataStatistic> strataList = getStrataStatistics(id, source); if (strataLines.isEmpty()) { strataLines.add( "db_id#targetId#outcomeId#strata_id#strata_name#total#timeAtRisk#cases".split("#")); } for (AnalysisReport.StrataStatistic strata : strataList) { strataLines.add(new String[] { source.getSourceKey(), String.valueOf(strata.targetId), String.valueOf(strata.outcomeId), String.valueOf(strata.id), String.valueOf(strata.name), String.valueOf(strata.totalPersons), String.valueOf(strata.timeAtRisk), String.valueOf(strata.cases) }); } // get the distribution data String distQuery = String.format( "select '%s' as db_id, target_id, outcome_id, strata_sequence, dist_type, total, avg_value, std_dev, min_value, p10_value, p25_value, median_value, p75_value, p90_value, max_value from %s.ir_analysis_dist where analysis_id = %d", source.getSourceKey(), resultsTableQualifier, id); String translatedSql = SqlTranslate.translateSql(distQuery, "sql server", source.getSourceDialect(), SessionUtils.sessionId(), resultsTableQualifier); SqlRowSet rs = this.getSourceJdbcTemplate(source).queryForRowSet(translatedSql); this.getTransactionTemplateRequiresNew().getTransactionManager().commit(initStatus); if (distLines.isEmpty()) { distLines.add(rs.getMetaData().getColumnNames()); } while (rs.next()) { ArrayList<String> columns = new ArrayList<>(); for (int i = 1; i <= rs.getMetaData().getColumnNames().length; i++) { switch (rs.getMetaData().getColumnName(i)) { case "dist_type": columns.add(distTypeLookup.get(rs.getInt(i))); break; default: columns.add(rs.getString(i)); break; } } distLines.add(columns.toArray(new String[0])); } } // Write report lines to CSV StringWriter sw = null; CSVWriter csvWriter = null; sw = new StringWriter(); csvWriter = new CSVWriter(sw); csvWriter.writeAll(summaryLines); csvWriter.flush(); fileList.put("ir_summary.csv", sw.getBuffer().toString()); sw = new StringWriter(); csvWriter = new CSVWriter(sw); csvWriter.writeAll(strataLines); csvWriter.flush(); fileList.put("ir_strata.csv", sw.getBuffer().toString()); sw = new StringWriter(); csvWriter = new CSVWriter(sw); csvWriter.writeAll(distLines); csvWriter.flush(); fileList.put("ir_dist.csv", sw.getBuffer().toString()); // build zip output ByteArrayOutputStream baos = new ByteArrayOutputStream(); ZipOutputStream zos = new ZipOutputStream(baos); for (String fileName : fileList.keySet()) { ZipEntry resultsEntry = new ZipEntry(fileName); zos.putNextEntry(resultsEntry); zos.write(fileList.get(fileName).getBytes()); } zos.closeEntry(); zos.close(); baos.flush(); baos.close(); response = Response.ok(baos).type(MediaType.APPLICATION_OCTET_STREAM).header("Content-Disposition", String.format("attachment; filename=\"%s\"", "ir_analysis_" + id + ".zip")).build(); } catch (Exception ex) { throw new RuntimeException(ex); } return response; }