List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet next
boolean next() throws InvalidResultSetAccessException;
From source file:org.kuali.kfs.vnd.batch.dataaccess.DebarredVendorDaoJdbc.java
@Override public List<DebarredVendorMatch> match() { String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'"; String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl"; String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl"; String where = " WHERE " + active; String eplsFields = "excl.VNDR_EXCL_ID, excl.VNDR_EXCL_LOAD_DT, excl.VNDR_EXCL_NM, excl.VNDR_EXCL_LN1_ADDR, excl.VNDR_EXCL_LN2_ADDR, excl.VNDR_EXCL_CTY_NM" + ", excl.VNDR_EXCL_ST_CD, excl.VNDR_EXCL_PRVN_NM, excl.VNDR_EXCL_ZIP_CD, excl.VNDR_EXCL_OTHR_NM, excl.VNDR_EXCL_DESC_TXT"; String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromName = " FROM pur_vndr_dtl_t dtl"; String name = filter("dtl.VNDR_NM", "., "); String eplsName = filter("excl.VNDR_EXCL_NM", "., "); String onName = " ON " + compare(name, eplsName, false); // use = to compare String sqlName = selectName + fromName + joinExcl + onName + where; String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromAlias = " FROM pur_vndr_alias_t als"; String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; String alias = filter("als.VNDR_ALIAS_NM", "., "); String eplsAlias = filter("excl.VNDR_EXCL_NM", "., "); String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where; String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID"; String fromAddr = " FROM pur_vndr_addr_t addr"; String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; ////from w ww .jav a 2 s .c om String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# "); String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# "); String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare // String city = filter("addr.VNDR_CTY_NM", "., "); String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., "); String cmpCity = compare(city, eplsCity, false); // use = to compare // String state = "upper(addr.VNDR_ST_CD)"; String eplsState = "upper(excl.VNDR_EXCL_ST_CD)"; String cmpState = compare(state, eplsState, false); // use = to compare // String zip = filter("addr.VNDR_ZIP_CD", "-"); String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-"); String cmpZip = compare(zip, eplsZip, false); // use = to compare String fullZip = "length(addr.VNDR_ZIP_CD) > 5"; // String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState; String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where; String max = ", MAX(VNDR_ADDR_GNRTD_ID)"; String selectFields = "VNDR_HDR_GNRTD_ID, VNDR_DTL_ASND_ID, VNDR_EXCL_ID, VNDR_EXCL_LOAD_DT, VNDR_EXCL_NM, VNDR_EXCL_LN1_ADDR, VNDR_EXCL_LN2_ADDR, VNDR_EXCL_CTY_NM" + ", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT"; String select = "SELECT " + selectFields + max; String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr; String from = " FROM (" + subqr + ")"; String group = " GROUP BY " + selectFields; String sql = select + from + group; List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>(); try { SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql); DebarredVendorMatch match; while (rs.next()) { match = new DebarredVendorMatch(); match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1))); match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2))); match.setLoadDate(rs.getDate(4)); match.setName(rs.getString(5)); match.setAddress1(rs.getString(6)); match.setAddress2(rs.getString(7)); match.setCity(rs.getString(8)); match.setState(rs.getString(9)); match.setProvince(rs.getString(10)); match.setZip(rs.getString(11)); match.setAliases(rs.getString(12)); match.setDescription(rs.getString(13)); match.setAddressGeneratedId(rs.getLong(14)); // didn't find a matched address, search for best one if (match.getAddressGeneratedId() == 0) { match.setAddressGeneratedId(getMatchAddressId(match)); } DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match); if (oldMatch == null) { // store the match only if an exact old match doesn't exist match.setConfirmStatusCode("U"); // status - Unprocessed matches.add(match); } } } catch (Exception e) { // if exception occurs, return empty results throw new RuntimeException(e); } return matches; }
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 ww w .ja v a 2 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
@Override public List<DatatableData> retrieveDatatableNames(final String appTable) { String andClause;//from w ww .j a va 2s .c o m if (appTable == null) { andClause = ""; } else { andClause = " and application_table_name = '" + appTable + "'"; } // PERMITTED datatables final String sql = "select application_table_name, registered_table_name" + " from x_registered_table " + " where exists" + " (select 'f'" + " from m_appuser_role ur " + " join m_role r on r.id = ur.role_id" + " left join m_role_permission rp on rp.role_id = r.id" + " left join m_permission p on p.id = rp.permission_id" + " where ur.appuser_id = " + this.context.authenticatedUser().getId() + " and (p.code in ('ALL_FUNCTIONS', 'ALL_FUNCTIONS_READ') or p.code = concat('READ_', registered_table_name))) " + andClause + " order by application_table_name, registered_table_name"; final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql); final List<DatatableData> datatables = new ArrayList<DatatableData>(); while (rs.next()) { final String appTableName = rs.getString("application_table_name"); final String registeredDatatableName = rs.getString("registered_table_name"); final List<ResultsetColumnHeaderData> columnHeaderData = this.genericDataService .fillResultsetColumnHeaders(registeredDatatableName); datatables.add(DatatableData.create(appTableName, registeredDatatableName, columnHeaderData)); } return datatables; }
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 .j ava 2 s. c o m 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 w w w . j a 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:repository.ClientsDAO.java
/** * Gets a count of how many clients in database, mainly for pagination * @return//from w w w .j a v a2s .co m */ public int getClientsCount() { String sql = "SELECT COUNT(ClientID) AS rowcount FROM clients"; SqlRowSet rs = template.queryForRowSet(sql); if (rs.next()) { return rs.getInt("rowcount"); } return 1; }
From source file:repository.UsersDAO.java
/** * Gets a count of all users, mainly for pagination * @return/*from w ww . ja va2s . com*/ */ public int getUsersCount() { String sql = "SELECT COUNT(UserID) AS rowcount FROM users"; SqlRowSet rs = template.queryForRowSet(sql); if (rs.next()) { return rs.getInt("rowcount"); } return 1; }