List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet next
boolean next() throws InvalidResultSetAccessException;
From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java
@Override public Set<String> loadBlackList(int companyID) throws Exception { final JdbcTemplate jdbcTemplate = createJdbcTemplate(); SqlRowSet rset = null; Set<String> blacklist = new HashSet<String>(); try {//from ww w . j a v a 2 s.c o m if (AgnUtils.isOracleDB()) { // ignore cust_ban_tbl so that global blacklisted addresses can be imported to local blacklist rset = jdbcTemplate.queryForRowSet("SELECT email FROM cust" + companyID + "_ban_tbl"); } else { rset = jdbcTemplate.queryForRowSet("SELECT email FROM cust_ban_tbl"); } while (rset.next()) { blacklist.add(rset.getString(1).toLowerCase()); } } catch (Exception e) { logger.error("loadBlacklist: " + e.getMessage(), e); throw new Exception(e.getMessage()); } return blacklist; }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
@Override public Set<String> loadBlackList(int companyID) throws Exception { JdbcTemplate jdbcTemplate = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource")); SqlRowSet rset = null; Object[] params = new Object[] { new Integer(companyID) }; Set<String> blacklist = new HashSet<String>(); try {//from w w w . j a v a2 s . c o m rset = jdbcTemplate.queryForRowSet("SELECT email FROM cust_ban_tbl WHERE company_id=? OR company_id=0", params); while (rset.next()) { blacklist.add(rset.getString(1).toLowerCase()); } } catch (Exception e) { logger.error("loadBlacklist (company ID: " + companyID + ")", e); throw e; } return blacklist; }
From source file:org.agnitas.web.ImportWizardForm.java
/** * Loads blacklist./* w w w. j av a2s .c o m*/ */ protected void loadBlacklist(int companyID, JdbcTemplate jdbc) throws Exception { SqlRowSet rset = null; String blackList = null; Object[] params = new Object[] { new Integer(companyID) }; this.blacklist = new HashSet(); try { blackList = "SELECT email FROM cust_ban_tbl WHERE company_id=? OR company_id=0"; rset = jdbc.queryForRowSet(blackList, params); while (rset.next()) { this.blacklist.add(rset.getString(1).toLowerCase()); } } catch (Exception e) { AgnUtils.logger().error("loadBlacklist: " + e); throw new Exception(e.getMessage()); } }
From source file:org.apache.fineract.infrastructure.dataexport.helper.CsvFileHelper.java
/** * Creates a new CSV file//www.j a va 2 s .com * * @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 {// www. jav 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.apache.fineract.infrastructure.dataqueries.service.ReadReportingServiceImpl.java
private String getSql(final String name, final String type) { final String inputSql = "select " + type + "_sql as the_sql from stretchy_" + type + " where " + type + "_name = '" + name + "'"; final String inputSqlWrapped = this.genericDataService.wrapSQL(inputSql); final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(inputSqlWrapped); if (rs.next()) { return rs.getString("the_sql"); }//from w w w . j ava 2s . co m throw new ReportNotFoundException(inputSql); }
From source file:org.apache.fineract.infrastructure.dataqueries.service.ReadReportingServiceImpl.java
@Override public String getReportType(final String reportName) { final String sql = "SELECT ifnull(report_type,'') as report_type FROM `stretchy_report` where report_name = '" + reportName + "'"; final String sqlWrapped = this.genericDataService.wrapSQL(sql); final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sqlWrapped); if (rs.next()) { return rs.getString("report_type"); }// w w w .java 2 s . co m throw new ReportNotFoundException(sql); }
From source file:org.apereo.lap.services.output.handlers.CSVOutputHandler.java
@Override public OutputResult writeOutput(Output output) { OutputResult result = new OutputResult(output); // make sure we can write the CSV File csv = configuration.getOutputDirectory().resolve(output.filename).toFile(); boolean created; try {//from w w w. ja v a 2 s . c om created = csv.createNewFile(); if (logger.isDebugEnabled()) logger.debug("CSV file created (" + created + "): " + csv.getAbsolutePath()); } catch (IOException e) { throw new IllegalStateException("Exception creating CSV file: " + csv.getAbsolutePath() + ": " + e, e); } if (!created) { // created file is going to be a writeable file so no check needed if (csv.isFile() && csv.canRead() && csv.canWrite()) { // file exists and we can write to it if (logger.isDebugEnabled()) logger.debug("CSV file is writeable: " + csv.getAbsolutePath()); } else { throw new IllegalStateException("Cannot write to the CSV file: " + csv.getAbsolutePath()); } } // make sure we can read from the temp data source try { int rows = storage.getTempJdbcTemplate().queryForObject(output.makeTempDBCheckSQL(), Integer.class); logger.info( "Preparing to output " + rows + " from temp table " + output.from + " to " + output.filename); } catch (Exception e) { throw new RuntimeException( "Failure while trying to count the output data rows: " + output.makeTempDBCheckSQL()); } Map<String, String> sourceToHeaderMap = output.makeSourceTargetMap(); String selectSQL = output.makeTempDBSelectSQL(); // fetch the data to write to CSV SqlRowSet rowSet; try { // for really large data we probably need to use http://docs.spring.io/spring/docs/3.0.x/api/org/springframework/jdbc/core/RowCallbackHandler.html rowSet = storage.getTempJdbcTemplate().queryForRowSet(selectSQL); } catch (Exception e) { throw new RuntimeException("Failure while trying to retrieve the output data set: " + selectSQL); } // write data to the CSV file int lines = 0; PrintWriter pw = null; try { pw = new PrintWriter(new BufferedWriter(new FileWriter(csv, true))); CSVWriter writer = new CSVWriter(pw); // write out the header writer.writeNext(sourceToHeaderMap.values().toArray(new String[sourceToHeaderMap.size()])); // write out the rows while (rowSet.next()) { String[] rowVals = new String[sourceToHeaderMap.size()]; for (int i = 0; i < sourceToHeaderMap.size(); i++) { rowVals[i] = (rowSet.wasNull() ? null : rowSet.getString(i + 1)); } writer.writeNext(rowVals); } IOUtils.closeQuietly(writer); } catch (Exception e) { throw new RuntimeException("Failure writing output to CSV (" + csv.getAbsolutePath() + "): " + e, e); } finally { IOUtils.closeQuietly(pw); } result.done(lines, null); return result; }
From source file:org.apereo.lap.services.output.handlers.SSPEarlyAlertOutputHandler.java
@Override public OutputResult writeOutput(Output output) { logger.debug(output.toString());/*from w ww .ja v a 2 s . co m*/ SSPConfigPersistentStorage sspConfigPersistentStorage = storageFactory.getSSPConfigPersistentStorage(); SSPConfig sspConfig = sspConfigPersistentStorage.get(); if (sspConfig == null) { throw new RuntimeException("No SSP Configuration"); } ClientCredentialsResourceDetails resourceDetails = new ClientCredentialsResourceDetails(); resourceDetails.setClientId(sspConfig.getKey()); resourceDetails.setClientSecret(sspConfig.getSecret()); String baseUrl = sspConfig.getUrl(); if (!baseUrl.endsWith("/")) { baseUrl = baseUrl.concat("/"); } resourceDetails.setAccessTokenUri(baseUrl + "ssp/api/1/oauth2/token"); DefaultOAuth2ClientContext clientContext = new DefaultOAuth2ClientContext(); OAuth2RestTemplate restTemplate = new OAuth2RestTemplate(resourceDetails, clientContext); MappingJackson2HttpMessageConverter converter = new MappingJackson2HttpMessageConverter(); converter.setSupportedMediaTypes( Arrays.asList(MediaType.APPLICATION_JSON, MediaType.valueOf("text/javascript"))); restTemplate.setMessageConverters(Arrays.<HttpMessageConverter<?>>asList(converter)); OutputResult result = new OutputResult(output); String selectSQL = output.makeTempDBSelectSQL(); SqlRowSet rowSet; try { rowSet = storage.getTempJdbcTemplate().queryForRowSet(selectSQL); } catch (Exception e) { throw new RuntimeException("Failure while trying to retrieve the output data set: " + selectSQL); } Map<String, Integer> riskMap = new HashMap<String, Integer>(); riskMap.put("NO RISK", 0); riskMap.put("LOW RISK", 1); riskMap.put("MEDIUM RISK", 2); riskMap.put("HIGH RISK", 3); Integer riskThreshold = riskMap.get(sspConfig.getRiskRule()); List<EarlyAlert> earlyAlertList = new ArrayList<SSPEarlyAlertOutputHandler.EarlyAlert>(); while (rowSet.next()) { if (!rowSet.wasNull()) { String student = rowSet.getString(1); String course = rowSet.getString(2); String risk = rowSet.getString(3); Integer riskScore = riskMap.get(risk); if (riskScore >= riskThreshold) { EarlyAlert earlyAlert = new EarlyAlert(course, student, "Automated early alert due to risk score above acceptable limit", risk, null); earlyAlertList.add(earlyAlert); } logger.debug(String.format("student: %s, course: %s, risk:%s", student, course, risk)); } } if (earlyAlertList.size() > 0) { EarlyAlertMessage message = new EarlyAlertMessage("test.com", "test", earlyAlertList); restTemplate.postForLocation(baseUrl + "ssp/api/1/bulkEarlyAlerts", message); } return result; }
From source file:org.geoserver.jdbcconfig.internal.DbMappings.java
private BiMap<Integer, Class<?>> loadTypes(NamedParameterJdbcOperations template) { String sql = "select oid, typename from type"; SqlRowSet rowSet = template.queryForRowSet(sql, params("", "")); BiMap<Integer, Class<?>> types = HashBiMap.create(); if (rowSet.first()) { do {/*from w ww . jav a2s . c om*/ Number oid = (Number) rowSet.getObject(1); String typeName = rowSet.getString(2); Class<?> clazz; try { clazz = Class.forName(typeName); } catch (ClassNotFoundException e) { throw Throwables.propagate(e); } types.put(oid.intValue(), clazz); } while (rowSet.next()); } return types; }