List of usage examples for org.springframework.jdbc.support.rowset SqlRowSetMetaData getColumnLabel
String getColumnLabel(int columnIndex) throws InvalidResultSetAccessException;
From source file:org.restsql.core.impl.SqlUtils.java
public static String lookupColumnName(SqlRowSetMetaData resultSetMetaData, int columnIndex) throws SQLException { String name = resultSetMetaData.getColumnLabel(columnIndex); if (name == null || name.length() < 1) { name = resultSetMetaData.getColumnName(columnIndex); }/*from ww w. ja v a2 s . co m*/ return name; }
From source file:org.owasp.proxy.http.dao.JdbcMessageDAOTest.java
private static void dump(String sql) { logger.fine("\n" + sql); SqlRowSet rs = dao.getJdbcTemplate().queryForRowSet(sql); try {// ww w. ja v a 2s . c o m SqlRowSetMetaData rsmd = rs.getMetaData(); int c = rsmd.getColumnCount(); StringBuffer buff = new StringBuffer(); for (int i = 1; i <= c; i++) { buff.append(rsmd.getColumnLabel(i)); buff.append(i == c ? "\n" : "\t"); } logger.fine(buff.toString()); buff.delete(0, buff.length()); while (rs.next()) { for (int i = 1; i <= c; i++) { buff.append(rs.getObject(i)); buff.append(i == c ? "\n" : "\t"); } logger.fine(buff.toString()); buff.delete(0, buff.length()); } logger.fine("================\n\n"); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.esa.infocontrol.data.jdbc.BaseDataJDBC.java
public static DataArrayWrapper getList(DataSource dataSource, String query, MapSqlParameterSource params) { LOG.debug("QUERY: {}", query); if (params != null) { LOG.debug("\tPARAMETERS: {}", params.getValues().toString()); }//w w w . j a va 2 s.c o m NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource); SqlRowSet rs = jdbcTemplate.queryForRowSet(query, params); SqlRowSetMetaData md = rs.getMetaData(); LOG.debug("\tCOLUMNS: {}", Arrays.toString(md.getColumnNames())); List<DataRow> dataList = new ArrayList<>(); ColumnMetaData[] columnMetaData = new ColumnMetaData[md.getColumnCount()]; for (int i = 1; i <= md.getColumnCount(); ++i) { columnMetaData[i - 1] = new ColumnMetaData(md.getColumnLabel(i), md.getColumnType(i)); } while (rs.next()) { DataRow row = new DataRow(md.getColumnCount()); for (int i = 1; i <= md.getColumnCount(); ++i) { row.add(rs.getString(i)); } dataList.add(row); } return new DataArrayWrapper(dataList, columnMetaData); }
From source file:com.univocity.app.data.Data.java
public void reloadData() { data.clear();//from w w w. j av a 2 s .c om SqlRowSet queryResult = database.getJdbcTemplate().queryForRowSet(selectScript); SqlRowSetMetaData metaData = queryResult.getMetaData(); columnNames = metaData.getColumnNames(); for (int i = 0; i < columnNames.length; i++) { String label = metaData.getColumnLabel(i + 1); if (label != null) { columnNames[i] = label; } } while (queryResult.next()) { Object[] row = new Object[columnNames.length]; for (int i = 0; i < row.length; i++) { row[i] = queryResult.getObject(i + 1); } data.add(row); } }
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 ww w .ja va 2 s .c o m 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 {//from w ww . jav a 2 s . co 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:org.restsql.core.impl.AbstractSqlResourceMetaData.java
/** * Builds table and column meta data./*w ww. ja va 2 s . c o m*/ * * @throws SqlResourceException */ @SuppressWarnings("fallthrough") private void buildTablesAndColumns(final SqlRowSet resultSet) throws SqlResourceException { final SqlRowSetMetaData resultSetMetaData = resultSet.getMetaData(); final int columnCount = resultSetMetaData.getColumnCount(); allReadColumns = new ArrayList<ColumnMetaData>(columnCount); parentReadColumns = new ArrayList<ColumnMetaData>(columnCount); childReadColumns = new ArrayList<ColumnMetaData>(columnCount); tableMap = new HashMap<String, TableMetaData>(DEFAULT_NUMBER_TABLES); tables = new ArrayList<TableMetaData>(DEFAULT_NUMBER_TABLES); childPlusExtTables = new ArrayList<TableMetaData>(DEFAULT_NUMBER_TABLES); parentPlusExtTables = new ArrayList<TableMetaData>(DEFAULT_NUMBER_TABLES); final HashSet<String> databases = new HashSet<String>(DEFAULT_NUMBER_DATABASES); for (int colNumber = 1; colNumber <= columnCount; colNumber++) { final String databaseName, qualifiedTableName, tableName; // boolean readOnly = isColumnReadOnly(resultSetMetaData, // colNumber); // if (readOnly) { databaseName = SqlResourceDefinitionUtils.getDefaultDatabase(definition); tableName = SqlResourceDefinitionUtils.getTable(definition, TableRole.Parent).getName(); qualifiedTableName = getQualifiedTableName(databaseName, tableName); final ColumnMetaDataImpl column = new ColumnMetaDataImpl(colNumber, databaseName, qualifiedTableName, tableName, getColumnName(definition, resultSetMetaData, colNumber), resultSetMetaData.getColumnLabel(colNumber), resultSetMetaData.getColumnTypeName(colNumber), resultSetMetaData.getColumnType(colNumber), true, this); TableMetaDataImpl table = (TableMetaDataImpl) tableMap.get(column.getQualifiedTableName()); if (table == null) { // Create table metadata object and add to special references final Table tableDef = SqlResourceDefinitionUtils.getTable(definition, column); if (tableDef == null) { throw new SqlResourceException("Definition requires table element for " + column.getTableName() + ", referenced by column " + column.getColumnLabel()); } table = new TableMetaDataImpl(tableName, qualifiedTableName, databaseName, TableRole.valueOf(tableDef.getRole())); tableMap.put(column.getQualifiedTableName(), table); tables.add(table); switch (table.getTableRole()) { case Parent: parentTable = table; if (tableDef.getAlias() != null) { table.setTableAlias(tableDef.getAlias()); } // fall through case ParentExtension: parentPlusExtTables.add(table); break; case Child: childTable = table; if (tableDef.getAlias() != null) { table.setTableAlias(tableDef.getAlias()); } // fall through case ChildExtension: childPlusExtTables.add(table); break; case Join: // unlikely to be in the select columns, but just in // case joinTable = table; joinList = new ArrayList<TableMetaData>(1); joinList.add(joinTable); break; default: // Unknown } } // Add column to the table table.addColumn(column); column.setTableRole(table.getTableRole()); // Add column to special column lists allReadColumns.add(column); switch (table.getTableRole()) { case Parent: case ParentExtension: parentReadColumns.add(column); break; case Child: case ChildExtension: childReadColumns.add(column); break; default: // Unknown } } // Determine number of databases multipleDatabases = databases.size() > 1; }
From source file:org.apache.fineract.infrastructure.dataexport.helper.CsvFileHelper.java
/** * Creates a new CSV file//from www. ja va2 s.c om * * @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 w w w . j a v a 2 s . co 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. ja va 2 s. c om*/ 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(); } }