Example usage for org.springframework.jdbc.support.rowset SqlRowSet getString

List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet getString

Introduction

In this page you can find the example usage for org.springframework.jdbc.support.rowset SqlRowSet getString.

Prototype

String getString(String columnLabel) throws InvalidResultSetAccessException;

Source Link

Document

Retrieve the value of the indicated column in the current row as a String.

Usage

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;
}