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

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

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.support.rowset.*
  5. SqlRowSet
  6. getMetaData

Introduction

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

Prototype

SqlRowSetMetaData getMetaData();

Source Link

Document

Retrieve the meta-data, i.e.

Usage

From source file:com.krawler.crm.reportBuilder.bizservice.ReportBuilderServiceImpl.java

public List getIdsList(SqlRowSet rs) {
    List<Object> ll = new ArrayList<Object>();
    List<String> idList = new ArrayList<String>();
    Boolean productColFlag = false;
    try {/*from   w w  w  .j a v  a2s.  c  om*/
        SqlRowSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnLabel = rsmd.getColumnLabel(i);
                if (columnLabel.equals(Constants.Crm_leadid) || columnLabel.equals(Constants.Crm_productid)
                        || columnLabel.equals(Constants.Crm_accountid)
                        || columnLabel.equals(Constants.Crm_contactid)
                        || columnLabel.equals(Constants.Crm_caseid)
                        || columnLabel.equals(Constants.Crm_opportunityid)) {
                    if (rs.getObject(i) != null) {
                        idList.add(rs.getObject(i).toString());
                    }
                } else if (columnLabel.equals(Constants.Crm_lead_product_key)
                        || columnLabel.equals(Constants.Crm_opportunity_product_key)
                        || columnLabel.equals(Constants.Crm_account_product_key)
                        || columnLabel.equals(Constants.Crm_case_product_key)) {
                    productColFlag = true;
                }
            }
        }
        rs.beforeFirst();
        ll.add(idList);
        ll.add(productColFlag);
    } catch (Exception ex) {
        LOGGER.warn(ex.getMessage(), ex);
    }
    return ll;
}

From source file:com.krawler.crm.reportBuilder.bizservice.ReportBuilderServiceImpl.java

public JSONObject getReportJSON(int moduleId, SqlRowSet rs, HashMap<String, DefaultMasterItem> defaultMasterMap,
        HashMap<String, FieldComboData> customComboMap, Map<String, List<CrmProduct>> productsMap,
        Map ownersMap, HashMap<String, String> dataIndexReftableMap, boolean detailFlag,
        Boolean productColFlag) {
    JSONObject jb = new JSONObject();
    try {/*  www.  j a  v a 2s  .  c o m*/
        SqlRowSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            String primaryKeyVal = "";
            JSONObject jobj = new JSONObject();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnLabel = rsmd.getColumnLabel(i);
                //                    boolean linkFlag = !detailFlag && customReportObj.isGroupflag() &&  columnLabel.equals(customReportObj.getGroupfield());
                if (rs.getObject(i) != null) {
                    if (columnLabel.equals(Constants.Crm_leadid) || columnLabel.equals(Constants.Crm_contactid)
                            || columnLabel.equals(Constants.Crm_accountid)
                            || columnLabel.equals(Constants.Crm_caseid)
                            || columnLabel.equals(Constants.Crm_opportunityid)
                            || columnLabel.equals(Constants.Crm_productid)) {
                        primaryKeyVal = rs.getObject(i).toString();
                    }

                    if (dataIndexReftableMap.containsKey(columnLabel)
                            && dataIndexReftableMap.get(columnLabel).equals("defaultmasteritem")) {
                        if (defaultMasterMap.containsKey(rs.getObject(i))) {
                            jobj.put(columnLabel, defaultMasterMap.get(rs.getObject(i)).getValue());
                            jobj.put(columnLabel + "_id", rs.getObject(i));
                        } else {
                            jobj.put(columnLabel, "");
                        }
                    } else if (dataIndexReftableMap.containsKey(columnLabel)
                            && dataIndexReftableMap.get(columnLabel).equals("users")) {
                        User userObj = (User) kwlCommonTablesDAOObj
                                .getClassObject("com.krawler.common.admin.User", rs.getObject(i).toString());
                        if (userObj != null) {
                            jobj.put(columnLabel, userObj.getFullname());
                            jobj.put(columnLabel + "_id", rs.getObject(i));
                        } else {
                            jobj.put(columnLabel, "(Blank)");
                        }
                    } else if (dataIndexReftableMap.containsKey(columnLabel)
                            && dataIndexReftableMap.get(columnLabel).equals(Constants.Crm_account)) {
                        CrmAccount accObj = (CrmAccount) kwlCommonTablesDAOObj
                                .getClassObject(Constants.Crm_account_classpath, rs.getObject(i).toString());
                        if (accObj != null) {
                            jobj.put(columnLabel, accObj.getAccountname());
                            jobj.put(columnLabel + "_id", rs.getObject(i));
                        } else {
                            jobj.put(columnLabel, rs.getObject(i));
                        }
                    } else if (dataIndexReftableMap.containsKey(columnLabel)
                            && dataIndexReftableMap.get(columnLabel).equals("fieldcombodata")) {
                        String[] comboIds = rs.getObject(i).toString().split(",");
                        String comboValue = " ";
                        String comboValueId = "";
                        for (int cnt = 0; cnt < comboIds.length; cnt++) {
                            String comboid = comboIds[cnt];
                            if (customComboMap.containsKey(comboid)) {
                                comboValue += customComboMap.get(comboid).getValue() + ",";
                                comboValueId += customComboMap.get(comboid).getId() + ",";
                            }
                        }
                        jobj.put(columnLabel, comboValue.substring(0, comboValue.length() - 1));
                        jobj.put(columnLabel + "_id",
                                (comboValueId.length() > 0
                                        ? comboValueId.substring(0, comboValueId.length() - 1)
                                        : comboValueId));
                    } else {
                        jobj.put(columnLabel, rs.getObject(i));
                    }
                } else {
                    jobj.put(columnLabel, "");
                }
            }
            if (productColFlag && productsMap.containsKey(primaryKeyVal)) {
                List<CrmProduct> crmProducts = productsMap.get(primaryKeyVal);
                String productNames = "";
                for (CrmProduct product : crmProducts) {
                    productNames += product.getProductname() + ", ";
                }
                String key = "";
                productNames = productNames.substring(0, productNames.lastIndexOf(","));
                switch (moduleId) {
                case 1:
                    key = Constants.Crm_account_product_key;
                    break;
                case 2:
                    key = Constants.Crm_lead_product_key;
                    break;
                case 3:
                    key = Constants.Crm_case_product_key;
                    break;
                case 5:
                    key = Constants.Crm_opportunity_product_key;
                    break;
                }
                jobj.put(key, productNames);
            }
            jb.append("data", jobj);
        }
    } catch (Exception ex) {
        LOGGER.warn(ex.getMessage(), ex);
    }
    return jb;
}

