Example usage for org.springframework.jdbc.support.rowset SqlRowSetMetaData getColumnName

List of usage examples for org.springframework.jdbc.support.rowset SqlRowSetMetaData getColumnName

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.support.rowset.*
  5. SqlRowSetMetaData
  6. getColumnName

Introduction

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

Prototype

String getColumnName(int columnIndex) throws InvalidResultSetAccessException;

Source Link

Document

Retrieve the column name for the indicated column.

Usage

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  www . ja va 2s. c om
    return name;
}

From source file:transaction.script.ProjectTrScript.java

/**
 * @param template//  w w w. j  a  v a  2 s  . c o m
 * @param query
 * @param conditionsMapList
 * @return
 * @throws SQLException
 */
public static boolean query(JdbcTemplate template, String query, List<Map<String, Object>> conditionsMapList)
        throws SQLException {
    logger.info("Query to execute is: " + query);

    SqlRowSet set = template.queryForRowSet(query);
    boolean result = true;

    SqlRowSetMetaData mdata = set.getMetaData();
    int columnAmount = mdata.getColumnCount();
    logger.info("Columns: " + columnAmount);
    logger.info("Map size: " + conditionsMapList.size());

    //TODO
    if (set.first()) {
        set.last();
        int rowNum = set.getRow();
        result = (rowNum == conditionsMapList.size());
        set.beforeFirst();
    } else {
        if (!conditionsMapList.get(0).isEmpty()) {
            result = false;
        }
    }

    logger.info("Two maps comparison result is " + result);

    if (result) {
        while (set.next()) {
            int rowNum = set.getRow();

            Map<String, Object> map = conditionsMapList.get(rowNum - 1);

            for (int i = 1; i <= columnAmount; i++) {
                result &= map.containsKey(mdata.getColumnName(i))
                        && map.get(mdata.getColumnName(i)).toString().equals(set.getObject(i).toString());
            }
        }
    }
    return result;
}

From source file:org.restsql.core.impl.postgresql.PostgreSqlSqlResourceMetaData.java

/**
 * Retrieves actual column name from result set meta data. Hook method for
 * buildTablesAndColumns() allows database-specific overrides.
 *//*from  w  w w.j a va  2 s  .  c  o m*/
@Override
protected String getColumnName(final SqlResourceDefinition definition,
        final SqlRowSetMetaData resultSetMetaData, final int colNumber) {
    // return ((PGResultSetMetaData)
    // resultSetMetaData).getBaseColumnName(colNumber);
    return resultSetMetaData.getColumnName(colNumber);
}

From source file:com.simplymeasured.prognosticator.HiveQueryCursorImplTest.java

License:asdf

@Test
public void testGet() {
    // build up a result inside the mock, test it.

    // column 1 = string, named STR
    // column 2 = long, named LNG
    // column 3 = Map, named MAP
    // column 4 = Array, named LIST
    // column 5 = Struct (serialized to a Java Map), named STRUCT

    SqlRowSetMetaData metadata = mock(SqlRowSetMetaData.class);

    when(metadata.getColumnCount()).thenReturn(5);

    when(metadata.getColumnName(1)).thenReturn("STR");
    when(metadata.getColumnTypeName(1)).thenReturn("STRING");

    when(metadata.getColumnName(2)).thenReturn("LNG");
    when(metadata.getColumnTypeName(2)).thenReturn("BIGINT");

    when(metadata.getColumnName(3)).thenReturn("MAP");
    when(metadata.getColumnTypeName(3)).thenReturn("MAP");

    when(metadata.getColumnName(4)).thenReturn("LIST");
    when(metadata.getColumnTypeName(4)).thenReturn("ARRAY");

    when(metadata.getColumnName(5)).thenReturn("STRUCT");
    when(metadata.getColumnTypeName(5)).thenReturn("STRUCT");

    when(rowSet.getMetaData()).thenReturn(metadata);

    when(rowSet.getString(1)).thenReturn("My string");
    when(rowSet.getObject(2)).thenReturn(123L);
    when(rowSet.getString(3)).thenReturn("{\"foo\":\"str1\", \"bar\":\"str2\"}");
    when(rowSet.getString(4)).thenReturn("[1, 2, 3]");
    when(rowSet.getString(5)).thenReturn("{\"struct1\": \"asdf\", \"struct2\": 1234 }");

    Map<String, Object> result = cursor.get();

    Assert.assertNotNull(result);/*from  w  w w .j  a  va 2s. c  om*/
    Assert.assertFalse(result.isEmpty());

    Map<String, Object> expected = new HashMap<String, Object>() {
        {
            put("STR", "My string");
            put("LNG", 123L);
            put("MAP", new HashMap<String, String>() {
                {
                    put("foo", "str1");
                    put("bar", "str2");
                }
            });
            put("LIST", new ArrayList<Integer>() {
                {
                    add(1);
                    add(2);
                    add(3);
                }
            });
            put("STRUCT", new HashMap<String, Object>() {
                {
                    put("struct1", "asdf");
                    put("struct2", 1234);
                }
            });
        }
    };

    Assert.assertEquals(expected, result);
}

From source file:org.restsql.core.impl.AbstractSqlResourceMetaData.java

/**
 * Retrieves actual column name from result set meta data. Hook method for
 * buildTablesAndColumns() allows database-specific overrides.
 *///from w  w w.j a va2s.  c  o m
protected String getColumnName(final SqlResourceDefinition definition,
        final SqlRowSetMetaData resultSetMetaData, final int colNumber) {
    return resultSetMetaData.getColumnName(colNumber);
}

From source file:com.simplymeasured.prognosticator.HiveQueryCursorImpl.java

@Override
public Map<String, Object> get() {
    Map<String, Object> result = Maps.newHashMap();

    final SqlRowSetMetaData metadata = rowSet.getMetaData();

    for (int i = 1; i <= metadata.getColumnCount(); i++) {
        String columnTypeName = metadata.getColumnTypeName(i);

        final Object value;

        if ("array".equalsIgnoreCase(columnTypeName)) {
            value = parseJson(rowSet.getString(i), List.class);
        } else if ("map".equalsIgnoreCase(columnTypeName) || "struct".equalsIgnoreCase(columnTypeName)) {
            value = parseJson(rowSet.getString(i), Map.class);
        } else if ("string".equalsIgnoreCase(columnTypeName)) {
            value = HiveUtils.unescapeString(rowSet.getString(i));
        } else {//w  w  w . j  a  v a2 s . c  om
            value = rowSet.getObject(i);
        }

        result.put(metadata.getColumnName(i), value);
    }

    return result;
}

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);
        }//from www  .  j av  a  2 s . c o m

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

    return resultsetDataRows;
}

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

/**
 * Creates a new CSV file//from w  w  w .j  a  va 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. jav a 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) 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   ww w.  ja va 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), 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();
    }
}