From source file:com.gst.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<>();

    final SqlRowSetMetaData rsmd = rs.getMetaData();

    while (rs.next()) {
        final List<String> columnValues = new ArrayList<>();
        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  .j  a v a2 s.c  om*/

        final ResultsetRowData resultsetDataRow = ResultsetRowData.create(columnValues);
        resultsetDataRows.add(resultsetDataRow);
    }

    return resultsetDataRows;
}

From source file:ome.testing.OMEData.java

/**
 * returns a list of results from the sql statement. if there is more than
 * one column in the result set, a map from column name to Object is
 * returned, else the Object itself.//from  w  w  w .  ja  v a2  s  .  co m
 * 
 * @param sql
 * @return
 */
List runSql(String sql) {
    JdbcTemplate jt = new JdbcTemplate(ds);
    SqlRowSet rows = jt.queryForRowSet(sql);
    List result = new ArrayList();
    while (rows.next()) {
        SqlRowSetMetaData meta = rows.getMetaData();
        int count = meta.getColumnCount();
        if (count > 1) {
            Map cols = new HashMap();
            String[] names = meta.getColumnNames();
            for (int i = 0; i < names.length; i++) {
                cols.put(names[i], rows.getObject(names[i]));
            }
            result.add(cols);
        } else {
            result.add(rows.getObject(1));
        }
    }
    log.debug("SQL:" + sql + "\n\nResult:" + result);
    return result;
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.CsvFileHelper.java

/**
 * Creates a new CSV file/*w  w  w .j av a 2 s.  c  o m*/
 * 
 * @param sqlRowSet
 * @param file
 */
public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {
        // create a new CSVWriter object
        final CSVWriter csvWriter = new CSVWriter(
                new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), ENCODING)), SEPARATOR,
                QUOTE_CHARACTER, ESCAPE_CHARACTER, DataExportApiConstants.WINDOWS_END_OF_LINE_CHARACTER);
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();
        final String[] headers = new String[columnCount];
        final List<String[]> data = new ArrayList<>();

        int columnIndex = 0;

        for (int i = 1; i <= columnCount; i++) {
            // get the column label of the dataset
            String columnLabel = WordUtils.capitalize(sqlRowSetMetaData.getColumnLabel(i));

            // add column label to headers array
            headers[columnIndex++] = columnLabel;
        }

        while (sqlRowSet.next()) {
            // create a new empty string array of length "columnCount"
            final String[] rowData = new String[columnCount];

            int rowIndex = 0;

            for (int i = 1; i <= columnCount; i++) {
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                rowData[rowIndex++] = StringEscapeUtils.escapeCsv(columnValue);
            }

            // add the row data to the array list of row data
            data.add(rowData);
        }

        // write file headers to file
        csvWriter.writeNext(headers);

        // write file data to file
        csvWriter.writeAll(data);

        // close stream writer
        csvWriter.close();
    }

    catch (Exception exception) {
        logger.error(exception.getMessage(), exception);
    }
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.XlsFileHelper.java

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {/*from   www  .  ja v  a  2s. c o m*/
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:
                        // TINYINT(1) is also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (mysqlDataType.equals(MysqlDataType.TINYINT)
                                && sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {/*from  w  w w  .j  ava2  s.c o  m*/
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:

                        // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

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);
        }//w w  w  .j a v 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
 *///from  ww  w .ja  v a  2 s.  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;
}

From source file:org.sipfoundry.sipxconfig.acd.stats.AcdHistoricalStatsImpl.java

public List<String> getReportFields(String reportName) {
    AcdHistoricalReport report = (AcdHistoricalReport) m_factory.getBean(reportName);

    Object[] sqlParameters = new Object[] { new Date(0), new Date(0), "" };
    SqlRowSet emptySet = getJdbcTemplate().queryForRowSet(report.getQuery() + " limit 0", sqlParameters);
    SqlRowSetMetaData meta = emptySet.getMetaData();
    List<String> names = Arrays.asList(meta.getColumnNames());
    return names;
}